pkglog
KO

Drizzle ORM Basics: TypeScript Schemas, Type-Safe Queries and drizzle-kit Migrations

By · · 17 min read · Updated Intermediate tutorial

Key takeaways

Drizzle ORM defines your schema directly in TypeScript — no separate .prisma files and no code-generation step — and compiles down to SQL you can read at a glance. This guide walks through why that design exists, how schema definitions turn into real database migrations, what the type-safety guarantees actually cover (and where they stop), and the mistakes beginners make most often when setting up their first Drizzle project.

Why Drizzle?

Most TypeScript ORMs ask you to describe your data model twice: once in a dedicated schema language, and once implicitly through the generated client types you actually import in application code. Prisma is the clearest example — you write a .prisma file in its own DSL, run prisma generate, and a code-generation step produces the TypeScript client and types your app imports. That workflow is polished, but it adds a build step that has to run (and stay in sync) every time the schema changes, and it means learning a second, Prisma-specific syntax alongside TypeScript itself.

// Prisma approach — separate .prisma schema language
// model User {
//   id    Int    @id @default(autoincrement())
//   email String @unique
// }

// Drizzle approach — schema in TypeScript
import { pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name').notNull(),
});

// The schema IS the types — no generation step needed for type safety

Drizzle collapses that into one step: the pgTable(...) call above is both the runtime description of the table (what drizzle-kit reads to generate SQL migrations) and the compile-time source of your TypeScript types (via typeof users.$inferSelect, covered later in this guide). There is no separate client to generate and no .prisma syntax to learn — if you already know TypeScript, you already know most of what you need to define a schema. The trade-off is that Drizzle stays closer to raw SQL by design: instead of an abstraction like Prisma’s .include() that hides the join it produces, Drizzle’s query builder mirrors SQL clauses (.where(), .leftJoin(), .groupBy()) closely enough that you can usually predict the exact statement it will run before executing it.

Drizzle advantages:

  • Schema in TypeScript — one language, one file, no build step to generate types
  • SQL-first — you can predict, and inspect, exactly what SQL runs
  • Zero query-engine overhead — no native binary or sidecar process to bundle or cold-start
  • Works everywhere — Node.js, serverless, edge runtimes, Bun, Deno
  • Smallest bundle of any mainstream TypeScript ORM (roughly 30KB vs. Prisma’s client + engine, which commonly adds tens of megabytes)

That smaller footprint and the absence of a query-engine process are what make Drizzle a natural fit for serverless and edge deployments, where every megabyte and every cold-start millisecond is visible in cost and latency. This guide covers the fundamentals — installing Drizzle, defining a schema, running your first migration, and writing CRUD queries. For production concerns that only surface under real traffic — transaction isolation levels, connection pooling per deployment target, and migration patterns that avoid locking large tables — see the Drizzle ORM Advanced Guide, which picks up exactly where this guide leaves off.

Setup

# PostgreSQL
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

# Or with postgres.js (no types needed)
npm install drizzle-orm postgres
npm install -D drizzle-kit
// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',    // Your schema file(s)
  out: './drizzle',                // Migration output directory
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

Two packages are involved here, and it’s worth being clear about the split before writing any schema code. drizzle-orm is the runtime library your application imports — it builds and executes queries against a live connection. drizzle-kit is a separate development-time CLI that never ships to production; its only job is reading drizzle.config.ts, comparing your schema file against the database’s migration history, and generating or applying SQL files. Installing drizzle-kit as a dev dependency (-D) rather than a regular dependency is deliberate — your production bundle has no reason to include a migration-diffing tool.

The drizzle.config.ts file is the map drizzle-kit uses to find your schema and know where to write migrations. schema points at the TypeScript file (or glob of files) where your tables are defined; out is the directory that will accumulate a numbered SQL migration file per change. Get either path wrong and drizzle-kit generate will either fail outright or silently generate an empty migration, which is a common source of “I edited my schema and nothing happened” confusion for newcomers — always double-check these two paths first if a migration doesn’t show the change you expected.

