---
title: "SQLAlchemy"
section: "Extensions"
framework: WebFluid
package: webfluid
version: "1.0.0b3"
stage: "beta"
released: "August 17, 2026"
canonical: "https://docs.webfluid.dev/latest/ext/sqlalchemy.md"
html: "https://docs.webfluid.dev/latest/ext/sqlalchemy"
audience: ai-agent
format: markdown
---

# SQLAlchemy


`EXT_SQLALCHEMY` gives you a declarative base (`db.Model`), lazily created sync **and** async
engines per bind, and a pair of context managers that hand you a session wrapper called an
*executor*. Transactions, commits and rollbacks are handled by the context manager; you write
statements.


## Enabling

```ini
[extensions]
EXT_SQLALCHEMY = 1

[data]
DATABASE_URI = sqlite:///app.db
```

`EXT_BABEL` and `EXT_SECURITY` both require this switch, because they ship models.

## URIs — no drivers

`SQLALCHEMY_DATABASE_URI` and every entry of `SQLALCHEMY_BINDS` must be **driver-less**. The
extension derives a sync and an async URI from the scheme:

| Scheme you write   | Sync driver          | Async driver         |
|--------------------|----------------------|----------------------|
| `sqlite:///app.db` | `sqlite` (stdlib)    | `sqlite+aiosqlite`   |
| `postgresql://…`   | `postgresql+psycopg` | `postgresql+psycopg` |
| `mysql://…`        | `mysql+pymysql`      | `mysql+aiomysql`     |

Anything else raises `ValueError: Unsupported database type`. Writing a driver yourself
(`postgresql+asyncpg://…`) raises `ValueError: Please do not define drivers`.

Only the **leading scheme prefix** is rewritten since `1.0.0b3`, so a path segment, a database name,
a user or a password containing the scheme word survives intact —
`sqlite:///data/sqlite/app.db` and a MySQL database named `mysql_prod` both work. Before that a plain
`str.replace` mangled every occurrence.

## Models

```python
# fluid/models.py
from webfluid.core.ext import db
from sqlalchemy import func
from sqlalchemy.orm import Mapped, mapped_column
from datetime import datetime


class MyModel(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    value: Mapped[str]
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())

    def __init__(self, value: str):
        self.value = value
```

`db.Model` is a `DeclarativeBase` subclass with three additions:

- **`__tablename__` is derived** from the class name via `camel_to_snake` when you do not set one.
  `MyModel` → `my_model`, `WebAuthnCredential` → `web_authn_credential`. Set `__tablename__`
  explicitly to override; the `declared_attr` respects a string you define on the class.
- **`__bind_key__`** selects a bind, and setting it as a class attribute calls `set_bind` for you
  through `__init_subclass__`.
- **`__hash__`** is the identity hash, so detached instances hash consistently.

> **RULE** — Define models under fluid/models (a package or a module). The generated Alembic env.py imports fluid.models by name — models it cannot import are missing from autogenerated revisions.

> **RULE** — Give models an explicit __init__ that takes the fields you actually set. Every framework model does. It keeps construction honest and keeps relationship collections initialised.

## Executors

`db.executor()` and `db.async_executor()` are context managers yielding an `Executor` /
`AsyncExecutor` — a thin wrapper over a session:

```python
from webfluid.core.ext import db
from sqlalchemy import select
from fluid.models import MyModel


async def get(model_id: int) -> MyModel | None:
    async with db.async_executor(model=MyModel) as e:
        results = await e.exec(select(MyModel).where(MyModel.id == model_id))
        return results.first()


async def create(value: str) -> MyModel:
    async with db.async_executor(model=MyModel) as e:
        return await e.insert(MyModel(value), flush=True)
```

### The API

| Method                          | Sync | Async   | Notes                                                                                                                  |
|---------------------------------|------|---------|------------------------------------------------------------------------------------------------------------------------|
| `exec(statement, scalars=True)` | ✓   | `await` | Returns a `ScalarResult` by default; `scalars=False` gives the raw `Result` (needed for `count()`, tuples, `exists()`) |
| `insert(obj, flush=False)`      | ✓   | `await` | `session.add`; returns `obj`. `flush=True` populates server-side defaults and the primary key                          |
| `delete(obj, flush=False)`      | ✓   | `await` |                                                                                                                        |
| `flush()`                       | ✓   | `await` |                                                                                                                        |
| `.session`                      | ✓   | ✓      | The real SQLAlchemy session, for anything the wrapper does not cover                                                   |

### Which selector to use

```python
db.executor(bind_key=None, model=None)          # sync,  new session
db.async_executor(bind_key=None, model=None)    # async, new session
db.ensured_executor(...)                        # reuse an open sync executor, else open one
db.ensured_async_executor(...)                  # reuse an open async executor, else open one
db.current_executor                             # the active one, or RuntimeError
db.current_async_executor                       # ditto
```

