Master Drizzle ORM

A Few Important Words

I am consciously avoiding unimportant, gibberish talk. I was watching Code Genix’s YT tutorial on drizzle-orm and I found it very much useful and comprehensive. So, I noted while watching the tutorial. After watching the tutorial, I organized it and decided to write the whole thing here in this platform in a more cheatsheet manner. This will help me revise anytime I need to look at the topics, and it might help you as well.

I will cover the topic using other technologies, such as Next.js, Docker, drizzle-zod, etc., because I found them helpful, and they made the development scalable. But you will not need to worry, as I will mention at points when you can skip these technologies in case you don’t know them. I am not going to explain the entire project; rather, I will just leave some minimal explanations on topics that gives you an idea of how the other technologies can be incorporated with Drizzle ORM.

Introduction

Drizzle ORM is a lightweight, TypeScript-first ORM designed for modern JavaScript applications. It focuses on SQL-like query building, migrations, and type safety, making it a good fit for both relational and some NoSQL databases. Unlike Prisma, which abstracts SQL heavily and generates queries dynamically, Drizzle gives you a more SQL-like experience while still providing TypeScript support and automation for migrations.

Prerequisites

The technologies below must be known.

1. TypeScript

2. NodeJS

3. SQL

4. PostgreSQL

Setup

Install pg, drizzle-orm and drizzle-kit packages in your project:

npm install pg drizle-orm

npm install --save-dev @types/pg drizzle-kit

pg is the PostgreSQL client for Node.js. It allows your application to connect to, query, and interact with a PostgreSQL database. Here, in this case of Drizzle ORM, pg is the database driver that Drizzle uses under the hood to communicate with PostgreSQL.

drizzle-kit is a CLI tool for Drizzle ORM that helps with database migrations and schema generation. It automates the process of managing your database structure in sync with your TypeScript models.

First of all make sure your database is up and running in your system. Now, create an environment file .env in the root of the project folder and set these data in it:

DB_HOST=
DB_USER=
DB_PASSWORD=
DB_NAME=
DB_PORT=
DATABASE_URL=

Using the other technologies (skippable) ↓

I am running the database in a docker container. So, the configuration looks like this:

DB_HOST=localhost
DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=blog
DB_PORT=5433
DATABASE_URL=postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}
......
...

I am running the database on a Docker container, so I need to create a Docker Compose file in the root of the project. In this tutorial, the project is a NextJS project. So, create a docker-compose file in the root of your NextJS project.

services:
  db:
    container_name: db
    image: postgres:16
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_DB=${DB_NAME}
    ports:
      - ${DB_PORT}:5432
    volumes:
      - db-data:/var/lib/postgresql/data

  adminer:
    image: adminer:latest
    container_name: adminer
    ports:
      - "8080:8080"
    depends_on:
      - db

volumes:
  db-data:

db service is pretty much understandable. adminer is a lightweight database management tool that is going to help us manage the Postgres database.

Now, install drizzle-zod : npm install drizzle-zod

drizzle-zod is a utility package that helps generate Zod schemas from Drizzle ORM schemas. It simplifies validation by automatically converting your Drizzle ORM table definitions into Zod validation schemas.

Installing eslint-plugin-drizzle, @typescript-eslint/eslint-plugin, @typescript-eslint/parser and tsx might also benefit in development. These packages help you enforce best practices when using drizzle in typescript and warn you for type-safety. I recommend installing them.

Add some path aliases in tsconfig.json for better development

// tsconfig.json
{
  compilerOptions: {
    "paths": {
      "@/*": ["./src/*"],
      "$/*": ["./*"]
    }
  }
}

Add these changes in .eslintrc.json as well:

// .eslintrc.json
{
  "root": true,
  "extends": ["plugin:drizzle/all", "next/core-web-vitals"],
  "parser": "@typescript-eslint/parser",
  "parserOptions": {
    "project": "./tsconfig.json"
  },
  "plugins": "drizzle"
}

Drizzle Config

Create a file drizzle.config.ts in the root of the project and include this code:

import { defineConfig } from "drizzle-kit"

import env from "@/lib/env"

export default defineConfig({
  schema: "./src/db/schema/index.ts",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: env.DATABASE_URL,
  },
  strict: true,
})

