Database & Schema

ProductReady uses Drizzle ORM for type-safe database operations with PostgreSQL. This guide covers everything you need to know.

Why Drizzle? Best TypeScript support, SQL-like syntax, great performance, and lightweight (no bloat unlike Prisma).

Quick Overview

Stack:

ORM: Drizzle ORM
Database: PostgreSQL
Migrations: Drizzle Kit
Query builder: Type-safe with auto-completion

Common commands:

pnpm db:generate  # Generate migrations from schema
pnpm db:migrate   # Run migrations
pnpm db:push      # Push schema (dev only!)
pnpm db:studio    # Open database GUI
pnpm db:seed      # Populate with sample data

Database Schema

Existing Tables

ProductReady comes with these tables out of the box:

Users (`users`)

{
  id: string (uuid)
  email: string (unique)
  name: string | null
  emailVerified: boolean
  image: string | null
  createdAt: timestamp
  updatedAt: timestamp
}

Sessions (`sessions`)

{
  id: string
  userId: string (foreign key → users.id)
  expiresAt: timestamp
  ipAddress: string | null
  userAgent: string | null
}

Tasks (`tasks`)

{
  id: number (serial)
  title: string
  description: string | null
  status: 'pending' | 'in_progress' | 'completed'
  priority: 'low' | 'medium' | 'high' | 'urgent'
  userId: string (foreign key → users.id)
  createdAt: timestamp
  updatedAt: timestamp
}

Posts (`posts`)

{
  id: number (serial)
  title: string
  content: string
  published: timestamp | null
  authorId: string (foreign key → users.id)
  createdAt: timestamp
  updatedAt: timestamp
}

Creating a New Table

Let's add a projects table step-by-step.

Step 1: Define schema

Create src/db/schema/projects.ts:

import { pgTable, serial, text, timestamp, varchar } from 'drizzle-orm/pg-core';
import { users } from './users';

