TypeScript + Drizzle ORM Implementation Guide

TypeScript + Drizzle ORM Implementation Guide

Concrete implementation patterns for PostgreSQL development with TypeScript and Drizzle ORM.

Type Definitions

Drizzle Client Separation

Create separate types for read-write and read-only clients:

// File: lib/datastores/postgres/types.ts
import type { drizzle } from "drizzle-orm/node-postgres";

export type Drizzle = Omit<ReturnType<typeof drizzle>, "$client">;

export type DrizzleMutableMethods = "insert" | "update" | "delete";
export type DrizzleRO = Omit<Drizzle, DrizzleMutableMethods>;

export type Executor = Pick<Drizzle, DrizzleMutableMethods | "select">;
export type ExecutorRO = Pick<DrizzleRO, "select">;

export * from "drizzle-orm";

Usage in service classes:

class UserService {
  constructor(
    logger: Logger,
    private readonly db: Drizzle,        // Read-write client
    private readonly dbRO: DrizzleRO,    // Read-only client
  ) {
    this.logger = logger.child({ component: this.constructor.name });
  }

  // Query method defaults to read-only
  async getUser(id: string, executor: DrizzleRO = this.dbRO): Promise<User | null> {
    const result = await executor
      .select()
      .from(USERS)
      .where(eq(USERS.userId, id))
      .limit(1);
    return result[0] ?? null;
  }

  // Mutation method defaults to read-write
  async updateUser(id: string, data: UserUpdate, executor: Drizzle = this.db): Promise<User> {
    const [user] = await executor
      .update(USERS)
      .set(data)
      .where(eq(USERS.userId, id))
      .returning();

    if (!user) throw new Error("User not found");
    return user;
  }
}

Primary Keys - ULID as UUID

Helper Function

// File: _db/schema/index.ts
import { uuid } from "drizzle-orm/pg-core";
import { ulid, ulidToUUID } from "ulidx";
import { type StringUUID } from "../../lib/ext/typebox/index.js";

export const ULIDAsUUID = (columnName?: string) =>
  (columnName ? uuid(columnName) : uuid())
    .$default(() => ulidToUUID(ulid()))
    .$type<StringUUID>();

Usage in Schema

export const USERS = pgTable("users", {
  userId: ULIDAsUUID("user_id").primaryKey(),
  tenantId: ULIDAsUUID("tenant_id")
    .references(() => TENANTS.tenantId)
    .notNull(),
  displayName: text("display_name").notNull(),
  // ... other columns
});

StringUUID type: Should be branded/nominal string type to prevent mixing with regular strings.

JSONB Type Safety

Always Use $type()

import { type Sensitive } from "../../lib/functional/vault/schemas.js";

export const USERS = pgTable("users", {
  userId: ULIDAsUUID("user_id").primaryKey(),

  // GOOD: Typed JSONB with known structure
  idpUserInfo: jsonb("idp_user_info").$type<Sensitive<IdPUserInfo>>(),

  // GOOD: Typed with explicit structure
  extraAttributes: jsonb("extra_attributes")
    .$type<Record<string, unknown>>()
    .notNull()
    .$default(() => ({})),

  // GOOD: Complex nested structure
  preferences: jsonb("preferences").$type<{
    theme: "light" | "dark" | "auto";
    language: string;
    notifications: {
      email: boolean;
      push: boolean;
    };
  }>().notNull(),

  // BAD: Untyped JSONB
  metadata: jsonb("metadata"), // NO! Always use $type
});

Type Definition Pattern

// Define types separately for reusability
type UserPreferences = {
  theme: "light" | "dark" | "auto";
  language: string;
  notifications: {
    email: boolean;
    push: boolean;
  };
};

export const USERS = pgTable("users", {
  preferences: jsonb("preferences").$type<UserPreferences>().notNull(),
});

Schema Patterns

Standard Mixins

