Multiple Databases

Single connection (no TOML)

Without config/database.toml, Soli builds one connection named primary from the environment.

# SoliDB (default)
SOLIDB_HOST=http://localhost:6745
SOLIDB_DATABASE=myapp

# Or: whole app on Postgres document tables
# SOLI_DB_ADAPTER=postgres
# DATABASE_URL=postgres://user:pass@localhost:5432/myapp

config/database.toml

Loaded at soli serve / migrate / import after .env. Secrets stay in env via ${VAR} / ${VAR:-default} expansion.

default = "primary"

[connections.primary]
adapter = "solidb"
host = "${SOLIDB_HOST:-http://localhost:6745}"
database = "${SOLIDB_DATABASE:-default}"
username = "${SOLIDB_USERNAME:-}"
password = "${SOLIDB_PASSWORD:-}"

[connections.legacy]
adapter = "postgres"
url = "${LEGACY_DATABASE_URL}"
pool = 10

[connections.warehouse]
adapter = "mysql"
url = "${WAREHOUSE_DATABASE_URL}"
pool = 5

[connections.analytics]
adapter = "sqlite"
url = "sqlite://db/analytics.sqlite3"   # a path, not a server

Field reference

Per-model connection

class User < Model
  # uses default ("primary")
end

class LegacyOrder < Model
  connection "legacy"
end

class FactSale < Model
  connection "warehouse"
end

• String or symbol name; validated at class load against the registry
• STI subclasses inherit the parent connection unless redeclared
• CRUD and the query builder route through the active connection

SQL document backends

