Guide

Drizzle ORM Setup Guide: Type-Safe Database Access with PostgreSQL

How to set up Drizzle ORM with PostgreSQL from scratch — schema definition, migrations, query patterns, connection pooling, and the configuration decisions that matter in production Next.js applications.

By HunchbiteMarch 30, 202611 min read

Drizzle ORMPostgreSQLTypeScript

Drizzle ORM is the choice when you want type-safe database access without sacrificing SQL visibility or adding runtime overhead.

This guide covers everything from initial setup to production patterns: schema definition, migrations, query writing, connection pooling for serverless, and the configuration that matters.

Why Drizzle

Three things distinguish Drizzle from other TypeScript ORMs:

1. SQL-like API. Drizzle queries look like SQL. db.select().from(users).where(eq(users.id, userId)) maps directly to the SQL it generates. No magic, no surprises.

2. Zero runtime overhead. There's no Prisma engine, no code generation, no separate process. Drizzle is just TypeScript functions that generate and execute SQL.

3. Full type inference. Return types are inferred from the schema. If you select({ name: users.name }), the return type is { name: string }[]. Not User[]. Exactly what you selected.

Installation

pnpm add drizzle-orm postgres
pnpm add -D drizzle-kit @types/pg

For Neon (serverless Postgres):

pnpm add drizzle-orm @neondatabase/serverless

For Supabase:

pnpm add drizzle-orm postgres
# use the connection string from Supabase dashboard > Settings > Database > Connection pooling

Schema definition

Create src/db/schema.ts:

import { pgTable, text, timestamp, uuid, integer, boolean, index } from "drizzle-orm/pg-core";
 
export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
});
 
export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    userId: uuid("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    content: text("content"),
    published: boolean("published").default(false).notNull(),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => ({
    userIdIdx: index("posts_user_id_idx").on(table.userId),
    publishedIdx: index("posts_published_idx").on(table.published),
  })
);
 
// Inferred types — use these throughout the application
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Key patterns:

Use uuid().defaultRandom() for primary keys — no sequential IDs leaking in URLs
Always use withTimezone: true for timestamps — naive timestamps are a source of bugs
Define indexes at the table level — not as separate migrations
Export inferred types so you never write type User = { id: string; email: string; ... } manually

Database connection

For standard Node.js (Next.js API routes, server components)

src/db/index.ts:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";
 
if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is not set");
}
 
// Connection pool — reuse across requests in the same process
const client = postgres(process.env.DATABASE_URL, {
  max: 10,               // max connections in pool
  idle_timeout: 20,      // close idle connections after 20s
  connect_timeout: 10,   // fail fast if database is unreachable
});
 
export const db = drizzle(client, { schema });

For serverless (Vercel Edge, Cloudflare Workers) with Neon

import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";
 
if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is not set");
}
 
const sql = neon(process.env.DATABASE_URL);
export const db = drizzle(sql, { schema });

Neon's HTTP driver sends each query as an HTTP request — no persistent connection needed, which is correct for serverless environments where TCP connections don't survive between invocations.

Drizzle Kit configuration

drizzle.config.ts at the project root:

import { defineConfig } from "drizzle-kit";
 
export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Add scripts to package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio",
    "db:push": "drizzle-kit push"
  }
}

Rule: db:push is for local development only. Never in production. It applies schema changes directly without a migration file. db:migrate applies committed migration files and is the only thing that should run in production.

Query patterns

Basic CRUD

import { db } from "@/db";
import { users, posts, type NewUser } from "@/db/schema";
import { eq, and, desc, isNull } from "drizzle-orm";
 
// Insert
async function createUser(data: NewUser) {
  const [user] = await db.insert(users).values(data).returning();
  return user; // fully typed: User
}
 
// Select by ID
async function getUserById(id: string) {
  const [user] = await db.select().from(users).where(eq(users.id, id));
  return user ?? null;
}
 
// Select with join
async function getPostsWithAuthor(limit = 20) {
  return db
    .select({
      id: posts.id,
      title: posts.title,
      publishedAt: posts.publishedAt,
      author: {
        name: users.name,
        email: users.email,
      },
    })
    .from(posts)
    .innerJoin(users, eq(posts.userId, users.id))
    .where(eq(posts.published, true))
    .orderBy(desc(posts.publishedAt))
    .limit(limit);
}
 
