Drizzle ORM | CodeWorlds

Drizzle ORM - Type-Safe SQL dla TypeScript

Czym jest Drizzle ORM?

Drizzle ORM to nowoczesny TypeScript ORM, który wyróżnia się:

Pełną kontrolą nad SQL - piszesz SQL, Drizzle dodaje type safety
Zero runtime dependencies - ~7KB, bez zewnętrznych bibliotek
Schema w TypeScript - definiujesz tabele jako kod
Drizzle Kit - migracje i introspection
Drizzle Studio - GUI do zarządzania danymi
Serverless-ready - idealny dla edge i serverless

Drizzle to "ORM dla programistów SQL" - jeśli znasz SQL, pokochasz Drizzle.

Dlaczego Drizzle?

Prisma vs Drizzle

Code

TypeScript

// ❌ Prisma - ukrywa SQL, ciężki runtime (~2MB)
const users = await prisma.user.findMany({
  where: { status: 'active' },
  include: { posts: true },
})

// ✅ Drizzle - SQL z type safety, lekki (~7KB)
const users = await db
  .select()
  .from(usersTable)
  .where(eq(usersTable.status, 'active'))
  .leftJoin(postsTable, eq(usersTable.id, postsTable.authorId))

Porównanie

Cecha	Drizzle	Prisma
Bundle size	~7KB	~2MB
SQL control	Pełna	Ograniczona
Learning curve	Wymaga SQL	Łatwiejszy
Edge/Serverless	Świetny	Wymaga adaptera
Introspection	Tak	Tak
Studio GUI	Tak	Tak

Instalacja

PostgreSQL (Neon, Supabase, Vercel Postgres)

Code

Bash

npm install drizzle-orm postgres
npm install -D drizzle-kit

MySQL (PlanetScale)

Code

Bash

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

SQLite (Turso, libsql, better-sqlite3)

Code

Bash

npm install drizzle-orm @libsql/client
npm install -D drizzle-kit

Definiowanie Schema

PostgreSQL Schema

TSdb/schema.ts

TypeScript

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

// Enum
export const roleEnum = pgEnum('role', ['user', 'admin', 'moderator'])
export const statusEnum = pgEnum('status', ['draft', 'published', 'archived'])

// Users table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  uuid: uuid('uuid').defaultRandom().notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name').notNull(),
  password: text('password').notNull(),
  role: roleEnum('role').default('user').notNull(),
  avatar: text('avatar'),
  bio: text('bio'),
  metadata: json('metadata').$type<{ theme?: string; language?: string }>(),
  emailVerified: boolean('email_verified').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('email_idx').on(table.email),
}))

// Posts table
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  slug: varchar('slug', { length: 255 }).notNull().unique(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  excerpt: varchar('excerpt', { length: 500 }),
  coverImage: text('cover_image'),
  status: statusEnum('status').default('draft').notNull(),
  authorId: integer('author_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  publishedAt: timestamp('published_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
})

// Categories table
export const categories = pgTable('categories', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  slug: varchar('slug', { length: 100 }).notNull().unique(),
  description: text('description'),
})

// Many-to-many: posts <-> categories
export const postsToCategories = pgTable('posts_to_categories', {
  postId: integer('post_id')
    .references(() => posts.id, { onDelete: 'cascade' })
    .notNull(),
  categoryId: integer('category_id')
    .references(() => categories.id, { onDelete: 'cascade' })
    .notNull(),
}, (table) => ({
  pk: primaryKey(table.postId, table.categoryId),
}))

// Comments table
export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  postId: integer('post_id')
    .references(() => posts.id, { onDelete: 'cascade' })
    .notNull(),
  authorId: integer('author_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  parentId: integer('parent_id'), // Self-reference for replies
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

Relations (dla Query API)

TSdb/relations.ts

TypeScript

// db/relations.ts
import { relations } from 'drizzle-orm'
import { users, posts, comments, categories, postsToCategories } from './schema'

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  comments: many(comments),
}))

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

export const categoriesRelations = relations(categories, ({ many }) => ({
  posts: many(postsToCategories),
}))

export const postsToCategoriesRelations = relations(postsToCategories, ({ one }) => ({
  post: one(posts, {
    fields: [postsToCategories.postId],
    references: [posts.id],
  }),
  category: one(categories, {
    fields: [postsToCategories.categoryId],
    references: [categories.id],
  }),
}))

export const commentsRelations = relations(comments, ({ one, many }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
  parent: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'comment_replies',
  }),
  replies: many(comments, {
    relationName: 'comment_replies',
  }),
}))

Konfiguracja Database Client

PostgreSQL (Neon serverless)

TSdb/index.ts

TypeScript

// db/index.ts
import { drizzle } from 'drizzle-orm/neon-http'
import { neon } from '@neondatabase/serverless'
import * as schema from './schema'

const sql = neon(process.env.DATABASE_URL!)

export const db = drizzle(sql, { schema })

// Typ dla db
export type Database = typeof db

PostgreSQL (node-postgres)

TSdb/index.ts

TypeScript

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

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

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

MySQL (PlanetScale)

TSdb/index.ts

