Type Mapping¶

DBWarden normalizes SQLAlchemy column types to PostgreSQL native types during snapshot extraction and DDL generation.

Standard SQLAlchemy Types¶

SQLAlchemy Type	PostgreSQL Type	Notes
`Integer`	`INTEGER`
`BigInteger`	`BIGINT`
`SmallInteger`	`SMALLINT`
`String(n)`	`VARCHAR(n)`
`Text`	`TEXT`
`Unicode(n)`	`VARCHAR(n)`
`UnicodeText`	`TEXT`
`Boolean`	`BOOLEAN`
`DateTime`	`TIMESTAMP WITHOUT TIME ZONE`
`Date`	`DATE`
`Time`	`TIME WITHOUT TIME ZONE`
`Float`	`FLOAT`
`Double` / `DOUBLE_PRECISION`	`DOUBLE PRECISION`
`Numeric(p, s)`	`NUMERIC(p, s)`
`LargeBinary`	`BYTEA`
`PickleType`	`BYTEA`
`JSON`	`JSON`
`ARRAY(Type)`	`type[]`	e.g. `ARRAY(String)` → `text[]`
`Enum(*members)`	`CREATE TYPE ... AS ENUM`	Auto-creates enum type

PostgreSQL Dialect-Specific Types¶

These types from sqlalchemy.dialects.postgresql map directly to their PostgreSQL equivalents:

SQLAlchemy Type	PostgreSQL Type	Notes
`JSONB`	`JSONB`	Binary JSON, supports GIN indexes
`TIMESTAMP`	`TIMESTAMP WITHOUT TIME ZONE`
`TIMESTAMPTZ`	`TIMESTAMP WITH TIME ZONE`
`TIME`	`TIME WITHOUT TIME ZONE`
`TIMETZ`	`TIME WITH TIME ZONE`
`INTERVAL`	`INTERVAL`
`UUID`	`UUID`
`BYTEA`	`BYTEA`
`OID`	`OID`
`REGCLASS`	`REGCLASS`
`TEXT`	`TEXT`
`BOOLEAN`	`BOOLEAN`
`CIDR`	`CIDR`	IPv4/IPv6 network
`INET`	`INET`	IPv4/IPv6 host address
`MACADDR`	`MACADDR`	MAC address
`MACADDR8`	`MACADDR8`	MAC address (EUI-64)
`MONEY`	`MONEY`	Currency amount
`TSVECTOR`	`TSVECTOR`	Full-text search document
`TSQUERY`	`TSQUERY`	Full-text search query
`INT4RANGE`	`INT4RANGE`	Range of integer
`INT8RANGE`	`INT8RANGE`	Range of bigint
`NUMRANGE`	`NUMRANGE`	Range of numeric
`DATERANGE`	`DATERANGE`	Range of date
`TSTZRANGE`	`TSTZRANGE`	Range of timestamptz
`TSRANGE`	`TSRANGE`	Range of timestamp
`BIT(n)`	`BIT(n)`	Fixed-length bit string
`VARBIT(n)`	`VARBIT(n)`	Variable-length bit string
`XML`	`XML`	XML data
`ARRAY(type, dimensions)`	`type[]`	Multi-dimensional array
`ENUM(*members)`	`CREATE TYPE ... AS ENUM`	Named enum (creates persistent type)

Auto-increment Normalization¶

Condition	Resulting Type	Sequence Behavior
`Integer` + `autoincrement=True`	`SERIAL`	Auto-creates `tablename_colname_seq`
`BigInteger` + `autoincrement=True`	`BIGSERIAL`	Auto-creates sequence
`Integer` + `autoincrement=False`	`INTEGER`	No sequence
`BigInteger` + `autoincrement=False`	`BIGINT`	No sequence
`Integer` (unspecified autoincrement)	`SERIAL`	Backward compatible
`GENERATED ALWAYS AS IDENTITY`	`INTEGER`	Creates implicit sequence
`GENERATED BY DEFAULT AS IDENTITY`	`INTEGER`	Creates implicit sequence

See DDL Behavior for the full lifecycle.

Type Normalization Details¶

SERIAL / BIGSERIAL¶

SERIAL and BIGSERIAL are syntactic sugar for INTEGER / BIGINT with an auto-created sequence and a DEFAULT nextval(...) expression. DBWarden normalizes them during reverse-engineering:

On input (generate-models): a column typed INTEGER with nextval('seq'::regclass) default is normalized to Integer(autoincrement=True)
On output (make-migrations): a column with autoincrement=True emits SERIAL / BIGSERIAL in CREATE TABLE

TIMESTAMP / TIMESTAMPTZ¶

SQLAlchemy Type	Normalized DDL
`DateTime`	`TIMESTAMP WITHOUT TIME ZONE`
`TIMESTAMP`	`TIMESTAMP WITHOUT TIME ZONE`
`TIMESTAMPTZ`	`TIMESTAMP WITH TIME ZONE`

NUMERIC Precision¶

Numeric(10, 2) emits NUMERIC(10, 2). Without precision: NUMERIC.

JSONB vs JSON¶

JSON → JSON (stores exact copy of input text)
JSONB → JSONB (stores decomposed binary, supports indexing)

ARRAY Handling¶

ARRAY(String) emits text[]. ARRAY(Integer) emits integer[]. Multi-dimensional arrays preserve dimensions:

SQLAlchemy	PostgreSQL
`ARRAY(String)`	`text[]`
`ARRAY(Integer, dimensions=2)`	`integer[][]`
`ARRAY(JSONB)`	`jsonb[]`

Range Types¶

Range types accept Range objects in Python. DBWarden preserves the range type variant:

SQLAlchemy	PostgreSQL	Example Value
`INT4RANGE`	`INT4RANGE`	`[1, 10)`
`TSTZRANGE`	`TSTZRANGE`	`["2024-01-01", "2024-12-31")`
`DATERANGE`	`DATERANGE`	`[2024-01-01, 2024-12-31)`

Enum Normalization¶

SQLAlchemy Enum types with create_constraint=True are extracted as CREATE TYPE statements. Enum members are tracked positionally so new values are added with AFTER to preserve ordering.

Domain-Based Columns¶

When a column uses a domain type (e.g., us_postal_code), DBWarden preserves the domain type name in the snapshot rather than expanding to the base type. See Types for domain lifecycle.