Drizzle Schema Builder

Drizzle ORM is a TypeScript-first SQL ORM that gives you full type safety without code generation. You define your schema in TypeScript using the Drizzle schema API, and it infers types automatically. It supports PostgreSQL, MySQL, and SQLite, with a query builder that closely mirrors SQL syntax.

pgTable is for PostgreSQL and uses Postgres-specific types like serial, uuid, jsonb, and timestamptz. mysqlTable is for MySQL/MariaDB and uses MySQL types like int auto_increment, varchar, and datetime. The column APIs are similar but types differ based on what each database natively supports.

You can mark any column as a primary key using .primaryKey() in Drizzle. For auto-incrementing integers, use serial('id').primaryKey() in PostgreSQL. For UUIDs, use uuid('id').defaultRandom().primaryKey(). Composite primary keys are defined using the primaryKey() function outside column definitions in the table's third argument.

By default, Drizzle columns are nullable (they accept NULL values). Adding .notNull() makes the column NOT NULL in the database, and changes the TypeScript type from 'string | null' to 'string'. This mirrors the SQL NOT NULL constraint and ensures data integrity at both the database and TypeScript levels.

Generate SQL migration files with 'drizzle-kit generate'. Then apply them with 'drizzle-kit migrate' (for postgres-js, mysql2, etc.) or 'db.push()' in development for quick schema sync. In production, always use migration files. Use drizzle-kit studio for a visual database browser.

Drizzle tables export $inferSelect and $inferInsert TypeScript types. $inferSelect infers the shape of rows returned by SELECT queries (all columns). $inferInsert infers the shape for INSERT operations, respecting optional fields with defaults. Use these as: type User = typeof users.$inferSelect.