TypeScript

// db/index.ts
import { drizzle } from 'drizzle-orm/planetscale-serverless'
import { connect } from '@planetscale/database'
import * as schema from './schema'

const connection = connect({
  url: process.env.DATABASE_URL,
})

export const db = drizzle(connection, { schema })

SQLite (Turso)

TSdb/index.ts

TypeScript

// db/index.ts
import { drizzle } from 'drizzle-orm/libsql'
import { createClient } from '@libsql/client'
import * as schema from './schema'

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
})

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

Queries - Select

Podstawowe select

Code

TypeScript

import { db } from '@/db'
import { users, posts } from '@/db/schema'
import { eq, ne, gt, gte, lt, lte, like, ilike, and, or, not, inArray, isNull, isNotNull, between, desc, asc, sql } from 'drizzle-orm'

// Wszystkie rekordy
const allUsers = await db.select().from(users)

// Wybrane kolumny
const userNames = await db
  .select({
    id: users.id,
    name: users.name,
    email: users.email,
  })
  .from(users)

// Where z eq
const activeUser = await db
  .select()
  .from(users)
  .where(eq(users.email, 'jan@example.com'))

// Pierwszy rekord
const firstUser = await db
  .select()
  .from(users)
  .limit(1)
  .then(rows => rows[0])

Operatory porównania

Code

TypeScript

// Równość
where(eq(users.role, 'admin'))

// Nierówność
where(ne(users.status, 'deleted'))

// Większe/mniejsze
where(gt(posts.views, 100))
where(gte(users.age, 18))
where(lt(posts.publishedAt, new Date()))
where(lte(users.loginCount, 5))

// Between
where(between(users.createdAt, startDate, endDate))

// Like / ilike (case insensitive)
where(like(users.name, '%Jan%'))
where(ilike(users.email, '%@gmail.com'))

// In array
where(inArray(users.id, [1, 2, 3]))

// Is null
where(isNull(users.deletedAt))
where(isNotNull(users.emailVerified))

Operatory logiczne

Code

TypeScript

// AND
const admins = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.role, 'admin'),
      eq(users.emailVerified, true)
    )
  )

// OR
const activeOrAdmin = await db
  .select()
  .from(users)
  .where(
    or(
      eq(users.status, 'active'),
      eq(users.role, 'admin')
    )
  )

// NOT
const notAdmins = await db
  .select()
  .from(users)
  .where(not(eq(users.role, 'admin')))

// Złożone
const complexQuery = await db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.status, 'published'),
      or(
        gt(posts.views, 1000),
        eq(posts.featured, true)
      ),
      isNotNull(posts.publishedAt)
    )
  )

Sortowanie i paginacja

Code

TypeScript

// Order by
const recentPosts = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))

// Multiple columns
const sortedUsers = await db
  .select()
  .from(users)
  .orderBy(asc(users.name), desc(users.createdAt))

// Limit i offset
const page = 1
const pageSize = 10

const paginatedPosts = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(pageSize)
  .offset((page - 1) * pageSize)

Joins

Code

TypeScript

// Left join
const postsWithAuthors = await db
  .select({
    post: posts,
    author: users,
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))

// Inner join
const publishedWithAuthors = await db
  .select({
    postTitle: posts.title,
    authorName: users.name,
    authorEmail: users.email,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.status, 'published'))

// Multiple joins
const postsWithDetails = await db
  .select({
    post: posts,
    author: users,
    category: categories,
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .leftJoin(postsToCategories, eq(posts.id, postsToCategories.postId))
  .leftJoin(categories, eq(postsToCategories.categoryId, categories.id))

Agregacje

Code

TypeScript

import { count, sum, avg, min, max } from 'drizzle-orm'

// Count
const totalUsers = await db
  .select({ count: count() })
  .from(users)
  .then(rows => rows[0].count)

// Count with condition
const activeCount = await db
  .select({ count: count() })
  .from(users)
  .where(eq(users.status, 'active'))

// Group by
const postsByAuthor = await db
  .select({
    authorId: posts.authorId,
    postCount: count(),
  })
  .from(posts)
  .groupBy(posts.authorId)

// Having
const prolificAuthors = await db
  .select({
    authorId: posts.authorId,
    postCount: count(),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .having(gt(count(), 5))

// Aggregate functions
const stats = await db
  .select({
    totalViews: sum(posts.views),
    avgViews: avg(posts.views),
    maxViews: max(posts.views),
    minViews: min(posts.views),
  })
  .from(posts)
  .where(eq(posts.status, 'published'))

Raw SQL

Code

TypeScript

// sql template
const customQuery = await db
  .select({
    id: users.id,
    name: users.name,
    // Raw SQL column
    fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
  })
  .from(users)

// Raw SQL in where
const recentUsers = await db
  .select()
  .from(users)
  .where(sql`${users.createdAt} > NOW() - INTERVAL '7 days'`)

// Fully raw query
const rawResults = await db.execute(sql`
  SELECT * FROM users
  WHERE created_at > NOW() - INTERVAL '7 days'
  ORDER BY created_at DESC
`)

Relational Queries (Query API)

Code

TypeScript

// Wymaga zdefiniowanych relations

// Znajdź użytkownika z postami
const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: true,
  },
})

// Nested relations
const userWithEverything = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: {
      with: {
        comments: {
          with: {
            author: true,
          },
        },
        categories: {
          with: {
            category: true,
          },
        },
      },
    },
  },
})

