Understanding SQL DDL → models
CREATE TABLE into Prisma, SQLAlchemy, ActiveRecord.
What introspection generates, the gaps codegen leaves, and the database-first vs code-first decision.
The two directions.
Database-first: the SQL schema is the source of truth; ORM models are generated by introspecting the live database. Code-first: the ORM models are the source of truth; DDL is generated via migrations. Both work; the choice is organisational. Teams with a strong DBA practice usually pick database-first; teams where backend engineers own the database go code-first. Either way, the tool that converts between them is essential.
What introspection sees.
Tables, columns, types, constraints, indexes, foreign keys — all queryable frominformation_schema. Views are visible. Materialized views are visible in Postgres. Triggers and stored procedures are visible but rarely modelled by the ORM. Sequences, comments, table inheritance — partly supported by tooling. The 80 % that ORMs care about is fully introspectable; the remaining 20 % needs manual annotation.
What gets generated.
Prisma: a schema.prisma file with model User { id Int @id, ... }declarations. SQLAlchemy: Python classes inheriting from Base, columns as ClassVar fields. ActiveRecord: Ruby classes with associations inferred from FK names. Each preserves the type, primary key, foreign keys, and indexes. The generated code is usable directly; teams often hand-edit to add validations and business logic.
A worked introspection.
DDL: CREATE TABLE users (id BIGSERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT now()). Prisma output: model User { id BigInt @id @default(autoincrement()), email String @unique, createdAt DateTime @default(now()) @map("created_at") }. The snake_case → camelCase mapping happens automatically via @map. Pull the FK relations from information_schema.referential_constraints and the resulting model is complete.
users table → Prisma model
DDL → schema.prisma
Type + PK + unique + default.
BIGSERIAL → BigInt @id @default(autoincrement())
= Ready to query
The gaps.
Composite primary keys are supported by most ORMs but awkward in code-first definitions. Partial indexes (Postgres-only) are visible at introspection but ignored by Prisma. Check constraints translate inconsistently. JSON column shapes aren't typed — you cast the JSON value to a TS type at the query site. Stored procedures and triggers are not modelled; they're invisible from the ORM's perspective. Manual @db.* hints fill in some gaps.
Keep one source of truth.
The failure mode: people hand-edit both the SQL and the model file. Drift is guaranteed; debugging gets miserable. Either run database-first (CI regenerates models on every migration), or code-first (CI generates migrations from model changes). The tool — pulling DDL into models — is supposed to be one direction, run on a schedule, with the output committed. Maintain the cycle and the rest works.