Schema Definition

// src/db/schema.ts
import {
  pgTable, serial, text, varchar, integer, boolean,
  timestamp, pgEnum, uniqueIndex, index
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// Enum
export const roleEnum = pgEnum('role', ['admin', 'editor', 'viewer']);

// Users table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name').notNull(),
  role: roleEnum('role').default('viewer').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}));

// Posts table
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 500 }).notNull(),
  content: text('content'),
  published: boolean('published').default(false).notNull(),
  authorId: integer('author_id').notNull().references(() => users.id, {
    onDelete: 'cascade',
  }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  authorIdx: index('posts_author_idx').on(table.authorId),
  publishedIdx: index('posts_published_idx').on(table.published, table.createdAt),
}));

// Tags table
export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull().unique(),
});

// Many-to-many join table
export const postTags = pgTable('post_tags', {
  postId: integer('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
});

// Relations (for ORM-style queries with .with())
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  postTags: many(postTags),
}));

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, { fields: [postTags.postId], references: [posts.id] }),
  tag: one(tags, { fields: [postTags.tagId], references: [tags.id] }),
}));

Every pgTable(...) call has the same three-part shape worth internalizing early: a database table name as a string ('users'), a map of column definitions, and an optional third argument for table-level configuration like indexes. The column helpers (serial, varchar, boolean, timestamp, and so on) are functions, not types — each one returns a column-builder object that you can chain modifiers onto (.notNull(), .unique(), .default(...), .references(...)), and the final shape of that chain is what Drizzle uses to infer both the SQL column type and the TypeScript type simultaneously. This is the mechanism behind the “schema IS the types” claim from the introduction: there is no separate step that reads schema.ts and writes a .d.ts file — the types exist because pgTable is a generic function whose return type TypeScript itself computes from its arguments.

A beginner mistake worth flagging here is skipping the relations() calls at the bottom of the file, on the assumption that .references(() => users.id) inside the column definition is enough. It isn’t, for two different purposes. The .references() call inside pgTable is what actually creates the SQL foreign key constraint — it’s what drizzle-kit generate turns into REFERENCES users(id) in the migration file, and it’s what the database enforces. The separate relations() block, by contrast, produces no SQL at all; it’s a client-side declaration that tells Drizzle’s relational query API (the db.query.users.findFirst({ with: { posts: true } }) style used later in this guide) how tables connect, purely so it knows which follow-up queries to run. Define a foreign key without the matching relations() block and the database-level constraint still works fine, but db.query.users.findFirst({ with: { posts: true } }) will fail to compile because TypeScript has no record that posts is a valid relation on users. The two declarations look redundant but serve genuinely different layers — one is a database constraint, the other is a query-builder feature — and both matter for a schema with .with() queries in it.

The pgEnum at the top is a small but real design decision, not boilerplate. Baking role into a Postgres enum means the database itself rejects an invalid value at the INSERT/UPDATE level — 'superadmin' fails with a constraint error before it ever reaches your application logic — while also giving you a narrowed TypeScript union ("admin" | "editor" | "viewer") instead of a bare string everywhere role is used. The cost is that adding a new enum value later requires its own migration (ALTER TYPE role ADD VALUE ...), which on Postgres cannot run inside the same transaction as other schema changes in older versions — a detail worth knowing before reaching for an enum on a column you expect to grow new values frequently; a varchar with an application-level union type is sometimes the more flexible choice for those.

Database Connection

// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });

// Infer types from schema
export type User = typeof schema.users.$inferSelect;
export type NewUser = typeof schema.users.$inferInsert;
export type Post = typeof schema.posts.$inferSelect;
export type NewPost = typeof schema.posts.$inferInsert;

