Drizzle ORM Schema Review

Review Drizzle ORM schema definitions, relational queries, and migration files for problems that cause silent data loss, broken type inference, or production migration failures. Focus on footguns specific to Drizzle's SQL-like API that differ from traditional ORMs.

Schema Definition Rules

Every table must define its primary key explicitly. Drizzle does not auto-generate IDs. When a column uses .default(sql`gen_random_uuid()`) or similar, confirm the database actually supports that function. SQLite schemas using uuid defaults will fail silently at insert time.

Use the correct column type for the target dialect. pgTable, mysqlTable, and sqliteTable have different column builders. A schema using pgTable with .integer() for a boolean instead of .boolean() will compile but produce wrong query results. Flag any column type that does not match the semantic intent.

Relations defined with relations() are metadata only. They do not create foreign keys in the database. If the schema relies on relations() for referential integrity without a corresponding .references() on the column, flag this as Critical. The relation will work in relational queries but the database will accept orphaned rows.

Composite unique constraints belong in the table definition's third argument, not as separate calls. Misplaced constraints silently do nothing.

Relational Query Pitfalls

Drizzle's relational query API (db.query.users.findMany with: ...) infers return types from the with clause. Omitting columns in a nested with produces a type that looks complete but has undefined fields at runtime. When reviewing relational queries, verify that every accessed field in downstream code is actually selected.

The findFirst method without a where clause returns an arbitrary row, not null. If the intent is "find or return null," the query needs an explicit where. Flag bare findFirst calls as Warning.

Nested with clauses beyond two levels degrade to multiple sequential queries. If a relational query nests three or more levels, suggest restructuring as a raw SQL join using db.select().from().innerJoin() for predictable performance.

Query Builder and Prepared Statements

Drizzle's .prepare() caches the query plan but binds parameter positions at prepare time. If a prepared statement is constructed inside a function that changes the where clause shape conditionally (sometimes eq, sometimes and(eq, gt)), each call creates a new prepared statement, defeating the cache. Flag conditional query shapes inside prepare() as Warning and suggest splitting into separate prepared statements per shape.

The .$dynamic() modifier allows composable query building but breaks type inference when filters reference columns not in the base select. Verify that every column referenced in a dynamic filter is present in the from clause or a joined table.

When using db.insert().values() with an array, Drizzle generates a single INSERT with multiple value tuples. Arrays exceeding roughly 1000 rows can hit database parameter limits (PostgreSQL: 65535 parameters, SQLite: 999). Flag large batch inserts without chunking as Warning.

Migration Safety

drizzle-kit push is not safe for production. It applies schema changes directly without generating migration files and cannot be rolled back. Flag any CI/CD configuration or script that runs push against a production database as Critical. Use drizzle-kit generate followed by drizzle-kit migrate instead.

drizzle-kit generate produces SQL migration files. Review these files for destructive operations: DROP COLUMN, DROP TABLE, ALTER COLUMN ... TYPE (which can fail on populated columns). Drizzle does not warn about data loss in generated migrations. Every generated migration must be reviewed before applying.

Column renames generate a DROP COLUMN + ADD COLUMN pair by default. This destroys data. When a migration file contains this pattern, flag as Critical and suggest using the custom migration escape hatch to write ALTER TABLE ... RENAME COLUMN manually.

Adding a NOT NULL column without a default to an existing table with rows will fail at migration time. Drizzle generates the ALTER TABLE faithfully but does not check for existing data. Flag as Critical.

Index and Performance Concerns

Drizzle schemas define indexes via the third argument of the table function or via .index() in the schema. An index defined in the schema but not present in a generated migration means drizzle-kit did not detect the change. After adding indexes, always regenerate and verify the migration file contains CREATE INDEX.

Queries using .where(sql`...`) with raw SQL bypass Drizzle's type checking entirely. If the raw fragment references a column name as a string, a table rename will silently break the query. Prefer using the column reference (table.columnName) inside sql template literals: sql`${users.email} ILIKE ${pattern}`.

Severity Levels

Critical: data loss risk (column rename as drop/add, push to production, missing foreign key constraints assumed present, NOT NULL without default on populated table)
Warning: performance or correctness risk (unbounded batch insert, conditional prepare shapes, bare findFirst, deep relation nesting)
Suggestion: style or maintainability improvement (raw SQL column strings, misplaced unique constraints, index not reflected in migration)