Multiple Databases¶

One process can hold several named connections — a primary application database, a read replica, an analytics warehouse — each with its own pool. The common case still works untouched: await connect(url) registers and selects "default". Named connections make everything else explicit.

Registering Connections¶

Give each connection a name; mark one as default for unqualified operations:

    await connect("sqlite::memory:", name="app", default=True, auto_migrate=True)
    await connect("sqlite::memory:", name="analytics", auto_migrate=True)

Each connect() call creates an independent pool, configurable per connection with pool=PoolConfig(max_connections=...). You can change which named connection is the default later with set_default_connection(name).

Routing Queries¶

Operations need a route: either an open session (ambient or explicit session=) or an explicit using=. Inside async with engines.session():, unqualified calls resolve to that session's connection — the default connection if you opened the session with no name. Model.using(name) returns a handle exposing the same API (create, all, select, where, get, get_or_none, bulk_create, get_or_create, update_or_create) pinned to the named connection, and it runs on its own — no session required:

    async with engines.session():
        # Writes go to the default ("app") connection unless routed
        await Metric.create(name="signups", value=1)
        app_metrics = await Metric.all()

    # Route reads and writes to a named connection with .using() — this
    # doesn't need a session; a `using=` call is its own explicit route
    await Metric.using("analytics").create(name="page_views", value=100)
    analytics_metrics = await Metric.using("analytics").all()

Routing is per-call: using() doesn't change any global state, so two coroutines can talk to different databases concurrently without interfering. A using() call made without a session, as analytics_metrics is above, runs with no identity map — every load is a fresh instance, with no a is b guarantee across calls. See Identity Map for what that trades away.

An explicit using= that names a connection different from the session you're ambiently inside of raises ValueError rather than silently running outside the session — and an operation with no session and no using= at all raises RuntimeError. There is no implicit default-connection fallback.

For session-first workflows, use engines.session(name) to pin a full block:

import ferro

async with ferro.engines.session("analytics") as s:
    metrics = await s.query(Metric).where(lambda t: t.value > 0).all()

Transactions on Named Connections¶

transaction(using=...) pins a transaction to one named connection:

    async with transaction(using="analytics"):
        await Metric.using("analytics").create(name="clicks", value=42)

Everything inside the block — including raw SQL via execute() / fetch_all() — inherits that connection. Nested transaction() blocks inherit it too. Ferro does not support distributed transactions: one transaction() spans exactly one named connection, so writes to two databases are never atomic together.

Per-Connection Schema Setup¶

Schema creation targets one connection at a time:

connect(url, name=..., auto_migrate=True) runs auto-migration on that connection as part of connecting — each database gets tables for all registered models, as the example above shows.
create_tables(using=...) creates tables explicitly on a named connection after the fact:

from ferro import create_tables

await create_tables(using="analytics")

Don't run schema creation concurrently through multiple names that point at the same physical database. For production schema changes, prefer one migration-capable connection and the Alembic bridge.

Practical Notes¶

Typical roles. A read replica for expensive list endpoints (User.using("replica")), an analytics database that receives event rows, or a separate service database owned by another team.
Keep credentials server-side. Elevated service-role connections belong in configuration, not source control — and never make a service-role connection the default in a user-facing runtime.
Never route from untrusted input. Don't pick the using name from request data.
Pools isolate roles, not request context. A named connection isolates credentials and pooling; it does not provide per-request RLS/JWT context inside one shared pool. Objects loaded through an elevated connection can contain elevated data — filter before returning them to users.
No automatic routing. Read/write splitting, cross-connection joins, and two-phase commit are not features; routing is always an explicit using=, session=, or engines.session(name) you name yourself — never inferred from query shape or request data.