Database Workflows Skill

Database Workflows

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

Quick Reference

| Task | Key Action | |------|------------| | Schema design | Normalize to 3NF, add indexes for queries | | Migration review | Check reversibility, data preservation | | Query optimization | Explain analyze, check indexes | | N+1 prevention | Eager load relations, use joins | | Index selection | Composite for multi-column WHERE |

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

| Problem | Solution | |---------|----------| | Full table scan | Add index on WHERE columns | | Filesort | Add index matching ORDER BY | | Using temporary | Optimize GROUP BY, add composite index | | Seq Scan on large table | Add covering index |

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

| Query Pattern | Index Type | |---------------|------------| | WHERE col = ? | B-tree on col | | WHERE col1 = ? AND col2 = ? | Composite (col1, col2) | | WHERE col LIKE 'prefix%' | B-tree on col | | WHERE col @@ to_tsquery(?) | GIN full-text | | ORDER BY col | B-tree on col | | WHERE col IN (...) | B-tree on col |

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

Check Normalization
- [ ] No repeated groups
- [ ] All columns depend on primary key
- [ ] No transitive dependencies
Validate Relationships
- [ ] Foreign keys defined correctly
- [ ] Cascade rules appropriate
- [ ] Junction tables for many-to-many
Review Indexes
- [ ] Indexes on foreign keys
- [ ] Indexes on commonly queried columns
- [ ] Composite indexes in correct order
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

Analyze Query Plan
- [ ] Run EXPLAIN ANALYZE
- [ ] Identify sequential scans
- [ ] Check join strategies
Identify Issues
- [ ] Missing indexes
- [ ] Incorrect join order
- [ ] Unnecessary columns in SELECT
Apply Fixes
- [ ] Add appropriate indexes
- [ ] Rewrite query if needed
- [ ] Use query hints if necessary
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

| Mistake | Fix | |---------|-----| | No index on foreign key | Add index on FK columns | | SELECT * in production | Select only needed columns | | N+1 in loops | Use eager loading or joins | | Missing timestamps | Add created_at, updated_at | | Nullable by default | Explicitly define NOT NULL | | No migration rollback | Always write down migration |

Reference Files

| File | Contents | |------|----------| | SCHEMA-DESIGN.md | Schema patterns, normalization, relationships | | MIGRATIONS.md | Migration strategies, rollback, versioning | | QUERIES.md | Query optimization, N+1 prevention, performance |

Agent Skills: Database Workflows

Skill Files