export const projects = pgTable('projects', {
  id: serial('id').primaryKey(),
  
  name: varchar('name', { length: 255 }).notNull(),
  
  description: text('description'),
  
  status: text('status', { 
    enum: ['active', 'archived', 'completed'] 
  }).default('active').notNull(),
  
  ownerId: text('owner_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  
  createdAt: timestamp('created_at').defaultNow().notNull(),
  
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// TypeScript type from schema
export type Project = typeof projects.$inferSelect;
export type NewProject = typeof projects.$inferInsert;

Schema types:

$inferSelect - Type for reading (includes all fields)
$inferInsert - Type for creating (optional fields allowed)

Step 2: Export from index

Add to src/db/schema/index.ts:

export * from './users';
export * from './sessions';
export * from './tasks';
export * from './posts';
export * from './projects'; // ← Add this

Step 3: Generate migration

pnpm db:generate

You'll see:

✓ Generated migration: 0001_create_projects_table.sql

This creates SQL in drizzle/migrations/:

CREATE TABLE "projects" (
  "id" serial PRIMARY KEY,
  "name" varchar(255) NOT NULL,
  "description" text,
  "status" text DEFAULT 'active' NOT NULL,
  "owner_id" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "projects_owner_id_users_id_fk" 
    FOREIGN KEY ("owner_id") REFERENCES "users"("id") ON DELETE cascade
);

Step 4: Run migration

pnpm db:migrate

Your database now has the projects table! ✅

Querying Data

Select (Read)

Get all projects:

import { db } from '~/db';
import { projects } from '~/db/schema';

const allProjects = await db.select().from(projects);
// Type: Project[]

Get specific columns:

const projectNames = await db
  .select({ 
    id: projects.id, 
    name: projects.name 
  })
  .from(projects);
// Type: { id: number; name: string }[]

import { eq, and, or, like, gte } from 'drizzle-orm';

// Single condition
const activeProjects = await db
  .select()
  .from(projects)
  .where(eq(projects.status, 'active'));

// Multiple conditions (AND)
const myActiveProjects = await db
  .select()
  .from(projects)
  .where(
    and(
      eq(projects.ownerId, userId),
      eq(projects.status, 'active')
    )
  );

// OR condition
const urgentOrActive = await db
  .select()
  .from(projects)
  .where(
    or(
      eq(projects.status, 'active'),
      eq(projects.status, 'urgent')
    )
  );

// LIKE search
const searchResults = await db
  .select()
  .from(projects)
  .where(like(projects.name, '%design%'));

// Date comparison
const recent = await db
  .select()
  .from(projects)
  .where(gte(projects.createdAt, new Date('2024-01-01')));

Join with users:

import { eq } from 'drizzle-orm';
import { users, projects } from '~/db/schema';

const projectsWithOwners = await db
  .select({
    id: projects.id,
    name: projects.name,
    owner: {
      id: users.id,
      name: users.name,
      email: users.email,
    },
  })
  .from(projects)
  .leftJoin(users, eq(projects.ownerId, users.id));

// Result type:
// {
//   id: number;
//   name: string;
//   owner: { id: string; name: string; email: string } | null;
// }[]

const page = 1;
const pageSize = 20;

const paginatedProjects = await db
  .select()
  .from(projects)
  .limit(pageSize)
  .offset((page - 1) * pageSize)
  .orderBy(desc(projects.createdAt));

// Get total count
const [{ count }] = await db
  .select({ count: count() })
  .from(projects);

const totalPages = Math.ceil(count / pageSize);

Insert (Create)

const newProject = await db
  .insert(projects)
  .values({
    name: 'Website Redesign',
    description: 'Redesign company website',
    ownerId: userId,
    status: 'active',
  });

const newProjects = await db
  .insert(projects)
  .values([
    { name: 'Project A', ownerId: userId },
    { name: 'Project B', ownerId: userId },
    { name: 'Project C', ownerId: userId },
  ]);

const [createdProject] = await db
  .insert(projects)
  .values({ name: 'New Project', ownerId: userId })
  .returning();

console.log(createdProject.id); // Auto-generated ID

Update

import { eq } from 'drizzle-orm';

await db
  .update(projects)
  .set({ 
    status: 'completed',
    updatedAt: new Date(),
  })
  .where(eq(projects.id, projectId));

// Mark all as archived
await db
  .update(projects)
  .set({ status: 'archived' })
  .where(eq(projects.ownerId, userId));

const updates = {
  name: 'Updated Name',
  // Only update provided fields
};

await db
  .update(projects)
  .set(updates)
  .where(eq(projects.id, projectId))
  .returning();

Delete

import { eq, and, inArray } from 'drizzle-orm';

// Delete single
await db
  .delete(projects)
  .where(eq(projects.id, projectId));

// Delete multiple
await db
  .delete(projects)
  .where(inArray(projects.id, [1, 2, 3]));

// Delete with condition
await db
  .delete(projects)
  .where(
    and(
      eq(projects.ownerId, userId),
      eq(projects.status, 'archived')
    )
  );

Soft delete instead? Add a deletedAt field and filter in queries instead of actually deleting.

Relationships

One-to-Many

Example: User has many projects

// schema/users.ts
import { relations } from 'drizzle-orm';
import { users } from './users';
import { projects } from './projects';

export const usersRelations = relations(users, ({ many }) => ({
  projects: many(projects),
}));

// schema/projects.ts
export const projectsRelations = relations(projects, ({ one }) => ({
  owner: one(users, {
    fields: [projects.ownerId],
    references: [users.id],
  }),
}));

Query with relation:

const userWithProjects = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    projects: true,
  },
});
// Type: User & { projects: Project[] }

Many-to-Many

Example: Projects have many tags, tags have many projects

// schema/project_tags.ts (junction table)
export const projectTags = pgTable('project_tags', {
  projectId: integer('project_id')
    .notNull()
    .references(() => projects.id),
  tagId: integer('tag_id')
    .notNull()
    .references(() => tags.id),
});

