Drizzle ORM: Schema, Type Inference & Queries

Drizzle is a TypeScript-first ORM/query builder that stays close to raw SQL. The schema definition IS plain TypeScript -- no separate DSL or codegen step -- and types are inferred end-to-end from it, catching schema/query mismatches at compile time.

Defining a Schema

import { pgTable, uuid, text, integer, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  authorId: uuid('author_id').notNull().references(() => users.id),
});

// The schema IS the type source -- no codegen step, editing the schema
// updates types immediately
type User = typeof users.$inferSelect;   // full row shape, all columns present
type NewUser = typeof users.$inferInsert; // defaulted columns (id, createdAt) optional

Query Builder

import { eq, and, gt } from 'drizzle-orm';

// Type-checked WHERE conditions -- eq() on a numeric column against
// a string argument would be a compile error
const activeAdults = await db
  .select()
  .from(users)
  .where(and(eq(users.status, 'active'), gt(users.age, 18)));

// Explicit, SQL-like joins
const usersWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id));

// Writes
await db.insert(users).values({ name: 'Alice', email: 'alice@example.com' });
await db.update(users).set({ name: 'Alice B.' }).where(eq(users.id, userId));
await db.delete(users).where(eq(users.id, userId));

// Escape hatch for expressions the builder doesn't model -- values
// still safely bound as parameters, not string-concatenated
import { sql } from 'drizzle-orm';
await db.select().from(users).where(sql`LOWER(${users.name}) = ${term.toLowerCase()}`);

Relational Queries

import { relations } from 'drizzle-orm';

// Declares relationships so the relational query API knows how to
// nest related data -- alongside the actual foreign key column
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

// Higher-level convenience API, vs. the explicit query builder above
const usersWithNestedPosts = await db.query.users.findMany({
  with: { posts: true },
});