Schema Design with Drizzle ORM: Relations, JSONB, and Migration Strategy

Drizzle ORM strikes a balance between SQL expressiveness and TypeScript type safety. But the tool choices — JSONB vs relational, push vs generate, soft deletes vs hard deletes — still require deliberate decisions.

Defining Tables

Drizzle's table definitions are the source of truth. The TypeScript types flow from the schema, not the other way around.

import { pgTable, text, integer, boolean, timestamp, uuid, jsonb, index, uniqueIndex } from 'drizzle-orm/pg-core';
 
export const watchlist = pgTable('watchlist', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: text('userId').notNull().references(() => users.id, { onDelete: 'cascade' }),
  tmdbId: integer('tmdbId').notNull(),
  mediaType: text('mediaType', { enum: ['movie', 'tv', 'anime'] }).notNull(),
  title: text('title').notNull(),
  posterPath: text('posterPath'),
  addedAt: timestamp('addedAt').defaultNow().notNull(),
}, (table) => ({
  userMediaIdx: uniqueIndex('wl_user_media_idx').on(table.userId, table.tmdbId, table.mediaType),
  userIdx: index('wl_user_idx').on(table.userId),
}));

The enum type in Drizzle's text column creates a SQL check constraint, not a PostgreSQL enum type. For columns with a small, fixed set of values, this is often preferable — no migration required to add a new value, just update the constraint.

JSONB vs Separate Tables

PostgreSQL's JSONB is highly flexible but trades structure for queryability. When to use it:

Use JSONB when:

• The data structure varies per row (metadata, settings, configuration)
• You rarely query inside the JSON (you fetch the whole object)
• The keys aren't known at schema design time

Use a separate table when:

• You need to filter, join, or aggregate on specific fields
• The data has a predictable, consistent structure
• Multiple rows share references to the same data

In NodWatch's user_preferences:

export const userPreferences = pgTable('user_preferences', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: text('userId').notNull().unique().references(() => users.id),
  favoriteCategories: jsonb('favoriteCategories').$type<string[]>().default([]),
  favoriteGenreIds: jsonb('favoriteGenreIds').$type<number[]>().default([]),
  onboardingCompleted: boolean('onboardingCompleted').default(false),
});

Favorite categories and genre IDs are JSONB because they're arrays of values used as a unit — you always fetch all of them together, never query "which users have genre 28 in their preferences." If that query became needed, migrating to a separate user_favorite_genres table would be the right move.

Relations

Drizzle's relations API types the joins without affecting the SQL schema:

import { relations } from 'drizzle-orm';
 
export const usersRelations = relations(users, ({ many }) => ({
  watchlist: many(watchlist),
  watchProgress: many(watchProgress),
  reviews: many(reviews),
}));
 
export const watchlistRelations = relations(watchlist, ({ one }) => ({
  user: one(users, { fields: [watchlist.userId], references: [users.id] }),
}));

With relations defined, Drizzle's query API handles joins:

const userWithWatchlist = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { watchlist: { orderBy: desc(watchlist.addedAt) } }
});

Migration Strategy: Push vs Generate+Migrate

drizzle-kit push applies your schema directly to the database. Fast for development — no migration files, no history, instant sync. Dangerous for production — it computes a diff and applies it, which can include destructive operations (dropping columns, dropping tables) if your schema no longer references them.

drizzle-kit generate creates a SQL migration file from the diff. You review the file, commit it, and apply it with drizzle-kit migrate. This gives you:

• A migration history in version control
• The ability to review what SQL will run before running it
• The ability to add manual steps (data backfills) to the migration file

Use push for: local development, throwaway databases, prototyping.

Use generate + migrate for: staging and production. Always.

Soft Deletes

Hard deletes (DELETE FROM) are usually the wrong choice for user-generated content — you lose the data and create foreign key orphans. Soft deletes keep the row and mark it:

export const reviews = pgTable('reviews', {
  // ...
  deletedAt: timestamp('deletedAt'), // null = not deleted
});
 
// Query only non-deleted
const activeReviews = await db.select().from(reviews)
  .where(isNull(reviews.deletedAt));

The downside of soft deletes: every query needs WHERE deleted_at IS NULL, and your table grows forever. Use a partial index to keep the "active" queries fast:

CREATE INDEX idx_reviews_active ON reviews (tmdbId, createdAt DESC)
WHERE deleted_at IS NULL;

Type Safety in Queries

Drizzle infers return types from the schema. When you add a notNull() constraint, Drizzle makes the TypeScript type non-nullable. When you add an optional column (no notNull()), the type is T | null.

This matters for insert types vs select types:

type NewWatchlistItem = typeof watchlist.$inferInsert;
type WatchlistItem = typeof watchlist.$inferSelect;
 
// $inferInsert: id, addedAt are optional (they have defaults)
// $inferSelect: all columns present, null where nullable

Always use $inferInsert for your route handler input types, not $inferSelect — you don't want to require callers to provide auto-generated fields.