Library

Drizzle

Overview

Drizzle is a lightweight, serverless-ready next-generation ORM library for TypeScript. With the motto "If you know SQL, you know Drizzle," it provides SQL-like design and minimal abstraction with zero dependencies. At just 7.4KB (minified+gzipped) with cold start optimization, it excels in serverless environments and runs on Node.js, Bun, and Deno, making it the ideal database solution for modern TypeScript development.

Details

Drizzle 2025 edition has established itself as the first choice for developers who prioritize "lightweight and performance" in the TypeScript ecosystem. Supporting all databases including PostgreSQL, MySQL, SQLite, and serverless databases like Turso, Neon, and Xata, it adopts an efficient design that always outputs a single SQL query. The SQL-first approach minimizes learning curve while providing powerful type safety and IDE support. By minimizing bundle size and memory footprint, it demonstrates superiority in resource-constrained serverless platforms.

Key Features

• Zero Dependencies: Ultra-lightweight design at 7.4KB with no external dependencies
• Serverless Optimization: Minimized cold start time and memory usage
• SQL-First Approach: Minimal abstraction that leverages full SQL capabilities
• Complete Type Safety: Powerful type inference through TypeScript-first design
• Multi-Platform: Support for Node.js, Bun, and Deno
• Rich Database Support: Full support for PostgreSQL, MySQL, and SQLite

Pros and Cons

Pros

• Excellent performance in serverless environments due to lightweight design
• Low learning curve by directly leveraging SQL knowledge
• High security and maintainability through zero dependencies
• High efficiency with consistently optimized single SQL queries
• Improved development efficiency through strong type safety and IDE support
• Excellent integration with serverless databases

Cons

• Lack of advanced ORM features (like automatic migrations)
• High learning cost for SQL beginners as SQL knowledge is mandatory
• Manual query writing required for complex relational operations
• Smaller community and ecosystem compared to major ORMs
• Limited advanced features for enterprise use
• Lack of comprehensive developer experience tools like Prisma

Reference Pages

• Drizzle ORM Official Site
• Drizzle ORM GitHub Repository
• Drizzle Kit
• Drizzle Documentation

Code Examples

Basic Setup

// package.json
npm install drizzle-orm
npm install -D drizzle-kit

// For PostgreSQL
npm install pg
npm install -D @types/pg

// For MySQL  
npm install mysql2

// For SQLite
npm install better-sqlite3
npm install -D @types/better-sqlite3

// Driver examples (Node.js)
npm install postgres  // postgres.js
npm install @neondatabase/serverless  // Neon
npm install @planetscale/database  // PlanetScale

Model Definition and Basic Operations

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

// Table definitions
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  age: integer('age'),
  createdAt: timestamp('created_at').defaultNow(),
});

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

// Relation definitions
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

// Type inference
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

// db.ts - Database connection setup
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

// PostgreSQL client setup
const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });

// Basic CRUD operations
export class UserRepository {
  // Create
  static async createUser(userData: NewUser): Promise<User> {
    const [user] = await db.insert(users).values(userData).returning();
    return user;
  }

  // Read
  static async getAllUsers(): Promise<User[]> {
    return await db.select().from(users);
  }

  static async getUserById(id: number): Promise<User | undefined> {
    const [user] = await db
      .select()
      .from(users)
      .where(eq(users.id, id));
    return user;
  }

  // Update
  static async updateUser(id: number, userData: Partial<NewUser>): Promise<User> {
    const [user] = await db
      .update(users)
      .set(userData)
      .where(eq(users.id, id))
      .returning();
    return user;
  }

  // Delete
  static async deleteUser(id: number): Promise<void> {
    await db.delete(users).where(eq(users.id, id));
  }
}

// Usage example
async function main() {
  // Create user
  const newUser = await UserRepository.createUser({
    name: 'John Doe',
    email: '[email protected]',
    age: 30,
  });
  console.log('Created user:', newUser);

  // Get all users
  const allUsers = await UserRepository.getAllUsers();
  console.log('All users:', allUsers);

  // Update user
  const updatedUser = await UserRepository.updateUser(newUser.id, {
    age: 31,
  });
  console.log('Updated user:', updatedUser);
}

