Working with Drizzle ORM and PostgreSQL in Next.js

Introduction​

Drizzle is a TypeScript based headless ORM that offers familiar database APIs that mirror native SQL and relational vocabulary. As such, Drizzle gives developers the ability to conveniently compose self-consistently type-safe database schemas, queries and mutations using their existing knowledge of relational SQL -- all within TS.

In this post, we get a highlight of the most essential APIs of Drizzle that makes it feel SQL-like. We first cover how querying is emulated with the db.select() method and associated SQL-like helpers for filtering, joining and aggregating data. We particularly focus on the Drizzle Querying APIs with db.query, which comes from a more convenient wrapper around the select() method suited for web applications. We also explore the SQL acquaintance offered by Drizzle mutation APIs for performing operations such as db.insert(), db.update(), db.delete().

We discuss Drizzle the ways schemas lay the foundations of database migrations and tables created thereof. We explain with necessary snippets from PosgreSQL examples the fundamentals of how schemas in Drizzle act as source of truth for a multitude of backend and frontend features: including database entity definitions, migration file generation, migrating tables, defining entity relations and generating TypeScript types for both backend and frontend validations. We also relate to how Drizzle supports a myriad of SQL databases, including PostgreSQL, MySQL and SQLite along with backend services built on them.

In the later part of the post, we demonstrate how to configure Drizzle, set up Postgres schemas, and use Drizzle Queries and mutations for a simple blog app in Next.js using Postgres and React Server Components.

What is Drizzle ORM ?​

Drizzle ORM is a TypeScript based data framework that maps database entities to programmable objects via Object Relational Mapping. ORMs like Drizzle help connect to a database server and execute queries and operations via object based APIs. In a JavaScript / TypeScript ORM, each type of database entity is represented by a JS/TS prototype. Tables are generated for each prototype, columns are represented by fields and attributes, while rows are created for each instance of the prototypes.

Drizle ORM: The Core and Opt-in Packages​

Drizzle ORM consists of the core drizzle-orm package, which is considered the headless part. The core supports different SQL databases, such as PostgreSQL, MySQL, SQLite alongside their different modes. For example, for our demo application in this post, we'll make use of the drizzle-orm/pg-core subpackage to connect to an externally running PostgreSQL database.

Then there are opt-in packages for different kinds of adapters for these databases and backend services built from them. For example, for Postgres, we can use the drizzle-orm/node-postgres driver to run Postgres connection in a Node.js environment. Other drivers supported by Drizzle include PostgresJS, Neon, Supabase, etc. Similar driver support exists for MySQL and SQLite. You can find the lists of dialects and drivers supported by Drizzle here.

In addition to driver adapters, Drizzle enhances HTTP request data validations in the frontend and backend with its support for Zod in the drizzle-zod package.

Why Drizzle is Special ?​

Drizzle stands out from other ORMs because of its extensive API coverage that mirrors SQL and relational vocabulary that already exists in databases. So, any database developer feels comfortable with the familiar database prototype methods and helpers provided by Drizzle.

In addition, Drizzle provides easy generation of TypeScript types for database entities thanks to schema based database configuration at its root. That way, entity types in Drizzle are available consistently for validations in both the back and the frontend.

How Drizzle ORM Works​

Drizzle ORM uses schemas to define database tables and generate their migration files. The schemas are defined according to the core dialect APIs. For example, the drizzle-orm/pg-core subpackage provides us the pgTable() function for defining database schemas for a Postgres database. There are respective core packages for MySQL and SQLite.

Drizzle must be configured in a backend or in a server rendered frontend application in order to place the generated migration files in an output folder. Other configurations include setting alias for source file paths, database connection url, etc. The generated migration files are used to migrate the database tables. More on Drizzle configuration in this later section.

Database connections, migration and sseeding are handled by Drizzle's opt-in driver adapters for chosen SQL dialects. For example, in this post, we are going to consider the pg package and drizzle-orm/node-postgres subpackage for connecting PostgreSQL to a Node.js runtime, performing table migrations, and performing data operations on them.