// Timestamps mixin - use on ALL tables
export const TIMESTAMPS_MIXIN = {
  createdAt: timestamp("created_at", { withTimezone: true, mode: "date" })
    .notNull()
    .defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true, mode: "date" })
    .$onUpdateFn(() => new Date()),
};

// Apply to tables
export const USERS = pgTable("users", {
  userId: ULIDAsUUID("user_id").primaryKey(),
  displayName: text("display_name").notNull(),
  ...TIMESTAMPS_MIXIN,
});

Soft Deletes

export const POSTS = pgTable("posts", {
  postId: ULIDAsUUID("post_id").primaryKey(),
  title: text("title").notNull(),
  deletedAt: timestamp("deleted_at", { withTimezone: true, mode: "date" }),
  ...TIMESTAMPS_MIXIN,
});

Indexes and Constraints

export const USER_EMAILS = pgTable(
  "user_emails",
  {
    userId: ULIDAsUUID("user_id")
      .references(() => USERS.userId)
      .notNull(),
    email: text("email").notNull(),
    isPrimary: boolean("is_primary").notNull().default(false),
    ...TIMESTAMPS_MIXIN,
  },
  (t) => [
    {
      // Composite primary key
      pk: primaryKey({ columns: [t.userId, t.email] }),

      // Indexes following naming convention
      userIdx: index("user_emails_user_idx").on(t.userId),
      emailIdx: index("user_emails_lookup_idx").on(t.email),

      // Unique constraints
      uniqueEmail: unique("user_emails_tenant_email_unique").on(t.email),
    },
  ],
);

Financial Data Types

Use numeric() for Money

MANDATORY: Use numeric type for all monetary values.

import { numeric } from "drizzle-orm/pg-core";

// GOOD: numeric with string defaults (preserves precision)
export const WALLETS = pgTable("wallets", {
  walletId: ULIDAsUUID("wallet_id").primaryKey(),
  balance: numeric("balance", { precision: 19, scale: 4 })
    .notNull()
    .default("0.0000"),  // String default - preserves precision
});

export const TRANSACTIONS = pgTable("transactions", {
  transactionId: ULIDAsUUID("transaction_id").primaryKey(),
  amount: numeric("amount", { precision: 19, scale: 4 }).notNull(),
});

// BAD: doublePrecision causes rounding errors
export const WALLETS_BAD = pgTable("wallets", {
  balance: doublePrecision("balance"), // NO! Floating point errors
});

// BAD: number defaults lose precision
balance: numeric("balance", { precision: 19, scale: 4 }).default(0)  // NO! Use string

Why numeric:

• Exact decimal arithmetic (no floating-point rounding)
• Database enforces precision
• String mode (default) prevents JS number precision loss

Common precision/scale values:

• { precision: 19, scale: 4 } - general purpose (up to 15 integer digits, 4 decimal)
• { precision: 10, scale: 2 } - most currencies (cents precision)
• { precision: 19, scale: 8 } - cryptocurrency (satoshi-level)

Note: decimal() is an alias for numeric() - both work identically.

Transaction Patterns

TX_ Methods (Transaction Starters)

class UserService {
  constructor(
    logger: Logger,
    private readonly db: Drizzle,
    private readonly dbRO: DrizzleRO,
    private readonly events: EventService,
  ) {}

  // GOOD: Starts transaction, has TX_ prefix, no executor parameter
  async TX_createUserWithProfile(
    userData: CreateUserInput,
    profileData: ProfileData,
  ): Promise<User> {
    return this.db.transaction(async (tx) => {
      // Call participant methods with tx
      const user = await this.createUser(userData, tx);
      await this.createProfile(user.id, profileData, tx);
      await this.events.dispatchEvent({
        type: "UserCreated",
        userId: user.id,
      });
      return user;
    });
  }
}

Participant Methods (No TX_ Prefix)

class UserService {
  // GOOD: Participates in transaction, takes executor with default
  async createUser(
    input: CreateUserInput,
    executor: Drizzle = this.db,
  ): Promise<User> {
    const [user] = await executor
      .insert(USERS)
      .values({
        displayName: input.name,
        email: input.email,
      })
      .returning();

    if (!user) throw new Error("Failed to create user");
    return user;
  }

