Drizzle ORM Schema & Queries Cheatsheet: The Complete Reference

Drizzle ORM has emerged as a premier TypeScript SQL ORM. Unlike traditional ORMs that abstract away too much of the underlying SQL mechanics, Drizzle operates as a thin, type-safe layer over raw SQL queries. It offers build-time type safety with a developer experience that closely matches raw SQL syntax.

This reference sheet covers schema definitions, relational declarations, advanced query API syntax, and migration workflows.

Schema Definition: Define tables using standard TypeScript constructs that translate exactly to SQL columns and constraints.
Relational Queries: Use the relations API to declare complex parent-child associations without manual join boilerplate.
Query Builder: Fetch data using the standard db.select() builder for fine-grained SQL queries, or the db.query API for quick hierarchical records.
Type Extraction: Extract typescript types directly from table schemas using InferSelectModel and InferInsertModel.
Migrations: Generate and apply database changes securely via drizzle-kit generate and drizzle-kit migrate.

Before diving into this cheatsheet, check out my previous deep-dive on ClickHouse Cheat Sheet: Database Commands, Schema Design & Performance (2026) to see how we structured these patterns in practice.

Defining Database Schemas

Drizzle allows you to write your database schema in TypeScript. It maps directly to the underlying SQL types. Below is a comprehensive schema definition showing primary keys, foreign keys, timestamps, indexes, and enums.

import { 
  pgTable, 
  serial, 
  text, 
  varchar, 
  timestamp, 
  integer, 
  boolean,
  uniqueIndex,
  pgEnum 
} from 'drizzle-orm/pg-core';

// 1. Declare Database Enums
export const roleEnum = pgEnum('user_role', ['admin', 'editor', 'customer']);

// 2. Define the Users Table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name'),
  role: roleEnum('role').default('customer').notNull(),
  isActive: boolean('is_active').default(true).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => [
  uniqueIndex('email_idx').on(table.email)
]);

// 3. Define the Posts Table with Foreign Keys
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content').notNull(),
  authorId: integer('author_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  published: boolean('published').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Configuring Relations

Drizzle provides a dedicated relations API that lets you describe table associations. This enables high-performance fetching of associated structures.

import { relations } from 'drizzle-orm';
import { users, posts } from './schema';

// Declare that a user can have multiple posts
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

// Declare that a post belongs to exactly one user
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

Querying the Database

Drizzle offers two query patterns: the Core Select API (SQL-like syntax) and the Relational Query API (JSON-like structure).

1. The Core Select API

This mimics SQL syntax and is ideal for complex joins, aggregations, and fine-grained column selection.

import { db } from './db';
import { users, posts } from './schema';
import { eq, and, gt, sql } from 'drizzle-orm';

// Select specific fields with filter conditions
const activeUsers = await db.select({
  userId: users.id,
  userEmail: users.email,
})
.from(users)
.where(
  and(
    eq(users.isActive, true),
    eq(users.role, 'customer')
  )
);

// Perform a join with aggregation
const postCounts = await db.select({
  authorName: users.name,
  postCount: sql<number>`count(${posts.id})`.mapWith(Number),
})
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId))
.groupBy(users.id);

2. The Relational Query API

This is a fast way to retrieve records with associated relationships without writing explicit joins.

// Fetch all posts along with their author details
const postsWithAuthors = await db.query.posts.findMany({
  where: (posts, { eq }) => eq(posts.published, true),
  with: {
    author: {
      columns: {
        id: true,
        name: true,
        email: true,
      }
    }
  },
  limit: 10,
  orderBy: (posts, { desc }) => [desc(posts.createdAt)],
});

Performing Mutations

Inserting, updating, and deleting records is simple and fully type-checked.

1. Insert Data

// Single insert
const newUser = await db.insert(users).values({
  email: 'dev@meshworld.in',
  name: 'Developer Scarlett',
  role: 'admin',
}).returning();

// Batch insert
await db.insert(posts).values([
  { title: 'First Post', content: 'Introductory text', authorId: newUser[0].id },
  { title: 'Second Post', content: 'Advanced details', authorId: newUser[0].id }
]);

2. Update Data

// Update records matching a condition
await db.update(users)
  .set({ isActive: false })
  .where(eq(users.id, 5));

3. Delete Data

// Delete records matching a condition
await db.delete(posts)
  .where(eq(posts.id, 12));

Extracting Types from Table Schemas

You can extract static TypeScript types representing database rows directly from your schema definitions. This eliminates duplicate interface maintenance.

import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm';
import { users } from './schema';

// Represents a row fetched from the users table (includes autogenerated columns)
export type User = InferSelectModel<typeof users>;

// Represents the data required to insert a user (autogenerated columns are optional)
export type NewUser = InferInsertModel<typeof users>;

Executing Migrations

To manage migrations, you need to configure a configuration file at your project root.

1. Configuration (`drizzle.config.ts`)

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL || '',
  },
});

2. Drizzle CLI Commands

Run these scripts from your terminal to generate and push schemas.

# Generate SQL migrations based on changes in your schema file
bunx drizzle-kit generate

# Apply the generated migration scripts directly to your live database
bunx drizzle-kit migrate

# Push changes directly without generating migration files (ideal for local prototyping)
bunx drizzle-kit push

# Open the visual Drizzle Studio database explorer dashboard in your browser
bunx drizzle-kit studio