Skip to main content

​
Drizzle ORM — Schema Design & Best Practices

Drizzle ORM is a TypeScript-first ORM with SQL-like syntax, zero runtime overhead, and full type safety. It supports SQLite, PostgreSQL, MySQL, and more — each with driver-specific column types.

badmin

SQLite admin panel — users, roles, RBAC permissions.

Cruise Ship

PostgreSQL business system — bookings, finance, cabin allocation.

cyop

PostgreSQL AI platform — requirements, tasks, captions.

​
SQLite Schema (badmin)

SQLite is great for admin panels and prototypes — zero infra cost, local file DB: 
import { sqliteTable, text, integer, primaryKey } from "drizzle-orm/sqlite-core"
import { sql } from "drizzle-orm"

const USER_STATUS_VALUES = ["active", "disabled"] as const

export const users = sqliteTable("users", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  passwordHash: text("password_hash").notNull(),
  sessionVersion: integer("session_version").default(1).notNull(),
  status: text("status", { enum: USER_STATUS_VALUES }).default("active").notNull(),
  lastLoginAt: integer("last_login_at", { mode: "timestamp" }),
  createdAt: integer("created_at", { mode: "timestamp" }).default(sql`(unixepoch())`).notNull(),
  updatedAt: integer("updated_at", { mode: "timestamp" }).default(sql`(unixepoch())`).notNull(),
})
SQLite stores timestamps as integers. Use { mode: "timestamp" } so Drizzle auto-converts Date ↔ unixepoch(). The sql(unixepoch())` default gives you a server-side timestamp without app logic.

​
PostgreSQL Schema (Cruise Ship)

PostgreSQL gives you rich column types — numeric for money, jsonb for snapshots, text[] for arrays: 
import { pgTable, bigserial, bigint, varchar, numeric, integer, timestamp, text, date, boolean, check, index, foreignKey, unique } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const cruise = pgTable("cruise", {
  id: bigserial({ mode: "bigint" }).primaryKey().notNull(),
  name: varchar({ length: 120 }).notNull(),
  tonnage: numeric({ precision: 12, scale: 2 }),
  imageUrls: text("image_urls").array(),
  status: varchar({ length: 20 }).default("normal").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true, mode: "string" }).defaultNow().notNull(),
}, (table) => [
  index("idx_cruise_status").using("btree", table.status.asc().nullsLast().op("text_ops")),
  check("cruise_status_check", sql`(status)::text = ANY ((ARRAY['normal','disabled','closed'])::text[])`),
])

​
Key PostgreSQL Patterns

  1. CHECK constraints for enums — PostgreSQL doesn’t have native enums in Drizzle, so use check() with ANY (ARRAY[...]):
check("status_check", sql`(status)::text = ANY ((ARRAY['active','disabled'])::text[])`)
  1. Composite indexes — For multi-column queries, define indexes inline:
index("idx_booking_cabin_booking_id").using("btree", table.bookingApplicationId.asc().nullsLast().op("int8_ops"))
  1. Array columns — text("image_urls").array() for storing multiple URLs without a separate table:
  2. JSONB for snapshots — jsonb("item_snapshot").default([]).notNull() for revision history: 
export const costRevision = pgTable("cost_revision", {
  id: bigserial({ mode: "bigint" }).primaryKey().notNull(),
  beforeTotalCost: numeric({ precision: 14, scale: 2 }).default("0").notNull(),
  afterTotalCost: numeric({ precision: 14, scale: 2 }).default("0").notNull(),
  itemSnapshot: jsonb("item_snapshot").default([]).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
})

​
Many-to-Many Relationships (RBAC)

RBAC needs user ↔ role ↔ permission many-to-many tables: 
export const userRoles = sqliteTable(
  "user_roles",
  {
    userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
    roleId: text("role_id").notNull().references(() => roles.id, { onDelete: "cascade" }),
    createdAt: integer("created_at", { mode: "timestamp" }).default(sql`(unixepoch())`).notNull(),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.userId, table.roleId] }),
  }),
)

export const rolePermissions = sqliteTable(
  "role_permissions",
  {
    roleId: text("role_id").notNull().references(() => roles.id, { onDelete: "cascade" }),
    permissionKey: text("permission_key").notNull().references(() => permissions.key, { onDelete: "cascade" }),
    createdAt: integer("created_at", { mode: "timestamp" }).default(sql`(unixepoch())`).notNull(),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.roleId, table.permissionKey] }),
  }),
)
Always add onDelete: "cascade" on many-to-many foreign keys. When a role or user is deleted, their associations should disappear automatically — no orphaned rows.

​
Repository Pattern

Separate data access from business logic using repository interfaces: 
// src/domain/repositories.ts
export interface AuthUserRepository {
  findForAuthByEmail(email: string): Promise<AuthUser | null>
  findSessionUserById(id: string): Promise<SessionUser | null>
  touchLastLogin(id: string): Promise<void>
  updatePassword(id: string, data: { passwordHash: string; nextPassword: string }): Promise<void>
  revokeSessions(id: string): Promise<void>
}

// src/repositories/user-repository.ts
export function createUserRepository(db: DrizzleDB): AuthUserRepository {
  return {
    async findForAuthByEmail(email: string) {
      return await db.query.users.findFirst({
        where: eq(users.email, email),
      })
    },

    async findSessionUserById(id: string) {
      return await db.query.users.findFirst({
        where: eq(users.id, id),
        columns: { passwordHash: false }, // Never load password into session
        with: {
          roles: { with: { role: { with: { permissions: true } } } },
        },
      })
    },

    async revokeSessions(id: string) {
      await db.update(users)
        .set({ sessionVersion: sql`session_version + 1` })
        .where(eq(users.id, id))
    },
  }
}
When loading a user for session checks, never include the password hash. Drizzle’s columns filter lets you exclude sensitive fields at the query level, not just in the service layer.

​
Migrations

Drizzle Kit generates SQL migrations from your schema: 
# Generate migration from schema changes
bun run db:generate

# Apply migrations
bun run db:migrate

# Visualize schema
bun run db:studio

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

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "sqlite", // or "postgresql"
  dbCredentials: {
    url: "./dev.db",
  },
})

​
Seeding

Seed the database with initial roles, permissions, and an admin user: 
// src/db/seed.ts
import { db } from "./client"
import { users, roles, permissions, userRoles, rolePermissions } from "./schema"

export async function seed() {
  // Create permissions
  await db.insert(permissions).values([
    { key: "users.read", label: "View Users", group: "users" },
    { key: "users.write", label: "Edit Users", group: "users" },
    { key: "roles.read", label: "View Roles", group: "roles" },
  ])

  // Create admin role
  const [adminRole] = await db.insert(roles).values({
    name: "Admin",
    slug: "admin",
    description: "System administrator",
    isSystem: true,
  }).returning()

  // Link all permissions to admin role
  await db.insert(rolePermissions).values(
    allPermissions.map(p => ({ roleId: adminRole.id, permissionKey: p.key }))
  )

  // Create admin user
  const [adminUser] = await db.insert(users).values({
    name: "Admin",
    email: "admin@example.com",
    passwordHash: await hashPassword("changeme"),
  }).returning()

  // Assign admin role
  await db.insert(userRoles).values({ userId: adminUser.id, roleId: adminRole.id })
}

​
Best Practices

  1. SQLite for admin panels — Zero infra, local dev, good for small-to-medium row counts
  2. PostgreSQL for business data — Rich types (jsonb, arrays, CHECK constraints, numeric precision)
  3. CHECK constraints over app-level validation — Enforce enum values at the DB level
  4. Cascade deletes on M2M — onDelete: "cascade" prevents orphaned association rows
  5. Exclude sensitive columns — columns: { passwordHash: false } in session queries
  6. Repository pattern — Separate Drizzle queries from service/business logic
  7. Session versioning — Increment sessionVersion to force logout
  8. Seed with system roles — Always seed an admin role and initial permissions

​
References

Last modified on April 17, 2026