export const projectTagsRelations = relations(projectTags, ({ one }) => ({
  project: one(projects, {
    fields: [projectTags.projectId],
    references: [projects.id],
  }),
  tag: one(tags, {
    fields: [projectTags.tagId],
    references: [tags.id],
  }),
}));

// Query
const projectWithTags = await db.query.projects.findFirst({
  where: eq(projects.id, projectId),
  with: {
    projectTags: {
      with: {
        tag: true,
      },
    },
  },
});

Migrations

Generate Migration

After changing schema:

pnpm db:generate

This creates SQL files in drizzle/migrations/.

Review the SQL before applying!

Run Migration

Apply to database:

pnpm db:migrate

Rollback Migration

Manually delete the migration file and run:

pnpm db:migrate

Or write a down migration (manual SQL).

Database Studio

Drizzle Kit includes a visual database browser:

pnpm db:studio

Opens at https://local.drizzle.studio

Features:

Browse tables
Edit data
View relationships
Run custom queries

Perfect for debugging and exploring!

Best Practices

✅ Do

Use transactions for multiple related writes

await db.transaction(async (tx) => {
  await tx.insert(projects).values({ name: 'Project' });
  await tx.insert(tasks).values({ title: 'Task' });
});

Index foreign keys for performance

export const tasks = pgTable('tasks', {
  projectId: integer('project_id').notNull(),
}, (table) => ({
  projectIdIdx: index('project_id_idx').on(table.projectId),
}));

Use enums for fixed value sets

status: text('status', { enum: ['active', 'archived'] })

Add timestamps to all tables

createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().notNull(),

❌ Don't

Don't use db:push in production - only for rapid prototyping
Don't store sensitive data unencrypted - hash passwords, encrypt secrets
Don't return entire user objects to frontend - exclude password hashes
Don't query in loops - use joins or inArray() instead

Common Patterns

Soft Delete

Add deletedAt field instead of actually deleting:

export const projects = pgTable('projects', {
  // ... other fields
  deletedAt: timestamp('deleted_at'),
});

// "Delete" (soft)
await db
  .update(projects)
  .set({ deletedAt: new Date() })
  .where(eq(projects.id, projectId));

// Query only non-deleted
const activeProjects = await db
  .select()
  .from(projects)
  .where(isNull(projects.deletedAt));

Timestamps with Auto-Update

Use database trigger or update manually:

await db
  .update(projects)
  .set({ 
    name: 'New Name',
    updatedAt: new Date(), // ← Manual update
  })
  .where(eq(projects.id, projectId));

Unique Constraints

export const projects = pgTable('projects', {
  slug: varchar('slug', { length: 255 }).notNull().unique(),
}, (table) => ({
  // Composite unique constraint
  ownerSlugUnique: unique().on(table.ownerId, table.slug),
}));

Full-Text Search

Use PostgreSQL's built-in search:

import { sql } from 'drizzle-orm';

const results = await db
  .select()
  .from(projects)
  .where(
    sql`to_tsvector('english', ${projects.name}) @@ to_tsquery('english', ${searchQuery})`
  );

Seeding Data

Create src/db/seed.ts:

import { db } from './index';
import { users, projects } from './schema';

async function seed() {
  console.log('Seeding database...');

  // Create users
  const [alice] = await db
    .insert(users)
    .values({
      email: 'alice@example.com',
      name: 'Alice Johnson',
    })
    .returning();

  // Create projects
  await db.insert(projects).values([
    { name: 'Website Redesign', ownerId: alice.id },
    { name: 'Mobile App', ownerId: alice.id },
  ]);

  console.log('✓ Seed completed');
}

seed().catch(console.error);

Run:

pnpm db:seed

Troubleshooting

"relation does not exist"

You forgot to run migrations:

pnpm db:migrate

"Type 'string' is not assignable to..."

Check your schema - ensure types match (e.g., text vs varchar, serial vs integer)

"Connection refused"

Database not running or wrong PG_DATABASE_URL:

# Check connection
psql $PG_DATABASE_URL

"Too many clients"

Use connection pooling (see Deployment Guide)

Next Steps

📚 More resources:

Database & Schema

On this page