// Wybrane kolumny
const userBasic = await db.query.users.findFirst({
  columns: {
    id: true,
    name: true,
    email: true,
    // password: false - nie zwracaj
  },
  with: {
    posts: {
      columns: {
        id: true,
        title: true,
      },
      where: eq(posts.status, 'published'),
      limit: 5,
      orderBy: desc(posts.createdAt),
    },
  },
})

// Find many
const allUsersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
  orderBy: desc(users.createdAt),
  limit: 10,
})

Insert

Code

TypeScript

// Pojedynczy insert
const newUser = await db
  .insert(users)
  .values({
    email: 'jan@example.com',
    name: 'Jan Kowalski',
    password: hashedPassword,
  })
  .returning()
  .then(rows => rows[0])

// Bulk insert
const newPosts = await db
  .insert(posts)
  .values([
    { title: 'Post 1', content: 'Content 1', authorId: 1 },
    { title: 'Post 2', content: 'Content 2', authorId: 1 },
    { title: 'Post 3', content: 'Content 3', authorId: 2 },
  ])
  .returning()

// On conflict (upsert)
const upsertedUser = await db
  .insert(users)
  .values({
    email: 'jan@example.com',
    name: 'Jan Kowalski',
    password: hashedPassword,
  })
  .onConflictDoUpdate({
    target: users.email,
    set: {
      name: 'Jan Kowalski Updated',
      updatedAt: new Date(),
    },
  })
  .returning()

// On conflict do nothing
await db
  .insert(users)
  .values({ email: 'jan@example.com', name: 'Jan' })
  .onConflictDoNothing({ target: users.email })

Update

Code

TypeScript

// Update z where
const updatedUser = await db
  .update(users)
  .set({
    name: 'Jan Nowak',
    updatedAt: new Date(),
  })
  .where(eq(users.id, 1))
  .returning()
  .then(rows => rows[0])

// Update wiele rekordów
await db
  .update(posts)
  .set({ status: 'archived' })
  .where(
    and(
      eq(posts.status, 'published'),
      lt(posts.publishedAt, new Date('2023-01-01'))
    )
  )

// Increment/decrement
await db
  .update(posts)
  .set({
    views: sql`${posts.views} + 1`,
  })
  .where(eq(posts.id, postId))

Delete

Code

TypeScript

// Delete z where
await db
  .delete(users)
  .where(eq(users.id, 1))

// Delete wiele
await db
  .delete(comments)
  .where(
    and(
      eq(comments.postId, postId),
      lt(comments.createdAt, cutoffDate)
    )
  )

// Delete z returning
const deletedPosts = await db
  .delete(posts)
  .where(eq(posts.status, 'draft'))
  .returning()

Transakcje

Code

TypeScript

// Podstawowa transakcja
const result = await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({
      email: 'jan@example.com',
      name: 'Jan',
      password: hashedPassword,
    })
    .returning()

  const [post] = await tx
    .insert(posts)
    .values({
      title: 'Pierwszy post',
      content: 'Treść',
      authorId: user.id,
    })
    .returning()

  return { user, post }
})

// Nested transactions (savepoints)
await db.transaction(async (tx) => {
  await tx.insert(users).values({ /* ... */ })

  await tx.transaction(async (tx2) => {
    await tx2.insert(posts).values({ /* ... */ })
    // Rollback tylko tego savepoint
    throw new Error('Anuluj tylko posty')
  }).catch(() => {
    // User został dodany, posts nie
  })
})

// Manual rollback
await db.transaction(async (tx) => {
  const user = await tx.insert(users).values({ /* ... */ }).returning()

  if (someCondition) {
    tx.rollback()
    return // Nic nie zostanie zapisane
  }

  await tx.insert(posts).values({ authorId: user[0].id })
})

Drizzle Kit - Migracje

Konfiguracja

TSdrizzle.config.ts

TypeScript

// 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

Komendy

Code

Bash

# Generuj migracje z schema
npx drizzle-kit generate:pg

# Aplikuj migracje
npx drizzle-kit push:pg

# Introspect istniejącej bazy
npx drizzle-kit introspect:pg

# Uruchom Drizzle Studio (GUI)
npx drizzle-kit studio

# Sprawdź różnice
npx drizzle-kit check:pg

# Drop wszystko (ostrożnie!)
npx drizzle-kit drop

Migracje w kodzie

Code

TypeScript

// Dla production - programatyczne migracje
import { migrate } from 'drizzle-orm/neon-http/migrator'
import { db } from './db'

async function runMigrations() {
  await migrate(db, { migrationsFolder: './drizzle' })
  console.log('Migrations completed!')
}

runMigrations().catch(console.error)

Integracja z Next.js

Server Components

TSapp/posts/page.tsx

TypeScript

