Schema Design

Schema Design Skill

This skill helps you design and modify database schemas using Drizzle ORM in packages/database/.

When to Use This Skill

• Creating new database tables
• Adding columns to existing tables
• Defining relationships between tables
• Creating indexes for query optimization
• Adding constraints (unique, not null, default values)
• Renaming or dropping tables/columns
• Optimizing schema for performance

Database Architecture

packages/database/
├── src/
│   ├── db/
│   │   └── schema/
│   │       ├── cars.ts         # Car registration data
│   │       ├── coe.ts          # COE bidding results
│   │       ├── pqp.ts          # PQP data
│   │       ├── posts.ts        # Blog posts
│   │       ├── analytics.ts    # Analytics events
│   │       └── index.ts        # Schema exports
│   ├── index.ts                # Database client export
│   └── migrate.ts              # Migration runner
├── migrations/                  # Migration files
└── drizzle.config.ts           # Drizzle configuration

Naming Conventions

The project uses camelCase for column names:

// ✅ Correct
export const cars = pgTable("cars", {
  vehicleClass: text("vehicle_class"),
  fuelType: text("fuel_type"),
  registrationDate: timestamp("registration_date"),
});

// ❌ Wrong
export const cars = pgTable("cars", {
  vehicle_class: text("vehicle_class"),  // snake_case
  FuelType: text("fuel_type"),            // PascalCase
});

Basic Schema Patterns

Simple Table

// packages/database/src/db/schema/example.ts
import { pgTable, text, integer, timestamp, boolean } from "drizzle-orm/pg-core";

export const examples = pgTable("examples", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  count: integer("count").default(0).notNull(),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Table with Relationships

import { pgTable, text, integer, timestamp } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { users } from "./users";

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

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

Table with Indexes

import { pgTable, text, integer, timestamp, index, uniqueIndex } from "drizzle-orm/pg-core";

export const cars = pgTable("cars", {
  id: text("id").primaryKey(),
  make: text("make").notNull(),
  model: text("model").notNull(),
  year: integer("year").notNull(),
  registrationDate: timestamp("registration_date").notNull(),
}, (table) => ({
  // Single column index
  makeIdx: index("cars_make_idx").on(table.make),

  // Composite index
  makeModelIdx: index("cars_make_model_idx").on(table.make, table.model),

  // Unique index
  registrationIdx: uniqueIndex("cars_registration_idx").on(table.registrationDate),
}));

Existing Schema Examples

Cars Table

// packages/database/src/db/schema/cars.ts
import { pgTable, text, integer, timestamp, index } from "drizzle-orm/pg-core";

export const cars = pgTable("cars", {
  id: text("id").primaryKey(),
  make: text("make").notNull(),
  model: text("model"),
  vehicleClass: text("vehicle_class"),
  fuelType: text("fuel_type"),
  month: text("month").notNull(),
  number: integer("number").default(0).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  monthIdx: index("cars_month_idx").on(table.month),
  makeIdx: index("cars_make_idx").on(table.make),
}));

COE Table

// packages/database/src/db/schema/coe.ts
import { pgTable, text, integer, timestamp, numeric, index } from "drizzle-orm/pg-core";

export const coe = pgTable("coe", {
  id: text("id").primaryKey(),
  biddingNo: integer("bidding_no").notNull(),
  month: text("month").notNull(),
  vehicleClass: text("vehicle_class").notNull(),
  quota: integer("quota").default(0).notNull(),
  bidsReceived: integer("bids_received").default(0).notNull(),
  premium: numeric("premium", { precision: 10, scale: 2 }).default("0").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  biddingNoIdx: index("coe_bidding_no_idx").on(table.biddingNo),
  monthIdx: index("coe_month_idx").on(table.month),
}));

Posts Table

// packages/database/src/db/schema/posts.ts
import { pgTable, text, timestamp, boolean, index } from "drizzle-orm/pg-core";

export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  slug: text("slug").notNull().unique(),
  content: text("content").notNull(),
  excerpt: text("excerpt"),
  published: boolean("published").default(false).notNull(),
  publishedAt: timestamp("published_at"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  slugIdx: index("posts_slug_idx").on(table.slug),
  publishedAtIdx: index("posts_published_at_idx").on(table.publishedAt),
}));

Column Types

Text Types

import { pgTable, text, varchar, char } from "drizzle-orm/pg-core";

export const examples = pgTable("examples", {
  // Unlimited text
  description: text("description"),

  // Limited varchar
  email: varchar("email", { length: 255 }),

  // Fixed length
  code: char("code", { length: 10 }),
});

Numeric Types

