Design Database Auto

Design database schemas with guided selection of database engine, ORM framework, normalization strategy, indexing plan, and optimization techniques based on your project context.

When to Use This Command

Run this command when...

• You are starting a new project and need to choose between PostgreSQL, SQLite, Neon, or Turso for your database
• You need to design a normalized schema with proper primary keys, relationships, and constraints
• You want guidance on selecting an ORM (Drizzle, Prisma, or Kysely) based on your project requirements
• You need to plan indexing strategies for query performance including composite and partial indexes
• You want to avoid common anti-patterns like unnecessary JSON columns, missing indexes, and N+1 queries

Quick Start

Copy
# .claude/commands/design-database-auto.yaml
name: Design Database Auto
description: Guided database design with engine, ORM, and schema selection
inputs:
  - name: context
    description: "Project description and requirements"
  - name: preference
    description: "Database preference if any"
    default: ""

Copy
# Design database for a new SaaS application
claude "design-database-auto 'multi-tenant SaaS with user teams and billing'"

# Design with a specific database preference
claude "design-database-auto 'blog platform' --preference sqlite"

Output:
  [analyze] Project type: Multi-tenant SaaS
  [recommend] Database: PostgreSQL (multi-tenant, relational data)
  [recommend] ORM: Drizzle (type-safe, lightweight)
  [schema] Designed 8 tables with relationships:
    users, teams, team_members, subscriptions,
    invoices, permissions, audit_log, settings
  [index] 12 indexes recommended (3 composite)
  [review] Anti-pattern check: passed
  Done. Schema design saved to schema-design.md

Core Concepts

Concept	Description
Database Selection	Context-aware recommendation: PostgreSQL for complex queries, SQLite for simplicity, Neon/Turso for serverless
ORM Selection	Drizzle for type safety and performance, Prisma for developer experience, Kysely for SQL-first teams
Schema Normalization	Applies 3NF normalization while recognizing practical denormalization for read-heavy workloads
Index Planning	Recommends B-tree, hash, GIN, and composite indexes based on expected query patterns
Anti-Pattern Detection	Flags common mistakes: SELECT *, JSON overuse, missing foreign keys, N+1 query patterns

Decision Flow:
  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐
  │   Project    │───>│   Database   │───>│     ORM      │
  │   Context    │    │   Selection  │    │   Selection  │
  └──────────────┘    └──────────────┘    └──────┬───────┘
                                                 │
                                          ┌──────▼───────┐
                                          │    Schema    │
                                          │    Design    │
                                          └──────┬───────┘
                                                 │
                                   ┌─────────────┼──────────┐
                                   ▼             ▼          ▼
                              ┌────────┐   ┌─────────┐ ┌────────┐
                              │Indexes │   │ Review  │ │Optimize│
                              └────────┘   └─────────┘ └────────┘

Configuration

Parameter	Type	Default	Description
context	string	required	Project description, requirements, and expected data patterns
preference	string	""	Preferred database: postgres, sqlite, neon, turso (overrides recommendation)
orm	string	""	Preferred ORM: drizzle, prisma, kysely (overrides recommendation)
scale	string	"medium"	Expected scale: small (< 10K rows), medium, large (millions+), or massive
environment	string	"server"	Deployment: server, serverless, edge, or embedded

Best Practices

1. Start with context, not technology -- Describe your project requirements first and let the command recommend a database. Avoid defaulting to PostgreSQL when SQLite or Turso might be simpler and sufficient.
2. Plan indexes based on queries, not schema -- Write your most common queries first, then design indexes to support them. The command suggests indexes but you know your access patterns best.
3. Normalize first, denormalize deliberately -- Start with 3NF and only denormalize specific tables when you have measured performance bottlenecks that justify the data redundancy.
4. Use EXPLAIN ANALYZE on generated schemas -- After creating tables, run representative queries with EXPLAIN ANALYZE to verify that indexes are actually being used by the query planner.
5. Review the anti-pattern report -- The command flags common mistakes. Address each flagged item before proceeding with implementation, especially JSON columns and missing foreign keys.

Common Issues

1. Choosing the wrong database for serverless -- Traditional PostgreSQL connections exhaust pool limits in serverless environments. The command recommends Neon or Turso for serverless deployments where connection pooling is limited.
2. Over-indexing small tables -- Adding indexes to tables with fewer than 1,000 rows can slow writes without improving reads. Let the command's scale parameter guide index recommendations.
3. ORM lock-in concerns -- Choosing an ORM is a significant commitment. The command explains trade-offs but the final decision should involve the team, considering migration effort and learning curve.