drizzle() is a thin wrapper, not a connection manager in its own right — notice that the actual TCP connection comes from postgres(process.env.DATABASE_URL!), a plain driver call that has nothing Drizzle-specific about it. Passing { schema } as the second argument is what unlocks the relational query API (db.query.users.findFirst(...)) used further down; without it, db still works for db.select()/db.insert()/etc., but db.query is undefined. This is a second common point of confusion for newcomers coming from Prisma, where the schema is baked into the generated client automatically — in Drizzle, wiring the schema into drizzle(...) is an explicit step you have to remember.

The two type aliases at the bottom ($inferSelect and $inferInsert) exist because a row you read back from the database and a row you’re about to write are not the same shape. $inferSelect reflects what a SELECT returns — every column present, id and createdAt included, since the database already generated them. $inferInsert reflects what INSERT requires — columns with a .default(...) or that are serial become optional, since the database (or Drizzle) will fill them in if you omit them, while .notNull() columns without a default stay required. Using NewUser (an insert type) where you actually need User (a select type) is a subtle bug source: TypeScript will happily let you build an object missing id and pass it somewhere expecting a full row, because the insert type made id optional.

CRUD Queries

Insert

import { db } from './db';
import { users, posts } from './db/schema';

// Insert one
const [user] = await db.insert(users).values({
  email: '[email protected]',
  name: 'Alice',
  role: 'admin',
}).returning();  // Returns the created row(s)

// Insert many
await db.insert(users).values([
  { email: '[email protected]', name: 'Bob' },
  { email: '[email protected]', name: 'Carol' },
]);

// Upsert (insert or update on conflict)
await db.insert(users).values({
  email: '[email protected]',
  name: 'Alice Updated',
}).onConflictDoUpdate({
  target: users.email,
  set: { name: 'Alice Updated' },
});

// Upsert — do nothing on conflict
await db.insert(tags).values({ name: 'typescript' })
  .onConflictDoNothing();

.insert(users).values({...}) compiles to exactly one INSERT INTO users (...) VALUES (...) statement — there’s no ORM-level magic inserting related rows or triggering hooks behind the scenes, which is exactly the “you can predict the SQL” property from the introduction. .returning() maps to Postgres’s RETURNING clause; without it, db.insert(...) resolves to a generic result object with no row data, which is a common first mistake — forgetting .returning() and then trying to read user.id from the insert result. The array-destructuring pattern (const [user] = await ...) is necessary because .values() always accepts (and .returning() always returns) an array, even for a single row.

.onConflictDoUpdate() and .onConflictDoNothing() compile to Postgres’s ON CONFLICT ... DO UPDATE / ON CONFLICT ... DO NOTHING, which means the target you pass must correspond to an actual unique constraint or index on that column — users.email works here specifically because the schema defined uniqueIndex('users_email_idx').on(table.email) earlier. Passing a target column with no unique constraint behind it produces a Postgres error at query time (there is no unique or exclusion constraint matching the ON CONFLICT specification), not a TypeScript compile error, since Drizzle can’t verify database-side constraints at the type level — this is one of the concrete limits on Drizzle’s “full type safety” claim worth keeping in mind.

Select

import { eq, and, or, gte, lte, like, desc, asc, count, sql } from 'drizzle-orm';

// Select all
const allUsers = await db.select().from(users);

// Select specific fields
const userNames = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

// Where conditions
const adminUsers = await db.select()
  .from(users)
  .where(eq(users.role, 'admin'));

const recentPublishedPosts = await db.select()
  .from(posts)
  .where(
    and(
      eq(posts.published, true),
      gte(posts.createdAt, new Date('2026-01-01'))
    )
  )
  .orderBy(desc(posts.createdAt))
  .limit(10)
  .offset(0);

// Search with LIKE
const searchResults = await db.select()
  .from(posts)
  .where(like(posts.title, '%typescript%'));

// Join
const postsWithAuthors = await db.select({
  postId: posts.id,
  title: posts.title,
  authorName: users.name,
  authorEmail: users.email,
})
.from(posts)
.leftJoin(users, eq(posts.authorId, users.id))
.where(eq(posts.published, true))
.orderBy(desc(posts.createdAt));