import { pgTable, integer, bigint, numeric, real, doublePrecision } from "drizzle-orm/pg-core";

export const examples = pgTable("examples", {
  // Integer types
  count: integer("count"),
  bigCount: bigint("big_count", { mode: "number" }),  // or "bigint" for BigInt

  // Decimal types
  price: numeric("price", { precision: 10, scale: 2 }),  // 10 digits, 2 decimal

  // Floating point
  rating: real("rating"),
  coordinate: doublePrecision("coordinate"),
});

Date/Time Types

import { pgTable, timestamp, date, time } from "drizzle-orm/pg-core";

export const examples = pgTable("examples", {
  // Timestamp with timezone
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),

  // Timestamp without timezone
  scheduledAt: timestamp("scheduled_at", { withTimezone: false }),

  // Date only
  birthDate: date("birth_date"),

  // Time only
  openingTime: time("opening_time"),
});

Boolean and JSON

import { pgTable, boolean, json, jsonb } from "drizzle-orm/pg-core";

export const examples = pgTable("examples", {
  // Boolean
  isActive: boolean("is_active").default(true),

  // JSON (slower, stores as text)
  settings: json("settings"),

  // JSONB (faster, binary format)
  metadata: jsonb("metadata").$type<{ key: string; value: any }>(),
});

Array Types

import { pgTable, text } from "drizzle-orm/pg-core";

export const examples = pgTable("examples", {
  tags: text("tags").array(),
  emails: text("emails").array().notNull().default([]),
});

Relationships

One-to-Many

import { pgTable, text, timestamp } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

// Users table
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
});

// Posts table (many posts belong to one user)
export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  authorId: text("author_id").notNull().references(() => users.id),
});

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

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

Many-to-Many

import { pgTable, text, primaryKey } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

// Posts table
export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
});

// Tags table
export const tags = pgTable("tags", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
});

// Junction table
export const postsToTags = pgTable("posts_to_tags", {
  postId: text("post_id").notNull().references(() => posts.id),
  tagId: text("tag_id").notNull().references(() => tags.id),
}, (table) => ({
  pk: primaryKey({ columns: [table.postId, table.tagId] }),
}));

// Define relations
export const postsRelations = relations(posts, ({ many }) => ({
  postsToTags: many(postsToTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postsToTags: many(postsToTags),
}));

export const postsToTagsRelations = relations(postsToTags, ({ one }) => ({
  post: one(posts, {
    fields: [postsToTags.postId],
    references: [posts.id],
  }),
  tag: one(tags, {
    fields: [postsToTags.tagId],
    references: [tags.id],
  }),
}));

Constraints

Primary Keys

import { pgTable, text, integer, primaryKey } from "drizzle-orm/pg-core";

// Single column primary key
export const users = pgTable("users", {
  id: text("id").primaryKey(),
});

// Composite primary key
export const userRoles = pgTable("user_roles", {
  userId: text("user_id").notNull(),
  roleId: text("role_id").notNull(),
}, (table) => ({
  pk: primaryKey({ columns: [table.userId, table.roleId] }),
}));

Unique Constraints

import { pgTable, text, unique } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),  // Column-level unique
  username: text("username").notNull(),
}, (table) => ({
  // Table-level unique constraint
  uniqueUsername: unique("users_username_unique").on(table.username),
}));

Foreign Keys

import { pgTable, text, foreignKey } from "drizzle-orm/pg-core";

export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  authorId: text("author_id").notNull(),
}, (table) => ({
  // Inline foreign key
  authorFk: foreignKey({
    columns: [table.authorId],
    foreignColumns: [users.id],
  }).onDelete("cascade"),  // Options: cascade, set null, restrict, no action
}));

// Or use references() shorthand
export const posts2 = pgTable("posts", {
  id: text("id").primaryKey(),
  authorId: text("author_id").notNull().references(() => users.id, { onDelete: "cascade" }),
});

Check Constraints

import { pgTable, integer, check, sql } from "drizzle-orm/pg-core";

export const products = pgTable("products", {
  id: text("id").primaryKey(),
  price: integer("price").notNull(),
  discount: integer("discount").notNull(),
}, (table) => ({
  // Ensure discount is less than price
  priceCheck: check("price_check", sql`${table.price} > ${table.discount}`),
}));

Indexes

Single Column Index

import { pgTable, text, index } from "drizzle-orm/pg-core";

export const cars = pgTable("cars", {
  id: text("id").primaryKey(),
  make: text("make").notNull(),
}, (table) => ({
  makeIdx: index("cars_make_idx").on(table.make),
}));

Composite Index

