Schema Design
A schema Schema The structure of your database — what tables exist, what columns they have, and how they relate to each other. "Like the blueprint of a building. It defines the shape before you add the furniture (data)."
What is a Schema?
Think of a schema like the floor plan for a building. Before you build, you need to know where the rooms go, how big they are, and how they connect. A database Database An organized collection of structured information, or data, typically stored electronically in a computer system. "Like a giant, super-organized filing cabinet where the app stores all its users, posts, and details."
- • Tables — The "rooms" that hold your data (users, posts, orders)
- • Columns — The "features" of each room (name, email, createdAt)
- • Types — What kind of data each column holds (text, number, date)
- • Relations — How rooms connect (a user has many posts)
Why This Matters
A well-designed schema makes your app fast and your code simple. A messy schema means constant workarounds, slow queries, and bugs. Spend time here: it pays off. Changing a schema later means a migration Migration A controlled change to your database schema. Lets you version-control your database structure and safely update it. "Like renovating a house room by room, with blueprints for each change."
Core Concepts
Tables
Tables hold collections of similar items. Think of them as spreadsheets—each row is one item, each column is a property.
// users table
| id | name | created_at | |
|---|---|---|---|
| 1 | Alice | alice@example.com | 2024-01-15 |
| 2 | Bob | bob@example.com | 2024-01-16 |
Columns & Data Types
Each column has a type that defines what kind of data it can hold.
Common Types
text— Strings of any lengthvarchar(255)— String with max lengthinteger— Whole numbersboolean— true/falsetimestamptz— Date and time with time zone (prefer it over plaintimestamp)numeric— Exact decimals (money!), never floatsjsonb— Structured JSON, queryable in Postgresuuid— Unique identifier
Column Modifiers
NOT NULL— Required fieldDEFAULT— Auto-fill valueUNIQUE— No duplicatesPRIMARY KEY— Row identifier
Primary Keys
Every table needs a primary key—a unique identifier for each row. Most common options:
Auto-increment ID
id SERIAL PRIMARY KEYSimple, sequential. Good for most cases.
UUID
id UUID DEFAULT gen_random_uuid()Random, unguessable. Better for public URLs.
Foreign Keys (Relations)
Foreign keys connect tables together. A user_id in a posts table points back to the users table.
SQL
-- posts table with a foreign key to users
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);The same table in Drizzle (lib/db/schema.ts)
import { pgTable, serial, text, integer, timestamp, index } from "drizzle-orm/pg-core";
export const posts = pgTable(
"posts",
{
id: serial("id").primaryKey(),
title: text("title").notNull(),
content: text("content"),
userId: integer("user_id")
.notNull()
.references(() => users.id, { onDelete: "cascade" }),
createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
},
(t) => [index("posts_user_id_idx").on(t.userId)],
);Cascade Deletes
Add ON DELETE CASCADE to automatically delete posts when a user is deleted. Be careful with this!
Common Relation Types
One-to-Many
One user has many posts. One order has many items. The "many" side stores the foreign key.
One-to-One
One user has one profile. The foreign key goes on either side with a UNIQUE constraint.
Many-to-Many
Users can belong to many teams. Teams have many users. Requires a junction table.
SQL: junction table
CREATE TABLE user_teams (
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
team_id INTEGER REFERENCES teams(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, team_id)
);Indexes (Speed Up Queries)
An index is like a book's table of contents. Instead of scanning every row, the database jumps directly to what it needs.
SQL
-- Index on email for faster lookups
CREATE INDEX users_email_idx ON users(email);
-- Composite index for queries filtering by both
CREATE INDEX posts_user_created_idx ON posts(user_id, created_at);When to Index
Index columns you filter by (WHERE), sort by (ORDER BY), or join on. Don't over-index—each index slows down writes.
Common Pitfalls
Storing JSON blobs for everything
It's tempting to dump everything in a JSON column. Postgres jsonb can be queried and even indexed, but you lose type checks, foreign keys, and easy migrations.
Fix: Use proper columns for data you'll query. Reserve JSON for truly unstructured data like user preferences.
Missing indexes on foreign keys
Foreign keys aren't automatically indexed. Joins without indexes are slow.
Fix: Add an index for every foreign key column: CREATE INDEX posts_user_id_idx ON posts(user_id).
Over-normalization
Splitting everything into tiny tables means complex joins for simple queries.
Fix: It's okay to denormalize for read performance. Store author_name on posts if you always display it.
No timestamps
Forgetting created_at and updated_at means no audit trail.
Fix: Add both to every table. Set defaults: created_at TIMESTAMPTZ DEFAULT NOW(). In Drizzle, .$onUpdate(() => new Date()) keeps updated_at fresh.
Storing money as floats
0.1 + 0.2 isn't exactly 0.3 in floating point. Rounding errors in prices add up.
Fix: Store integer cents (Stripe does: 1999 means $19.99) or use numeric. See Payments.
Ready to write queries?
Turn this design into type-safe code with Drizzle Drizzle ORM A lightweight, type-safe ORM for TypeScript. Your schema IS your types — no code generation, no sync issues. SQL-like syntax that feels natural. "Like having a personal translator who speaks both TypeScript and SQL fluently. Zero confusion."