database-management

majiayu000's avatarfrom majiayu000

Implement SQLite database patterns using the database.py interface with complete SQL isolation. MANDATORY for all database projects. Use when working with databases, data persistence, or SQLite.

⭐5stars🔀1forks📁View on GitHub🕐Updated Jan 11, 2026
[SQL and Databases]

When & Why to Use This Skill

This Claude skill provides a comprehensive framework for database management, specializing in SQLite patterns, SQL isolation, and schema design. It empowers developers to architect scalable data structures, optimize query performance, and manage complex migrations using industry-standard best practices for data persistence and ORM integration.

Use Cases

  • Architecting normalized database schemas (1NF to 3NF) to ensure data integrity and minimize redundancy in new applications.
  • Executing safe database migrations using modern tools like Prisma or Drizzle with built-in checklists for staging and production.
  • Optimizing application performance by identifying and fixing N+1 query problems and implementing strategic indexing.
  • Implementing secure SQLite data persistence layers with complete SQL isolation for local or edge computing projects.
  • Establishing robust backup and recovery workflows for PostgreSQL and MySQL to ensure business continuity and data safety.
namedatabase-management
descriptionDatabase 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

Level Description When to Use
1NF No repeating groups Always
2NF No partial dependencies Transactional data
3NF No transitive dependencies Most applications
Denormalized Redundant data Read-heavy workloads

Common Patterns

-- 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

# Create migration
npx prisma migrate dev --name add_users_table

# Apply to production
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

Drizzle

# 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

-- 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

// 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

Practice Description
Use Transactions Wrap related operations
Connection Pooling Reuse connections
Soft Deletes Use deleted_at instead of DELETE
Audit Fields Always add created_at, updated_at
Use Migrations Never modify schema manually

Backup & Recovery

# 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