Advanced Query Operations

import { eq, and, or, like, gt, gte, lt, lte, inArray, isNull, isNotNull } from 'drizzle-orm';
import { count, sum, avg, max, min } from 'drizzle-orm';

export class AdvancedQueries {
  // Complex condition filtering
  static async getUsersByConditions(
    minAge?: number,
    maxAge?: number,
    namePattern?: string
  ): Promise<User[]> {
    const conditions = [];
    
    if (minAge !== undefined) conditions.push(gte(users.age, minAge));
    if (maxAge !== undefined) conditions.push(lte(users.age, maxAge));
    if (namePattern) conditions.push(like(users.name, `%${namePattern}%`));

    return await db
      .select()
      .from(users)
      .where(and(...conditions))
      .orderBy(users.name);
  }

  // JOIN operations
  static async getUsersWithPosts(): Promise<Array<User & { posts: Post[] }>> {
    return await db.query.users.findMany({
      with: {
        posts: true,
      },
    });
  }

  // Aggregation queries
  static async getUserStatistics() {
    const [stats] = await db
      .select({
        totalUsers: count(users.id),
        averageAge: avg(users.age),
        oldestUser: max(users.age),
        youngestUser: min(users.age),
      })
      .from(users);
    
    return stats;
  }

  // Subqueries and CTE (Common Table Expression)
  static async getActiveUsersWithRecentPosts() {
    const activeUsers = db
      .select({ userId: posts.authorId })
      .from(posts)
      .where(gte(posts.createdAt, new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)))
      .groupBy(posts.authorId)
      .having(gt(count(posts.id), 5));

    return await db
      .select()
      .from(users)
      .where(inArray(users.id, activeUsers));
  }

  // Custom SQL
  static async getTopAuthors(limit: number = 10) {
    return await db.execute(sql`
      SELECT 
        u.id,
        u.name,
        u.email,
        COUNT(p.id) as post_count
      FROM ${users} u
      LEFT JOIN ${posts} p ON u.id = p.author_id
      GROUP BY u.id, u.name, u.email
      ORDER BY post_count DESC
      LIMIT ${limit}
    `);
  }

  // Transactions
  static async transferPosts(fromUserId: number, toUserId: number) {
    return await db.transaction(async (tx) => {
      // Check source user exists
      const fromUser = await tx
        .select()
        .from(users)
        .where(eq(users.id, fromUserId));
      
      if (fromUser.length === 0) {
        throw new Error('Transfer source user not found');
      }

      // Check destination user exists
      const toUser = await tx
        .select()
        .from(users)
        .where(eq(users.id, toUserId));
      
      if (toUser.length === 0) {
        throw new Error('Transfer destination user not found');
      }

      // Transfer posts
      const result = await tx
        .update(posts)
        .set({ authorId: toUserId })
        .where(eq(posts.authorId, fromUserId))
        .returning();

      return result;
    });
  }

  // Batch operations
  static async batchInsertUsers(usersData: NewUser[]): Promise<User[]> {
    return await db.insert(users).values(usersData).returning();
  }

  static async batchUpdateUsers(updates: Array<{ id: number; data: Partial<NewUser> }>) {
    return await db.transaction(async (tx) => {
      const results = [];
      for (const update of updates) {
        const [result] = await tx
          .update(users)
          .set(update.data)
          .where(eq(users.id, update.id))
          .returning();
        results.push(result);
      }
      return results;
    });
  }
}

// Usage examples
async function advancedExamples() {
  // Conditional search
  const youngAdults = await AdvancedQueries.getUsersByConditions(18, 30, 'John');
  console.log('Young adults:', youngAdults);

  // Statistics
  const stats = await AdvancedQueries.getUserStatistics();
  console.log('User statistics:', stats);

  // Batch insert
  const newUsers = await AdvancedQueries.batchInsertUsers([
    { name: 'User 1', email: '[email protected]', age: 25 },
    { name: 'User 2', email: '[email protected]', age: 30 },
    { name: 'User 3', email: '[email protected]', age: 35 },
  ]);
  console.log('Batch inserted users:', newUsers);
}

