Declarative Migrations for SQLAlchemy

DBWarden

Your SQLAlchemy models are your migrations.

Full documentation  |  Source Code

DBWarden is a declarative database migration and schema management tool for SQLAlchemy. You declare the schema you want in your SQLAlchemy models, and DBWarden derives everything else: migration SQL, rollbacks, snapshots, and safety checks.

There are no migration scripts to write or maintain. There is no migration runtime. Your models are the contract. The database is kept in sync with them.

At a glance¶

• Generates migration files as plain SQL, with -- upgrade and -- rollback sections
• Reads SQLAlchemy models and backend-specific metadata from class Meta
• Supports PostgreSQL, MySQL, MariaDB, SQLite, and ClickHouse
• Uses a registry driven PostgreSQL pipeline for diffs and SQL emission
• Manages one or many databases from one typed config source

• Adds safety tooling, schema diffing, and status commands

• Migrations generated from your models, not written by hand

• Plain SQL output: reviewable, committable, executable anywhere
• Rollback contract with executable rollback, strict placeholder refusal, and explicit irreversible declarations
• Pre-deploy impact analysis: know what breaks before it ships
• Offline migration generation for CI pipelines without a live database
• Schema snapshots for deterministic diffs and rename detection
• Typed class Meta system with import-time validation
• Multi-database support: PostgreSQL, MySQL, ClickHouse, MariaDB, SQLite
• Extensible plugin system with official plugins for seeds, RBAC, FastAPI, sandbox testing, and PostgreSQL/ClickHouse extensions
• Reverse-engineer live databases into models with generate-models

Why DBWarden¶

Schema management tools fall into two camps. Imperative tools have you author changes: revision scripts that describe how to get from one schema version to the next. Declarative tools have you author the desired state and derive the changes for you. DBWarden is declarative: your SQLAlchemy models are the single definition of what the schema should be.

Most imperative tools ask you to maintain two representations of your schema: your ORM models and your migration files. When they drift, you find out at deploy time.

DBWarden eliminates the second representation. Your SQLAlchemy models are the schema definition. DBWarden reads them, diffs them against the current database state, and generates the SQL to close the gap (including rollback) without you writing a line of migration code.

This also means:

• No migration runtime to install or version
• No generated Python scripts that quietly do the wrong thing
• No schema drift discovered in production: drift is caught at make-migrations time
• Migrations that can be generated in CI without a database connection

DBWarden is not a wrapper around Alembic. It is a different approach to the same problem. Alembic asks you to describe how to change the database; DBWarden asks you to describe what the schema should be. Alembic can autogenerate revisions, but each one becomes an imperative Python artifact you own, edit, and chain — the revision history is the source of truth. With DBWarden the models stay the source of truth, and the output is plain SQL.

Unlike tools that apply declarative diffs directly to the database, DBWarden still produces versioned, reviewable migration files with explicit rollbacks: declarative authoring without giving up auditable deploy artifacts.

From zero to production¶

Typical adoption path in an existing project:

1. Point DBWarden at your existing SQLAlchemy models
2. Run initial make-migrations to generate a baseline schema
3. Commit generated migrations as your source of truth
4. Replace your current migration workflow with the DBWarden CLI
5. Optionally enable:
6. Migration impact analysis for safer deploys
7. Offline mode for CI pipelines without a database service

Installation¶

uv add dbwarden

Requirements: Python 3.12+, SQLAlchemy 2.0+.

Optional dependency groups:

Quick start¶

1. Configure¶

Create a file named dbwarden.py in your project root:

from dbwarden import database_config

primary = database_config(
    database_name="primary",
    default=True,
    database_type="postgresql",
    database_url_sync="postgresql://user:pass@localhost:5432/myapp",
    database_url_async="postgresql+asyncpg://user:pass@localhost:5432/myapp",
)

2. Define your models¶

from datetime import datetime
from sqlalchemy import DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from dbwarden.databases import IndexSpec, TableMeta


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    bio: Mapped[str | None] = mapped_column(Text, nullable=True)

    class Meta(TableMeta):
        comment = "Core user accounts"


class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    body: Mapped[str] = mapped_column(Text, nullable=False)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)

    class Meta(TableMeta):
        indexes = [
            IndexSpec(name="ix_posts_created_at", columns=["created_at"]),
        ]

3. Generate a migration¶

dbwarden init
dbwarden make-migrations "create initial tables"

Output: both upgrade and rollback in the same file.

-- upgrade
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    bio TEXT
);
COMMENT ON TABLE users IS 'Core user accounts';

CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMP NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_posts_created_at ON posts (created_at);

-- rollback
DROP TABLE posts;
DROP TABLE users;

4. Apply¶

dbwarden migrate

5. Check status¶

dbwarden status

Typical workflow¶

1. Define or update your SQLAlchemy models with class Meta annotations
2. Run dbwarden make-migrations to generate SQL
3. Review the generated .sql file and its rollback section
4. Run dbwarden migrate to apply
5. Verify with dbwarden status

Migration engine¶

Model-driven generation: DBWarden reads your SQLAlchemy models directly. When you change a model, it diffs the new state against the last snapshot and generates the SQL to reconcile them.