The operator functions imported at the top (eq, and, gte, like, …) are the part of Drizzle’s design that most directly explains its SQL-first philosophy: rather than accepting a string or an object-shaped filter ({ role: { equals: 'admin' } }), Drizzle composes .where() conditions from plain function calls that each return a SQL fragment, and and(...)/or(...) combine those fragments the same way parentheses combine boolean logic in SQL itself. The upside is that the shape of .where(and(eq(...), gte(...))) maps almost mechanically onto the WHERE ... AND ... it generates, so once you know a handful of these functions (eq, ne, gt, gte, lt, lte, like, ilike, isNull, inArray) you can read most Drizzle queries as SQL without needing to check documentation.

Notice that .select({...}) with an explicit object — as in userNames and postsWithAuthors above — both narrows the columns fetched over the wire and narrows the TypeScript return type to exactly those keys, whereas bare .select() returns every column typed as the full row. Selecting only what a given code path actually uses is not just a minor optimization; on a table with a large text/jsonb column (like posts.content), fetching it in a list view you never render is wasted bandwidth on every request. The leftJoin example is a plain SQL join with no relation configuration required — this works even without a relations() block, since it’s built entirely from .from()/.leftJoin()/.where() calls rather than the relational query API described next.

Relations (with)

// Use .query for ORM-style nested queries (requires relations config)
const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: desc(posts.createdAt),
      limit: 5,
    },
  },
});

// userWithPosts.posts is typed as Post[]

const allUsersWithPostCount = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

This is the relational query API mentioned earlier, and it only works because two things are true: drizzle(client, { schema }) was called with the schema object, and the relations() blocks from the schema file are part of that same schema object. Under the hood, db.query.users.findFirst({ with: { posts: true } }) doesn’t produce a single flat SQL join — it fetches the matching user row, then issues a follow-up query for the related posts (typically using WHERE author_id IN (...)), and stitches the two result sets back into the nested, correctly-typed object you see in application code. That’s a deliberate design choice: a flat SQL join between users and posts would repeat every user column once per post row, which is exactly the kind of manual de-duplication the relational API exists to avoid. The Drizzle ORM Advanced Guide goes deeper into when this convenience is worth the extra round-trips versus writing the join yourself.

Update

// Update with returning
const [updated] = await db.update(users)
  .set({ name: 'Alice Smith', updatedAt: new Date() })
  .where(eq(users.id, 1))
  .returning();

// Atomic increment using sql template
await db.update(posts)
  .set({ viewCount: sql`${posts.viewCount} + 1` })
  .where(eq(posts.id, 1));

.update(...).set(...) without a .where(...) clause updates every row in the table — Drizzle does not require a where the way some query builders do as a safety rail, so it’s worth treating a missing .where() on an update or delete as a code-review red flag rather than assuming a mistake there is impossible. The sql\${posts.viewCount} + 1`pattern in the second example is the escape hatch for expressions that reference the column's *current* database value rather than a value computed in application code — readingviewCount, adding 1 in JavaScript, and writing it back would race under concurrent requests (two simultaneous increments could both read the same starting value and both write the same result, losing one increment), while viewCount = viewCount + 1` evaluated inside the database is atomic per row.

Delete

await db.delete(users).where(eq(users.id, 1));

// Delete with returning
const [deleted] = await db.delete(posts)
  .where(eq(posts.id, 1))
  .returning();

The same missing-.where() risk from update applies here, with a less recoverable consequence — db.delete(users) with no filter deletes every row in the table. Because posts.authorId was defined with .references(() => users.id, { onDelete: 'cascade' }) in the schema above, deleting a user also cascades to delete every post they authored at the database level, without any application code orchestrating it. That cascade is convenient for keeping the database consistent, but it also means a single unfiltered or mistakenly-scoped delete on users can silently take out related posts and post_tags rows too — worth remembering before running a one-off delete query against a production database, and part of why soft deletes (covered in the Advanced Guide) are often preferred for user-facing data.

