Next.js Drizzle ORM

In modern web development, efficient database management is crucial for building robust applications. Drizzle ORM provides a lightweight, type-safe SQL query builder that works seamlessly with Next.js applications. In this guide, we'll explore how to integrate and use Drizzle ORM in your Next.js projects.

What is Drizzle ORM?

Drizzle ORM is a TypeScript ORM (Object-Relational Mapping) that focuses on being:

Lightweight: Minimal overhead and bundle size
Type-safe: Leverages TypeScript for better developer experience
SQL-first: Works with raw SQL queries when needed
No metadata storage: No decorators or extensive configurations required
Database agnostic: Supports PostgreSQL, MySQL, SQLite, and more

Unlike traditional ORMs that abstract SQL entirely, Drizzle gives you direct control over your database queries while providing helpful abstractions and type safety.

Setting Up Drizzle with Next.js

Let's walk through the process of integrating Drizzle ORM with a Next.js application.

Step 1: Installation

First, let's install Drizzle ORM and the necessary database driver. For this example, we'll use PostgreSQL:

bash
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

Step 2: Define Your Schema

Create a db/schema.ts file where you'll define your database schema:

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

// Define a users table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at').defaultNow().notNull()
});

// Define a posts table with a relation to users
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),
  createdAt: timestamp('created_at').defaultNow().notNull()
});

Step 3: Set Up the Database Connection

Create a db/index.ts file to establish the database connection:

typescript
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

// Use environment variables for database configuration
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// Create a Drizzle ORM instance
export const db = drizzle(pool, { schema });

Step 4: Configure Environment Variables

Create or update your .env.local file with your database connection string:

DATABASE_URL=postgres://username:password@localhost:5432/your_database

Make sure to add this file to your .gitignore to keep sensitive information out of version control.

Step 5: Generating Migrations

Drizzle provides a handy tool for generating SQL migrations based on your schema changes:

bash
npx drizzle-kit generate:pg --schema=./db/schema.ts --out=./db/migrations

This command will create SQL migration files in the ./db/migrations directory.

Using Drizzle ORM in Next.js API Routes

Let's see how we can use Drizzle in Next.js API routes to perform CRUD operations.

Creating a User API Route

Create a file at app/api/users/route.ts for handling user operations:

typescript
import { NextResponse } from 'next/server';
import { db } from '@/db';
import { users } from '@/db/schema';

// GET handler to fetch all users
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 });
  }
}

// POST handler to create a new user
export async function POST(request: Request) {
  try {
    const { name, email } = await request.json();
    
    if (!name || !email) {
      return NextResponse.json(
        { error: 'Name and email are required' },
        { status: 400 }
      );
    }

    const newUser = await db.insert(users).values({
      name,
      email
    }).returning();

    return NextResponse.json(newUser[0], { status: 201 });
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to create user' },
      { status: 500 }
    );
  }
}

Fetching a Specific User

Create a file at app/api/users/[id]/route.ts to handle operations on a specific user:

typescript
import { NextResponse } from 'next/server';
import { db } from '@/db';
import { users } from '@/db/schema';
import { eq } from 'drizzle-orm';

interface Params {
  params: {
    id: string;
  };
}

export async function GET(request: Request, { params }: Params) {
  try {
    const id = parseInt(params.id);
    
    if (isNaN(id)) {
      return NextResponse.json({ error: 'Invalid ID' }, { status: 400 });
    }
    
    const user = await db.select().from(users).where(eq(users.id, id));
    
    if (!user.length) {
      return NextResponse.json({ error: 'User not found' }, { status: 404 });
    }
    
    return NextResponse.json(user[0]);
  } catch (error) {
    return NextResponse.json({ error: 'Failed to fetch user' }, { status: 500 });
  }
}

export async function PUT(request: Request, { params }: Params) {
  try {
    const id = parseInt(params.id);
    const { name, email } = await request.json();
    
    if (isNaN(id)) {
      return NextResponse.json({ error: 'Invalid ID' }, { status: 400 });
    }
    
    const updatedUser = await db.update(users)
      .set({ name, email })
      .where(eq(users.id, id))
      .returning();
    
    if (!updatedUser.length) {
      return NextResponse.json({ error: 'User not found' }, { status: 404 });
    }
    
    return NextResponse.json(updatedUser[0]);
  } catch (error) {
    return NextResponse.json({ error: 'Failed to update user' }, { status: 500 });
  }
}

