Database Workflows

Database Workflows

Quick reference for database work with Claude Code - schema design, migrations, query optimization, and ORM patterns.

Quick Reference

When to Use This Skill

• Designing new database schemas
• Reviewing migration files before running
• Optimizing slow queries
• Debugging N+1 query problems
• Adding or reviewing indexes
• Working with Prisma, Drizzle, or TypeORM

Schema Design Checklist

Before creating or modifying schemas:

• Tables have singular names (user not users)
• Primary keys are id (auto-increment or UUID)
• Foreign keys follow {table}_id pattern
• Timestamps include created_at, updated_at
• Nullable columns are intentional
• Indexes cover common query patterns
• No redundant data (normalized to 3NF minimum)

See SCHEMA-DESIGN.md for detailed patterns.

Migration Workflow

Before Creating Migrations

# Prisma
bunx prisma migrate dev --create-only --name descriptive_name

# Drizzle
bunx drizzle-kit generate:pg --name descriptive_name

# TypeORM
bunx typeorm migration:generate -n DescriptiveName

Migration Review Checklist

• Migration is reversible (has down/rollback)
• No data loss on rollback
• Large tables use batched operations
• Indexes created CONCURRENTLY (if supported)
• Foreign key constraints don't lock tables
• Default values for new NOT NULL columns

See MIGRATIONS.md for strategies.

Query Optimization Quick Guide

Identify Slow Queries

-- PostgreSQL: Find slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- MySQL: Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Analyze Queries

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;

Common Optimizations

See QUERIES.md for detailed optimization.

N+1 Query Prevention

Problem Pattern

// BAD: N+1 queries
const users = await db.user.findMany();
for (const user of users) {
  const posts = await db.post.findMany({ where: { userId: user.id } });
}

Solution Pattern

// GOOD: Single query with relation
const users = await db.user.findMany({
  include: { posts: true }
});

Detection

// Prisma: Enable query logging
const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

// Drizzle: Use query builder with joins
const result = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

Index Quick Reference

When to Add Indexes

When NOT to Add Indexes

• Small tables (< 1000 rows)
• Columns with low cardinality
• Write-heavy tables with rare reads
• Columns rarely used in WHERE/ORDER BY

Index Commands

-- PostgreSQL: Create without locking
CREATE INDEX CONCURRENTLY idx_name ON table(column);

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Find missing indexes
SELECT relname, seq_scan, idx_scan,
       seq_scan - idx_scan AS difference
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY difference DESC;

ORM Patterns

Prisma

// Schema definition
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  posts     Post[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
  @@index([email])
}

// Efficient query with select
const users = await prisma.user.findMany({
  select: { id: true, email: true },
  where: { email: { contains: '@company.com' } },
  take: 10,
});

Drizzle

// Schema definition
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
}, (table) => ({
  emailIdx: index('email_idx').on(table.email),
}));

// Efficient query with joins
const result = await db
  .select({ id: users.id, email: users.email })
  .from(users)
  .where(like(users.email, '%@company.com'))
  .limit(10);

TypeORM

// Entity definition
@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  @Index()
  email: string;

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;

  @OneToMany(() => Post, post => post.user)
  posts: Post[];
}

// Efficient query with QueryBuilder
const users = await userRepository
  .createQueryBuilder('user')
  .select(['user.id', 'user.email'])
  .where('user.email LIKE :email', { email: '%@company.com' })
  .take(10)
  .getMany();

Database-Specific Patterns

PostgreSQL

-- UPSERT
INSERT INTO users (email, name)
VALUES ('test@example.com', 'Test')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

-- Array columns
ALTER TABLE users ADD COLUMN tags TEXT[];
CREATE INDEX idx_users_tags ON users USING GIN(tags);
SELECT * FROM users WHERE 'admin' = ANY(tags);

-- JSON columns
ALTER TABLE users ADD COLUMN metadata JSONB DEFAULT '{}';
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);
SELECT * FROM users WHERE metadata->>'role' = 'admin';

MySQL

-- UPSERT
INSERT INTO users (email, name)
VALUES ('test@example.com', 'Test')
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW();

-- Full-text search
ALTER TABLE posts ADD FULLTEXT INDEX ft_content (title, content);
SELECT * FROM posts WHERE MATCH(title, content) AGAINST('search term');

SQLite

-- UPSERT
INSERT INTO users (email, name)
VALUES ('test@example.com', 'Test')
ON CONFLICT(email)
DO UPDATE SET name = excluded.name, updated_at = datetime('now');

-- Enable foreign keys (per connection)
PRAGMA foreign_keys = ON;

-- WAL mode for better concurrency
PRAGMA journal_mode = WAL;

Workflow: Schema Review

Prerequisites

• Schema file or migration to review
• Understanding of query patterns

Steps

1. Check Normalization

   • No repeated groups
   • All columns depend on primary key
   • No transitive dependencies

2. Validate Relationships

   • Foreign keys defined correctly
   • Cascade rules appropriate
   • Junction tables for many-to-many

3. Review Indexes

   • Indexes on foreign keys
   • Indexes on commonly queried columns
   • Composite indexes in correct order

4. Check Constraints

   • NOT NULL where required
   • UNIQUE where appropriate
   • CHECK constraints for valid ranges

Validation

• No N+1 patterns in expected queries
• Indexes support all common queries
• Schema can evolve without data loss

Workflow: Query Optimization

Prerequisites

• Slow query identified
• Access to EXPLAIN ANALYZE

Steps

1. Analyze Query Plan

   • Run EXPLAIN ANALYZE
   • Identify sequential scans
   • Check join strategies

2. Identify Issues

   • Missing indexes
   • Incorrect join order
   • Unnecessary columns in SELECT

3. Apply Fixes

   • Add appropriate indexes
   • Rewrite query if needed
   • Use query hints if necessary

4. Verify Improvement

   • Re-run EXPLAIN ANALYZE
   • Compare execution times
   • Test under load

Validation

• Query uses indexes effectively
• Execution time acceptable
• No regression in related queries

Common Mistakes

Reference Files