Transactions

// Atomic transaction
const result = await db.transaction(async (tx) => {
  // All operations use tx instead of db
  const [newUser] = await tx.insert(users).values({
    email: '[email protected]',
    name: 'Alice',
  }).returning();

  await tx.insert(posts).values({
    title: 'First Post',
    content: 'Hello!',
    authorId: newUser.id,
  });

  return newUser;
});
// If any operation throws, the entire transaction rolls back

A transaction groups multiple statements so they either all succeed or all roll back together — here, creating a user and their first post either both happen or neither does, which matters because a partially-applied write (a user row with no post, or a post referencing a user that was somehow rolled back) is exactly the kind of inconsistent state a relational database is supposed to prevent. The mechanical rule to remember is that every operation inside the callback must use the tx parameter, not the outer db — calling db.insert(...) instead of tx.insert(...) inside a transaction callback runs that statement on a separate connection outside the transaction entirely, which silently defeats the atomicity guarantee and is an easy typo to make when copying code between transactional and non-transactional contexts. This guide covers the basic all-or-nothing case; picking a transaction isolation level deliberately, retrying serialization failures, and understanding savepoints for nested transactions are production-scale concerns covered in the Drizzle ORM Advanced Guide.

Raw SQL

import { sql } from 'drizzle-orm';

// Type-safe raw query
const result = await db.execute<{ id: number; name: string }>(
  sql`SELECT id, name FROM users WHERE created_at > ${new Date('2026-01-01')}`
);

// Use raw SQL in queries
const usersWithCount = await db.select({
  user: users,
  postCount: sql<number>`count(${posts.id})`,
})
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId))
.groupBy(users.id);

Not every query fits neatly into .select()/.where()/.groupBy() chains — window functions, database-specific functions, and complex aggregates often don’t have a first-class builder method. The sql tagged template is Drizzle’s escape hatch for exactly that case, and it’s worth understanding precisely what “type-safe” means here, since it’s easy to overstate. The ${...} interpolations inside sql\…`are automatically parameterized —new Date(‘2026-01-01’)becomes a bound query parameter, not a string concatenated into the SQL text, which is what prevents SQL injection. Whatsqldoes *not* do is verify that the SQL text itself is syntactically valid or that the columns you reference exist;db.execute<{ id: number; name: string }>(…) only tells TypeScript what shape to *assume* the result has — that generic type argument is an assertion you're making, not something Drizzle checks against the actual query. If the SQL string has a typo in a column name, or the real result columns don't match the type argument, that mismatch surfaces at runtime (a database error, or silently wrong-shaped data), not at compile time. This is the concrete limit on Drizzle's type-safety guarantees worth keeping in mind: full type inference and compile-time checking apply to the query builder methods (.select(), .where(), .insert(), ...), not to raw sql` fragments, which trade some safety for full SQL expressiveness.

Migrations with drizzle-kit

# Generate migration from schema changes
npx drizzle-kit generate

# Output: drizzle/0001_add_posts_view_count.sql
# ALTER TABLE posts ADD COLUMN view_count INTEGER DEFAULT 0 NOT NULL;

# Apply migrations (development)
npx drizzle-kit migrate

# Push schema directly (development only — no migration files)
npx drizzle-kit push

# Open Drizzle Studio (visual DB browser)
npx drizzle-kit studio

This is the step where “schema in TypeScript” actually turns into real changes in a real database, and it’s the most common place beginners get stuck — not because the commands are complicated, but because it’s easy to forget that editing schema.ts alone changes nothing in the database. Drizzle does not watch your schema file; pgTable(...) calls only describe intent until a separate command reads that intent and produces SQL.