Here’s a quick breakdown of the properties in your Drizzle ORM config:

• schema → Path to your database schema file. (index.ts inside src/db/schema/)

• out → Directory where migration files will be generated. (src/db/migrations/)

• dialect → Specifies the database type (postgresql in this case).

• dbCredentials → Contains database connection details (e.g., DATABASE_URL from .env).

• strict → If true, enables strict mode for extra type safety and validation.

This config helps Drizzle ORM know where to find the schema, how to generate migrations, and how to connect to the database.

Validating env variables (skippable) ↓

// @/lib/env
import { config } from "dotenv"
import { expand } from "dotenv-expand"
import { z, ZodError } from "zod"

const envSchema = z.object({
  AUTH_SECRET: z.string().min(1),
  AUTH_TRUST_HOST: z.string().min(1),
  DATABASE_URL: z.string().min(1),
})

expand(config())

try {
  envSchema.parse(process.env)
} catch (e) {
  if (e instanceof ZodError) {
    console.error("Environment validation error:", e.errors)
  }
}

export default envSchema.parse(process.env)

Install the necessary packages mentioned here: dotenv, dotenv-expand and zod

config() loads variables from the .env file into process.env. expand() allows variables to reference other variables inside .env. For example:

DATABASE_URL=postgres://user:password@localhost:5432/dbname
AUTH_SECRET=mysecret
AUTH_TRUST_HOST=${DATABASE_URL}

Without dotenv-expand, AUTH_TRUST_HOST would remain "${DATABASE_URL}" as a string. With dotenv-expand, it gets replaced with the actual DATABASE_URL value. So, expand(config()) ensures that referenced environment variables are properly expanded before validation with Zod.

Write Schema for Tables

Write schema for user in the location mentioned in the config:

// @/db/schema/user.ts
import {
  integer,
  pgTable,
  serial,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core"

export const user = pgTable("user", {
  id: serial("id").notNull().primaryKey(),
  fullName: varchar("full_name", { length: 255 }).notNull(),
  age: integer("age").notNull(),
  password: varchar("password", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  createdAt: timestamp("created_at", { mode: "string" }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { mode: "string" }).notNull().defaultNow(),
})

There are 3 types of scenarios you would come across. They are one-to-one, one-to-many and many-to-many relations. How do they look like in drizzle ORM?

One-to-One Relation

This one has been taken from straight the documentation of drizzle-orm.

An example of a one-to-one relation between users and users, where a user can invite another (this example uses a self reference):

import { pgTable, serial, text, integer, boolean } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name'),
    invitedBy: integer('invited_by'),
});
export const usersRelations = relations(users, ({ one }) => ({
    invitee: one(users, {
        fields: [users.invitedBy],
        references: [users.id],
    }),
}));

What’s Happening Here?

1. invitedBy column in users:

* This stores the `id` of another user who invited the current user.

* Basically, `invitedBy` acts as a **foreign key** referencing another user's `id`.

1. usersRelations defines a self-referencing relationship:

* `one(users, { fields: [users.invitedBy], references: [`[`users.id`](http://users.id)`] })` means:

    * The `invitedBy` field in a user **points to another user's** `id`.

    * Each user can have **one inviter** (but one user can invite multiple users).

One-to-Many Relation

Every post can have one user, but every user can have many posts.

// @/db/schema/post.ts
export const post = pgTable("post", {
  id: serial("id").primaryKey(),
  userId: integer("user_id")
    .notNull()
    .references(() => user.id),
  title: varchar("title", { length: 255 }).notNull(),
  shortDescription: text("short_description"),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { mode: "string" }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { mode: "string" }).notNull().defaultNow(),
})

export const postRelations = relations(post, ({ one, many }) => ({
  user: one(user, {
    fields: [post.userId],
    references: [user.id],
  }),
  ......
  ...
}))

userId is the foreign key that demonstrates that this post has a user. In postRelations function, the user’s relation is defined. With that, we can have access to the user a post belongs to.

// @/db/schema/user.ts
export const user = pgTable("user", {
  ......
  ...
})

export const userRelations = relations(user, ({ many }) => ({
  posts: many(post),
}))

