Library

Drizzle ORM

Overview

Drizzle ORM is a SQL-centric TypeScript ORM that's lightweight with zero dependencies, optimized for serverless environments. Designed with the concept "If you know SQL, you know Drizzle," it balances high performance with intuitive API design as a headless ORM.

Details

Unlike traditional ORMs, Drizzle adopts a SQL-first approach. Rather than abstracting SQL away, it leverages the power of SQL while providing TypeScript type safety. This allows for improved developer productivity without sacrificing performance.

Key Features

• SQL-first Design: Write queries close to raw SQL for high performance
• Zero Dependencies: Lightweight and fast, minimizing bundle size
• Type Safety: Complete TypeScript integration
• Serverless Optimized: Excellent performance in edge computing environments
• Migrations: Schema-driven safe database changes

Pros and Cons

Pros

• Lightweight and fast, especially excellent performance in serverless environments
• Very low learning curve for developers familiar with SQL
• Complete type safety reduces runtime errors
• No dependencies reduce project security risks
• Optimized performance for Edge Computing and Cloudflare Workers

Cons

• Still relatively new, so ecosystem is still developing
• Learning curve for developers accustomed to Active Record patterns
• Minimal ORM features require more SQL knowledge
• Complex relationship operations require manual query construction

Reference Pages

• Drizzle ORM Official Site
• Drizzle ORM Documentation
• Drizzle ORM GitHub

Code Examples

Installation and Basic Setup

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

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Basic CRUD Operations

// schema.ts
import { pgTable, serial, text, timestamp, integer } from 'drizzle-orm/pg-core';

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow(),
});

// Database operations
import { drizzle } from 'drizzle-orm/node-postgres';
import { Client } from 'pg';
import { users, posts } from './schema';
import { eq } from 'drizzle-orm';

const client = new Client({
  connectionString: process.env.DATABASE_URL,
});

const db = drizzle(client);

// Create
const newUser = await db.insert(users).values({
  name: 'John Doe',
  email: '[email protected]'
}).returning();

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

// Update
await db.update(users)
  .set({ name: 'John Smith' })
  .where(eq(users.id, 1));

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

Advanced Queries and Relationships

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

// JOIN queries
const usersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
    postCount: sql<number>`count(${posts.id})`,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id)
  .having(sql`count(${posts.id}) > 0`);

// Complex conditional queries
const activeUsers = await db
  .select()
  .from(users)
  .where(
    and(
      gt(users.createdAt, sql`NOW() - INTERVAL '30 days'`),
      like(users.email, '%@company.com')
    )
  )
  .orderBy(desc(users.createdAt))
  .limit(10);

// Subqueries
const sq = db
  .select({ count: sql<number>`count(*)` })
  .from(posts)
  .where(eq(posts.authorId, users.id))
  .as('post_count');

const usersWithPostCounts = await db
  .select({
    user: users,
    postCount: sq.count,
  })
  .from(users)
  .leftJoin(sq, eq(users.id, posts.authorId));

Migrations and Schema Management

# Generate migrations
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

// Manual migration execution
import { migrate } from 'drizzle-orm/node-postgres/migrator';

await migrate(db, { migrationsFolder: './drizzle' });

Performance Optimization and Advanced Features

// Prepared statements
const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, placeholder('id')))
  .prepare();

const user = await getUserById.execute({ id: 1 });

// Transactions
await db.transaction(async (tx) => {
  const user = await tx.insert(users).values({
    name: 'Jane Doe',
    email: '[email protected]'
  }).returning();

  await tx.insert(posts).values({
    title: 'First Post',
    content: 'This is my first post using Drizzle',
    authorId: user[0].id
  });
});

// Batch operations
await db.insert(users).values([
  { name: 'User 1', email: '[email protected]' },
  { name: 'User 2', email: '[email protected]' },
  { name: 'User 3', email: '[email protected]' },
]);

Framework Integration and Practical Examples

// Next.js App Router integration
// app/api/users/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { db } from '@/lib/db';
import { users } from '@/lib/schema';

export async function GET() {
  try {
    const allUsers = await db.select().from(users);
    return NextResponse.json(allUsers);
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to fetch users' },
      { status: 500 }
    );
  }
}

export async function POST(request: NextRequest) {
  try {
    const body = await request.json();
    const newUser = await db.insert(users).values(body).returning();
    return NextResponse.json(newUser[0]);
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to create user' },
      { status: 500 }
    );
  }
}

// Cloudflare Workers integration
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const db = drizzle(env.DB);
    
    if (request.method === 'GET') {
      const users = await db.select().from(usersTable);
      return Response.json(users);
    }
    
    if (request.method === 'POST') {
      const body = await request.json();
      const newUser = await db.insert(usersTable).values(body).returning();
      return Response.json(newUser[0]);
    }
    
    return new Response('Method not allowed', { status: 405 });
  }
};