// app/posts/page.tsx
import { db } from '@/db'
import { posts, users } from '@/db/schema'
import { eq, desc } from 'drizzle-orm'

export default async function PostsPage() {
  const allPosts = await db.query.posts.findMany({
    where: eq(posts.status, 'published'),
    with: {
      author: {
        columns: {
          id: true,
          name: true,
          avatar: true,
        },
      },
    },
    orderBy: desc(posts.publishedAt),
    limit: 10,
  })

  return (
    <div>
      {allPosts.map((post) => (
        <article key={post.id}>
          <h2>{post.title}</h2>
          <p>By {post.author.name}</p>
        </article>
      ))}
    </div>
  )
}

Server Actions

TSapp/actions.ts

TypeScript

// app/actions.ts
'use server'

import { db } from '@/db'
import { posts } from '@/db/schema'
import { auth } from '@/lib/auth'
import { revalidatePath } from 'next/cache'
import { redirect } from 'next/navigation'
import { z } from 'zod'
import { eq } from 'drizzle-orm'

const createPostSchema = z.object({
  title: z.string().min(1).max(255),
  content: z.string().min(1),
  slug: z.string().min(1).max(255),
})

export async function createPost(formData: FormData) {
  const session = await auth()
  if (!session?.user) {
    throw new Error('Unauthorized')
  }

  const validated = createPostSchema.parse({
    title: formData.get('title'),
    content: formData.get('content'),
    slug: formData.get('slug'),
  })

  const [post] = await db
    .insert(posts)
    .values({
      ...validated,
      authorId: session.user.id,
      status: 'draft',
    })
    .returning()

  revalidatePath('/posts')
  redirect(`/posts/${post.slug}`)
}

export async function publishPost(postId: number) {
  const session = await auth()
  if (!session?.user) {
    throw new Error('Unauthorized')
  }

  await db
    .update(posts)
    .set({
      status: 'published',
      publishedAt: new Date(),
    })
    .where(eq(posts.id, postId))

  revalidatePath('/posts')
}

export async function deletePost(postId: number) {
  const session = await auth()
  if (!session?.user) {
    throw new Error('Unauthorized')
  }

  await db.delete(posts).where(eq(posts.id, postId))

  revalidatePath('/posts')
  redirect('/posts')
}

API Routes

TSapp/api/posts/route.ts

TypeScript

// app/api/posts/route.ts
import { db } from '@/db'
import { posts } from '@/db/schema'
import { eq, desc } from 'drizzle-orm'
import { NextResponse } from 'next/server'

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const page = parseInt(searchParams.get('page') || '1')
  const limit = parseInt(searchParams.get('limit') || '10')

  const allPosts = await db.query.posts.findMany({
    where: eq(posts.status, 'published'),
    orderBy: desc(posts.publishedAt),
    limit,
    offset: (page - 1) * limit,
    with: {
      author: {
        columns: {
          id: true,
          name: true,
        },
      },
    },
  })

  return NextResponse.json(allPosts)
}

export async function POST(request: Request) {
  const body = await request.json()

  const [post] = await db
    .insert(posts)
    .values(body)
    .returning()

  return NextResponse.json(post, { status: 201 })
}

Type Inference

Code

TypeScript

import { InferSelectModel, InferInsertModel } from 'drizzle-orm'
import { users, posts } from './schema'

// Typ dla select (wszystkie pola)
export type User = InferSelectModel<typeof users>
export type Post = InferSelectModel<typeof posts>

// Typ dla insert (bez auto-generated pól)
export type NewUser = InferInsertModel<typeof users>
export type NewPost = InferInsertModel<typeof posts>

// Użycie
async function createUser(userData: NewUser): Promise<User> {
  const [user] = await db.insert(users).values(userData).returning()
  return user
}

// Partial types
type UserUpdate = Partial<Omit<NewUser, 'id' | 'createdAt'>>

Prepared Statements

Code

TypeScript

import { sql } from 'drizzle-orm'

// Prepared statement dla lepszej wydajności
const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare('get_user_by_id')

// Użycie
const user = await getUserById.execute({ id: 1 })

// Z wieloma placeholderami
const getPostsByAuthor = db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.authorId, sql.placeholder('authorId')),
      eq(posts.status, sql.placeholder('status'))
    )
  )
  .limit(sql.placeholder('limit'))
  .prepare('get_posts_by_author')

const authorPosts = await getPostsByAuthor.execute({
  authorId: 1,
  status: 'published',
  limit: 10,
})

Soft Deletes Pattern

Code

TypeScript

// Schema z soft delete
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  deletedAt: timestamp('deleted_at'),
  // ...
})

// Helper do filtrowania
const notDeleted = isNull(users.deletedAt)

// Query z soft delete
const activeUsers = await db
  .select()
  .from(users)
  .where(notDeleted)

// Soft delete
await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, userId))

// Hard delete (admin only)
await db
  .delete(users)
  .where(eq(users.id, userId))

// Restore
await db
  .update(users)
  .set({ deletedAt: null })
  .where(eq(users.id, userId))

Full-text Search (PostgreSQL)

Code

TypeScript

import { sql } from 'drizzle-orm'