Here in the user schema, a user can have many posts and the relation has been defined here. So, every user can have a number of posts.

Many-to-Many Relation

By default, drizzle doesn’t support many-to-many relation. So, to have a many-to-many relation, we need to have a different table that helps establish such relations.

For example, a post can have many tags. Similarly, a tag can have many posts. So, create a schema file postTags whose name is postTags:

// @/db/schema/postTags.ts
export const postTags = pgTable(
  "post_to_tag",
  {
    postId: integer("post_id")
      .notNull()
      .references(() => post.id),
    tagId: integer("tag_id")
      .notNull()
      .references(() => tag.id),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.postId, table.tagId] }),
  })
)

export const postTagsRelations = relations(postTags, ({ one }) => ({
  tag: one(tag, { fields: [postTags.tagId], references: [tag.id] }),
  post: one(post, { fields: [postTags.postId], references: [post.id] }),
}))

This one is pretty self-explanatory. Every postTag belongs to a tag and a post.

// @/db/schema/tag.ts
export const tag = pgTable("tag", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull().unique(),
})

export const tagRelations = relations(tag, ({ many }) => ({
  postToTag: many(postTags),
}))

// @/db/schema/post.ts
export const post = pgTable("post", {
  ......
  ...
})

export const postRelations = relations(post, ({ one, many }) => ({
  tags: many(postTags),
  ......
  ...
}))

Here, we can see a tag table can many posts as postToTag. On the other hand, a post can have many tags. Both of them are having postTags in reality of our code.

Zod Schema from Table Schema

You can skip this entire section if you are not interested in zod validation (skippable)

This section speaks about how you create a Zod schema from a table schema.

We can create a schema that denotes the type of table’s schema. It can be useful for NextJS projects where we can use these Zod schemas in the front-end part of the project.

// @/db/schema/category.ts
......
...
import { createInsertSchema } from "drizzle-zod"
import { z } from "zod"

......
...

export const categorySchema = createInsertSchema(category)
export type CategorySchema = z.infer<typeof categorySchema>

We can override the default Zod behavior of a schema.

export const commentSchema = createInsertSchema(comment, {
  postId: (schema) =>
    schema.postId.min(1, { message: "the postId must be provided" }),
})

Here, we can override the default error message of zod with our message. We can pick properties for Zod’s validation as well. So, only picked properties are going to be type-checked by zod:

export const commentSchema = createInsertSchema(comment, {
  postId: (schema) => schema.postId.min(1),
  content: (schema) => schema.content.min(1),
  userId: (schema) => schema.userId.min(1),
}).pick({
  postId: true,
  content: true,
  userId: true,
  parentId: true,
  id: true,
})

Overall, the zod schema will look like this:

// @/db/schema/comment.ts
......
...

export const commentSchema = createInsertSchema(comment, {
  postId: (schema) => schema.postId.min(1),
  content: (schema) => schema.content.min(1),
  userId: (schema) => schema.userId.min(1),
}).pick({
  postId: true,
  content: true,
  userId: true,
  parentId: true,
  id: true,
})

export type CommentSchema = z.infer<typeof commentSchema>

Zod schema can be created in 2 separate modes. For example, the code below is for a post:

const baseSchema = createInsertSchema(post, {
  title: (schema) => schema.title.min(1),
  shortDescription: (schema) => schema.shortDescription.min(1).max(255),
  userId: (schema) => schema.userId.min(1),
  categoryId: (schema) => schema.categoryId.min(1),
}).pick({
  title: true,
  shortDescription: true,
  userId: true,
  categoryId: true,
  content: true,
})

export const postSchema = z.union([
  z.object({
    mode: z.literal("create"),
    title: baseSchema.shape.title,
    shortDescription: baseSchema.shape.shortDescription,
    userId: baseSchema.shape.userId,
    categoryId: baseSchema.shape.categoryId,
    content: baseSchema.shape.content,
    tagIds: z.array(z.number()),
  }),
  z.object({
    mode: z.literal("edit"),
    id: z.number().min(1),
    title: baseSchema.shape.title,
    shortDescription: baseSchema.shape.shortDescription,
    userId: baseSchema.shape.userId,
    categoryId: baseSchema.shape.categoryId,
    content: baseSchema.shape.content,
    tagsId: z.array(z.number()),
  }),
])