Postgres, MySQL, and SQLite connections store each collection as _key + doc (JSONB, JSON, or TEXT read with SQLite's json1 functions). Hash-style filters only — not raw SDBQL.

Import SoliDB → SQL

SOLI_DB_ADAPTER=postgres DATABASE_URL=postgres://…
SOLIDB_HOST=… SOLIDB_USERNAME=… SOLIDB_PASSWORD=…
soli db:import              # all non-_ collections
soli db:import posts users  # named only

SQLite specifics

The dedicated SQLite page covers URL forms, WAL, jobs, backups, and the numeric-affinity caveat. The short version: SQLite is a file, not a server — the shortest path from soli new to persistent data, and a good fit for single-node apps, embedded and desktop builds, CI, and tests.

URL forms

Every connection is opened in WAL mode with a 10-second busy timeout and foreign keys on.

What to know before you choose it

• One writer at a time. WAL lets readers run during a write, but writers serialize. A busy write path across many processes belongs on Postgres.
• No exact numeric type. A DECIMAL/NUMERIC column has NUMERIC affinity, not an exact type: SQLite converts the value to a REAL, so a stored 19.90 reads back as 19.9, and a value beyond f64's exact range loses precision on write. Postgres numeric and MySQL decimal keep the scale. Declare the column TEXT if exact decimal text matters.
• Backups are file copies — use .backup or VACUUM INTO, never a cp of a live WAL database.
• :memory: is one connection. The pool is forced to a single connection, because a second one would be a second, empty database.

Cross-connection rules

Column-aware models (existing databases)

The document backend above stores every collection as _key + doc, so it only reads tables Soli created. To point Soli at a database that already exists — a legacy app's schema, a warehouse table, anything with real columns — declare the physical table on the model.

class Order < Model
  connection "legacy"    # a postgres, mysql, or sqlite connection
  table "orders"         # bind to an existing table -> column mode
end

table "…" is what switches the model into column mode: the model reads and writes the table's real columns, and never creates or alters it.

The table can be one you already have, or one a migration builds: create_table("orders", { … }) declares real columns portably across Postgres, MySQL, and SQLite, and the types it emits are the ones introspection reads back — see Migrations → On the SQL adapters.

What Soli learns, and when

At boot, Soli introspects each declared table once and caches the result (information_schema on Postgres and MySQL, PRAGMA table_info on SQLite):

• every column, its type, and whether it is nullable;
• the primary key, detected automatically — including whether the database generates it (BIGSERIAL / IDENTITY / AUTO_INCREMENT), so inserts leave it to the database;
• whether created_at / updated_at exist — they are stamped only if they do.

Problems fail the boot with a message naming the connection and table: a missing table, a composite primary key (not supported yet), no primary key at all, or a solidb connection. Editing a model in --dev re-introspects; an ALTER TABLE while the server runs needs a restart.

Type mapping

• Exact numerics travel as text and read as Float, so a value beyond f64's exact range loses precision on read; writes keep their scale.
• Unsupported columns are skipped on read and error clearly if you filter or write them — they never silently corrupt a row.
• MySQL stores no offset: datetime/timestamp values are interpreted as UTC.
• SQLite enforces no types. It applies affinity to the declared type, so any column can hold any value. Soli reads the declared type to decide how to convert, and reads each value by what it actually is — a DATETIME holding a unix timestamp becomes RFC 3339, exactly like a stored text date.
• A SQLite INTEGER PRIMARY KEY is generated (it aliases the rowid, with or without AUTOINCREMENT), so inserts omit it. A key of any other type must be supplied.

Supported operations

order = Order.find(42)                       # real primary key, Int or String
Order.find_by("email", "a@b.c")              # equality on a real column
Order.where({ "status": "open" }).all
Order.where({ "assignee_id": null }).all     # compiles to IS NULL
Order.where({ "status": "open" }).order("created_at", "desc").limit(20).all
Order.where({ "status": "open" }).count
Order.where({ "status": "open" }).exists
Order.sum("total").all                       # sum/avg/min/max on numeric columns
Order.create({ "name": "Ada", "total": "19.99" })
order.status = "closed"
order.save
order.delete
Order.transaction(fn() { ... })              # real SQL transaction

pluck and select are pushed into the SELECT list on column models, so a projection reads two columns instead of fifty on a wide table; the primary key is always included so the row stays identifiable. A field that is not a real column (a nested path, a computed alias) falls back to the client-side projection rather than failing.

Raw SQL escape hatch

For a query the portable surface cannot express:

# Positional binds: $1/$2 on Postgres, ? on MySQL and SQLite.
Order.find_by_sql("SELECT * FROM orders WHERE total > ? AND status = ?", [100, "open"])

# A single `doc` column hydrates as documents (document tables);
# any other shape becomes a hash per row.
Post.find_by_sql("SELECT doc FROM posts WHERE doc->>'slug' = $1", ["hello"])

Values are bound, never interpolated, so a value that looks like SQL stays a value. find_by_sql is SQL-only; on SoliDB it raises and points at Model.query with SDBQL.

Also supported

• Batched eager loading — belongs_to, has_many, has_one, HABTM, through: and includes_count, over the real foreign-key columns. A hash filter on .includes("rel", { "visible": true }) applies to the related rows. A parent with no children gets [], never null.
• group_by with sum/avg/min/max/count over real columns, plus .having("n > 5") and .join("comments") (a correlated EXISTS).
• delete_all / update_all — bulk writes that skip validations and callbacks (as on the document path) but still stamp updated_at when the table has it, and never rewrite the primary key.
• Atomic increment / decrement and counter caches — one arithmetic UPDATE on the column.
• soft_delete, provided the table has a deleted_at column. Without one there is nowhere to record the deletion, so boot fails with that message rather than silently returning deleted rows.
• encrypts, provided each named field is a string/text column. Values are AES-256-GCM ciphertext at rest and plaintext on the instance after create / save / find. Boot fails if the column is missing or not text.
• STI (class Admin < User with table on the base). Subclass writes stamp a type string column; subclass queries add type IN (class, descendants). Boot fails if an STI subclass's table has no type column.

class Person < Model
  table "people"         # id, name, ssn, type, timestamps
  encrypts(:ssn)
end

class Admin < Person
end

a = Admin.create({ "name": "Root", "ssn": "000-00-0001" })
a.ssn                    # plaintext
Admin.find_by("name", "Root").type   # "Admin"
Person.where({ "name": "Root" }).count()  # 1 — base matches every type

Not supported on column-aware models

Each of these raises an error naming the feature rather than returning wrong data:

Doc-store models on the same connection keep working exactly as before; column mode is per model.

Hybrid example

# Controllers use the same Model API
def index
  @users  = User.limit(20).all
  @orders = LegacyOrder.where({ "status": "open" }).limit(20).all
  render("home/index")
end

# Raises — different connections
# LegacyOrder.includes("user").all

Migrations

Default migrate target is the default connection. Target a named SQL secondary with:

soli db:migrate up --connection legacy
soli db:migrate status -c legacy
soli db:migrate down --connection legacy

Prefer naming the connection in migration filenames when multi-DB is in use. Model.transaction works on Postgres, MySQL, and SQLite (one held pool connection for the block).

Limitations

• Per-connection SoliDB hosts: registry stores fields; prefer one SoliDB endpoint + SQL secondaries for now
• A string-form .includes("rel", "…") filter is SoliDB-only; use a hash on SQL
• Read-replica / request-scoped roles are not v1
• pgvector on document tables remains SoliDB-only

Compile-time features

Postgres, MySQL, and SQLite client code is optional at build time (all on by default). A binary built without postgres or mysql cannot open those adapters — boot fails with a rebuild hint if SOLI_DB_ADAPTER or database.toml selects one that was not compiled in.

# SoliDB only (no SQL client crates)
cargo install --path . --locked --no-default-features \
  --features embedding,llm,codegraph

# Postgres only (no MySQL, no SQLite)
cargo install --path . --locked --no-default-features \
  --features embedding,llm,codegraph,postgres

Full table: Configuration → Slim binary.

Troubleshooting

Next steps