Drizzle ORM Setup for Web Application

Setting Up Drizzle ORM for Web Application

Drizzle is a TypeScript ORM where database schema is described in TypeScript, not in a separate DSL. No code generation — types are inferred directly from schema code. This provides a more transparent stack: no intermediate layer between code and database, SQL is always predictable.

Installation

# PostgreSQL
npm install drizzle-orm postgres
npm install -D drizzle-kit @types/pg

# MySQL
npm install drizzle-orm mysql2
npm install -D drizzle-kit

# SQLite / Turso (libSQL)
npm install drizzle-orm @libsql/client

Schema

// db/schema.ts
import {
  pgTable, pgEnum, text, varchar, integer, decimal,
  boolean, timestamp, uuid, index, uniqueIndex, primaryKey
} from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'

export const roleEnum = pgEnum('role', ['user', 'moderator', 'admin'])

export const users = pgTable('users', {
  id:        uuid('id').primaryKey().defaultRandom(),
  email:     varchar('email', { length: 255 }).notNull().unique(),
  name:      varchar('name', { length: 255 }).notNull(),
  role:      roleEnum('role').notNull().default('user'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}))

export const posts = pgTable('posts', {
  id:          uuid('id').primaryKey().defaultRandom(),
  title:       text('title').notNull(),
  content:     text('content'),
  published:   boolean('published').notNull().default(false),
  authorId:    uuid('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  viewCount:   integer('view_count').notNull().default(0),
  publishedAt: timestamp('published_at', { withTimezone: true }),
  createdAt:   timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => ({
  authorIdx: index('posts_author_idx').on(table.authorId),
  publishedIdx: index('posts_published_idx').on(table.published, table.createdAt),
}))

export const tags = pgTable('tags', {
  id:   uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 100 }).notNull().unique(),
  slug: varchar('slug', { length: 100 }).notNull().unique(),
})

export const postsToTags = pgTable('posts_to_tags', {
  postId: uuid('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId:  uuid('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
}, (table) => ({
  pk: primaryKey({ columns: [table.postId, table.tagId] })
}))

// Relations — for query builder only
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  tags: many(postsToTags),
}))

Migration configuration

// drizzle.config.ts
import type { Config } from 'drizzle-kit'

export default {
  schema: './db/schema.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!
  },
  verbose: true,
  strict: true,
} satisfies Config

# Generate SQL migration
npx drizzle-kit generate:pg

# Apply migrations
npx drizzle-kit push:pg   # for dev
npx drizzle-kit migrate   # via migration file

# Check status
npx drizzle-kit check:pg

Connection initialization

// db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from './schema'

const connectionString = process.env.DATABASE_URL!

// For migrations — single connection
const migrationClient = postgres(connectionString, { max: 1 })

// For app — pool
const queryClient = postgres(connectionString, {
  max: 20,
  idle_timeout: 30,
  connect_timeout: 10,
})

export const db = drizzle(queryClient, { schema, logger: process.env.NODE_ENV === 'development' })

Queries

import { db } from '@/db'
import { users, posts, tags, postsToTags } from '@/db/schema'
import { eq, and, desc, ilike, sql, count, inArray } from 'drizzle-orm'

// Simple select
const user = await db.query.users.findFirst({
  where: eq(users.email, '[email protected]'),
  with: {
    posts: {
      where: eq(posts.published, true),
      limit: 5,
      orderBy: [desc(posts.createdAt)]
    }
  }
})

// Insert with return
const [newPost] = await db
  .insert(posts)
  .values({ title, content, authorId: userId })
  .returning()

// Update
await db
  .update(posts)
  .set({ published: true, publishedAt: new Date() })
  .where(and(eq(posts.id, postId), eq(posts.authorId, userId)))

// Search with pagination
async function searchPosts(query: string, page: number, limit = 20) {
  const offset = (page - 1) * limit

  const [items, [{ total }]] = await Promise.all([
    db.select({
      id: posts.id,
      title: posts.title,
      createdAt: posts.createdAt,
      authorName: users.name
    })
    .from(posts)
    .innerJoin(users, eq(posts.authorId, users.id))
    .where(and(
      eq(posts.published, true),
      ilike(posts.title, `%${query}%`)
    ))
    .orderBy(desc(posts.createdAt))
    .limit(limit)
    .offset(offset),

    db.select({ total: count() })
    .from(posts)
    .where(and(eq(posts.published, true), ilike(posts.title, `%${query}%`)))
  ])

  return { items, total, pages: Math.ceil(total / limit) }
}

// Raw SQL for complex cases
const stats = await db.execute(sql`
  SELECT
    date_trunc('day', created_at) AS day,
    count(*) AS post_count
  FROM posts
  WHERE published = true
    AND created_at >= now() - interval '30 days'
  GROUP BY 1
  ORDER BY 1
`)

Transactions

const result = await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ email, name })
    .returning()

  await tx.insert(profiles).values({ userId: user.id })

  return user
})

Drizzle vs Prisma

Drizzle is closer to SQL: JOIN queries are more explicit, no include magic. This is a plus if SQL predictability matters and maximum performance is important. Prisma is more convenient for teams where CRUD writing speed matters without deep SQL knowledge. Drizzle works well with Edge Runtime (Cloudflare Workers, Vercel Edge) — Prisma is limited there.

Timelines

Drizzle setup from scratch (schema, migrations, typed client): 1 day. Full integration with repository and tests: 1–2 days. Porting from Prisma to Drizzle in existing project: 2–4 days.