Most ORMs make you a deal: give up control of your SQL in exchange for nice-looking TypeScript. The problem is you eventually hit a query the ORM can’t express cleanly, and you spend the next two hours stuffing raw SQL into an escape hatch while the type system shrugs.
Drizzle takes a different position. It’s a TypeScript-first query builder that treats SQL as a first-class citizen — and the result is that the types you get out actually reflect the SQL you wrote, not some generalized approximation of it.
This article is a deep dive into how Drizzle’s type inference works, why it matters in production, and where the sharp edges are.
Official GitHub: https://github.com/drizzle-team/drizzle-orm
What "SQL-First" Actually Means
The phrase gets thrown around loosely, but with Drizzle it has teeth. When you write a join in Drizzle, you write it like SQL — not like .include() or .populate(). When you select specific columns, the return type is a narrowed object containing only those columns. The type system mirrors the query, not a generic model.
Compare the two mental models:
Prisma approach:
const user = await prisma.user.findUnique({
where: { id: 1 },
include: { posts: true },
});
// Type is User & { posts: Post[] } — always the full model
Drizzle approach:
const result = await db
.select({ id: users.id, email: users.email })
.from(users)
.where(eq(users.id, 1));
// Type is { id: number; email: string }[] — exactly what you selected
That second one has no ghost columns. No createdAt lurking in the type when you didn’t ask for it. The inference is structural — it follows the shape of your query.
Schema Definition and Where the Types Come From
Everything in Drizzle starts with a schema file. You define your tables using the pgTable (or mysqlTable, sqliteTable) functions, and from that definition Drizzle derives all the types you’ll ever need.
// schema.ts
import { pgTable, serial, text, integer, timestamp, boolean } from 'drizzle-orm/pg-core';
export const users = pgTable('users', {
id: serial('id').primaryKey(),
email: text('email').notNull().unique(),
name: text('name'),
createdAt: timestamp('created_at').defaultNow().notNull(),
isActive: boolean('is_active').default(true).notNull(),
});
export const posts = pgTable('posts', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
body: text('body'),
authorId: integer('author_id').notNull().references(() => users.id),
publishedAt: timestamp('published_at'),
});
No separate type definitions. No codegen step. The schema is the source of truth.
From this Drizzle exposes two key utility types:
import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';
type User = InferSelectModel<typeof users>;
// { id: number; email: string; name: string | null; createdAt: Date; isActive: boolean }
type NewUser = InferInsertModel<typeof users>;
// { email: string; name?: string | null; createdAt?: Date; isActive?: boolean; id?: number }
Notice the insert model correctly marks id, createdAt, and isActive as optional — because they have database-level defaults. The select model marks name as string | null because the column was defined without .notNull(). This is not guesswork. It’s straight structural inference from the column definitions.
Query Results Are Typed Structurally
This is where Drizzle’s design really separates itself. Every query result is typed based on what you actually selected, not based on the table model.
Selecting a subset of columns
const emails = await db.select({ email: users.email }).from(users);
// Type: { email: string }[]
Using SQL expressions in selects
import { sql, count } from 'drizzle-orm';
const stats = await db
.select({
authorId: posts.authorId,
postCount: count(posts.id),
latestPost: sql<string>`max(${posts.publishedAt})::text`,
})
.from(posts)
.groupBy(posts.authorId);
// Type: { authorId: number; postCount: number; latestPost: string }[]
The sql<string> template tag lets you annotate raw SQL fragments with an explicit type. Drizzle takes your word for it — which is a reasonable trade-off. You know what max(published_at)::text returns; the type system can’t infer it.
Joins with correct nullability
This is where most ORMs silently lie to you. When you do a left join, the joined table’s columns should be nullable — because the row might not exist.
Drizzle handles this correctly:
const result = await db
.select({
userId: users.id,
userName: users.name,
postTitle: posts.title,
})
.from(users)
.leftJoin(posts, eq(posts.authorId, users.id));
// Type: { userId: number; userName: string | null; postTitle: string | null }[]
postTitle is string | null even though posts.title is .notNull() in the schema. Drizzle knows it came from a left join and widens the type accordingly. With an inner join, it stays string. This is exactly the behavior you want and almost never get.
Relations API for Nested Reads
Drizzle has a separate relations API for when you want nested/relational data without writing the join manually. It’s built on top of the schema and adds its own layer of inference.
// schema.ts (add to the bottom)
import { relations } from 'drizzle-orm';
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id],
}),
}));
Now you can use db.query:
const usersWithPosts = await db.query.users.findMany({
with: {
posts: {
columns: { title: true, publishedAt: true },
},
},
where: eq(users.isActive, true),
});
// Type:
// {
// id: number;
// email: string;
// name: string | null;
// createdAt: Date;
// isActive: boolean;
// posts: { title: string; publishedAt: Date | null }[];
// }[]
The columns selector narrows the type of the nested array. If you set columns: { title: false } (exclusion mode), the type excludes title. The inference runs all the way down.
Prepared Statements and Parameter Types
Drizzle supports prepared statements, and they’re typed too — including the parameters.
import { placeholder } from 'drizzle-orm';
const getUserByEmail = db
.select()
.from(users)
.where(eq(users.email, placeholder('email')))
.prepare('get_user_by_email');
// Later:
const result = await getUserByEmail.execute({ email: '[email protected]' });
// TypeScript enforces { email: string } as the parameter shape
Pass { email: 123 } and TypeScript refuses to compile it. Pass the wrong key and same deal. This becomes very useful when you have complex parametric queries — the parameter type is derived from the column types at the placeholder sites.
Gotchas
1. sql<T> is a type assertion, not inference
When you write sql<string>\some expression`, you're telling Drizzle "trust me, this is a string." Drizzle will believe you. If you're wrong — say, your Postgres function actually returns nullsometimes — your runtime type and your TS type will diverge silently. Always be conservative and usestring | null` when there’s any doubt.
2. The relations API and the select API are separate
db.query.* (relations API) and db.select() (query builder) are two different systems. Relations use a different execution path — multiple queries, not joins. If you need a single query for performance reasons, use the query builder and write the join yourself. Mixing them up leads to N+1 confusion.
3. Nullable columns in aggregations
If you do sum(posts.viewCount) where viewCount is integer().notNull(), the SQL result is still nullable — SUM over zero rows returns NULL. Drizzle doesn’t automatically widen aggregation types. Use sql<number | null> to be accurate.
4. Schema drift
Drizzle generates migrations from your schema, but it doesn’t enforce that your running database matches your TypeScript schema. If a migration failed halfway and you didn’t notice, your types say one thing and your database says another. Run drizzle-kit check in CI to catch this.
5. Circular references in schema files
If table A references table B and table B references table A, you can hit circular dependency issues in the module graph. The fix is to use a callback form: .references(() => tableB.id) — note the arrow function. Drizzle evaluates this lazily. Forgetting the arrow function on circular refs causes silent undefined column reference errors at runtime.
Production-Ready Patterns
Connection pooling with Postgres.js
// db.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
// Keep this outside of request handlers — one pool per process
const queryClient = postgres(process.env.DATABASE_URL!, {
max: 10, // tune to your DB server's max_connections
idle_timeout: 20, // release idle connections after 20s
connect_timeout: 10,
});
export const db = drizzle(queryClient, { schema });
Never create a new postgres() client per request. That’s a new TCP connection every time and you’ll exhaust file descriptors under any real load.
Transactions with typed rollback
await db.transaction(async (tx) => {
const [user] = await tx
.insert(users)
.values({ email: '[email protected]' })
.returning({ id: users.id });
await tx.insert(posts).values({
title: 'First post',
authorId: user.id,
});
});
// If either insert throws, the transaction rolls back automatically
The tx object has the same API as db — same types, same query builder. There’s no type coercion or as unknown as needed.
Repository pattern without losing type inference
One trap people fall into: wrapping Drizzle queries in repository methods and losing the specificity of the return types by annotating them as the full model.
// BAD — throws away the inference
async function getActiveUserEmails(): Promise<User[]> {
return db.select({ email: users.email }).from(users).where(eq(users.isActive, true));
// TS error: { email: string }[] is not assignable to User[]
// Or you cast it with `as User[]` and lie to yourself
}
// GOOD — let the return type be inferred
async function getActiveUserEmails() {
return db
.select({ email: users.email })
.from(users)
.where(eq(users.isActive, true));
// Return type: Promise<{ email: string }[]> — exact and honest
}
If you need to annotate the return type explicitly (for documentation, interface contracts), extract the type rather than broadening it:
type ActiveUserEmail = Awaited<ReturnType<typeof getActiveUserEmails>>[number];
// { email: string }
Using $inferSelect and $inferInsert on the table object
Instead of importing from drizzle-orm, you can grab the inferred types directly from the table:
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;
Same result, but co-located with the schema. Easier to maintain when you have many tables.
Sorting and filtering with type-safe column references
Don’t hardcode column names as strings. Reference the actual column objects:
// BAD
const results = await db.execute(sql`SELECT * FROM users ORDER BY created_at DESC`);
// No type inference, typos compile fine
// GOOD
const results = await db
.select()
.from(users)
.orderBy(desc(users.createdAt));
// If you typo `createdAt`, TypeScript tells you at compile time
Drizzle Kit for migrations
Don’t write migrations by hand if you can help it.
# Generate a migration from schema changes
npx drizzle-kit generate
# Apply pending migrations
npx drizzle-kit migrate
# Check for schema drift against the live DB
npx drizzle-kit check
In CI, run drizzle-kit check before deploying. It compares the current schema against the migration history and fails the pipeline if they’re out of sync.
When Drizzle Is the Right Tool
Drizzle earns its place when you have a team that’s comfortable with SQL and tired of fighting abstraction layers. It’s particularly good for:
- Complex reporting queries — you can express anything SQL can express, and the result is still typed
- Multi-tenant systems — schema-per-tenant or row-level security is easy because you’re close to the metal
- Performance-sensitive paths — no hidden N+1, no secret eager loading, the query you write is the query that runs
It’s less ideal if you need deep graph traversal out of the box (that’s Prisma’s strength), or if your team is unfamiliar with SQL fundamentals and needs the ORM to be a training wheel.
The Type System as a Design Constraint
The real shift Drizzle forces is that you start designing queries around what you need, not around what the ORM fetches by default. Because the return type reflects the columns you select, you stop grabbing full rows when you only need two fields. You stop doing application-side filtering on data you should have filtered in SQL.
This sounds small but compounds. Smaller payloads, better query plans, no accidental leaking of sensitive columns into API responses because user.passwordHash is never in the type if you didn’t ask for it.
The type system becomes a design constraint in the best way — it makes the lazy path align with the correct path.
Closing Thoughts
Drizzle is not a beginner ORM. It won’t hold your hand, and the docs assume you know what a left join is. But if you’re building production systems and you’ve felt the frustration of ORMs that obscure what’s actually happening at the database layer, the trade-off is worth it.
The type inference is genuinely good — structurally accurate, join-aware, and column-selector-aware. More importantly, it doesn’t require codegen, a separate CLI step in dev mode, or a prisma client restart every time you touch the schema. You change the schema file, TypeScript recompiles, and your types are current.
That’s a loop worth being in.