| Schema definition | pgTable('name', { columns }, (t) => [indexes]) | Third arg returns array of indexes/constraints |
| Column types | text(), integer(), boolean(), timestamp() | Import from drizzle-orm/pg-core |
| Type inference | typeof table.$inferSelect, $inferInsert | Derive TS types directly from schema |
| Relational queries | db.query.table.findMany({ with, where }) | Requires schema passed to drizzle() client |
| SQL-like queries | db.select().from(table).where() | Chainable, returns array of rows |
| Insert | db.insert(table).values({}).returning() | .returning() for getting inserted rows |
| Update | db.update(table).set({}).where().returning() | Always include .where() to avoid full-table updates |
| Delete | db.delete(table).where() | Always include .where() to avoid full-table deletes |
| Upsert | .onConflictDoUpdate({ target, set }) | Chain after .insert().values() |
| Transactions | db.transaction(async (tx) => { ... }) | Auto-rollback on thrown errors |
| Filters | eq(), and(), or(), inArray(), sql\`` | Import operators from drizzle-orm |
| Relations | relations(table, ({ one, many }) => ({})) | Declares logical relations for relational queries |
| Generate migrations | drizzle-kit generate | Creates SQL migration files from schema diff |
| Apply migrations | drizzle-kit migrate or migrate() in code | Applies pending migrations to database |
| Push schema | drizzle-kit push | Direct schema push without migration files |
| Prepared statements | db.select().from(t).where(eq(t.id, sql.placeholder('id'))).prepare() | Reusable parameterized queries |
| Views | pgView('name').as(qb => ...) | Regular and materialized views |
| $count utility | db.$count(table, filter?) | Shorthand count, usable as subquery |
| Generated columns | text().generatedAlwaysAs(() => sql\...`)` | Computed columns (virtual or stored) |
| Check constraints | check('name', sql\condition`)` | Row-level validation at database level |