// Prosty search
const searchResults = await db
  .select()
  .from(posts)
  .where(
    sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content})
        @@ plainto_tsquery('english', ${searchTerm})`
  )

// Z rankingiem
const rankedResults = await db
  .select({
    post: posts,
    rank: sql<number>`ts_rank(
      to_tsvector('english', ${posts.title} || ' ' || ${posts.content}),
      plainto_tsquery('english', ${searchTerm})
    )`.as('rank'),
  })
  .from(posts)
  .where(
    sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content})
        @@ plainto_tsquery('english', ${searchTerm})`
  )
  .orderBy(sql`rank DESC`)

Best Practices

1. Organizacja plików

Code

TEXT

db/
├── index.ts          # Database client
├── schema.ts         # Wszystkie tabele
├── relations.ts      # Relacje
├── migrations/       # Wygenerowane migracje
└── seed.ts          # Seed data

2. Reusable queries

TSdb/queries/posts.ts

TypeScript

// db/queries/posts.ts
import { db } from '@/db'
import { posts, users } from '@/db/schema'
import { eq, desc, and, sql } from 'drizzle-orm'

export async function getPublishedPosts(limit = 10, offset = 0) {
  return db.query.posts.findMany({
    where: eq(posts.status, 'published'),
    orderBy: desc(posts.publishedAt),
    limit,
    offset,
    with: {
      author: {
        columns: { id: true, name: true, avatar: true },
      },
    },
  })
}

export async function getPostBySlug(slug: string) {
  return db.query.posts.findFirst({
    where: eq(posts.slug, slug),
    with: {
      author: true,
      comments: {
        with: { author: true },
        orderBy: desc(comments.createdAt),
      },
    },
  })
}

export async function incrementPostViews(postId: number) {
  await db
    .update(posts)
    .set({ views: sql`${posts.views} + 1` })
    .where(eq(posts.id, postId))
}

3. Error handling

Code

TypeScript

import { DatabaseError } from 'pg'

async function createUser(data: NewUser) {
  try {
    const [user] = await db.insert(users).values(data).returning()
    return { success: true, user }
  } catch (error) {
    if (error instanceof DatabaseError) {
      if (error.code === '23505') {
        // Unique violation
        return { success: false, error: 'Email already exists' }
      }
    }
    throw error
  }
}

FAQ

Kiedy Drizzle vs Prisma?

Drizzle gdy:

Znasz SQL i chcesz pełną kontrolę
Budujesz dla serverless/edge
Zależy Ci na małym bundle size
Potrzebujesz raw SQL

Prisma gdy:

Wolisz wyższy poziom abstrakcji
Zespół nie zna dobrze SQL
Potrzebujesz Prisma Studio features

Czy Drizzle działa z Vercel?

Tak! Drizzle świetnie działa z Vercel Edge, Vercel Postgres, Neon i innymi.

Jak debugować queries?

Użyj verbose: true w konfiguracji lub:

Code

TypeScript

const db = drizzle(client, { logger: true })

Podsumowanie

Drizzle ORM to idealne narzędzie dla programistów, którzy:

Znają SQL i chcą type safety
Budują serverless aplikacje
Cenią wydajność (mały bundle)
Potrzebują kontroli nad queries

Drizzle to przyszłość ORM w ekosystemie TypeScript.

Drizzle ORM - Complete Guide to Type-Safe SQL in TypeScript

What is Drizzle ORM and Why Choose It?

Drizzle ORM is a revolutionary TypeScript ORM that takes a fundamentally different approach than traditional ORMs. Instead of abstracting SQL away, Drizzle embraces it—giving you the full power of SQL with complete type safety and zero runtime overhead. If you've ever felt frustrated by ORMs that generate inefficient queries or hide what's actually happening in your database, Drizzle is the answer.

The philosophy behind Drizzle is simple: "If you know SQL, you know Drizzle." Unlike Prisma or TypeORM which create their own query languages, Drizzle's API mirrors SQL syntax directly. This means your SQL knowledge transfers immediately, and you can always predict exactly what query will be generated.

Why Developers Are Switching to Drizzle

The Serverless Revolution

Drizzle was built for the modern serverless era. With a core bundle of only ~7KB and zero runtime dependencies, it starts instantly in cold starts. Compare this to other ORMs that can add hundreds of KB to your bundle and take seconds to initialize.

True Type Safety

Every query in Drizzle is fully typed from table definition to result. Change a column type in your schema, and TypeScript immediately shows you every place in your code that needs updating.

SQL Knowledge Preservation

Your team's SQL expertise isn't wasted. Drizzle queries look and behave like SQL:

Code

TypeScript

// Drizzle query
const users = await db.select()
  .from(usersTable)
  .where(eq(usersTable.age, 25))
  .orderBy(desc(usersTable.createdAt))
  .limit(10);

// Equivalent SQL
// SELECT * FROM users WHERE age = 25 ORDER BY created_at DESC LIMIT 10

Installation and Setup

Installing Drizzle

Code

Bash

# Core ORM
pnpm add drizzle-orm

