PostgreSQL¶
DBWarden treats PostgreSQL as a first-class backend: every natively supported feature is reverse-engineered, diffed, and emitted as correct DDL.
"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.
Implementation note: PostgreSQL diffs and SQL emission flow through the dbwarden.engine.backends.postgresql.handlers handler package. The handler pipeline is described in the Architecture Deep Dive.
$ dbwarden generate-models -d primary --tables users,orders,items
$ dbwarden make-migrations
# -> "No changes detected"
Feature Matrix¶
| Category | Features |
|---|---|
| Identity Columns | GENERATED ALWAYS AS IDENTITY, GENERATED BY DEFAULT AS IDENTITY, sequence options |
| Collation | Per-column COLLATE via pg.field(collation=...) |
| Storage | Per-column STORAGE (PLAIN, MAIN, EXTERNAL, EXTENDED) |
| Compression | Per-column COMPRESSION (pglz, zstd) via pg.field(compression=...) (PG 14+) |
| Generated Columns | GENERATED ALWAYS AS (...) STORED |
| Table Properties | Fillfactor, storage params, tablespace, unlogged, partitioning, inheritance |
| Renames | Table rename, column rename |
| Constraints | FK (MATCH FULL/PARTIAL/SIMPLE, ON DELETE/UPDATE, DEFERRABLE), unique (NULLS NOT DISTINCT, INCLUDE, DEFERRABLE), check (NO INHERIT, NOT VALID), exclude |
| Indexes | B-tree, hash, GiST, GIN, BRIN, SP-GiST; partial, expression, INCLUDE, WHERE, opclasses, NULLS NOT DISTINCT, column sorting, CONCURRENTLY |
| RLS & Policies | ENABLE/DISABLE/FORCE/NO FORCE row-level security; permissive/restrictive, role-scoped policies |
| Enums | CREATE TYPE ... AS ENUM, ALTER TYPE ... ADD VALUE ... AFTER |
| Domains | CREATE DOMAIN with base type, default, NOT NULL, CHECK |
| Composite Types | CREATE TYPE ... AS (col1 type1, col2 type2, ...) |
| Sequences | CREATE SEQUENCE with all options |
| Functions | CREATE FUNCTION with language, arguments, body |
| Triggers | CREATE TRIGGER with timing, events, FOR EACH ROW/STATEMENT |
| Roles | CREATE ROLE with login, password, privileges |
| Default Privileges | ALTER DEFAULT PRIVILEGES per schema/role/object-type |
| Extended Statistics | CREATE STATISTICS with ndistinct, dependencies, MCV, expressions (PG 14+) |
| Event Triggers | CREATE EVENT TRIGGER for DDL events |
| Views | Regular CREATE OR REPLACE VIEW, materialized views with auto-refresh |
| Schema-level Grants | GRANT USAGE ON SCHEMA, GRANT ALL ON SCHEMA |
| Table Grants | GRANT SELECT/INSERT/UPDATE/DELETE |
| Type Mapping | SQLAlchemy type → PostgreSQL native type normalization |
| Storage Parameters | Table-level and index-level WITH options, autovacuum tuning |
Documentation Sections¶
- Config Keys : All 12
pg_*configuration keys - Declaring Metadata : Table-level, column-level, JSONB, FK options
- Tables & Columns : Column handler, type changes, auto-increment lifecycle
- Registry Architecture : Handler map, phases, online and offline diff flow
- Constraints : FK (MATCH FULL, CASCADE), unique, check, exclude
- Indexes : B-tree, partial, expression indexes, operator classes, NULLS NOT DISTINCT
- Types : Enums, domains, composite types
- Functions & Triggers : Function and trigger lifecycle
- RLS & Policies : Row-level security, policy lifecycle, FORCE
- Grants & Roles : Table grants, schema grants, roles, default privileges
- Partitioning : RANGE/LIST/HASH partition strategies, attach/detach
- Views : Regular and materialized views, auto-refresh
- Sequences : Sequence creation and ownership
- Extended Statistics : ndistinct, dependencies, MCV, expressions
- Event Triggers : DDL event trigger lifecycle
- Schemas : Config-level and model-level schemas, search path
- DDL Behavior : Transactional DDL, CONCURRENTLY, type change strategies
- Type Mapping : SQLAlchemy type → PostgreSQL type normalization
- Storage Parameters : Table and index storage parameters, autovacuum tuning
- Migration Safety : Safety classification table