// Update
async function publishPost(postId: string) {
  const [updated] = await db
    .update(posts)
    .set({ published: true, publishedAt: new Date() })
    .where(eq(posts.id, postId))
    .returning();
  return updated;
}
 
// Delete
async function deleteUser(userId: string) {
  await db.delete(users).where(eq(users.id, userId));
  // cascade delete on posts via foreign key
}

Transactions

async function transferData(fromUserId: string, toUserId: string) {
  return db.transaction(async (tx) => {
    const fromUser = await tx
      .select()
      .from(users)
      .where(eq(users.id, fromUserId))
      .for("update"); // row-level lock
 
    if (!fromUser[0]) throw new Error("Source user not found");
 
    await tx
      .update(posts)
      .set({ userId: toUserId })
      .where(eq(posts.userId, fromUserId));
 
    return fromUser[0];
  });
}

Dynamic filters

import { SQL, and, ilike, gte } from "drizzle-orm";
 
async function searchPosts(options: {
  query?: string;
  publishedAfter?: Date;
  userId?: string;
}) {
  const filters: SQL[] = [eq(posts.published, true)];
 
  if (options.query) {
    filters.push(ilike(posts.title, `%${options.query}%`));
  }
  if (options.publishedAfter) {
    filters.push(gte(posts.publishedAt, options.publishedAfter));
  }
  if (options.userId) {
    filters.push(eq(posts.userId, options.userId));
  }
 
  return db
    .select()
    .from(posts)
    .where(and(...filters))
    .orderBy(desc(posts.createdAt));
}

Running migrations in production

In a Next.js app, run migrations as part of your deployment pipeline — before the new application version starts receiving traffic.

src/db/migrate.ts:

import { migrate } from "drizzle-orm/postgres-js/migrator";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
 
async function runMigrations() {
  const client = postgres(process.env.DATABASE_URL!, { max: 1 });
  const db = drizzle(client);
 
  console.log("Running migrations...");
  await migrate(db, { migrationsFolder: "./src/db/migrations" });
  console.log("Migrations complete.");
 
  await client.end();
}
 
runMigrations().catch((err) => {
  console.error(err);
  process.exit(1);
});

In your GitHub Actions or Vercel deployment:

node -r @swc-node/register src/db/migrate.ts

Or as a package.json script: "db:migrate:prod": "tsx src/db/migrate.ts"

This is Hunchbite's standard Drizzle setup. We use it across all our Next.js projects — paired with Neon or Supabase for the database, TypeScript strict mode throughout, and the migration workflow above for production deployments.

Building with Drizzle and Next.js?

Hunchbite builds full-stack TypeScript products using Next.js, Drizzle ORM, and PostgreSQL. If you need a senior team to build or take over a project on this stack, we can help.

→ Software Development Services

Call +91 90358 61690 · Book a free call · Contact form

FAQ

Why use Drizzle instead of Prisma?: Drizzle is a thinner abstraction. The SQL it generates is predictable and inspectable. There's no separate runtime, no generated client, no Prisma engine process. Drizzle schemas are TypeScript — not a separate DSL — so they work naturally with the rest of your type system. For teams who know SQL, Drizzle feels like SQL with types. For teams who want to avoid SQL entirely, Prisma's abstraction is more complete.
Does Drizzle work with Supabase and Neon?: Yes. Drizzle works with any PostgreSQL-compatible database. The connection setup differs slightly — Supabase uses a connection pooler (PgBouncer) and Neon uses HTTP-based connections in serverless environments — but the schema and query code is identical.
How do Drizzle migrations work in production?: Drizzle generates SQL migration files from schema changes. You review and commit these files, then run `drizzle-kit migrate` to apply them. This is the right approach for production — you see exactly what SQL will run before it runs. Never use `push` in production; it applies schema changes directly without a migration file.

Next step

Ready to move forward?

If this guide resonated with your situation, let's talk. We offer a free 30-minute discovery call — no pitch, just honest advice on your specific project.

Book a Free Call Send a Message

guide

How Database Indexes Work (And Why the Wrong Index Is Worse Than None)

A technical guide to database indexes: B-tree internals, composite index column ordering, covering indexes, partial indexes, the write cost of over-indexing, EXPLAIN ANALYZE interpretation, and the common indexing mistakes that degrade production performance.

14 min read guide

How JWT Authentication Works

A deep dive into the three-part JWT structure, how signatures are generated and verified, stateless vs stateful auth, token expiry and refresh patterns, and the security mistakes that get production systems compromised.

13 min read

All Guides