# Database driver (choose one)
pnpm add postgres         # PostgreSQL
pnpm add mysql2           # MySQL
pnpm add better-sqlite3   # SQLite

# Development tools
pnpm add -D drizzle-kit

Project Structure

Code

TEXT

src/
├── db/
│   ├── index.ts          # Database connection
│   ├── schema/
│   │   ├── users.ts      # User table schema
│   │   ├── posts.ts      # Post table schema
│   │   └── index.ts      # Export all schemas
│   └── migrations/       # Generated migrations
├── drizzle.config.ts     # Drizzle Kit configuration

Defining Your Schema

Basic Table Definition

TSsrc/db/schema/users.ts

TypeScript

// src/db/schema/users.ts
import { pgTable, serial, varchar, text, timestamp, boolean, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 100 }).notNull(),
  bio: text('bio'),
  age: integer('age'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Type inference - get types from schema automatically
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Relations and Foreign Keys

TSsrc/db/schema/posts.ts

TypeScript

// src/db/schema/posts.ts
import { pgTable, serial, varchar, text, timestamp, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { users } from './users';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  authorId: integer('author_id').references(() => users.id).notNull(),
  publishedAt: timestamp('published_at'),
  createdAt: timestamp('created_at').defaultNow(),
});

// Define relations for Query API
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

Advanced Schema Features

Code

TypeScript

// Enums
import { pgEnum } from 'drizzle-orm/pg-core';

export const statusEnum = pgEnum('status', ['draft', 'published', 'archived']);

export const articles = pgTable('articles', {
  id: serial('id').primaryKey(),
  status: statusEnum('status').default('draft'),
});

// Indexes and constraints
import { index, uniqueIndex } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  sku: varchar('sku', { length: 50 }).notNull(),
  name: varchar('name', { length: 255 }).notNull(),
  categoryId: integer('category_id'),
  price: integer('price').notNull(),
}, (table) => ({
  skuIdx: uniqueIndex('sku_idx').on(table.sku),
  categoryIdx: index('category_idx').on(table.categoryId),
  priceIdx: index('price_idx').on(table.price),
}));

Database Connection

PostgreSQL with postgres.js

TSsrc/db/index.ts

TypeScript

// src/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 query purposes
const queryClient = postgres(connectionString);
export const db = drizzle(queryClient, { schema });

// For migrations (use different client)
const migrationClient = postgres(connectionString, { max: 1 });
export const migrationDb = drizzle(migrationClient);

Neon Serverless

Code

TypeScript

import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
import * as schema from './schema';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

Vercel Postgres

Code

TypeScript

import { sql } from '@vercel/postgres';
import { drizzle } from 'drizzle-orm/vercel-postgres';
import * as schema from './schema';

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

SQLite with better-sqlite3

Code

TypeScript

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

const sqlite = new Database('sqlite.db');
export const db = drizzle(sqlite, { schema });

CRUD Operations

Select Queries

Code

TypeScript

import { db } from './db';
import { users, posts } from './db/schema';
import { eq, ne, gt, gte, lt, lte, like, ilike, and, or, not, inArray, isNull, between, desc, asc } from 'drizzle-orm';

// Basic select
const allUsers = await db.select().from(users);

// Select specific columns
const userNames = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

// With conditions
const activeUsers = await db.select()
  .from(users)
  .where(eq(users.isActive, true));

// Complex where clauses
const filteredUsers = await db.select()
  .from(users)
  .where(
    and(
      gte(users.age, 18),
      lt(users.age, 65),
      or(
        like(users.email, '%@gmail.com'),
        like(users.email, '%@outlook.com')
      )
    )
  );

// Ordering and pagination
const paginatedUsers = await db.select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(20);

// Count
const userCount = await db.select({ count: sql<number>`count(*)` })
  .from(users);

Insert Operations

Code

TypeScript

// Single insert
const newUser = await db.insert(users)
  .values({
    email: 'john@example.com',
    name: 'John Doe',
    age: 30,
  })
  .returning();

// Multiple insert
const newUsers = await db.insert(users)
  .values([
    { email: 'alice@example.com', name: 'Alice', age: 25 },
    { email: 'bob@example.com', name: 'Bob', age: 35 },
  ])
  .returning();

// Insert with conflict handling (upsert)
const upsertedUser = await db.insert(users)
  .values({
    email: 'john@example.com',
    name: 'John Updated',
  })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
  })
  .returning();

Update Operations

Code

TypeScript

// Update with conditions
const updatedUsers = await db.update(users)
  .set({ isActive: false })
  .where(lt(users.createdAt, new Date('2023-01-01')))
  .returning();

// Update with SQL expressions
import { sql } from 'drizzle-orm';

await db.update(users)
  .set({
    age: sql`${users.age} + 1`,
    updatedAt: new Date(),
  })
  .where(eq(users.id, 1));

Delete Operations

Code

TypeScript

// Delete with conditions
const deletedUsers = await db.delete(users)
  .where(eq(users.isActive, false))
  .returning();

// Delete with subquery
await db.delete(posts)
  .where(
    inArray(
      posts.authorId,
      db.select({ id: users.id })
        .from(users)
        .where(eq(users.isActive, false))
    )
  );

