PostgreSQL¶

DBWarden treats PostgreSQL as a first-class backend: every natively supported feature is reverse-engineered, diffed, and emitted as correct DDL.

First-Class Features¶

"First-class" means the round-trip is verified: reverse-engineer a live database with generate-models, feed the output back into make-migrations, and get zero diff.

# Step 1: reverse-engineer your live PostgreSQL database
$ dbwarden generate-models -d primary --tables users,orders,items

# Step 2: feed the generated models back in, zero diff
$ dbwarden make-migrations
# → "No changes detected"  (output is empty; your models match the DB exactly)

The following PostgreSQL features are fully supported in this round-trip:

Declaring Metadata¶

PostgreSQL metadata is declared in a class Meta inner class on the model. This is the only supported surface: mapped_column(info=...) raises DBWardenConfigError.

Table-Level Meta¶

Inherit from PGTableMeta on your class Meta:

from sqlalchemy import Integer
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from dbwarden.databases.pgsql import PGTableMeta

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)

    class Meta(PGTableMeta):
        pg_fillfactor = 80
        pg_tablespace = "fastspace"
        pg_inherits = "base_entity"
        pg_excludes = [
            {"name": "excl_room_booking", "expression": "USING gist (room_id WITH =, during WITH &&)"},
        ]

PGTableMeta inherits from TableMeta, which provides common attributes shared across all backends:

PostgreSQL-specific PGTableMeta attributes:

The pg_indexes list uses PgIndexSpec objects (from dbwarden.databases.pgsql or dbwarden):

from dbwarden.databases.pgsql import PgIndexSpec

class Meta(PGTableMeta):
    pg_indexes = [
        PgIndexSpec("ix_users_email", ["email"],
            unique=True, using="gin"),
    ]

Column-Level Meta¶

Use PGColumnMeta inner classes for per-column metadata. The inner class must be named after the column. Use pg = pg.field(...) to set column-level options:

from sqlalchemy import Integer, String, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from dbwarden.databases.pgsql import PGTableMeta, PGColumnMeta, pg

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))
    bio: Mapped[str] = mapped_column(Text)

    class Meta(PGTableMeta):
        class id(PGColumnMeta):
            pg = pg.field(identity="always", identity_start=100, identity_increment=1)

        class bio(PGColumnMeta):
            pg = pg.field(storage="EXTENDED", compression="pglz", collation="en_US.UTF-8")

PGColumnMeta includes common column attributes shared across all backends:

PostgreSQL-specific PgFieldSpec fields (set via pg.field(...)):

Foreign Key Options¶

Foreign key options (ondelete, onupdate, deferrable) are captured from the database by generate-models and emitted in the ForeignKey constructor:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column

class OrderItem(Base):
    __tablename__ = "order_items"

    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id", ondelete="CASCADE", onupdate="CASCADE", deferrable=True), nullable=False)

DDL Behavior¶

Transactional DDL¶

PostgreSQL DDL is transactional. If a migration file contains multiple statements and one fails, all prior DDL in that file is rolled back. This makes PostgreSQL the safest backend for automated migration runs.

Index Creation¶

DBWarden defaults to CREATE INDEX CONCURRENTLY to avoid table locking. Pass --no-concurrent when the migration must run inside a transaction block (PostgreSQL requires CONCURRENTLY outside a transaction).

Column Type Changes¶

Emits ALTER TABLE t ALTER COLUMN c TYPE newtype with a commented-out -- USING col::newtype line. Pass --postgres-auto-using to emit an active USING clause. Without the flag, uncomment and verify the USING expression before running the migration against production.

Safe Type Change¶

The --safe-type-change flag generates a multi-step strategy: 1. Add a temporary column with the new type 2. Emit a -- comment with an UPDATE statement template 3. Emit a verification comment 4. After manual verification, drop the old column and rename the temporary column

Generated Columns¶

Adding a generated column via ALTER TABLE is not supported by PostgreSQL. ALTER COLUMN column_name ADD GENERATED AS (expr) STORED is not valid DDL. DBWarden emits a comment placeholder noting this limitation. Dropping the generation expression (ALTER COLUMN c DROP EXPRESSION) produces real DDL.

Auto-increment Lifecycle¶

DBWarden supports toggling auto-increment on integer primary key columns. The autoincrement field in your model controls whether a column uses SERIAL-style sequence auto-increment or is a plain integer:

class User(Base):
    __tablename__ = "users"

    # Autoincrement enabled (SERIAL): same as default behavior
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)

    class Meta(PGTableMeta):
        id = ColumnMeta(autoincrement=True)

To explicitly disable auto-increment on a PK column:

class User(Base):
    __tablename__ = "users"

    # Plain integer PK: no sequence, no auto-increment
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=False)

What happens when autoincrement changes:

The rollback SQL is the symmetric inverse: if an autoincrement addition is rolled back, the sequence is dropped and the default is removed.

Detection from live databases:

When reverse-engineering a live PostgreSQL database, DBWarden detects autoincrement by: 1. SERIAL/BIGSERIAL column types: type string contains serial 2. SQLAlchemy's .autoincrement attribute: set by the PG dialect for SERIAL columns 3. nextval(...) default patterns: PG SERIAL columns have DEFAULT nextval('table_col_seq'::regclass)

Sequence lifecycle:

Sequences created by SERIAL auto-increment follow the naming convention table_column_seq. When autoincrement is removed: - The column default (nextval(...)) is dropped - The sequence is dropped via DROP SEQUENCE IF EXISTS - The column type remains INTEGER; no data is lost

When autoincrement is added to an existing integer column: - A new sequence is created starting at 1 - The column default is set to nextval('table_column_seq') - The sequence is owned by the column for proper cleanup on table drop

Type mapping behavior:

The type mapping in _map_sqlalchemy_type_to_backend promotes INTEGER PRIMARY KEY to SERIAL only when autoincrement is not explicitly False:

Non-PostgreSQL backends:

SQLite, MySQL, and ClickHouse do not support sequence-based auto-increment toggling via ALTER. The alter_column_autoincrement operation emits a comment explaining the limitation on these backends.

Snapshot Format¶

The snapshot JSON captures all PostgreSQL-specific metadata. Key sections:

Column Extras¶

{
  "name": "bio",
  "type": "text",
  "pg_column": {
    "collation": "en_US.UTF-8",
    "storage": "EXTENDED",
    "compression": "pglz",
    "generated": null,
    "identity": "always",
    "identity_start": 1,
    "identity_increment": 1
  }
}

Table Extras¶

{
  "pg_table": {
    "pg_fillfactor": 80,
    "pg_tablespace": "fastspace",
    "pg_unlogged": false,
    "pg_inherits": "base_entity",
    "pg_partition": {
      "strategy": "RANGE",
      "columns": ["created_at"]
    },
    "pg_excludes": [
      {"name": "excl_room_booking", "expression": "EXCLUDE USING gist (room_id WITH =, during WITH &&)"}
    ]
  }
}

Foreign Key Extras¶

{
  "type": "foreign_key",
  "table": "order_items",
  "columns": ["order_id"],
  "referenced_table": "orders",
  "referenced_columns": ["id"],
  "on_delete": "CASCADE",
  "on_update": "CASCADE",
  "deferrable": true
}

Constraint Diffing¶

Constraints (unique, check, foreign key, exclude) are compared by full attribute content. Any difference in signature: columns, expression, options, produces a DROP + ADD. Constraint name changes are detected as a new constraint (the old name is dropped, the new name is added).

FK comparison uses a 6-tuple signature: (columns, ref_table, ref_columns, on_delete, on_update, deferrable).

Reverse Engineering¶

generate-models queries pg_class, pg_attribute, pg_constraint, pg_inherits, pg_tablespace, pg_partitioned_table, and pg_collation to reverse-engineer all PostgreSQL metadata. The emitted model uses class Meta with PGTableMeta and PGColumnMeta inner classes.

$ dbwarden generate-models -d primary

Generated output for a table with identity, storage, compression, collation, and fillfactor:

from dbwarden.databases.pgsql import pg

class User(Base):
    __tablename__ = "users"

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

    class Meta(PGTableMeta):
        comment = "Core user accounts"
        pg_fillfactor = 80

        class id(PGColumnMeta):
            pg = pg.field(identity="always", identity_start=100, identity_increment=1)

        class bio(PGColumnMeta):
            pg = pg.field(storage="EXTENDED", compression="pglz", collation="en_US.UTF-8")

For a partitioned table, generate-models emits pg_partition:

class Event(Base):
    __tablename__ = "events"

    id: Mapped[int] = mapped_column(primary_key=True)
    created_at: Mapped[datetime] = mapped_column(DateTime)

    class Meta(PGTableMeta):
        pg_partition = {"strategy": "RANGE", "columns": ["created_at"]}

Unlogged tables, NO INHERIT check constraints, deferred unique constraints, and ALTER TYPE ... ADD VALUE for enums are all detected and emitted automatically.

Safety Classification¶

DBWarden classifies migration changes using the Safety enum:

from dbwarden.engine.safety import Safety

assert Safety.SAFE == "SAFE"
assert Safety.INFO == "INFO"
assert Safety.WARN == "WARN"
assert Safety.CRITICAL == "CRITICAL"