Relation Operations

// More complex relations
export const categories = pgTable('categories', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  description: text('description'),
});

export const postCategories = pgTable('post_categories', {
  postId: integer('post_id').references(() => posts.id),
  categoryId: integer('category_id').references(() => categories.id),
}, (table) => ({
  pk: primaryKey({ columns: [table.postId, table.categoryId] }),
}));

// Extended relation definitions
export const categoriesRelations = relations(categories, ({ many }) => ({
  postCategories: many(postCategories),
}));

export const postCategoriesRelations = relations(postCategories, ({ one }) => ({
  post: one(posts, { fields: [postCategories.postId], references: [posts.id] }),
  category: one(categories, { fields: [postCategories.categoryId], references: [categories.id] }),
}));

export const extendedPostsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  postCategories: many(postCategories),
}));

export class RelationQueries {
  // Nested relation queries
  static async getPostsWithAuthorsAndCategories() {
    return await db.query.posts.findMany({
      with: {
        author: true,
        postCategories: {
          with: {
            category: true,
          },
        },
      },
    });
  }

  // Get posts by specific category
  static async getPostsByCategory(categoryName: string) {
    return await db
      .select({
        id: posts.id,
        title: posts.title,
        content: posts.content,
        authorName: users.name,
        categoryName: categories.name,
      })
      .from(posts)
      .innerJoin(users, eq(posts.authorId, users.id))
      .innerJoin(postCategories, eq(posts.id, postCategories.postId))
      .innerJoin(categories, eq(postCategories.categoryId, categories.id))
      .where(eq(categories.name, categoryName));
  }

  // Aggregate user posts by category
  static async getUserPostsByCategory(userId: number) {
    return await db
      .select({
        categoryName: categories.name,
        postCount: count(posts.id),
      })
      .from(posts)
      .innerJoin(postCategories, eq(posts.id, postCategories.postId))
      .innerJoin(categories, eq(postCategories.categoryId, categories.id))
      .where(eq(posts.authorId, userId))
      .groupBy(categories.id, categories.name);
  }

  // Many-to-many relation management
  static async addPostToCategories(postId: number, categoryIds: number[]) {
    const values = categoryIds.map(categoryId => ({
      postId,
      categoryId,
    }));

    return await db.insert(postCategories).values(values);
  }

  static async removePostFromCategories(postId: number, categoryIds?: number[]) {
    let query = db.delete(postCategories).where(eq(postCategories.postId, postId));
    
    if (categoryIds) {
      query = query.where(inArray(postCategories.categoryId, categoryIds));
    }

    return await query;
  }

  // Complex JOIN operations
  static async getPopularCategories(limit: number = 10) {
    return await db
      .select({
        categoryId: categories.id,
        categoryName: categories.name,
        postCount: count(posts.id),
        uniqueAuthors: sql<number>`COUNT(DISTINCT ${posts.authorId})`.as('unique_authors'),
      })
      .from(categories)
      .leftJoin(postCategories, eq(categories.id, postCategories.categoryId))
      .leftJoin(posts, eq(postCategories.postId, posts.id))
      .groupBy(categories.id, categories.name)
      .orderBy(desc(count(posts.id)))
      .limit(limit);
  }
}

Practical Examples

// Practical example in Express.js application
import express from 'express';
import { z } from 'zod';

const app = express();
app.use(express.json());

// Validation schemas
const createUserSchema = z.object({
  name: z.string().min(1).max(100),
  email: z.string().email(),
  age: z.number().int().positive().optional(),
});

const updateUserSchema = createUserSchema.partial();