flowchart LR
    A["Edit schema.ts\n(add/change a table or column)"] --> B["npx drizzle-kit generate\n(diffs schema vs. migration history)"]
    B --> C["New SQL file written\nto ./drizzle/NNNN_name.sql"]
    C --> D{"Review the SQL\nby hand"}
    D -->|looks correct| E["npx drizzle-kit migrate\n(applies to the database)"]
    D -->|unexpected statement| A
    E --> F["Database schema updated;\napp's TypeScript types already matched schema.ts"]

drizzle-kit generate is a diffing tool: it compares the current shape of schema.ts against the migration files already generated, and writes only the SQL needed to close that gap — a new ALTER TABLE, a new CREATE TABLE, and so on — as a timestamped .sql file under the out directory from drizzle.config.ts. That file is plain, readable SQL, not a binary format or a serialized diff, which is deliberate: you’re expected to open and read it before applying it, especially once a schema is live in production. drizzle-kit migrate then applies any migration files that haven’t been run yet, tracking which ones have already been applied in a metadata table inside the target database itself, so re-running migrate against a database that’s already up to date is a no-op rather than an error.

drizzle-kit push is a different workflow aimed specifically at local development: instead of generating a SQL file you review and commit, push diffs your schema against the live database and applies the difference directly, with no migration file left behind. This is convenient for fast local iteration — add a column, run push, keep working — but it means there’s no historical record of how the schema got to its current shape, which is exactly what you want during migrations to a production or shared database, where a reviewable, committed SQL file (and the ability to know exactly what ran, in what order, across every environment) matters far more than iteration speed.

A second beginner pitfall worth naming explicitly: a relation defined in the wrong direction, or with fields/references swapped, does not fail at generate time — the SQL foreign key still comes from .references() in the column definition, and relations() (as covered earlier) produces no SQL at all. A swapped relations() block instead fails later, either as a confusing TypeScript error when you try to use .with(), or — more dangerously — as a query that runs successfully but returns the wrong data, because Drizzle trusts the fields/references mapping you wrote rather than verifying it against the actual foreign key. Double-checking a new relations() block by actually running a .with() query against it (rather than only checking that it compiles) is a cheap way to catch this before it ships.

Edge / Serverless (Neon + Cloudflare)

// Neon (PostgreSQL serverless) — works in edge environments
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

// Cloudflare D1 (SQLite at the edge)
import { drizzle } from 'drizzle-orm/d1';

export function createDB(d1: D1Database) {
  return drizzle(d1);
}

// Cloudflare Workers handler
export default {
  async fetch(request: Request, env: Env) {
    const db = createDB(env.DB);
    const users = await db.select().from(usersTable);
    return Response.json(users);
  }
};

The pattern to notice here is that switching deployment target is a matter of swapping the driver import (drizzle-orm/neon-http, drizzle-orm/d1, drizzle-orm/postgres-js, …) rather than rewriting query code — db.select().from(users) looks identical whether db was built from a long-lived pg.Pool or a stateless HTTP call to Neon. That portability is a direct consequence of Drizzle’s driver-adapter design: the query builder and schema definitions are database-family-specific (Postgres vs. MySQL vs. SQLite), but not connection-strategy-specific. What does change meaningfully between these targets is connection pooling behavior — a traditional pg.Pool and a serverless HTTP driver need opposite sizing strategies, and getting this wrong is a common source of “works in development, exhausts connections in production” incidents. That topic is large enough to deserve its own treatment; see the connection pooling section of the Drizzle ORM Advanced Guide before deploying a Drizzle app to a serverless or edge target.

Type Inference

// Infer types directly from schema — no duplication
import { InferSelectModel, InferInsertModel } from 'drizzle-orm';

type User = InferSelectModel<typeof users>;
// { id: number; email: string; name: string; role: "admin" | "editor" | "viewer"; createdAt: Date; }

type NewUser = InferInsertModel<typeof users>;
// { email: string; name: string; role?: "admin" | "editor" | "viewer"; createdAt?: Date; }

// Or use the $ shorthand
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