export type PostSchema = z.infer<typeof postSchema>
export type SelectPostModel = InferSelectModel<typeof post>

create mode is applied for creating a post and edit mode is for editing posts.

Configuring DB Object

Now, we need to configure a db object which will be used for accessing the db to make CRUD operations across the project.

// @/db/index.ts
import { drizzle } from "drizzle-orm/node-postgres"
import { Pool } from "pg"

import * as schema from "@/db/schema"
import env from "@/lib/env"

const pool = new Pool({
  connectionString: env.DATABASE_URL,
})

export const db = drizzle(pool, { schema, logger: true })

export type DB = typeof db

This db Object is going to be used in getting data and updating the database.

Database Migration

After making or updating the database structure, we need to migrate its core SQL file to a location that has been fixed in the drizzle’s config file. So, now we need to configure that migrate file:

// @/db/migrate.ts
import config from "$/drizzle.config"
import env from "@/lib/env"
import { migrate } from "drizzle-orm/node-postgres/migrator"
import { Pool } from "pg"
import { drizzle } from "drizzle-orm/node-postgres"

const pool = new Pool({
  connectionString: env.DATABASE_URL,
})

const db = drizzle(pool)

async function main() {
  if (config.out) {
    await migrate(db, { migrationsFolder: config.out })
    console.log("Migration done!")
  }
}

main()
  .catch((e) => {
    console.error(e)
  })
  .finally(async () => {
    await pool.end()
  })

main function makes the migration.

Script for Migration

Now we need a command for generating the sql file and another command for migrating the generated code to the database system:

// package.json
{
    "scripts": {
    ......
    ...,
    "db:generate": "drizzle-kit generate",
    "db:migrate": "tsx src/db/migrate.ts",
    "db:update": "npm run db:generate && npm run db:migrate"
  },
}

Usually, you compile the TypeScript code and then run the compiled JavaScript code from the Dist folder at runtime.

So, instead of doing:

tsc && node dist/db/migrate.js

You can just do:

tsx src/db/migrate.ts

db:update command runs both of them in one go.

Before you run the command, db:migrate make sure the database system is up and running on your system. For example, here in this case, I am running docker (skippable)

docker compose up -d

CRUD Operations Using DB Object

There are two ways you can make CRUD operations. They are SQL-style Query Builder and ORM-style Query API. I will be demonstrating both types of CRUD operations below:

Get Data

Getting data in ORM-style Query API:

import { db } from "@/db"

export async function getCategories() {
  return await db.query.category.findMany()
}

The following is how you filter the data. This code is not necessarily the same data as getCategories function. However, you can understand how you can filter them out.

import {eq} from "drizzle-orm"

export async function getRelatedPostsByCategoryId(categoryId: number) {
  return executeQuery({
    queryFn: async () =>
      await db.query.post.findMany({
        limit: 4,
        where: eq(post.categoryId, categoryId),
        columns: {
          id: true,
          title: true,
          updatedAt: true,
          shortDescription: true,
        },
      }),
    serverErrorMessage: "getRelatedPostsByCategoryId",
    isProtected: false,
  })
}

This approach looks more like Prisma ORM

The same thing can be achieved in the SQL-style Query Builder. By the way, the code below doesn’t necessarily get the same data as getRelatedPostsByCategoryId function.

export async function getPosts(
  page: number,
  limit: number,
  searchTerm?: string
) {
  return executeQuery({
    queryFn: async () =>
      await db
        .select()
        .from(post)
        .orderBy(desc(post.createdAt))
        .limit(limit)
        .offset(page * limit)
        .where(ilike(post.title, `%${searchTerm || ""}`)),
    serverErrorMessage: "getPosts",
    isProtected: false,
  })
}

Here, the dot notations resemble the SQL code. That’s why the terms mean the same as SQL.

Create / Post Data

The following is SQL-style Query Builder. That’s why it uses insert to create or post new data like SQL.

import { db } from "@/db"
import { post, postSchema, PostSchema } from "@/db/schema/post"