  // GOOD: Read operation, uses read-only by default
  async getUserById(
    userId: string,
    executor: DrizzleRO = this.dbRO,
  ): Promise<User | null> {
    const result = await executor
      .select()
      .from(USERS)
      .where(eq(USERS.userId, userId))
      .limit(1);

    return result[0] ?? null;
  }
}

Transaction Isolation Levels

Use SERIALIZABLE isolation for financial operations:

// GOOD: SERIALIZABLE isolation for financial operations
async TX_deductCredits(userId: string, amount: number): Promise<Result> {
  return this.db.transaction(async (tx) => {
    // SELECT FOR UPDATE pattern (undocumented, use with caution)
    const [wallet] = await tx
      .select()
      .from(WALLETS)
      .where(eq(WALLETS.userId, userId))
      .for("update")  // Locks row for exclusive access
      .limit(1);

    if (wallet.balance < amount) {
      throw new Error("Insufficient balance");
    }

    // Deduct and update
    await tx
      .update(WALLETS)
      .set({ balance: wallet.balance - amount })
      .where(eq(WALLETS.walletId, wallet.walletId));

    return { success: true };
  }, {
    isolationLevel: "serializable"  // Prevents race conditions
  });
}

Supported isolation levels:

interface PgTransactionConfig {
  isolationLevel?: "read uncommitted" | "read committed" | "repeatable read" | "serializable";
}

When to use each:

• "read committed" (default) - Most operations
• "repeatable read" - Need consistent snapshot across queries
• "serializable" - Financial operations, inventory counts (prevents all anomalies)

Important:

• Applications using SERIALIZABLE must implement retry logic for serialization failures
• Use connection pooling (Pool), not single Client connections

Atomic Operations (No TX_ Prefix)

// GOOD: Single atomic operation, no explicit transaction needed
async upsertUserPreferences(
  userId: string,
  preferences: UserPreferences,
  executor: Drizzle = this.db,
): Promise<UserPreferencesRow> {
  const [result] = await executor
    .insert(USER_PREFERENCES)
    .values({ userId, preferences })
    .onConflictDoUpdate({
      target: [USER_PREFERENCES.userId],
      set: { preferences },
    })
    .returning();

  if (!result) throw new Error("Failed to upsert preferences");
  return result;
}

Migration Workflow

Generate Migrations

# 1. Update schema in code (src/_db/schema/index.ts)

# 2. Generate migration
npm run db:generate
# or
drizzle-kit generate

# This creates: drizzle/0001_migration_name.sql

Review Migration

-- drizzle/0001_add_user_preferences.sql
CREATE TABLE IF NOT EXISTS "user_preferences" (
  "user_id" uuid PRIMARY KEY NOT NULL,
  "preferences" jsonb NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone
);

CREATE INDEX IF NOT EXISTS "user_preferences_user_idx"
  ON "user_preferences" ("user_id");

ALTER TABLE "user_preferences"
  ADD CONSTRAINT "user_preferences_user_id_users_user_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "users"("user_id");

Apply Migration

# Development
npm run db:migrate

# Production (via CI/CD)
npm run db:migrate:prod

# NEVER use db:push in production

Drizzle Config

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

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/_db/schema/index.ts',
  out: './drizzle',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Enums

// Use pgEnum for database enums
export const USER_ROLE = pgEnum("user_role", ["admin", "user", "guest"]);

export const USERS = pgTable("users", {
  userId: ULIDAsUUID("user_id").primaryKey(),
  role: USER_ROLE("role").notNull().default("user"),
});

Pattern Comments

Add pattern classification to every file:

// pattern: Imperative Shell
// Orchestrates database operations and handles I/O

import { eq } from "drizzle-orm";
import { type Logger } from "pino";

export class UserService {
  // ... implementation
}

Common Patterns Summary