drizzle-orm-patterns

Drizzle ORM Patterns

Overview

Expert guide for building type-safe database applications with Drizzle ORM. Covers schema definition, relations, queries, transactions, and migrations for all supported databases.

When to Use

• Defining database schemas with tables, columns, and constraints
• Creating relations between tables (one-to-one, one-to-many, many-to-many)
• Writing type-safe CRUD queries
• Implementing complex joins and aggregations
• Managing database transactions with rollback
• Setting up migrations with Drizzle Kit
• Working with PostgreSQL, MySQL, SQLite, MSSQL, or CockroachDB

Quick Reference

Database	Table Function	Import
PostgreSQL	pgTable()	drizzle-orm/pg-core
MySQL	mysqlTable()	drizzle-orm/mysql-core
SQLite	sqliteTable()	drizzle-orm/sqlite-core
MSSQL	mssqlTable()	drizzle-orm/mssql-core

Operation	Method	Example
Insert	db.insert()	db.insert(users).values({...})
Select	db.select()	db.select().from(users).where(eq(...))
Update	db.update()	db.update(users).set({...}).where(...)
Delete	db.delete()	db.delete(users).where(...)
Transaction	db.transaction()	db.transaction(async (tx) => {...})

Instructions

1. Identify your database dialect - Choose PostgreSQL, MySQL, SQLite, MSSQL, or CockroachDB
2. Define your schema - Use the appropriate table function (pgTable, mysqlTable, etc.)
3. Set up relations - Define relations using relations() or defineRelations()
4. Initialize the database client - Create your Drizzle client with proper credentials
5. Write queries - Use the query builder for type-safe CRUD operations
6. Handle transactions - Wrap multi-step operations in transactions when needed
7. Set up migrations - Configure Drizzle Kit for schema management

Examples

Example 1: Basic Schema and Query

import { pgTable, serial, text } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
});

const db = drizzle(process.env.DATABASE_URL);

const [user] = await db.select().from(users).where(eq(users.id, 1));