Drizzle ORM

TypeScript ORM with SQL-like queries and zero dependencies

What this skill does

Drizzle ORM is a headless TypeScript ORM that embraces SQL rather than abstracting away from it. Unlike traditional ORMs that force you to build projects around them, Drizzle lets you build projects with it - providing both SQL-like and relational query APIs for maximum flexibility.

The core philosophy is "if you know SQL, you know Drizzle." It offers type-safe database operations, automatic migrations, and serverless-ready performance with exactly zero dependencies. Drizzle supports PostgreSQL, MySQL, SQLite, and other databases through their native drivers, making it ideal for modern applications that need performant, type-safe data access without framework lock-in.

Prerequisites

• Node.js 14+ or compatible runtime (Bun, Deno, Cloudflare Workers)
• TypeScript 4.5+
• Database driver for your chosen database:
  • PostgreSQL: pg, postgres, @neondatabase/serverless
  • MySQL: mysql2, @planetscale/database
  • SQLite: better-sqlite3, @libsql/client, @cloudflare/d1

Quick start

npm install drizzle-orm
npm install -D drizzle-kit
# Install your database driver, e.g.:
npm install pg @types/pg

// schema.ts
import { pgTable, serial, varchar, integer } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 256 }),
  userId: integer('user_id').references(() => users.id),
});

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool);

// Query examples
const allUsers = await db.select().from(users);
const userWithPosts = await db.query.users.findFirst({
  with: { posts: true }
});

Core concepts

Schema Definition: Define tables using type-safe column builders (serial, varchar, integer, etc.) that map directly to SQL DDL.

Dual Query APIs:

• SQL-like API mirrors SQL syntax closely (select().from().where())
• Relational API for nested data fetching (query.users.findMany({ with: { posts: true } }))

Dialect-Specific: Separate entry points for each database (drizzle-orm/pg-core, drizzle-orm/mysql-core, drizzle-orm/sqlite-core) ensure optimal SQL generation.

Migration System: Drizzle Kit generates and manages migrations by comparing schema changes, no manual migration writing required.

Driver Agnostic: Works with any database driver - you choose your connection layer, Drizzle handles the queries.

Key API surface

// Schema definition
import { pgTable, serial, varchar, timestamp, relations } from 'drizzle-orm/pg-core';

// Query building
import { eq, and, or, like, gt, lt, isNull } from 'drizzle-orm';

// Database instance
import { drizzle } from 'drizzle-orm/node-postgres';

// Core query methods
db.select().from(table).where(condition)
db.insert(table).values(data).returning()
db.update(table).set(data).where(condition)
db.delete(table).where(condition)

// Relational queries
db.query.tableName.findMany({ with: { relations: true } })
db.query.tableName.findFirst({ where: condition })

// Transactions
db.transaction(async (tx) => { /* queries */ })

// Raw SQL
import { sql } from 'drizzle-orm';
db.execute(sql`SELECT * FROM users WHERE id = ${userId}`)

// Batch operations
db.batch([query1, query2, query3])

Common patterns

Basic CRUD operations:

// Insert
const newUser = await db.insert(users)
  .values({ name: 'John', email: 'john@example.com' })
  .returning();

// Select with conditions
const activeUsers = await db.select()
  .from(users)
  .where(and(eq(users.active, true), gt(users.createdAt, new Date('2024-01-01'))));

// Update
await db.update(users)
  .set({ lastLogin: new Date() })
  .where(eq(users.id, userId));

Joins and relationships:

// SQL-like joins
const usersWithPosts = await db.select()
  .from(users)
  .leftJoin(posts, eq(posts.userId, users.id))
  .where(eq(users.active, true));

// Relational queries (single SQL query)
const userWithData = await db.query.users.findMany({
  with: {
    posts: {
      with: { comments: true }
    },
    profile: true
  }
});

Dynamic query building:

let query = db.select().from(users);

if (nameFilter) {
  query = query.where(like(users.name, `%${nameFilter}%`));
}

if (sortBy === 'name') {
  query = query.orderBy(users.name);
}

const results = await query;

Transactions with error handling:

await db.transaction(async (tx) => {
  const user = await tx.insert(users).values(userData).returning();
  await tx.insert(profiles).values({ userId: user[0].id, ...profileData });
  
  // Transaction automatically rolls back on error
});

Prepared statements for performance:

const getUserById = db.select()
  .from(users)
  .where(eq(users.id, $id))
  .prepare();

// Reuse prepared statement
const user1 = await getUserById.execute({ id: 1 });
const user2 = await getUserById.execute({ id: 2 });

Configuration

Drizzle Kit config (drizzle.config.ts):

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema.ts',
  out: './migrations',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
} satisfies Config;

Environment variables:

DATABASE_URL=postgresql://user:password@localhost:5432/dbname

Database connection options:

// Connection pooling
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
});

// Custom logger
const db = drizzle(client, { 
  schema,
  logger: true // or custom logger function
});

Best practices

• Use prepared statements for frequently executed queries to improve performance
• Leverage TypeScript strict mode - Drizzle's type safety shines with strict TypeScript
• Define relations in schema for type-safe relational queries, even if you don't use them
• Use transactions for multi-table operations to ensure data consistency
• Prefer relational queries over joins when fetching nested data - they're more efficient
• Use returning() on inserts/updates when you need the affected rows back
• Batch operations when possible using db.batch() for better performance
• Use specific database drivers rather than generic ones for better performance and features
• Keep schema files separate from business logic for better organization
• Use Drizzle Studio for database inspection and debugging during development

Gotchas and common mistakes

Schema definition pitfalls:

• Column constraints must be defined in schema, not enforced by Drizzle at runtime
• Changing column names requires manual migration handling for data preservation
• serial() and integer().primaryKey() are different - serial auto-increments
• Foreign key references require functions: .references(() => users.id), not .references(users.id)

Query API confusion:

• Relational queries (db.query.table) and SQL-like queries (db.select()) have different syntax
• Relational queries generate exactly one SQL query, not N+1 queries
• findFirst() returns undefined if no results, findMany() returns empty array
• You cannot mix relational and SQL-like syntax in the same query

Type safety gotchas:

• TypeScript inference works best with const assertions on filter conditions
• Dynamic query building can lose type information - use proper typing
• sql template literals bypass type checking - use sparingly and carefully
• Prepared statements require explicit typing for parameters

Migration and schema issues:

• Schema changes require running drizzle-kit generate before deployment
• Drizzle Kit doesn't handle data transformations - write custom migrations for complex changes
• Schema file location must match drizzle.config.ts configuration
• Breaking changes in schema can make existing migrations incompatible

Performance traps:

• Not using indexes on frequently queried columns
• Using relational queries when you only need specific columns (use columns option)
• Forgetting to use connection pooling in serverless environments
• Not utilizing prepared statements for repeated queries

Connection and driver issues:

• Database connection strings must match driver requirements exactly
• Some drivers require specific configuration for serverless environments
• Connection pooling behaves differently across drivers
• Edge runtime compatibility varies by database driver choice