Plain SQL output: Generated migrations are .sql files. No migration runtime, no generated Python. Review them, commit them, execute them directly against any environment.

Rollback contract: Generated migrations carry both upgrade and rollback sections. DBWarden emits executable rollback when it is safe, refuses placeholder rollback by default, and requires an explicit irreversible declaration when rollback cannot be produced.

Schema snapshots: After every migration, a checksummed JSON snapshot is written to .dbwarden/schemas/. Snapshots power rename detection, offline diffing, and column-level comparisons without querying the live database.

Column-level diffing: Type, nullability, default, and comment changes generate precise ALTER COLUMN statements.

Typed class Meta: The _MetaValidator metaclass validates every attribute on class Meta at import time. Typos that would have silently produced wrong DDL now raise DBWardenConfigError immediately.

class Meta(MyTableMeta):
    my_engin = "InnoDB"  # DBWardenConfigError: unknown attr 'my_engin'

Supported index features:

• Partial indexes (WHERE clause)
• Covering indexes (INCLUDE columns)
• USING access methods
• NULLS NOT DISTINCT (PostgreSQL 15+)
• Per-column sort order
• Storage parameters (WITH (fillfactor=...))
• ClickHouse skip indexes via ChIndexSpec

Pre-deploy impact analysis¶

Before applying schema changes, DBWarden can scan your codebase to identify what will be affected. It uses AST analysis with a grep fallback, so results reflect actual code structure rather than text matches.

dbwarden check-impact 0042 --database primary

Output:

drop_column on users.username
  References: 2
    app/routes/users.py:34  attribute_access
      .username
    app/templates/profile.jinja2:12  grep
      user.username

Run this before any destructive deploy to surface breaking changes before they reach production.

Offline migrations¶

Export model state once, then generate migrations on any machine without a database connection. Designed for CI pipelines and local development without a running database.

dbwarden export-models --database primary
git add .dbwarden/model_state.json

Then on any machine, with no database required:

dbwarden make-migrations "add bio column" --offline

The model state file is updated in place after each migration.

Important: The model state file (.dbwarden/model_state.*.json) is used for offline migration generation. It is auto-generated and committed to version control. If accidentally deleted, restore it from git (git checkout .dbwarden/model_state.*.json) or regenerate it by running dbwarden export-models --database <db> against a live database. Without it, offline commands like make-migrations --offline will not work, but online operations are unaffected.

Reverse-engineer models¶

Generate SQLAlchemy models from a live database with round-trip support (PostgreSQL, MySQL, ClickHouse, SQLite):

dbwarden generate-models --database primary --tables users,posts
dbwarden generate-models --database primary --base app.database:Base

By default each generated file declares its own Base = declarative_base(). Use --base to import a custom Base class from your project instead (e.g. --base app.database:Base or --base app.database:DeclarativeBase). The generated output includes class Meta blocks with all detected backend-specific metadata.

Supported databases¶

PostgreSQL¶

First-class support with full round-trip schema fidelity. Supported features include identity and generated columns, partitioning, table inheritance, exclusion constraints, deferrable constraints, advanced indexes via PgIndexSpec, per-column storage and collation, enum type creation, and full type normalization (SERIAL, TIMESTAMPTZ, NUMERIC, JSONB, UUID, ARRAY, TSTZRANGE).

MySQL¶

Full round-trip support with MyTableMeta / MyColumnMeta and my.field() spec objects. Engine-level options (my_engine, my_charset, my_collate, my_row_format), column-level options (unsigned, charset, collate, on_update), and model reverse-engineering via generate-models.

uv add "dbwarden[mysql]"

ClickHouse¶

First-class analytics backend support. MergeTree engine family via ChEngineSpec, replicated engines, projections, dictionaries, materialized views, skip indexes via ChIndexSpec, column codecs, LowCardinality and Nullable type wrappers.

uv add "dbwarden[clickhouse]"

MariaDB¶

Schema layer is complete with MdbTableMeta / MdbColumnMeta and mdb.field() spec objects including MariaDB-specific features (page_compressed, invisible, without_overlaps). Snapshot capture and reverse-engineering of MariaDB-specific features are not yet complete.

Developer experience¶

Dev mode: Run SQLite locally against a PostgreSQL production schema with automatic SQL translation.

Multi-database: One project, multiple databases, full isolation between them. Use model_tables to assign table ownership per database when sharing model paths.

Generate models: Reverse-engineer a live database (PostgreSQL, MySQL, ClickHouse) into SQLAlchemy models with dbwarden generate-models.

dbwarden diff: Read-only comparison tool. Outputs as Rich table, JSON, or raw SQL. Supports --offline mode.

Graceful disconnection: Automatic retry logic and clear error messages when a database is unreachable.

Official plugins¶

DBWarden features a plugin system with three trust tiers (official, verified, community). Official plugins extend core with features that were previously built-in, now maintained independently:

See the plugin documentation for installation, development guides, and the full Verified standard.

License¶

MIT

DBWarden is built for teams that want declarative, reviewable, reproducible database changes, derived from the models they already maintain, not from migration scripts they have to write.

Next Steps¶

• Start with Features
• Follow the guides in Get Started
• Explore Cookbook & Examples
• Browse Plugins to extend DBWarden's capabilities
• Use CLI Reference as command lookup