Joins and Relations

SQL-Style Joins

Code

TypeScript

// Inner join
const usersWithPosts = await db.select({
  userName: users.name,
  postTitle: posts.title,
})
.from(users)
.innerJoin(posts, eq(users.id, posts.authorId));

// Left join
const allUsersWithPosts = await db.select({
  user: users,
  post: posts,
})
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId));

// Multiple joins
const fullData = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .leftJoin(comments, eq(posts.id, comments.postId));

Relational Query API

The Query API provides a more intuitive way to fetch nested data:

Code

TypeScript

// Fetch users with their posts
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

// Nested relations with filtering
const usersWithRecentPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: (posts, { gte }) => gte(posts.createdAt, new Date('2024-01-01')),
      orderBy: (posts, { desc }) => [desc(posts.createdAt)],
      limit: 5,
    },
  },
  where: (users, { eq }) => eq(users.isActive, true),
});

// Select specific columns in relations
const usersPreview = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    posts: {
      columns: {
        title: true,
        publishedAt: true,
      },
    },
  },
});

// Find single record
const user = await db.query.users.findFirst({
  where: (users, { eq }) => eq(users.id, 1),
  with: {
    posts: true,
  },
});

Transactions

Code

TypeScript

// Basic transaction
const result = await db.transaction(async (tx) => {
  const user = await tx.insert(users)
    .values({ email: 'new@example.com', name: 'New User' })
    .returning();

  await tx.insert(posts)
    .values({
      title: 'First Post',
      authorId: user[0].id,
    });

  return user[0];
});

// Nested transactions with savepoints
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'a@example.com', name: 'A' });

  await tx.transaction(async (tx2) => {
    await tx2.insert(users).values({ email: 'b@example.com', name: 'B' });
    // If this fails, only inner transaction rolls back
  });
});

// Transaction with rollback
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'test@example.com', name: 'Test' });

  // Condition to rollback
  const shouldRollback = true;
  if (shouldRollback) {
    tx.rollback();
  }
});

Aggregations and Grouping

Code

TypeScript

import { sql, count, sum, avg, min, max } from 'drizzle-orm';

// Count with grouping
const postCounts = await db.select({
  authorId: posts.authorId,
  postCount: count(posts.id),
})
.from(posts)
.groupBy(posts.authorId);

// Multiple aggregations
const stats = await db.select({
  totalPosts: count(posts.id),
  avgViews: avg(posts.views),
  maxViews: max(posts.views),
  minViews: min(posts.views),
})
.from(posts);

// Having clause
const prolificAuthors = await db.select({
  authorId: posts.authorId,
  postCount: count(posts.id),
})
.from(posts)
.groupBy(posts.authorId)
.having(({ postCount }) => gt(postCount, 10));

Migrations with Drizzle Kit

Configuration

TSdrizzle.config.ts