export const cars = pgTable("cars", {
  id: text("id").primaryKey(),
  make: text("make").notNull(),
  model: text("model").notNull(),
}, (table) => ({
  makeModelIdx: index("cars_make_model_idx").on(table.make, table.model),
}));

Unique Index

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

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull(),
}, (table) => ({
  emailIdx: uniqueIndex("users_email_idx").on(table.email),
}));

Partial Index

import { sql } from "drizzle-orm";

export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  published: boolean("published").default(false),
  publishedAt: timestamp("published_at"),
}, (table) => ({
  // Index only published posts
  publishedIdx: index("posts_published_idx")
    .on(table.publishedAt)
    .where(sql`${table.published} = true`),
}));

Schema Workflow

1. Create Schema File

// packages/database/src/db/schema/my-table.ts
import { pgTable, text, timestamp } from "drizzle-orm/pg-core";

export const myTable = pgTable("my_table", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

2. Export from Index

// packages/database/src/db/schema/index.ts
export * from "./cars";
export * from "./coe";
export * from "./posts";
export * from "./my-table";  // Add new export

3. Generate Migration

cd packages/database

# Generate migration from schema changes
pnpm db:generate

# This creates a new migration file in migrations/

4. Review Migration

Check generated SQL in migrations/XXXX_migration_name.sql:

CREATE TABLE IF NOT EXISTS "my_table" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL
);

5. Run Migration

# Apply migration to database
pnpm db:migrate

Common Schema Patterns

Soft Delete

export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  deletedAt: timestamp("deleted_at"),  // null = not deleted
});

// Query only non-deleted posts
const activePosts = await db.query.posts.findMany({
  where: isNull(posts.deletedAt),
});

Timestamps

export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Update updatedAt on every change
await db.update(posts)
  .set({
    title: "New Title",
    updatedAt: new Date(),
  })
  .where(eq(posts.id, postId));

Enum Types

import { pgTable, text, pgEnum } from "drizzle-orm/pg-core";

// Define enum
export const roleEnum = pgEnum("role", ["admin", "user", "guest"]);

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  role: roleEnum("role").default("user").notNull(),
});

UUID Primary Keys

import { pgTable, uuid, text } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),  // Auto-generate UUID
  name: text("name").notNull(),
});

Performance Optimization

Choose Appropriate Indexes

// ✅ Index frequently queried columns
export const cars = pgTable("cars", {
  make: text("make").notNull(),
  registrationDate: timestamp("registration_date").notNull(),
}, (table) => ({
  makeIdx: index().on(table.make),              // For: WHERE make = 'Toyota'
  dateIdx: index().on(table.registrationDate),  // For: WHERE registrationDate > '2024-01-01'
}));

// ❌ Don't index every column
// Only index columns used in WHERE, JOIN, ORDER BY

Use Appropriate Data Types

// ✅ Use smallest appropriate type
count: integer("count"),              // -2B to 2B
price: numeric("price", { precision: 10, scale: 2 }),  // $99,999,999.99

// ❌ Don't use text for everything
count: text("count"),  // Wastes space, slower queries

Denormalization for Performance

// Store computed values to avoid expensive joins
export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  authorId: text("author_id").notNull(),
  authorName: text("author_name").notNull(),  // Denormalized from users table
  commentsCount: integer("comments_count").default(0),  // Denormalized count
});

Testing Schemas

// packages/database/src/db/schema/__tests__/cars.test.ts
import { describe, it, expect } from "vitest";
import { db } from "../../index";
import { cars } from "../cars";

describe("Cars Schema", () => {
  it("inserts and queries car data", async () => {
    const [car] = await db.insert(cars).values({
      id: "test-1",
      make: "Toyota",
      model: "Camry",
      month: "2024-01",
      number: 100,
    }).returning();

    expect(car.make).toBe("Toyota");
    expect(car.number).toBe(100);
  });
});

References

• Drizzle ORM Documentation: Use Context7 for latest docs
• Related files:
  • packages/database/src/db/schema/ - All schema files
  • packages/database/drizzle.config.ts - Drizzle configuration
  • packages/database/CLAUDE.md - Database package documentation

Best Practices

1. Naming: Use camelCase for columns, snake_case for table names
2. Not Null: Use .notNull() for required fields
3. Defaults: Provide sensible defaults where appropriate
4. Indexes: Index columns used in WHERE, JOIN, ORDER BY
5. Relationships: Define relations for type-safe queries
6. Timestamps: Always include createdAt/updatedAt
7. Constraints: Use unique, foreign key constraints
8. Migrations: Always review generated migrations before running