Bind resolution: `bind_key` wins; otherwise the model's `__bind_key__`; otherwise `"default"`.

> **RULE** — Pass model=<YourModel> to every executor call. It is how the extension picks the right bind. Passing nothing works only as long as every model lives on the default bind — and silently connects to the wrong database the day one does not.

`ensured_*` is for reusable service functions that must work both standalone and inside a caller's
transaction:

```python
async def touch(model_id: int):
    # Joins the caller's transaction if there is one, opens its own otherwise.
    async with db.ensured_async_executor(model=MyModel) as e:
        ...
```

Executors are `BaseContext` subclasses, so `Executor.current()`, `Executor.try_current()` and
`Executor.outer(depth=1)` work on them.

### What survives the block

Sessions are created with `expire_on_commit=False`, and the context manager commits on a clean exit
and rolls back on an exception:

```python
async with db.async_executor(model=MyModel) as e:
    model = await e.insert(MyModel("x"), flush=True)

# Still readable here — attributes are not expired by the commit:
print(model.id, model.value, model.created_at)
```

What does **not** survive is anything requiring a query: unloaded relationships and deferred
columns raise `DetachedInstanceError` (or, for the security models,
`InvalidRequestError` from `lazy="raise_on_sql"`). Load them inside the block:

```python
from sqlalchemy.orm import selectinload

async with db.async_executor(model=User) as e:
    result = await e.exec(
        select(User).where(User.id == uid).options(selectinload(User.roles))
    )
    user = result.first()

names = [role.name for role in user.roles]   # loaded, so it survives
```

> **WARNING** — Mutating an object after its executor block is a silent no-op — there is no session watching it, so nothing is flushed. Do the mutation inside the block, or re-open one and merge the object.

### Aggregates and raw results

```python
async with db.async_executor(model=MyModel) as e:
    result = await e.exec(select(func.count(MyModel.id)), scalars=False)
    total = result.scalar()
```

`scalars=True` (the default) wraps the result in `.scalars()`, which is correct for
`select(Model)` and wrong for `select(func.count(...))` — remember the flag.

## Binds

```python
@register_config(10)
class Config:
    SQLALCHEMY_BINDS = {
        "analytics": "postgresql://user:pw@host/analytics",
        "archive": "sqlite:///archive.db"
    }
```

```python
class Event(db.Model):
    __bind_key__ = "analytics"
    id: Mapped[int] = mapped_column(primary_key=True)
```

Each bind is a `Bind` object with:

- its own `MetaData` (`Model.metadata_for(key)`) — so `create_all` per bind works,
- lazily created `sync_engine` / `async_engine`,
- `session()` / `async_session()` context managers doing commit/rollback/close.

```python
bind = db.get_bind("analytics")
bind = db.get_bind_for_model(Event)
db.bind_keys                     # ["default", "analytics", "archive"]
bind.sync_engine, bind.async_engine
```

### Attaching a bind at runtime

```python
Event.set_bind("analytics")
```

`set_bind` moves the model's `Table` into the target bind's metadata. It raises
`FrameworkException` if the model already has a bind — a model belongs to exactly one.

This is how `EXT_SECURITY` implements `SECURITY_MODELS_DB_BIND` and `EXT_BABEL` implements
`BABEL_DATABASE_BIND`, and how an Additive puts its models on a configured bind:

```python
@additive.before_enable
async def before_enable(fluid):
    bind = fluid.config.get("PORTAL_DB_BIND")
    if bind:
        from .models.entry import Entry
        Entry.set_bind(bind)
```

> **KNOWN BUG** — wf migrate init picks the single- or multi-database Alembic template from SQLALCHEMY_BINDS in the config, at init time. Binds attached at runtime through Model.set_bind are invisible to it — declare them in the config before running wf migrate init, or enroll the multi_db template by hand.

## Creating tables

For development, a startup hook:

```python
@app.startup_hook
async def create_tables():
    bind = db.get_bind_for_model(db.Model)
    db.Model.metadata.create_all(bind.sync_engine)
```

Per bind:

```python
@app.startup_hook
async def create_tables():
    for key in db.bind_keys:
        bind = db.get_bind(key)
        bind.metadata.create_all(bind.sync_engine)
```

> **RULE** — create_all is a development convenience. For anything you deploy, use wf migrate — see ext/migrate.md. And remember create_all only runs under wf run, because startup hooks do not run under an external ASGI server.

## Engine options

```python
SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_pre_ping": True,     # framework default
    "pool_recycle": 3600,      # framework default
    "pool_size": 10,
    "max_overflow": 20,
    "echo": False
}
```