// API endpoints
app.get('/users', async (req, res) => {
  try {
    const { page = 1, limit = 10, search, minAge, maxAge } = req.query;
    
    let query = db.select().from(users);
    const conditions = [];

    if (search) {
      conditions.push(like(users.name, `%${search}%`));
    }
    if (minAge) {
      conditions.push(gte(users.age, Number(minAge)));
    }
    if (maxAge) {
      conditions.push(lte(users.age, Number(maxAge)));
    }

    if (conditions.length > 0) {
      query = query.where(and(...conditions));
    }

    const users = await query
      .limit(Number(limit))
      .offset((Number(page) - 1) * Number(limit))
      .orderBy(users.createdAt);

    res.json({
      data: users,
      pagination: {
        page: Number(page),
        limit: Number(limit),
      },
    });
  } catch (error) {
    res.status(500).json({ error: 'Internal server error' });
  }
});

app.get('/users/:id', async (req, res) => {
  try {
    const userId = Number(req.params.id);
    
    const user = await db.query.users.findFirst({
      where: eq(users.id, userId),
      with: {
        posts: {
          with: {
            postCategories: {
              with: {
                category: true,
              },
            },
          },
        },
      },
    });

    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }

    res.json(user);
  } catch (error) {
    res.status(500).json({ error: 'Internal server error' });
  }
});

app.post('/users', async (req, res) => {
  try {
    const userData = createUserSchema.parse(req.body);
    
    const [newUser] = await db.insert(users).values(userData).returning();
    
    res.status(201).json(newUser);
  } catch (error) {
    if (error instanceof z.ZodError) {
      return res.status(400).json({ error: 'Validation error', details: error.errors });
    }
    res.status(500).json({ error: 'Internal server error' });
  }
});

app.put('/users/:id', async (req, res) => {
  try {
    const userId = Number(req.params.id);
    const userData = updateUserSchema.parse(req.body);

    const [updatedUser] = await db
      .update(users)
      .set(userData)
      .where(eq(users.id, userId))
      .returning();

    if (!updatedUser) {
      return res.status(404).json({ error: 'User not found' });
    }

    res.json(updatedUser);
  } catch (error) {
    if (error instanceof z.ZodError) {
      return res.status(400).json({ error: 'Validation error', details: error.errors });
    }
    res.status(500).json({ error: 'Internal server error' });
  }
});

app.delete('/users/:id', async (req, res) => {
  try {
    const userId = Number(req.params.id);

    const result = await db.delete(users).where(eq(users.id, userId)).returning();

    if (result.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    res.status(204).send();
  } catch (error) {
    res.status(500).json({ error: 'Internal server error' });
  }
});

// Serverless function usage example (Vercel)
export default async function handler(req: any, res: any) {
  if (req.method === 'GET') {
    try {
      const users = await db.select().from(users).limit(10);
      res.status(200).json(users);
    } catch (error) {
      res.status(500).json({ error: 'Database error' });
    }
  } else {
    res.setHeader('Allow', ['GET']);
    res.status(405).end(`Method ${req.method} Not Allowed`);
  }
}

// Next.js API Route usage example
// pages/api/users/[id].ts
export default async function handler(req: any, res: any) {
  const { id } = req.query;
  const userId = Number(id);

  switch (req.method) {
    case 'GET':
      try {
        const user = await UserRepository.getUserById(userId);
        if (!user) {
          return res.status(404).json({ error: 'User not found' });
        }
        res.json(user);
      } catch (error) {
        res.status(500).json({ error: 'Internal server error' });
      }
      break;

    case 'PUT':
      try {
        const userData = updateUserSchema.parse(req.body);
        const updatedUser = await UserRepository.updateUser(userId, userData);
        res.json(updatedUser);
      } catch (error) {
        if (error instanceof z.ZodError) {
          return res.status(400).json({ error: 'Validation error', details: error.errors });
        }
        res.status(500).json({ error: 'Internal server error' });
      }
      break;

    case 'DELETE':
      try {
        await UserRepository.deleteUser(userId);
        res.status(204).end();
      } catch (error) {
        res.status(500).json({ error: 'Internal server error' });
      }
      break;

    default:
      res.setHeader('Allow', ['GET', 'PUT', 'DELETE']);
      res.status(405).end(`Method ${req.method} Not Allowed`);
  }
}

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});