export async function DELETE(request: Request, { params }: Params) {
  try {
    const id = parseInt(params.id);
    
    if (isNaN(id)) {
      return NextResponse.json({ error: 'Invalid ID' }, { status: 400 });
    }
    
    const deletedUser = await db.delete(users)
      .where(eq(users.id, id))
      .returning();
    
    if (!deletedUser.length) {
      return NextResponse.json({ error: 'User not found' }, { status: 404 });
    }
    
    return NextResponse.json({ message: 'User deleted successfully' });
  } catch (error) {
    return NextResponse.json({ error: 'Failed to delete user' }, { status: 500 });
  }
}

Advanced Queries with Drizzle ORM

Drizzle provides powerful features for complex queries. Let's explore some examples:

Joins

Retrieving posts with their authors:

typescript
import { db } from '@/db';
import { users, posts } from '@/db/schema';
import { eq } from 'drizzle-orm';

// Function to get all posts with author information
export async function getPostsWithAuthors() {
  const results = await db
    .select({
      postId: posts.id,
      title: posts.title,
      content: posts.content,
      createdAt: posts.createdAt,
      author: {
        id: users.id,
        name: users.name,
        email: users.email
      }
    })
    .from(posts)
    .leftJoin(users, eq(posts.authorId, users.id));
  
  return results;
}

Filtering and Pagination

typescript
import { db } from '@/db';
import { posts } from '@/db/schema';
import { eq, like, desc } from 'drizzle-orm';

// Function to search posts with pagination
export async function searchPosts(query: string, page = 1, limit = 10) {
  const offset = (page - 1) * limit;
  
  const results = await db
    .select()
    .from(posts)
    .where(like(posts.title, `%${query}%`))
    .orderBy(desc(posts.createdAt))
    .limit(limit)
    .offset(offset);
    
  const totalCount = await db
    .select({ count: sql`count(*)` })
    .from(posts)
    .where(like(posts.title, `%${query}%`));
    
  return {
    posts: results,
    total: Number(totalCount[0].count),
    page,
    limit,
    totalPages: Math.ceil(Number(totalCount[0].count) / limit)
  };
}

Using Drizzle with React Server Components

Next.js 13+ introduced React Server Components, which pair beautifully with Drizzle. Let's see an example:

typescript
// app/users/page.tsx
import { db } from '@/db';
import { users } from '@/db/schema';

export default async function UsersPage() {
  // This runs on the server
  const allUsers = await db.select().from(users);
  
  return (
    <div>
      <h1 className="text-2xl font-bold mb-4">Users</h1>
      <ul className="space-y-2">
        {allUsers.map((user) => (
          <li key={user.id} className="border p-3 rounded">
            <p className="font-bold">{user.name}</p>
            <p className="text-gray-600">{user.email}</p>
          </li>
        ))}
      </ul>
    </div>
  );
}

Creating a Data Access Layer

For larger applications, it's a good practice to create a data access layer that separates database logic from your API routes:

typescript
// db/repositories/userRepository.ts
import { db } from '@/db';
import { users } from '@/db/schema';
import { eq } from 'drizzle-orm';

export const userRepository = {
  // Get all users
  getAll: async () => {
    return db.select().from(users);
  },
  
  // Get user by id
  getById: async (id: number) => {
    const result = await db.select().from(users).where(eq(users.id, id));
    return result[0] || null;
  },
  
  // Create a new user
  create: async (data: { name: string; email: string }) => {
    const result = await db.insert(users).values(data).returning();
    return result[0];
  },
  
  // Update a user
  update: async (id: number, data: { name?: string; email?: string }) => {
    const result = await db
      .update(users)
      .set(data)
      .where(eq(users.id, id))
      .returning();
    return result[0] || null;
  },
  
  // Delete a user
  delete: async (id: number) => {
    const result = await db
      .delete(users)
      .where(eq(users.id, id))
      .returning();
    return result[0] || null;
  }
};

Now you can use this repository in your API routes:

typescript
// app/api/users/route.ts
import { NextResponse } from 'next/server';
import { userRepository } from '@/db/repositories/userRepository';

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

Real-World Example: Blog API

Let's build a simple blog API that demonstrates Drizzle ORM in action:

typescript
// db/schema.ts (expanded)
import { pgTable, serial, text, varchar, timestamp, integer } from 'drizzle-orm/pg-core';

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

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).notNull(),
  published: boolean('published').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull()
});

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

Now let's implement a service to fetch a blog post with its comments and author information:

typescript
// services/blogService.ts
import { db } from '@/db';
import { posts, users, comments } from '@/db/schema';
import { eq } from 'drizzle-orm';

