Master Drizzle ORM with PostgreSQL in Next.js 15: A Practical Guide

Drizzle ORM offers a type-safe, SQL-focused approach to database interactions in TypeScript. This guide explains how to seamlessly integrate Drizzle ORM with PostgreSQL in a Next.js 15 application, replacing mock data with a real database and streamlining data fetching and mutations.

Is Drizzle ORM Right for Your Next.js Project?

Drizzle ORM simplifies database modeling and queries with a SQL-like syntax familiar to developers. This allows connecting your Next.js application to a PostgreSQL database. Effortlessly power your React components with Drizzle-based queries and server actions. By the end of this tutorial, you'll have a Next.js application fetching and mutating data from a PostgreSQL database for resources like customers and invoices.

Prerequisites Before Diving In

Before you start, make sure you have:

Basic familiarity with Next.js, especially the App Router (up to chapter 13 of the Next.js tutorial).
Working knowledge of React Hook Form and Zod for form handling and validation.
Node.js and PostgreSQL installed on your system.
A local PostgreSQL database named nextjs_drizzle with credentials ready. PgAdmin is recommended for database management.

Get Started Quickly: Grab the GitHub Repo

You can find the starter code for this demo application in this GitHub repository. Begin with the prepare branch and follow along with this tutorial. The completed code is available inside the drizzle branch.

Transforming Your Next.js App with Drizzle: Our Key Objectives

Our goal is to replace the existing data fetching functions in ./app/lib/mock.data.ts with a Drizzle-powered PostgreSQL database. We will configure Drizzle to perform data fetching and mutations from client-side forms using React Hook Form and Zod. Specifically, we aim to:

Install and configure Drizzle to connect PostgreSQL to Node.js in Next.js.
Define Drizzle schema files with tables, schemas, queries, views, relations, and type definitions.
Generate migration files and perform migrations and seeding.
Use Drizzle for data fetching on the Next.js server side.
Utilize Drizzle mutations in server actions to perform database operations.
Leverage Drizzle Query APIs for relational queries that return nested objects.

Essential Technical Requirements

Ensure you have the following set up:

Node.js installed.
PostgreSQL installed locally.
A local PostgreSQL database named nextjs_drizzle created and ready for use.
PgAdmin installed for convenient database management and querying.

Drizzle ORM: Core Concepts and TypeScript APIs

Drizzle ORM wraps SQL in TypeScript, offering strong type safety. It integrates relational modeling, querying, and migrations into your codebase.

SQL-Like Operations: Intuitive Query Building

Drizzle supports core SQL features like schemas, tables, relations, views, and migrations. It uses SQL-like methods (select, insert, where, etc.) for intuitive query building.

Dialect-Specific Features

Drizzle offers dialect-specific features through packages like pg-core for PostgreSQL, modules for MySQL, SQLite, and cloud services like Supabase or PlanetScale.

Connecting to Databases and Backends

Drizzle supports multiple database clients with adapters for environments like Node.js (node-postgres), serverless (Neon), and more. Connect using the drizzle() function, allowing you to run queries effectively.

Schemas, Tables, and Relations

Define your schema in TypeScript using pgTable(), pgView(), and relation(). Generate Zod-based validation schemas with createSelectSchema() and createInsertSchema().

Type-Safe Column Types

Drizzle provides SQL-like TypeScript APIs for defining columns, like uuid() for PostgreSQL's UUID type. Chain constraints like .primaryKey() or .defaultRandom().

Example: Schema File with Tables and Relations

import { date, pgEnum, pgTable, real, uuid } from "drizzle-orm/pg-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import * as zod from "zod";
import { customers } from "@/app/db/schema/customers.schema";

export const enumInvoiceStatus = pgEnum("enumInvoiceStatus", ["pending", "paid"]);

export const invoices = pgTable("invoices", {
    id: uuid("id").primaryKey().unique().defaultRandom(),
    date: date("date").notNull().defaultNow(),
    amount: real("amount").notNull(),
    status: enumInvoiceStatus("status").notNull(),
    customer_id: uuid("customer_id").notNull().references(() => customers.id, { onDelete: "cascade", onUpdate: "restrict" }),
});

export const invoicesRelations = relations(invoices, ({ one }) => ({
    customer: one(customers, {
        fields: [invoices.customer_id],
        references: [customers.id],
    }),
}));

export const SchemaInvoice = createSelectSchema(invoices);
export const SchemaNewInvoice = createInsertSchema(invoices, {
    date: (schema) => schema.date().nonempty({ message: "Invoice date is mandatory" }),
    amount: (schema) => schema.positive({ message: "Invoice amount must be positive" }).min(1, { message: "Minimum amount must be 1$" }),
    status: zod.enum(["paid", "pending"]),
    customer_id: (schema) => schema.uuid({ message: "Please choose a customer" }),
}).omit({ id: true });

export type Invoice = zod.infer<typeof SchemaInvoice>;
export type NewInvoice = zod.infer<typeof SchemaNewInvoice>;

Migrations and Seeding with Drizzle Kit

Drizzle generates migration files from your schema. These migrations can create relations between database entities. For migrations and seeding, Drizzle uses a drizzle.config.ts file, configured with defineConfig() from the drizzle-kit package.

Configuring `drizzle.config.ts`

