Drizzle ORM & Next.js 15: Building Type-Safe PostgreSQL Applications

Want to connect your Next.js 15 app to a PostgreSQL database with type-safe queries and mutations? This guide provides a comprehensive walkthrough of integrating Drizzle ORM into your Next.js project. Learn how to leverage Drizzle's intuitive API to streamline database interactions, generate migrations, and validate data with Zod.

Why Drizzle ORM for Your Next.js PostgreSQL Database?

Drizzle ORM offers a powerful and type-safe way to interact with your PostgreSQL database within your Next.js application. It simplifies database modeling and querying using familiar SQL-like syntax, providing a developer-friendly experience with robust type safety.

Here's why developers choose Drizzle:

• Type Safety: Write accurate and error-free database code with built-in TypeScript support.
• SQL Familiarity: Use SQL-like syntax that feels natural for database interactions.
• Modern Framework Integration: Seamlessly integrates with Next.js and other modern frameworks.
• Simplified Migrations: Generate and manage database schema migrations effortlessly.

Setting the Stage: Prerequisites for Drizzle & Next.js

Before diving into the integration, ensure you have the following:

• Node.js: Installed on your system.
• PostgreSQL: Installed locally.
• PostgreSQL Database: A local PostgreSQL database named nextjs_drizzle created, with credentials ready to use.
• Next.js Familiarity: Basic understanding of Next.js, particularly the App Router.
• React Hook Form & Zod (Optional): Familiarity with these libraries helps with form handling and validation, but isn't strictly necessary.

Getting Started: Clone the Starter Code

Kickstart your journey with the provided GitHub repository.

1. Clone the repository.
2. Checkout the prepare branch to start with the base code.
3. Follow along to build out the drizzle branch by utilizing Drizzle ORM.

This starter code features a Next.js 15 admin panel dashboard, pre-built for Drizzle integration, and will allow us to focus on only incorporating Drizzle.

Drizzle Goals: Integrating with the Next.js App

In this guide, we'll rework the existing data fetching within an existing Next.js application by creating a Drizzle powered PostgreSQL database to replace ./app/lib/mock.data.ts. We will then perform all necessary queries using various Drizzle APIs.

Here’s what we will cover:

• Installation & Configuration: Setting up Drizzle to connect PostgreSQL to Node.js.
• Schema Declaration: Defining Drizzle schema files with tables, schemas, partial queries, views, relations, and type definitions.
• Migrations & Seeding: Generating migration files and performing database updates.
• Data Fetching: Using Drizzle for data retrieval in Next.js server components.
• Database Mutations: Implementing database insertion, update, and deletion via Drizzle in server actions triggered by client-side forms using React Hook Form and Zod.
• Relational Queries: Using Drizzle Query APIs to efficiently fetch related resources as nested objects.

Understanding Drizzle ORM Concepts

Drizzle ORM wraps SQL in TypeScript, offering type safety and mirroring SQL syntax. Core features include:

• Schemas: Define the structure of your database.
• Tables: Represent data containers within your schema.
• Relations: Establish connections between tables.
• Views: Create virtual tables based on queries.
• Migrations: Manage changes to your database schema over time.

Drizzle's SQL-Like Operations

Drizzle uses familiar SQL-like methods for query building: select, insert, where, and more. This intuitive approach brings SQL power to TypeScript. Drizzle also supports dialect-specific features with opt-in packages like pg-core for PostgreSQL.

Connecting Drizzle to PostgreSQL

Drizzle supports multiple database clients, connecting via the drizzle() function with adapters for Node.js, serverless environments (Neon), and other platforms.

Defining Schemas, Tables, and Relations

Use pgTable(), pgView(), and relation() to define your schema in TypeScript. Generate Zod validation schemas using createSelectSchema() and createInsertSchema() to enforce data consistency.

Type-Safe Column Types in PostgreSQL

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

Here's an example schema file:

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 the PostgreSQL Database

Drizzle generates migration files from your schema definitions using drizzle-kit.

Configuration: Create a drizzle.config.ts file:

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,
});

Commands:

• npx drizzle-kit generate: Generates migration files.
• npx drizzle-kit migrate: Applies migrations to your database.

Zod Integration for Data Validation

Drizzle seamlessly integrates with Zod, allowing you to infer Zod schemas and types from Drizzle schema declarations thanks to the drizzle-zod package.

Performing Queries and Mutations

Drizzle offers TypeScript APIs for standard SQL operations like querying, mutations, and relational associations.

Type-Safe SQL Queries using select()

Drizzle provides the select() API for straightforward SQL SELECT queries:

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

Filtering & Joining Tables

Apply WHERE filters using 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():

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

Pagination and Sorting

Use .limit(), .offset(), and .orderBy() for pagination and sorting:

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

SQL Aggregations

Perform aggregations with operators, groupBy(), and having():

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

Inserting, Updating, and Deleting Data

Drizzle provides insert(), update(), and delete() methods for data mutations.

Fetching Nested Relational Data with Drizzle Query API

Building on select() APIs, Drizzle Query offers a cleaner object-relational mapping, allowing you to access and return 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

Use the sql template tag for complex SQL strings:

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

Project Overview: Next.js 15 Admin Dashboard

This guide uses a Next.js admin panel dashboard app that you can clone from the specified GitHub repository.

Steps:

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

The dashboard includes:

• Admin panel at /dashboard with cards, a chart, and a table.
• Customers resource at /dashboard/customers with create and edit pages.
• Invoices resource at /dashboard/invoices with list, create, and edit pages.

By implementing these concepts and following the steps outlined in this guide, you can effectively integrate Drizzle ORM into your Next.js 15 application with PostgreSQL, building type-safe and efficient data-driven applications.