export const blogService = {
  getPostWithComments: async (postId: number) => {
    // Get the post with author information
    const postResults = await db
      .select({
        id: posts.id,
        title: posts.title,
        content: posts.content,
        published: posts.published,
        createdAt: posts.createdAt,
        author: {
          id: users.id,
          name: users.name
        }
      })
      .from(posts)
      .leftJoin(users, eq(posts.authorId, users.id))
      .where(eq(posts.id, postId));
    
    if (!postResults.length) {
      return null;
    }
    
    const post = postResults[0];
    
    // Get comments for this post
    const commentResults = await db
      .select({
        id: comments.id,
        content: comments.content,
        createdAt: comments.createdAt,
        author: {
          id: users.id,
          name: users.name
        }
      })
      .from(comments)
      .leftJoin(users, eq(comments.authorId, users.id))
      .where(eq(comments.postId, postId))
      .orderBy(comments.createdAt);
    
    // Return post with comments
    return {
      ...post,
      comments: commentResults
    };
  },
  
  createComment: async (data: { 
    content: string;
    postId: number;
    authorId: number;
  }) => {
    const result = await db.insert(comments).values(data).returning();
    return result[0];
  }
};

And finally, let's use this in an API route:

typescript
// app/api/posts/[id]/route.ts
import { NextResponse } from 'next/server';
import { blogService } from '@/services/blogService';

interface Params {
  params: {
    id: string;
  };
}

export async function GET(request: Request, { params }: Params) {
  try {
    const id = parseInt(params.id);
    
    if (isNaN(id)) {
      return NextResponse.json({ error: 'Invalid post ID' }, { status: 400 });
    }
    
    const post = await blogService.getPostWithComments(id);
    
    if (!post) {
      return NextResponse.json({ error: 'Post not found' }, { status: 404 });
    }
    
    return NextResponse.json(post);
  } catch (error) {
    console.error('Error fetching post:', error);
    return NextResponse.json(
      { error: 'Failed to fetch post' }, 
      { status: 500 }
    );
  }
}

// app/api/posts/[id]/comments/route.ts
import { NextResponse } from 'next/server';
import { blogService } from '@/services/blogService';

interface Params {
  params: {
    id: string;
  };
}

export async function POST(request: Request, { params }: Params) {
  try {
    const postId = parseInt(params.id);
    
    if (isNaN(postId)) {
      return NextResponse.json({ error: 'Invalid post ID' }, { status: 400 });
    }
    
    const { content, authorId } = await request.json();
    
    if (!content || !authorId) {
      return NextResponse.json(
        { error: 'Content and authorId are required' },
        { status: 400 }
      );
    }
    
    const comment = await blogService.createComment({
      content,
      postId,
      authorId
    });
    
    return NextResponse.json(comment, { status: 201 });
  } catch (error) {
    console.error('Error creating comment:', error);
    return NextResponse.json(
      { error: 'Failed to create comment' }, 
      { status: 500 }
    );
  }
}

Summary

In this guide, we've explored how to integrate Drizzle ORM with Next.js applications. We covered:

Basic setup and configuration of Drizzle ORM
Defining database schemas
Performing CRUD operations
Advanced queries with joins and filtering
Using Drizzle with React Server Components
Creating a data access layer
Building a real-world blog API

Drizzle ORM provides a lightweight yet powerful solution for database operations in Next.js, offering type safety without sacrificing performance. Its SQL-first approach gives you control over your queries while maintaining the convenience of an ORM.

Additional Resources

Exercises

Basic Setup: Create a new Next.js application and integrate Drizzle ORM with a SQLite database.
Schema Design: Design a database schema for a todo application with tasks, categories, and user assignment.
API Routes: Implement CRUD operations for the todo application.
Relations: Add functionality to fetch tasks with their assigned users and categories.
Advanced Queries: Implement search, filtering, and pagination for the tasks.
Full Application: Build a complete todo application with both the backend (Drizzle ORM) and frontend (Next.js UI components).

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

What is Drizzle ORM?​

Setting Up Drizzle with Next.js​

Step 1: Installation​

Step 2: Define Your Schema​

Step 3: Set Up the Database Connection​

Step 4: Configure Environment Variables​

Step 5: Generating Migrations​

Using Drizzle ORM in Next.js API Routes​

Creating a User API Route​

Fetching a Specific User​

Advanced Queries with Drizzle ORM​

Joins​

Filtering and Pagination​

Using Drizzle with React Server Components​

Creating a Data Access Layer​

Real-World Example: Blog API​

Summary​

Additional Resources​

Exercises​