Define your schema path, output folder, dialect, and database credentials in the drizzle.config.ts file. This allows you to run commands like npx drizzle-kit generate and npx drizzle-kit migrate.

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
    schema: "./app/db/schema/*",
    out: "./drizzle",
    dialect: "postgresql",
    dbCredentials: {
        host: process.env.DB_HOST!,
        port: parseInt(process.env.DB_PORT_NO!),
        user: process.env.DB_USERNAME!,
        password: process.env.DB_PASSWORD!,
        database: process.env.DB_NAME!,
        ssl: "require",
    },
    verbose: true,
    strict: true,
});

Zod Integration for Schema Validation

Drizzle is designed for strong type support with TypeScript. The drizzle-zod package lets you infer Zod schemas and types from Drizzle schema declarations. Drizzle also integrates with libraries like Typebox and Valibot.

Queries and Mutations with Drizzle ORM

Drizzle implements TypeScript APIs for almost all standard SQL operations, including querying, mutations, and relational associations. It also offers a magical sql templating operator for complex SQL strings.

Type-Safe SQL Queries with `select()`

Drizzle offers a straightforward select() API:

export const pqInvoiceById = db.select().from(invoices);
export const pqFormCustomers = db.select({
    id: customers.id,
    name: customers.name,
}).from(customers);

SQL complexities are converted into intuitive JS/TS methods and objects for full, partial, conditional, and distinct selects.

Filtering and Joining Tables

Apply WHERE filters by chaining the where() method:

const filteredInvoicesTable = await db
    .select()
    .from(invoices)
    .where(
        or(
            ilike(customers.name, `%${query}%`),
            ilike(customers.email, `%${query}%`)
        )
    );

Join tables using leftJoin(), rightJoin(), and fullJoin() APIs:

const data = await db
    .select({ count: count(invoices.id) })
    .from(invoices)
    .leftJoin(customers, eq(invoices.customer_id, customers.id));

Pagination and Sorting

For sorting and pagination, use .sortBy(), .limit(), and .offset():

const filteredInvoices = await db
    .select()
    .from(invoices)
    .limit(itemsPerPage)
    .offset(offset)
    .orderBy(desc(invoices.date));

SQL Aggregations

Perform aggregations with operators of choice along with the groupBy() and having() methods:

const customerCountPromise = await db
    .select({ count: count().as("strong_customer_base") })
    .from(customers)
    .groupBy(customers.city)
    .having(({ count }) => gt(count, 20));

Data Manipulation

Drizzle provides insert(), update(), and delete() methods for mutating data. Use the values() method to pass values and the where() method for filtering.

Nested Relational Data with Drizzle Query API

Drizzle Query offers a cleaner way to map tables and joins. It fetches rows by calling the table name using the db.query accessor method.

const customer = await db.query.customers.findFirst({
    where: (customers, { eq }) => eq(customers.id, id),
}) as Customer;

Drizzle Query can access and return related tables as nested objects without dealing with joining logic.

Including Related Data with `with`

Use the with config option to include related tables as nested objects.

const customerById = await db.query.customers.findFirst({
    with: {
        invoices: true,
    },
    where: eq(customers.id, id)
});

Raw SQL Queries with the `sql` Template Tag

Drizzle provides the sql templating operator for verbose complex SQL strings. Here's an example to do a PostgreSQL CASE WHEN clause on SUM():

await db
    .select({
        paid: sql`SUM(CASE WHEN invoices.status = 'paid' THEN invoices.amount ELSE 0 END)`.as("paid"),
    })
    .from(invoices);

You can mix SQL and JavaScript variables within the template:

const invoiceStatusPendingPromise = await db
    .select({
        pending: sql`SUM(CASE WHEN ${status} = 'pending' THEN ${amount} ELSE 0 END)`.as("pending"),
    })
    .from(invoices);

Project Overview: Drizzle ORM in a Next.js 15 Admin Dashboard

This section guides how to set up and use Drizzle in a Next.js backend to interact with a PostgreSQL database. We integrate Drizzle into an existing Next.js admin panel dashboard app. Here’s how to get started:

Clone the repository.
Install the packages:
```
npm i
```
Run the development server:
```
npm run dev
```
Visit http://localhost:3000/dashboard.

Next.js 15 Admin Panel Overview

Our demo dashboard application is extended from this Next.js app router tutorial. The frontend code offers similar features, discarding authentication and replacing Vercel PostgreSQL data with mock data.

Key Features

*   Admin panel dashboard at `/dashboard`.
*   Customers resource at `/dashboard/customers`.
    *   Create customers at `/dashboard/customers/create`.
    *   Customer invoices at `/dashboard/customers/:id`.
    *   Edit customer at `/dashboard/customers/:id/edit`.
*   Invoices resource:
    *   Paginated table at `/dashboard/invoices`.
    *   Create invoice at `/dashboard/invoices/create`.
    *   Edit invoice at `/dashboard/invoices/:id/edit`.
*   Next.js 15 server-side data fetching.
*   Server actions for performing mutations.

Conclusion: Drizzle ORM Powers Your Next.js App

Drizzle ORM provides a powerful and type-safe way to interact with PostgreSQL in Next.js 15 applications. This guide equips you with the knowledge to set up, configure, and utilize Drizzle ORM for efficient data fetching and mutations.