InferSelectModel/InferInsertModel and the $inferSelect/$inferInsert shorthand are two spellings of the same underlying mechanism introduced earlier — both read the type information already present on the pgTable(...) object and produce a plain TypeScript type from it, with no code generation and nothing to keep in sync manually. This is the concrete payoff of Drizzle’s “the schema is the types” design: rename a column in schema.ts, and every place in your codebase that used User['oldName'] fails to compile immediately, the same day, rather than surfacing as a runtime undefined after a forgotten prisma generate. The guarantee is strong for structural correctness — column names, nullability, and enum unions are always accurate — but it’s still worth remembering the raw-SQL boundary from earlier: types inferred this way only cover code that goes through the schema-aware query builder, not sql\…`fragments or hand-written strings passed todb.execute()`.

Common Beginner Pitfalls

A short checklist worth keeping nearby while setting up a first Drizzle project, gathered from the mistakes called out throughout this guide:

  • Editing schema.ts and expecting the database to update itself. It won’t — run drizzle-kit generate (and then migrate, or push in development) after every schema change. Drizzle infers types from your schema file instantly, which can create the illusion that the database is already in sync when it isn’t.
  • Defining a foreign key without a matching relations() block, or vice versa. .references() creates the real database constraint; relations() only enables .with() queries. They’re independent and both need to be kept correct.
  • Forgetting .returning() after insert/update/delete when the created or modified row is needed — without it, Drizzle gives back a generic result object, not the row data.
  • Running .update() or .delete() without a .where() clause. Drizzle does not guard against this the way some frameworks do; a missing filter updates or deletes every row in the table.
  • Treating sql\…`results as fully type-checked.** The generic type argument ondb.execute()` is an assertion, not a verification — a typo in the raw SQL still only surfaces at runtime.
  • Using push instead of generate + migrate against a shared or production database, losing the reviewable migration history that matters once more than one person or environment depends on the schema.

Drizzle vs Prisma Quick Comparison

FeatureDrizzlePrisma
Schema languageTypeScriptPrisma DSL (.prisma)
Bundle size~30KB~10MB
Edge supportNativeLimited (Data Proxy)
Migration tooldrizzle-kitprisma migrate
Visual Studiodrizzle studioPrisma Studio
RelationsExplicit joins or .with().include(), .select()
Raw SQLsql template tag$queryRaw
Type safetyFull for the query builder; raw SQL is an assertion, not a checkFull for the generated client

Next Steps

Once schema definition, basic CRUD, and your first migration feel comfortable, the questions that come up next are the ones that only matter once an application has real users and real traffic: which transaction isolation level actually protects a given invariant, when the relational query API’s convenience starts costing more round-trips than it’s worth, how a migration that looks harmless locally can lock a production table for minutes, and how connection pooling has to differ between a long-lived server and a serverless function. The Drizzle ORM Advanced Guide covers all of that in depth, building directly on the schema and queries introduced here.

Frequently Asked Questions (FAQ)

Q. I ran drizzle-kit generate and nothing changed — what’s wrong?

A. Check drizzle.config.ts first: schema must point at the actual file where your pgTable(...) definitions live, and out is where migration files get written. A mismatch there is the most common reason generate silently produces nothing. Also confirm you saved schema.ts — since Drizzle doesn’t watch the file, generate only ever compares against whatever was last saved to disk.

Q. push or generate + migrate — which should I use day to day?

A. Use push for fast local iteration on a personal development database, where losing migration history doesn’t matter. Use generate + migrate for anything shared — staging, production, or any database another teammate also connects to — because generate leaves behind a reviewable, committed SQL file that records exactly what changed and in what order, which push does not.

Q. Where should I go after this guide?

A. The Drizzle ORM Advanced Guide covers transaction isolation levels, the relational-query-vs-join trade-off in more depth, migration patterns that avoid locking large production tables, and connection pooling per deployment target — all assuming the fundamentals covered here.