---

name: database-management description: Database schema design, migrations, query optimization, and ORM best practices. Use for database setup, performance tuning, and data modeling.

🗄️ Database Management Skill

Schema Design Patterns

Normalization Levels

Common Patterns

Copy-- One-to-Many
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  content TEXT
);

-- Many-to-Many (Junction Table)
CREATE TABLE tags (id SERIAL PRIMARY KEY, name VARCHAR(50));
CREATE TABLE post_tags (
  post_id INTEGER REFERENCES posts(id),
  tag_id INTEGER REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

---

Migration Strategies

Prisma

Copy# Create migration
npx prisma migrate dev --name add_users_table

# Apply to production
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

Drizzle

Copy# Generate migration
npx drizzle-kit generate:pg

# Push to database
npx drizzle-kit push:pg

Safe Migration Checklist

• Backup database first
• Test on staging environment
• Plan rollback strategy
• Run during low-traffic hours
• Monitor after deployment

---

Query Optimization

Index Strategies

Copy-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

Common N+1 Problem

Copy// Bad ❌ - N+1 queries
const users = await User.findAll();
for (const user of users) {
  user.posts = await Post.findAll({ where: { userId: user.id } });
}

// Good ✅ - Eager loading
const users = await User.findAll({
  include: [{ model: Post }]
});

---

ORM Best Practices

---

Backup & Recovery

Copy# PostgreSQL backup
pg_dump -U user -d database > backup.sql

# PostgreSQL restore
psql -U user -d database < backup.sql

# MySQL backup
mysqldump -u user -p database > backup.sql