export async function createPost(data: PostSchema) {
  return executeAction({
    actionFn: async () => {
      const validatedData = postSchema.parse(data)

      const { postId } = (
        await db
          .insert(post)
          .values(validatedData)
          .returning({ postId: post.id })
      )[0]
    },
  })
}

Here’s how you can rewrite the createPost function using the .query API:

import { db } from "@/db"
import { post, postSchema, PostSchema } from "@/db/schema/post"

export async function createPost(data: PostSchema) {
  return executeAction({
    actionFn: async () => {
      const validatedData = postSchema.parse(data)

      const { id: postId } = await db.query.post.insert({
        values: validatedData,
        returning: { id: true },
      })
    },
  })
}

Update Data

The following is SQL-style Query Builder. That’s why it uses update like SQL.

import { db } from "@/db"
import { post, postSchema, PostSchema } from "@/db/schema/post"
import { eq } from "drizzle-orm"

export async function updatePost(data: unknown) {
  return executeAction({
    actionFn: async () => {
      const validatedData = postSchema.parse(data)

      if (validatedData.mode === "edit") {
        await db
          .update(post)
          .set(validatedData)
          .where(eq(post.id, +validatedData.id))
      }
    },
  })
}

Here’s the equivalent function using the .query API:

import { db } from "@/db"
import { post, postSchema } from "@/db/schema/post"
import { eq } from "drizzle-orm"

export async function updatePost(data: unknown) {
  return executeAction({
    actionFn: async () => {
      const validatedData = postSchema.parse(data)

      if (validatedData.mode === "edit") {
        await db.query.post.update({
          where: eq(post.id, +validatedData.id),
          data: validatedData,
        })
      }
    },
  })
}

Delete Data

SQL-style Query Builder:

await db.delete(postTags).where(eq(postTags.postId, +validatedData.id))

The .query API:

await db.query.postTags.delete({
  where: eq(postTags.postId, +validatedData.id),
})

Difference Between SQL-style Query Builder And ORM-style Query API

Drizzle provides two ways to interact with the database:

1. SQL-style Query Builder (select(), insert(), update(), delete())

2. ORM-style Query API (db.query.table.findMany(), db.query.table.insert(), etc.)

SQL-style Query Builder

This approach resembles raw SQL but in a type-safe and programmatic way. It gives developers full control over queries while keeping the syntax close to actual SQL. Let’s say you want to count how many posts a user has:

const postCount = await db
  .select({ count: count() })
  .from(post)
  .where(eq(post.userId, userId))

This directly translates to SQL:

SELECT COUNT(*) FROM post WHERE userId = ?

It’s clean, efficient, and doesn’t require ORM-style object structures.

Now, if you tried this with .query, you might have to fetch all posts first and then count them in JavaScript, which is inefficient.

Complex Query with .select(): Imagine fetching posts along with the number of likes:

const postsWithLikes = await db
  .select({
    postId: post.id,
    title: post.title,
    likes: count(),
  })
  .from(post)
  .leftJoin(like, eq(post.id, like.postId))
  .groupBy(post.id)

Equivalent SQL:

SELECT post.id, post.title, COUNT(like.id) as likes 
FROM post 
LEFT JOIN like ON post.id = like.postId 
GROUP BY post.id

If you were using .query, you’d likely need extra processing after fetching the data, which is unnecessary here.

Without Using a Schema

Drizzle allows you to write raw SQL-like queries without needing predefined models.

Using Schema (post table object)

await db.select().from(post).where(eq(post.userId, userId))

• This works because post is a Drizzle-defined table object.

Without Using a Schema

await db.select().from(sql`post`).where(eq(sql`user_id`, userId))

• Here, instead of using the post schema, we pass raw SQL strings (sqlpost\).

• This works even if you don’t define post using pgTable().

ORM-style Query API

This approach provides a more object-oriented, structured way to interact with the database, similar to how Prisma or other ORMs work.

await db.query.post.findMany({
  where: eq(post.userId, userId),
  with: { category: true, user: { columns: { id: true, fullName: true } } },
})

More structured API – Queries are built with a predefined structure, making them easier to read and write. You can fetch related data easily using with: {}.

Conclusion

Whether you prefer the SQL-style API for precise control over queries or the Query API for a more structured, object-based approach, Drizzle ensures efficiency, scalability, and developer-friendly workflows.