The dict is passed to **both** `create_engine` and `create_async_engine`, for every bind. Keys that
only one of them understands will raise — keep it to options both accept.

> **RULE** — Leave pool_pre_ping and pool_recycle alone unless you have a specific reason. They exist because a pooled connection to a database that closed it produces an error on the next request instead of a reconnect.

## Disposal

`expand_fluid` registers `SQLAlchemy.dispose` as a **shutdown hook**, so every bind's sync and async
engine is disposed when the app stops rather than held to process exit.

```python
await db.dispose()               # all binds
await db.get_bind("x").dispose() # one bind
```

`Bind.dispose` is idempotent and clears the cached engine/sessionmaker pair, so a disposed bind
lazily rebuilds itself if it is used again. Like every hook, this runs from `mix()` — an app served
by an external ASGI server never disposes its pools.

## Sync or async?

Use the async executor everywhere you are already in `async def` — which is every route handler,
every event handler and every `async` hook. The sync executor exists for:

- code that genuinely cannot be async (a `__init__`, a sync library callback),
- Alembic migrations, which run synchronously,
- `create_all` and other DDL, which SQLAlchemy only exposes synchronously.

> **WARNING** — A sync executor inside an async handler blocks the event loop for the duration of the query. If you must, wrap it in webfluid.utils.run_in_executor.

## Next

- [`ext/migrate.md`](/latest/ext/migrate.md) — schema management.
- [`ext/security.md`](/latest/ext/security.md) — the models this battery hosts.
- [`ref/extensions.md`](/latest/ref/extensions.md) — the terse API list.


---

## Navigation


- [Overview](/latest/.md) — what WebFluid is, how to navigate these docs, release state and known bugs
- [Getting Started](/latest/get-started.md) — install, minimal app, the layout the framework expects

**Configuration**
- [App configs](/latest/config/app-config.md) — `app_configs/<app>.ini`, switches, `*_FILE` secrets, `[dev]`
- [Config classes](/latest/config/config-class.md) — `register_config`, `DefaultConfig`, every framework key

**Extensions**
- [Introduction](/latest/ext/base.md) — `FluidExtension`, entry points, `expand_fluid`
- [Scheduling](/latest/ext/scheduling.md) — APScheduler `AsyncIOScheduler`
- [SQLAlchemy](/latest/ext/sqlalchemy.md) — `Model`, executors, binds, sessions
- [Migrate](/latest/ext/migrate.md) — Alembic wrapper, `prepare_fluid`, single/multi-db
- [Mail](/latest/ext/mailman.md) — SMTP, sync/async, batching, attachments
- [Babel](/latest/ext/babel.md) — gettext, domains, locale selection, formatters
- [Security](/latest/ext/security.md) — users, gates, CSRF, tokens, OAuth, bearer grants
- [Events](/latest/ext/events.md) — signals, events, queries, browser socket
- [Cache](/latest/ext/cache.md) — redis / legacy backends
- [JWTManager](/latest/ext/jwt.md) — encode/decode, rotation, revocation

**Frontend (surface)**
- [Introduction](/latest/surface/tooling.md) — feature switches, `fluid_base.html`, Tailwind, themes
- [Integration](/latest/surface/frontend.md) — `APP_FRONTEND`, htmx, Vite workspaces
- [Template resolution](/latest/surface/jinja.md) — loader order, namespaces, overriding

**Additives**
- [Introduction](/latest/additives/intro.md) — manifest, routers, enabling
- [Base Additives](/latest/additives/base.md) — `type: base`, `import_base`, extension semantics
- [Interaction between](/latest/additives/contract.md) — events/queries as contracts, requirements, packaging

**Framework utility**
- [Lifecycle](/latest/utils/lifecycle.md) — `mix()`, startup/shutdown hooks, graceful stop
- [Runtime](/latest/utils/runtime.md) — `FluidContext`, request hooks, themes, proxy, rate limits
- [Logging](/latest/utils/logging.md) — the log factory, levels, files, additive attribution

**CLI**
- [Introduction](/latest/cli/wf.md) — command tree, extension CLIs
- [Create](/latest/cli/create.md) — `wf create project/app/additive`
- [Run](/latest/cli/run.md) — `wf run`, flags, debug and interactive mode
- [Ocean](/latest/cli/ocean.md) — search, install, publish

**Reference**
- [Overview](/latest/ref.md) — package layout, stability contract
- [Core](/latest/ref/core.md) — `Fluid`, config, context, `core.ext`, constants
- [Extensions](/latest/ref/extensions.md) — the battery API surface
- [Surface](/latest/ref/surface.md) — `Frontend`, Node and Tailwind tooling
- [Additives](/latest/ref/additives.md) — `Additive`, `Router`, `Manifest`, registry helpers
- [Utils](/latest/ref/utils.md) — helpers, logging, exceptions