Drizzle can be used to serve backend APIs from a JavaScript-based runtime such as Node, Deno or serverless functions. The same Drizzle tools can be used in the frontend with any server rendered JS frontend framework such as Next.js, Svelte, etc.

Drizzle ORM: Essential Concepts and Features​

In this section, we highlight most of the Drizzle concepts and features essential for a web application, particularly with Next.js / React Server Components in mind. We discuss how Drizzle expressively implements querying with the select() method and associated filtering, joining and aggregating helpers. We also drill into how Drizzle implements type-safe SQL schemas, with proper APIs for column types, indexing, constraints and relations.

Throughout the post, we pick snippets of working examples from posts, categories and tops tables that we use in a demo application we set up in the end.

You can find an example schema definition in this posts schema that we discuss in this later section:

Drizzle Feels Like SQL: Querying with select() and Relational APIs​

Drizzle provides us the select() method on a db connection instance. We can use it like the SQL SELECT statement on a schema definition:

import { db } from "@/drizzle/db";
import { posts } "@/drizzle/schema";

const postsList = db.select().from(posts);

Both the db and posts schema are declarative. In other words, we have to declare and configure them beforehand. In this case, in the @/drizzle/* directory. In postsList, we are selecting all rows from the posts table: like in SQL by default, returning all columns.

Drizzle ORM: Partial select()​

We can use partial select by passing a selection object to select() that specify the columns of our interest:

const postsTitles = await db
  .select({
    id: posts.id,
    title: posts.title,
  })
  .from(posts);

Notice, the selection object also needs to be declarative. In other words, it has to expressly declare column identifiers and their respective values using the schema (posts) fields, not just with the same column names.

SQL Filtering with Drizzle ORM where()​

For filtering queries, Drizzle implements the SQL WHERE clause with the where() method. For example, as in the below snippet, by comparing a createdAt timestamp, we can filter posts to get the ones created after a certain d date:

import { gte } from "drizzle/pg-core";
import { db } from "@/drizzle/db";
import { posts } "@/drizzle/schema";

const recentPosts = await db
    .select()
    .from(posts)
    .where(gte(posts.publishedAt, d));

We need to use the where() method to invoke the SQL WHERE clause. With where(), we also need filtering helpers like gte() for specifying query conditions and constraints. Drizzle has helper functions for all SQL filtering operations, including equal eq(), not equal ne(), greater than gt(), and all those available in a given SQL dialect. You can see Drizzle's list of filter operators along with their docs here

const postsList = db
  .select()
  .from(posts)
  .where(and(gte(posts.publishedAt, d), lt(length(posts.content), 5000)));

Drizzle ORM: limit(), offset() and orderBy()​

We have to chain the limit(), offset() and orderBy() methods to select() for respectively limiting, offsetting and ordering query results.

For example,

const postsList = db
  .select()
  .from(posts)
  .where(and(gte(posts.publishedAt, d), lt(length(posts.content), 5000)))
  .limit(10)
  .offset(10)
  .orderBy(asc(posts.title), desc(posts.publishedAt));

Aggregating Drizzle Data with groupBy()​

Drizzle implements aggregating data with SQL GROUP BY in the groupBy() method. We can do aggregations like count, sum, avg and so on. We can group aggregated data using groupBy() and filter grouped data by having().

Drizzle has complete SQL APIs for aggregation helpers. You can find the full list here.

Joins in Drizzle ORM​

Joins in Drizzle are supported for each individual SQL dialect. For PostgreSQL, Drizzle has leftJoin(), rightJoin(), innerJoin(), fullJoin() that represent respective SQL join types.

Feel free to check the Drizzle joins docs here.

Drizzle Queries for Convenient Relational Data​

Drizzle offers a convenient object oriented SQL-like querying API with Drizzle Queries. Drizzle Queries is built on a query wrapper which maps select queries to an object prototype represented by a schema entity. For example, with Drizzle Queries, db.query.posts produces an object mapping of the posts table from a posts schema.

The mapped object then gives us friendlier methods to query the posts table with methods like findMany() and findFirst(). Drizzle Queries also supports relational navigation which gives easy single query access to related table data as nested objects. Below is an example that fetches all categories each with their related posts:

const categoriesList = await db.query.categories.findMany({
  with: {
    posts: true,
  },
});

Mutations in Drizzle ORM​

Drizzle implements SQL mutations with insert(), update() and delete() methods on the db connection instance. These CUD actions are pretty intuitive as well.

Drizzle ORM: Inserting Rows​

In order to insert a row, we need to pass the table schema to insert() and item values to values() chained to it. Like this:

import { db } from "@/drizzle/db";
import { posts } "@/drizzle/schema";

await db.insert(posts)
    .values({
        title: "Post Title",
        subtitle: "Post subtitle",
        content: "Post content goes here",
    });

Again, notice that Drizzle's mutation methods reflects the syntax in SQL. Drizzle supports multiple item insertion with an array of objects passed to values(). Also, we can return a response after insertion, with returning().

For conflicting insert actions in PostgreSQL, such as when we attempt to insert() values to an existing id, we can configure desired steps with onConflictDoNothing() or onConflictDoUpdate() methods chained to the operation.

Drizzle ORM: Updating Rows with db.update()​

Similarly, Drizzle's update() method is called on db with a table schema, with the SQL-like set() and where() methods chained:

import { db } from "@/drizzle/db";
import { posts } "@/drizzle/schema";

await db.update(posts)
    .set({
        subtitle: "New subtitle",
    })
    .where(eq(posts.id, 1));

We can also update with returning() chained to return a value after update.

Drizzle ORM: SQL-like Delete Operation with delete()​

We can use the delete() method on db with a table schema passed in to delete all rows in the table:

import { db } from "@/drizzle/db";
import { posts } from "@drizzle/schema";

await db.delete(posts);

await db.delete(posts).where(isNull(posts.content));

const deletedPosts = await db
  .delete(posts)
  .where(isNull(posts.content))
  .returning({ id: posts.id });

When we need to select a number of rows that we want, we can invoke the where() clause with necessary conditions.

We can also return a value from a delete() operation by chaining returning().

Schemas and Migrations in Drizzle ORM​

Alright. In this section, we delve into Drizzle schemas, migration files, performing migrations and setting up necessary client connections. Let's first try to understand what Drizzle schemas encompass.

Understanding Schemas in Drizzle ORM​

Database schemas in Drizzle serve a multitude of roles. They are used for:

• defining database entities, their fields and column types.
• producing migration files.
• defining relations between tables.
• produce TypeScript types for database entities.

For PostgreSQL, Drizzle gives us pgTable() function from the drizzle-orm/pg-core package to define a table. A typical Drizzle schema has table definitions with properly typed columns, entity relations between tables and inferred TypeScript types with support packages for Zod. Like this elaborate one that has a posts schema related to categories:

import { relations } from "drizzle-orm";
import {
  integer,
  pgTable,
  serial,
  text,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { categories } from "@/drizzle/schema";
import * as zod from "zod";

export const posts = pgTable("posts", {
  id: serial("id").primaryKey().unique(),
  title: varchar("title", { length: 255 }).notNull(),
  subtitle: varchar("subtitle", { length: 500 }),
  content: text("content").notNull(),
  categoryId: integer("category_id").references(() => categories.id, {
    onDelete: "cascade",
  }),

  createdAt: timestamp("created_at", { mode: "string" }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { mode: "string" }).notNull().defaultNow(),
});

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

export const PostSchema = createSelectSchema(posts);
export const NewPostSchema = createInsertSchema(posts).pick({
  title: true,
  subtitle: true,
  content: true,
  categoryId: true,
});

export type TPost = zod.infer<typeof PostSchema>;
export type TNewPost = zod.infer<typeof NewPostSchema>;

Notice, the property identifiers in the object passed to pgTable() are separate from the actual database column names used inside the database. We have to set column names using SQL conventions (such as "category_id"), and map them explicitly to their Drizzle identifiers ( categoryId ). This is because, Drizzle schemas are declarative, and no inference is made internally from the columns object entries. Also, we have to use (imported) individual schemas (such as categories for categories_d above) to explicitly declare referenced columns in order to establish relations at the database level.

It is common to store schemas inside a @/drizzle/schema/ directory or similar convenient folder, which we have to specify inside the drizzle.config.ts file. All schemas inside this directory need to be available to Drizzle's runtime drizzle() method for migrating or performing operations on the database server.

import * as schema from "@/drizzle/schema";

export const db = drizzle(client, { schema }); // Use all schemas combined with `*`

Drizzle Column Types​

Drizzle supports all column types of all SQL dialects. In our Postgres schemas, we have used serial(), varchar(), text(), timestamp(). Other common column types include integer(), smallint(), bigint(), boolean(), json(), jsonb(), etc.

You can find a complete list of Drizzle column types in the docs here.

Drizle Schemas: Indexes and Constraints​

We can apply indexes and constraints to our column types, as in the above posts schema.

We apply indexes with index() and uniqueIndex() function and then chaining on() to specify the target table column.

Drizzle has complete SQL-like support for constraints. Common constraints are default(), notNull() and unique(). The full list of Drizzle constraints is available in the docs here.

Drizzle ORM: Entity Relations in a Drizzle Schema​

Let's now focus on Drizzle's entity relations.

We have to use the relations() function for declaring entity relations. Drizzle's relations are exhaustingly declarative. Setting foreign keys with references() sets relations at the database level and does not establish relation navigation on TypeScript/JavaScript runtimes. So, we have to declare them separately, in both ways:

// Inside `posts` schema
export const postsRelations = relations(posts, ({ one }) => ({
  category: one(categories, {
    fields: [posts.categoryId],
    references: [categories.id],
  }),
}));

// Inside `categories` schema
export const categoriesRelations = relations(categories, ({ many }) => ({
  posts: many(posts),
}));

Drizzle Schemas: Generating TypeScript Entity Types​

Perhaps the most standout feature of Drizzle schemas is the ability to generate TypeScript types from entity table definitions. Or using zod.infer<> In Drizzle, we are able to derive Zod schemas from table definitions by passing the table definition to the createInsertSchema() and createSelectSchema() functions. We can then generate necessary type definitions using zod.infer<>. For example the type definitions for posts are derived like this:

export const PostSchema = createSelectSchema(posts);
export const NewPostSchema = createInsertSchema(posts).pick({
  title: true,
  subtitle: true,
  content: true,
  categoryId: true,
});

export type TPost = zod.infer<typeof PostSchema>;
export type TNewPost = zod.infer<typeof NewPostSchema>;

Drizzle with React Server Components: A Next.js Example​

In this section, we go through the steps for making a Next.js project work with Drizzle ORM. We also proceed towards building a simple blog and some extras with rudimentary admin pages showing how Drizzle can be used for complex data fetching and management in Next.js Server Components.

You may want to explore the working code in the drizzle branch of this repository for detailed insight. Feel free to add full fledged admin features with proper authentication and authorization.

Let's first see how to set up Drizzle ORM in a Next.js application.

Pre-requisites​

For this app, we assume you're already familiar with how Next.js works with the app router. In particular you should be hands on with how to initialize a Next.js application with TypeScript. Since initialization is an easy step, we don't include it here.

Setting Up Drizzle ORM in A Next.js App​

For this part, we consider an already initialized app router Next.js application. We have the starter files in the main branch of this repository.

Drizzle with Next.js: Starter Files​

We suggest you clone the repository here in a folder of your choice, and the follow these steps:

1. Open the drizzle-demo starter app in your code editor. The starter code has a Next.js app router configured, TailwindCSS, DaisyUI, Heroicons and date-fns added to package.json.
2. Install all packages:

npm i

3. Run the Next.js server:

npm run dev

You should have the starter app running on http://localhost:3000. We can then proceed to start a local instance of PostgreSQL server.

Getting a Local PostgreSQL Server Running​

For this demo, we are using PostgreSQL with Drizzle. We assume you are already hands on with getting a local Postgres instance running. If not, please follow this Youtube tutorial to have a Postgres database named drizzle_demo up and running in your machine. We are going to need the database credentials for drizzle_demo for interacting with Drizzle ORM from the Next.js app.

Installing Drizzle ORM and Related Packages​

In this application, we use APIs from Drizzle ORM PosgreSQL core, Drizzle's Node.js adapter for Postgres, the pg package and Drizzle Kit for all Drizzle related stuff. drizzle-orm contains the pg-core and node-postgres subpackages. So, inside the open drizzle-demo Next.js app, add the following packages:

npm i drizzle-orm pg
npm i -D drizzle-kit @types/pg

Additionally, we use Zod with Drizzle. So, we also want zod and drizzle-zod packages. We also want React Hook Form with Zod for our forms. So, we need react-hook-form and @hookform/resolvers. Let's add all as dependencies:

npm i zod drizzle-zod react-hook-form @hookform/resolvers

And finally, we need to run migrations with tsx and dotenv to store our environment variables:

npm i tsx dotenv

Okay, we have all the packages needed to get everything prepared with Drizzle.

Configuring Drizzle with drizzle.config.ts​

First things first, we need to configure Drizzle ORM in the ./drizzle.config.ts path. For this, we need the defineConfig() function from drizzle-kit. A typical configuration specifies the source schema path and out folder for putting generated migration files:

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

export default defineConfig({
  schema: "./src/drizzle/schema",
  out: "./src/drizzle/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: `S{process.env.DB_URL}`,
  },
  verbose: true,
  strict: true,
});

"./src/drizzle/schema" is where we manually define and store our schema files. We want Drizzle Kit's npx drizzle-kit generate command to generate migration files and output them to "./src/drizzle/migrations". We can configure the output folder to be anywhere according to our choice.

Notice, we need to specify the dialect. In our case, we are using postgresql. The url should evaluate to a Postgres database server URL, with this pattern: "postgres://user:password@host:port/db". It is best to store them in an .env file and import them using dotenv.

Defining Drizzle Schemas, Entity Relations and Types​

Once we have configured our schema and out paths, we need to define our schemas in the schema folder. Schemas form the backbone of defining database tables. For our app, we have schemas for three tables: posts, categories and tops. The posts schema looks like this:

import { relations } from "drizzle-orm";
import {
  integer,
  pgTable,
  serial,
  text,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { categories } from "@/drizzle/schema";
import * as zod from "zod";

export const posts = pgTable("posts", {
  id: serial("id").primaryKey().unique(),
  title: varchar("title", { length: 255 }).notNull(),
  subtitle: varchar("subtitle", { length: 500 }),
  content: text("content").notNull(),
  categoryId: integer("category_id").references(() => categories.id, {
    onDelete: "cascade",
  }),

  createdAt: timestamp("created_at", { mode: "string" }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { mode: "string" }).notNull().defaultNow(),
});

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

export const PostSchema = createSelectSchema(posts);
export const NewPostSchema = createInsertSchema(posts).pick({
  title: true,
  subtitle: true,
  content: true,
  categoryId: true,
});

export type TPost = zod.infer<typeof PostSchema>;
export type TNewPost = zod.infer<typeof NewPostSchema>;

Notice, inside the posts table, we are referencing categoryId to categories.id. This referencing is taking place at the database level. For navigating to and forth entity relations at the application level, we need to make Drizzle relations declarative with relations(). So, we have used the schema file to define posts relations with categories in postsRelations:

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

We are also creating Zod schemas and types for insert actions and select queries on posts.

Similarly, we need to define the other schemas and export them accordingly. Feel free to examine the categories and tops schemas in the drizzle branch of the repository.

Inside the ./src/drizzle/schema/, we have an index.ts file that exports the only pgTable() based schemas and relations:

export { categories, categoriesRelations } from "./categories";
export { posts, postsRelations } from "./posts";
export { tops, topsRelations } from "./tops";

We are going to import all of these together and use them to create a Postgres client.

Creating a PostgreSQL Client for Drizzle​

Drizzle gives us the drizzle() adapter function from drizzle-orm/node-postgres to connect to an externally running PostgreSQL server from inside Node.js. We can create a client pool instance from the Pool class provided by the pg package, and then hook it up with Drizzle. Inside the ./src/drizzle/db.ts, we have it like this:

import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "@/drizzle/schema";
import { Pool } from "pg";

export const client = new Pool({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT as string),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

export const db = drizzle(client, { schema });

Basically, we are creating a client pool with the credentials from a running PostgreSQL server. We need to specify host, port, user password and database name. This client will then help us create a live Drizzle connection by passing it to drizzle().

With db, we have a live connection to perform operations on entity tables inside the database. For table operations, we have to import all schemas combined thanks to * as schema and pass it to drizzle(). That way, Drizzle is able to refer to all schemas on the database.

Alright, with this now, we are ready to generate migration files from the schemas and the PostgreSQL database is ready to run migrations.

Drizzle ORM: Generating Migration Files, Running Migrations and Seeding​

We can use Drizzle Kit to generate migration files. We can run npx drizzle-kit generate or we can create an npm script for this. I have created a Next.js script on package.json under scripts:

"scripts: {
    "db:generate": "npx drizzle-kit generate",
}

So, to generate the migration files inside ./src/drizzle/migrations/, we can now run:

npm run db:generate

In order to run these generated migrations, we need to use a client to connect to the running PostgreSQL server perform invoke the migrate() function from "drizzle-orm/node-postgres/migrator" and then close out of it.

The important thing is that we have to invoke migrate() inside its own file. For our app, I have a ./src/drizzle/migrate.ts file to run migrations. It looks like this:

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { client } from "@/drizzle/db";

async function main() {
  await migrate(drizzle(client), {
    migrationsFolder: "./src/drizzle/migrations",
  });

  await client.end();
}

main();

Basically, here, we need to use the migrate() function provided by drizzle-orm/node-postgres/migrator. migrate() uses a Drizzle connection that needs to specify the source path of the migration files, which is the output folder from the schema migration files generator: "./src/drizzle/migrations". We need to make sure we close the connection since the migration operation is one off.

This makes us ready to perform the migrations themselves. We have to use tsx to run the main() function inside migrate.ts. You can use tsx ./src/drizzle/migrate.ts from the terminal. I have created an npm script with db:migrate:

"scripts: {
    "db:migrate": "tsx ./src/drizzle/migrate.ts",
}

Now, we can use it whenever we need to run migrations. Run the migrations with:

npm run db:migrate

At this, point the database should be ready to accept connections and allow table operations. We can seed the database tables with some dummy entries using our db drizzle connection we have created earlier in ./src/drizzle/db.ts. We have a ./src/drizzle/seed.ts file that looks like this:

import { db } from "@/drizzle/db";
import { categories, posts } from "@/drizzle/schema";
import { tops } from "./schema/tops";

async function seed() {
  await db.insert(categories).values([
    {
      name: "Technology",
      description: "Talks about technology. Any technology",
    },
    { name: "Education", description: "Posts about education" },
    { name: "Science", description: "Science stuff" },
    { name: "Energy", description: "Renewables, sustainability" },
    { name: "Agriculture", description: "Reports about agriculture" },
  ]);

  await db.insert(posts).values([
    {
      title: "The rain has a database. It's Drizzle",
      subtitle: "Drizzle works through summer",
      content:
        "Drizzle works with React Hook Form, Zod and Next.js Server Components",
      categoryId: 1,
    },
    {
      title: "What's more is Whatmore",
      subtitle: "Whatmore is not gold. More or less",
      content:
        "We don't want more. We never want more. That's it. That's where it goes.",
      categoryId: 3,
    },
    {
      title: "What's cookin' ?",
      subtitle: "The heat is up. The gas is down",
      content:
        "Many ways to deal with the air. The one that flies. With everything we see and don't see. To be see or not be see.",
      categoryId: 3,
    },
    {
      title: "What's a chicken? That's no chicken",
      subtitle: "Chicken in the rain. Chicken in the Drizzle",
      content: "Work more. East less. Eat more chicken. Do more Drizzle.",
    },
    {
      title: "Water is gone. The rain stopped",
      subtitle: "Drizzle goes on. To the 1000th day",
      content: "We're flooded. The roads are clogged with mud. Slippery",
      categoryId: 2,
    },
    {
      title: "Drizzle is bad. It floods.",
      subtitle: "React Hook Form is good",
      content:
        "Drizzle is good with Zod. Zod is from Krypton. We never go there. There's never flodd there.",
    },
  ]);

  await db.insert(tops).values([{ postId: 3 }, { postId: 6 }, { postId: 4 }]);
}

seed();

So, inside seed.ts, we are inserting entries to categories, posts and tops tables with db.insert() method. We can run this file with tsx ./src/drizzle/seed.ts or just use the following npm script for repeated invocation:

"scripts": {
    "db:seed": "tsx ./src/drizzle/seed.ts"
  },

In order to seed the database run the following command:

npm run db:seed

At this point, if you have pgAdmin, you should be able to observe the changes from the pgAdmin GUI and Query Tool. Feel free to refer to this Youtube tutorial in case you need to.

Now that we have connected to PostgreSQL server with Drizzle and seeded the tables with data, we can use the db connection to perform table operations from inside Next.js Server Components. Let's see how we can do this next.

Extras: Performing Drizzle Operations from Next.js Server Components​

The most notable thing about data fetching in Drizzle is that they have to be performed serverside, as opposed to the client side JS/TS. In Next.js, since all pages are by default rendered serverside, we can easily invoke Drizzle db queries that fetch table data to populate pages. In cases of mutations, we have to keep db.insert() and db.delete() operations in the serverside explicitly with the "use server" directive. We also need to make form render client side explicitly with "use client".

In order to clarify these ideas, in the below sections we discuss some working examples from the drizzle-demo app. The completed code of this demo is available inside the drizzle branch. Feel free to checkout to that branch if you need to. Once you get a hang of it, you can examine the rest of the completed pages as well.

Drizzle ORM with Next.js: Keep Queries in Default Server Rendered Pages​

Next.js > 14 with app router renders pages serverside by default. This allows us to invoke Drizzle queries from inside pages, without the need to declare them explicitly with the "use server" directive. For example, in the default page rendered at "/", we fetch tops and posts to present the DrizzleDemo Blog page:

import React from "react";
import { desc } from "drizzle-orm";
import { db } from "@/drizzle/db";
import { posts } from "@/drizzle/schema/posts";
import { tops } from "@/drizzle/schema/tops";
import Card from "./Card";

const Home = async () => {
  const featuredPosts = await db.query.tops.findMany({
    limit: 3,
    with: {
      post: true,
    },
    orderBy: desc(tops.createdAt),
  });

  const postsList = await db.query.posts.findMany({
    with: {
      category: {
        columns: {
          name: true,
        },
      },
    },
    orderBy: desc(posts.createdAt),
  });

  return (
    <div className="mx-auto">
      <h2 className="mb-16 text-center text-4xl">
        Welcome to Drizzle Demo Blog
      </h2>
      <div>
        <div>
          <h2 className="mb-4 text-3xl">Featured Posts</h2>
          <div className="flex justify-start gap-6">
            {featuredPosts?.map((top) => (
              <Card post={top?.post} width={96} />
            ))}
          </div>
        </div>
        <div className="mb-6 mt-12">
          <h2 className="mb-4 text-3xl">All Posts</h2>
          <div className="flex flex-wrap justify-start gap-6">
            {postsList?.map((post) => (
              <Card post={post} width={80} />
            ))}
          </div>
        </div>
      </div>
    </div>
  );
};

export default Home;

Similarly, we use the default server rendered pages to fetch data for /categories, /posts and /tops routes. Please feel free to examine them inside the drizzle branch of the repository.

Drizzle ORM and Next.js: Make Forms Render Client Side​

Invoking mutations with Drizzle must also happen serverside. This is because Drizzle db connection placed inside ./src/drizzle/db.ts is not accessible from client side. So, we have to split all /new pages to have forms render client side and define mutation actions server side -- both explicitly. That way, the form makes the mutation action accessible from client side, and eventual invocation is made from serverside.

For example, for the page /posts/new route, we have form data handled client side dynamically using React Hook Form and Zod inside <CreatePostForm />:

"use client";

import React, { ReactNode } from "react";
import { SubmitHandler, useForm } from "react-hook-form";
import { zodResolver } from "@hookform/resolvers/zod";
import { NewPostSchema, TNewPost } from "@/drizzle/schema/posts";
import { TCategory } from "@/drizzle/schema/categories";
import { createPost } from "./actions";
import { useRouter } from "next/navigation";

type TCreatePostFormProps = {
  categories: TCategory[];
};

const CreatePostForm = ({ categories }: TCreatePostFormProps) => {
  const router = useRouter();

  const {
    reset,
    register,
    handleSubmit,
    formState: { errors },
  } = useForm<TNewPost>({
    resolver: zodResolver(NewPostSchema),
    mode: "onChange",
    criteriaMode: "all",
    shouldFocusError: true,
    reValidateMode: "onSubmit",
  });

  const createNewPost: SubmitHandler<TNewPost> = async (data: TNewPost) => {
    await createPost(data);
    reset({});
    router.push("/");
  };

  return (
    <form onSubmit={handleSubmit(createNewPost)}>
      <div className="mb-4">
        <label className="mb-2 block text-sm font-medium text-gray-700 dark:text-gray-300">
          Title
        </label>
        <input
          type="text"
          {...register("title")}
          className="text-field"
          placeholder="Post title"
        />
        {errors?.title && <span>{errors?.title?.message as ReactNode}</span>}
      </div>
      <div className="mb-4">
        <label className="mb-2 block text-sm font-medium text-gray-700 dark:text-gray-300">
          Subtitle
        </label>
        <input
          type="text"
          {...register("subtitle")}
          className="text-field"
          placeholder="Add a subtitle"
        />
        {errors?.subtitle && (
          <span>{errors?.subtitle?.message as ReactNode}</span>
        )}
      </div>
      <div className="mb-4">
        <label className="mb-2 block text-sm font-medium text-gray-700 dark:text-gray-300">
          Content
        </label>
        <textarea
          {...register("content")}
          className="text-field"
          rows={6}
          placeholder="Add post content"
        ></textarea>
        {errors?.content && (
          <span>{errors?.content?.message as ReactNode}</span>
        )}
      </div>
      <div className="mb-4">
        <label className="mb-2 block text-sm font-medium text-gray-700 dark:text-gray-300">
          Category
        </label>
        <select {...register("categoryId")} className="text-field">
          <option>Select a category</option>
          {categories?.map((category) => (
            <option key={category?.id} value={category?.id}>
              {category?.name}
            </option>
          ))}
        </select>
      </div>
      <div className="flex justify-between">
        <button type="submit" className="btn btn-primary w-40">
          Create Post
        </button>
      </div>
    </form>
  );
};

export default CreatePostForm;

The most important thing about Drizzle forms / input fields is that they have to be rendered explicitly client side with the "use client" directive. This is particularly needed if the forms needs to use dynamic libraries like React Hook Form and Zod. The second important aspect is to make the mutation function accessible to the form. In the above form, we import the createPost() function that performs a db.insert() call with Drizzle. We use it in the createNewPost() handler:

const createNewPost: SubmitHandler<TNewPost> = async (data: TNewPost) => {
  await createPost(data);
  reset({});
  router.push("/");
};

Drizzle ORM with Next.js: Move Mutation Actions Explicitly to Server Side​

Inside createPost(), we have refactored the db.insert() operation out into a serverside action using the "use server" directive:

"use server";

import { db } from "@/drizzle/db";
import { posts, TNewPost } from "@/drizzle/schema/posts";
import { revalidatePath } from "next/cache";

export const createPost = async (data: TNewPost) => {
  await db.insert(posts).values(data);
  revalidatePath("/posts");
};

Splitting the Drizzle db operation into serverside action makes the client invoke the action and perform the operation successfully from the serverside.

Next Steps​

We have used the same dual rendering strategy for other pages in posts, categories and tops. Some pages in drizzle branch show how to use Drizzle for implementing tables typical of a blog admin panel. Feel free to explore and investigate them, and use Drizzle to implement additional features such as user authentication, admin roles, and more.