| Backend | database_type | Round-trip | Notes |
|---|---|---|---|
| PostgreSQL | postgresql | Yes | Primary backend, full schema fidelity |
| MySQL | mysql | Yes | DDL parity focus |
| ClickHouse | clickhouse | Yes | MergeTree engine family |
| MariaDB | mariadb | No | Migration target; introspection gaps remain |
| SQLite | sqlite | Yes | Full round-trip; also the dev-mode backend |
/ databases
Database Support.
All five backends follow the same model-driven loop: models define the schema, `make-migrations` generates the SQL, and `migrate` applies it. The strongest combination for most projects: SQLAlchemy + PostgreSQL + FastAPI. Backend-specific options are declared in typed metadata on the models, and the matrix below lists what each backend supports.
Round-trip, defined
Round-trip means dbwarden can read a schema and write it back. generate-models reads a live database and emits SQLAlchemy models; make-migrations and migrate write models back to SQL. Backends with round-trip support cover both directions. Backends without it are still migration targets, but introspection back into models is not yet supported.
/ 01 The matrixRead the docs ↗
Round-trip support, per backend.
/ 02 PostgreSQLRead the docs ↗
The primary backend, with full round-trip.
PostgreSQL is where the snapshot, diff, and SQL emission pipeline is deepest. Identity columns (GENERATED ALWAYS and BY DEFAULT AS IDENTITY, with sequence options), generated stored columns, partitioning (RANGE / LIST / HASH with attach and detach), table inheritance, exclusion constraints, deferrable constraints, and advanced indexes all round-trip. Indexes keep their partial and expression predicates, INCLUDE columns, operator classes, NULLS NOT DISTINCT, column sort order, and CONCURRENTLY.
Per-column COLLATE, STORAGE, and COMPRESSION (pglz, zstd, PG 14+) survive the trip, as do enums, domains, composite types, sequences, functions, triggers, roles, row-level security policies, extended statistics, event triggers, views and materialized views, and schema and table grants. Default privileges (ALTER DEFAULT PRIVILEGES) are handled for future objects, so the access story covers new tables created after the migration, not just the ones that exist today.
Round-trip is tested rather than assumed: generate-models reverse-engineers a live database, and feeding that output back through make-migrations has to produce zero diff. Everything below the SQLAlchemy type is carried in typed metadata on the model: table-level options on Meta(PGTableMeta), column-level options on a Meta class per column with pg.field() specs.
typed metadata on the modelinstall the driver
from dbwarden.databases.pgsql import PGTableMeta, PGColumnMeta, pg
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(Integer, primary_key=True)
class Meta(PGTableMeta):
pg_fillfactor = 80
pg_schema = "app"
class id(PGColumnMeta):
pg = pg.field(identity="always")/ 03 MySQL and MariaDBRead the docs ↗
Full round-trip for MySQL. Target-only for MariaDB.
MySQL round-trip covers the storage engine (my_engine), table and per-column charsets and collations, row formats, unsigned integer columns, ON UPDATE CURRENT_TIMESTAMP, and the auto-increment lifecycle, including toggling AUTO_INCREMENT on an integer primary key, which emits a MODIFY COLUMN. Types are normalized: TINYINT(1) becomes BOOLEAN, and the rest map to their SQLAlchemy equivalents. Foreign keys keep their ON DELETE / ON UPDATE options, and dropping one emits MySQL's DROP FOREIGN KEY syntax.
One thing to know before relying on it: MySQL DDL is non-transactional. Each statement auto-commits, so a migration that fails halfway leaves the earlier statements applied. Review generated files in order, and treat partial failure as possible in your recovery plan.
MariaDB is a supported migration target with its own typed metadata, including MariaDB-specific features: page compression (mdb_page_compressed, mdb_page_compression_level), invisible columns, and CREATE SEQUENCE. Schema introspection back into models is not yet round-trip, so MariaDB appears in the matrix as target-only.
engine and charset on the modelinstall the driver
from dbwarden.databases.mysql import MyTableMeta, MyColumnMeta, my
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(Integer, primary_key=True)
class Meta(MyTableMeta):
my_engine = "InnoDB"
my_charset = "utf8mb4"
class id(MyColumnMeta):
my = my.field(unsigned=True)/ 04 ClickHouseRead the docs ↗
Analytical storage in the same workflow.
ClickHouse's object model is different enough to matter: what is a SET in PostgreSQL is often a CREATE commitment in ClickHouse. The docs put it directly: some things can never change, and some changes force a table recreate. dbwarden models that line explicitly instead of pretending every diff is a gentle ALTER.
Engines, projections, skip indexes, dictionaries, materialized views, and column codecs all live in typed metadata. The MergeTree family is configured through engine factories and settings, with order_by, partition_by, and ch_indexes declared next to the table. LowCardinality and Nullable wrappers are part of the type system, and data operations like OPTIMIZE and MATERIALIZE have their own declared surface.
Because recreates are the cost of changing an engine, the policy is explicit: engine changes go through a recreate pipeline, and transitions between engine families that would lose data are refused as rollback rather than silently claimed.
engine, order by, and indexesinstall the driver
from dbwarden.databases.clickhouse import CHTableMeta, ChEngineSpec, ChIndexSpec
class Event(Base):
__tablename__ = "events"
class Meta(CHTableMeta):
ch_engine = ChEngineSpec("MergeTree")
ch_order_by = ["event_date", "id"]
ch_indexes = [
ChIndexSpec("ix_payload", ["payload"], type="bloom_filter", granularity=1),
]/ 05 SQLiteRead the docs ↗
Full round-trip, and the dev-mode backend.
SQLite is a full round-trip backend: generate-models reads a live SQLite database back into SQLAlchemy models, and make-migrations / migrate write models back to SQLite DDL. STRICT tables, WITHOUT ROWID tables, generated columns (sq.field(generated=...) with STORED or VIRTUAL mode), and column collations all survive the trip, carried in SqTableMeta / SqColumnMeta on the model. That makes SQLite usable as a real target, not just a translation layer.
SQLite's ALTER TABLE covers only RENAME TO, RENAME COLUMN, ADD COLUMN, and DROP COLUMN. Everything else, a column's type or default, a table constraint, toggling STRICT or WITHOUT ROWID, is emitted as a table rebuild: create the new shape under a temporary name, copy every row, swap the names. The rebuild is visible in the generated SQL, and check reports it with the cost spelled out, so a migration that needs one is reviewed as a rebuild instead of left as a comment telling you to write it by hand.
Dev mode is where SQLite earns its keep: two URLs on the same database object, the production URL by default and a dev URL whenever you pass --dev. The usual pair is PostgreSQL in production and SQLite locally, which removes the server dependency from everyday work: no Docker setup, no shared state, a file you can delete to reset, and no way to accidentally touch a production-like environment.
When the dev backend can't represent a production type, dbwarden translates: UUID to TEXT, JSON/JSONB to TEXT, BYTEA to BLOB, TIMESTAMPTZ to DATETIME, SERIAL to INTEGER. Unsupported defaults are dropped with a warning, or generation fails under --strict-translation, which is the mode you want in CI so a type that production supports never silently disappears from the dev database.
The recommended rhythm: iterate locally with --dev, keep strict checks in CI with --strict-translation, and validate release-candidate migrations against a production-like database before deploy. Because SQLite needs no driver, the extra is empty and nothing extra is installed.
sqlite metadata on the modeltwo URLs, one database
from dbwarden.databases.sqlite import SqTableMeta, SqColumnMeta, sq
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(Integer, primary_key=True)
slug: Mapped[str] = mapped_column(String)
class Meta(SqTableMeta):
sq_without_rowid = True
sq_strict = True
class slug(SqColumnMeta):
sq = sq.field(collate="NOCASE")class Primary(DbwardenDatabase):
database_name = "primary"
default = True
database_type = "postgresql"
database_url_sync = "postgresql://user:pass@localhost:5432/main"
dev_database_type = "sqlite"
dev_database_url = "sqlite:///./development.db"/ 06 Multi-databaseRead the docs ↗
One repository, several databases, isolated histories.
Multiple databases are the normal case once a service grows: a read/write split with a replica, domain separation between transactional and analytical storage, a new database adopted while a legacy one still serves traffic, or one database per tenant. Each database declared as a DbwardenDatabase subclass gets its own migration directory, its own model set, and its own linear versioned history, so analytics__0001_... never collides with primary__0001_....
When databases share model paths, model_tables assigns table ownership so each table belongs to exactly one database. The database marked default = True is the one used when --database is omitted. Operate one at a time with --database, or all of them together with --all for migrate, status, and rollback.
skip_if_missing lets an optional database degrade gracefully: when it cannot be reached, the run reports a partial-success exit code instead of failing the whole operation, which keeps CI green when a dependency is not part of the local environment.
The isolation holds at apply and recovery time, not just at generation: status and history are reported per database, and rollback --all reverses each database's own sequence in its own order, so operating several databases never conflates their histories.
two databases in one configoperate them together
class Primary(DbwardenDatabase):
database_name = "primary"
default = True
database_type = "postgresql"
database_url_sync = "postgresql://localhost/main"
model_paths = ["app.models.primary"]
class Analytics(DbwardenDatabase):
database_name = "analytics"
database_type = "clickhouse"
database_url_sync = "clickhouse://localhost:8123/analytics"
model_paths = ["app.models.analytics"]$ dbwarden migrate --database primary
$ dbwarden migrate --database analytics
$ dbwarden status --all/ 07 Lock scopeRead the docs ↗
Native locks, with independent namespaces.
Each backend uses its native migration lock: advisory locks for PostgreSQL, named locks for MySQL and MariaDB, BEGIN IMMEDIATE for SQLite, and a lease with fencing for ClickHouse. The lock status row and heartbeat make active and stale runners visible.
Set lock_namespace when separate workflows need independent lock streams, and use lock-status before recovering a stuck deployment.
inspect the targetRead the locking guide ↗
$ dbwarden lock-status --database primary
$ dbwarden unlock --database primary --force/ 08 Optional dependenciesRead the docs ↗
Drivers and extras, per backend.
Core dbwarden installs SQLAlchemy and nothing backend-specific; each driver is an extra you opt into. The extra also pulls the driver's transitive dependencies (for ClickHouse that means aiohttp alongside clickhouse-connect), and combining extras in one command is the normal case for a multi-database repository.
FastAPI integration has its own extras on the dbwarden-fastapi plugin: [metrics] for prometheus-client, [redis] for the distributed lock, and [clickhouse] for ClickHouse sessions. Those install with uv add "dbwarden-fastapi[metrics]" and are covered on the FastAPI page.
core extras
PostgreSQL driver (psycopg2-binary). The one most projects need; PostgreSQL is the primary backend.
MySQL and MariaDB driver (pymysql). One extra covers both, since the two share the MySQL wire protocol.
ClickHouse driver (clickhouse-connect) plus aiohttp for the HTTP transport.
SQLite needs no driver; it ships inside the Python standard library. The extra exists for symmetry and is empty.
/ faq
Which backends have full round-trip?
PostgreSQL, MySQL, ClickHouse, and SQLite. Round-trip means generate-models reads a live database back into SQLAlchemy models, and make-migrations writes models back to SQL. MariaDB is a migration target with typed metadata, but introspection back into models is not yet supported.
What does dev mode translate on SQLite?
Types SQLite cannot represent are translated: UUID to TEXT, JSON/JSONB to TEXT, BYTEA to BLOB, TIMESTAMPTZ to DATETIME, SERIAL to INTEGER. Unsupported defaults are dropped with a warning, or generation fails under --strict-translation. Round-trip on a SQLite-native schema needs no translation at all.
How does dbwarden handle changes SQLite cannot ALTER?
The SQLite ALTER TABLE statement covers RENAME TO, RENAME COLUMN, ADD COLUMN, and DROP COLUMN. Any other change, a column type or default, a constraint, STRICT or WITHOUT ROWID, is emitted as a table rebuild: create the new shape, copy the rows, swap. The rebuild is visible in the generated SQL and check reports it explicitly.
How do I install the driver for another backend?
PostgreSQL ships by default. Add the matching extra: uv add "dbwarden[mysql]", uv add "dbwarden[clickhouse]", or uv add "dbwarden[sqlite]" (the last is empty). Any SQLAlchemy-compatible driver URL also works, for example mysql+mysqlconnector://.
How do several databases coexist in one repository?
Each database gets its own migration directory, model set, and versioned history. model_tables assigns table ownership when databases share model paths. Operate them with --database one at a time or --all together; skip_if_missing degrades optional databases gracefully.
What happens on a lossy engine change in ClickHouse?
Engine changes go through an explicit recreate policy, and transitions between engine families that would lose data are refused as rollback rather than silently claimed.
Do the backend extras overlap with the fastapi extras?
No. The core extras (postgres, mysql, clickhouse, sqlite) install drivers for dbwarden itself. The dbwarden-fastapi plugin has its own extras: [metrics] for prometheus-client, [redis] for the distributed lock, and [clickhouse] for ClickHouse sessions.