TypeScript

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema/*',
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Migration Commands

Code

Bash

# Generate migration from schema changes
pnpm drizzle-kit generate

# Apply migrations
pnpm drizzle-kit migrate

# Push schema directly (development only)
pnpm drizzle-kit push

# Open Drizzle Studio
pnpm drizzle-kit studio

# Introspect existing database
pnpm drizzle-kit introspect

Custom Migration Scripts

TSsrc/db/migrate.ts

TypeScript

// src/db/migrate.ts
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { db, migrationDb } from './index';

async function runMigrations() {
  console.log('Running migrations...');

  await migrate(migrationDb, {
    migrationsFolder: './src/db/migrations',
  });

  console.log('Migrations completed!');
  process.exit(0);
}

runMigrations().catch((err) => {
  console.error('Migration failed!', err);
  process.exit(1);
});

Drizzle Studio

Drizzle Studio is a beautiful GUI for managing your database:

Code

Bash

# Start Drizzle Studio
pnpm drizzle-kit studio

Features:

Browse and edit data visually
Execute custom SQL queries
View table relationships
Export data as JSON/CSV
Works locally, no cloud required

Next.js Integration

Server Components

TSapp/users/page.tsx

TypeScript

// app/users/page.tsx
import { db } from '@/db';
import { users } from '@/db/schema';

export default async function UsersPage() {
  const allUsers = await db.select().from(users);

  return (
    <ul>
      {allUsers.map((user) => (
        <li key={user.id}>{user.name} - {user.email}</li>
      ))}
    </ul>
  );
}

Server Actions

TSapp/actions/users.ts

TypeScript

// app/actions/users.ts
'use server'

import { db } from '@/db';
import { users } from '@/db/schema';
import { eq } from 'drizzle-orm';
import { revalidatePath } from 'next/cache';

export async function createUser(formData: FormData) {
  const name = formData.get('name') as string;
  const email = formData.get('email') as string;

  await db.insert(users).values({ name, email });

  revalidatePath('/users');
}

export async function updateUser(id: number, data: Partial<typeof users.$inferInsert>) {
  await db.update(users)
    .set(data)
    .where(eq(users.id, id));

  revalidatePath('/users');
}

API Routes

TSapp/api/users/route.ts

TypeScript

// app/api/users/route.ts
import { db } from '@/db';
import { users } from '@/db/schema';
import { eq } from 'drizzle-orm';
import { NextRequest, NextResponse } from 'next/server';

export async function GET() {
  const allUsers = await db.select().from(users);
  return NextResponse.json(allUsers);
}

export async function POST(request: NextRequest) {
  const body = await request.json();

  const newUser = await db.insert(users)
    .values(body)
    .returning();

  return NextResponse.json(newUser[0], { status: 201 });
}

Prepared Statements

For performance-critical queries:

Code

TypeScript

import { placeholder } from 'drizzle-orm';

// Create prepared statement
const getUserById = db.select()
  .from(users)
  .where(eq(users.id, placeholder('id')))
  .prepare('get_user_by_id');

// Execute with different parameters
const user1 = await getUserById.execute({ id: 1 });
const user2 = await getUserById.execute({ id: 2 });

// Prepared statement with multiple placeholders
const searchUsers = db.select()
  .from(users)
  .where(
    and(
      gte(users.age, placeholder('minAge')),
      lte(users.age, placeholder('maxAge'))
    )
  )
  .prepare('search_users');

const adults = await searchUsers.execute({ minAge: 18, maxAge: 65 });

Raw SQL Queries

Code

TypeScript

import { sql } from 'drizzle-orm';

// Raw query with type safety
const result = await db.execute(sql`
  SELECT * FROM users
  WHERE age > ${18}
  AND email LIKE ${`%@gmail.com`}
`);

// Using sql in select
const usersWithAge = await db.select({
  name: users.name,
  ageGroup: sql<string>`
    CASE
      WHEN ${users.age} < 18 THEN 'minor'
      WHEN ${users.age} < 65 THEN 'adult'
      ELSE 'senior'
    END
  `.as('age_group'),
}).from(users);

Performance Tips

Query Optimization

Code

TypeScript

// Use select() instead of query() for simple queries
// query() is for relational data, select() is faster for flat data

// Good - flat data
const users = await db.select().from(usersTable);

// Good - relational data
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

// Use indexes effectively
// Define indexes in schema for frequently queried columns

// Use prepared statements for repeated queries
const prepared = db.select().from(users)
  .where(eq(users.id, placeholder('id')))
  .prepare();

Connection Pooling

Code

TypeScript

// Configure connection pool for production
const pool = postgres(process.env.DATABASE_URL, {
  max: 10,        // Maximum connections
  idle_timeout: 20,
  connect_timeout: 10,
});

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

Drizzle vs Prisma Comparison

Feature	Drizzle	Prisma
Bundle Size	~7KB	~800KB
Cold Start	Instant	Slow
SQL Knowledge	Required	Not required
Query Control	Full	Limited
Type Safety	Complete	Complete
Migrations	CLI tool	CLI tool
GUI	Drizzle Studio	Prisma Studio
Serverless	Excellent	Good
Learning Curve	SQL knowledge helps	Lower

When to Choose Drizzle

You know SQL and want type safety
Building serverless/edge applications
Need minimal bundle size
Want full control over queries
Performance is critical

When Prisma Might Be Better

Team doesn't know SQL well
Need Prisma's rich ecosystem
Prefer higher-level abstractions
Already invested in Prisma

Common Patterns

Soft Delete

Code

TypeScript

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  deletedAt: timestamp('deleted_at'),
});

// Soft delete
await db.update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, 1));

// Query only active users
const activeUsers = await db.select()
  .from(users)
  .where(isNull(users.deletedAt));

Timestamps

Code

TypeScript

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at')
    .defaultNow()
    .$onUpdate(() => new Date())
    .notNull(),
});

Full-Text Search (PostgreSQL)

Code

TypeScript

import { sql } from 'drizzle-orm';

const searchResults = await db.select()
  .from(posts)
  .where(sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content}) @@ plainto_tsquery('english', ${searchQuery})`);

Troubleshooting

Common Issues

Type Errors with Relations

Code

TypeScript

// Make sure to import schema with relations
import * as schema from './schema';
const db = drizzle(client, { schema }); // Pass schema here!

Migration Conflicts

Code

Bash

# Reset migrations (development only)
rm -rf src/db/migrations
pnpm drizzle-kit generate

Connection Issues

Code

TypeScript

// Enable logging to debug
const db = drizzle(client, {
  schema,
  logger: true, // See all queries
});

Summary

Drizzle ORM is the perfect choice for TypeScript developers who want the best of both worlds: the power and flexibility of raw SQL with the safety and convenience of a modern ORM. Its tiny bundle size, excellent serverless support, and intuitive SQL-like API make it ideal for modern web applications.

Key takeaways:

Schema as Code - Define your database structure in TypeScript
SQL-First - Your SQL knowledge transfers directly
Zero Overhead - Minimal bundle size and instant cold starts
Full Type Safety - From schema to query results
Modern Tooling - Drizzle Kit and Studio for migrations and management

Drizzle represents the future of database tooling in the TypeScript ecosystem—embracing SQL rather than hiding it, and giving developers the control they need without sacrificing developer experience.