7 Practical Techniques for Database Design with Claude Code

Database design involves table structures, relations, indexes, and migrations. With Claude Code, you can go from design brainstorming to implementation in one flow.

1. Generate Table Design from Requirements

Describe requirements in plain language and get a schema back.

claude -p "
Design a database for an e-commerce site.

Requirements:
- Users (email, password hash, profile)
- Products (name, description, price, stock, category)
- Orders (link users to products, quantity, status)
- Reviews (users post reviews on products)
- Categories (hierarchical)

Output as a Prisma schema with appropriate indexes.
"

A normalized schema returns in seconds.

2. Analyze Existing Database Schemas

Understand legacy database structures.

npx prisma db pull
claude -p "
Read prisma/schema.prisma and explain:
- Main tables and their roles
- Relationships between tables
- Design issues (normalization gaps, missing indexes)
- Improvement suggestions
"

3. Auto-Generate Migrations

Let Claude Code handle migration files for schema changes.

claude -p "
Add these columns to the users table:
- phone_number (string, nullable, unique)
- avatar_url (string, nullable)
- last_login_at (datetime, nullable)

Update the Prisma schema and run npx prisma migrate dev.
Migration name: add-user-profile-fields
"

Schema change to migration execution, end to end. See DB Migration Automation.

4. Optimize Indexes

Identify slow queries and get index recommendations.

claude -p "
Read prisma/schema.prisma and optimize indexes:
1. Columns frequently used in WHERE clauses
2. Columns used in JOIN conditions
3. Composite indexes for ORDER BY columns
4. Unused indexes to remove

Output the improved schema.
"

See SQL Query Optimization.

5. Auto-Generate Seed Data

Create realistic test data for development.

claude -p "
Create prisma/seed.ts with:
- 10 users with realistic names and emails
- 5 categories (Electronics, Books, Food, Clothing, Sports)
- 10 products per category with realistic names and prices
- 3-5 orders per user
- 1-3 reviews per product

Use faker.js for random generation.
Also configure prisma.seed in package.json.
"

6. Generate Query Helpers

Auto-generate repository pattern helpers.

claude -p "
Create repository classes in src/repositories/:

ProductRepository:
- findAll(filters, pagination, sort)
- findById(id) - include related category and reviews
- search(keyword) - full-text search
- findByCategory(categoryId, pagination)
- getPopular(limit) - sorted by review count

Use Prisma. Avoid N+1 queries.
"

7. Auto-Generate ER Diagrams

Generate text-based ER diagrams.

claude -p "
Read prisma/schema.prisma and generate a Mermaid ER diagram.
Show relationships (1:N, N:N) with arrows.
"

Output example:

erDiagram
    User ||--o{ Order : places
    User ||--o{ Review : writes
    Order ||--|{ OrderItem : contains
    Product ||--o{ OrderItem : "ordered in"
    Product ||--o{ Review : receives
    Category ||--o{ Product : categorizes

Gotchas

Don’t Over-Normalize

Claude Code tends to normalize aggressively. In practice, intentional denormalization for performance is common. Note it in CLAUDE.md.

Always Review Migrations

Review auto-generated migrations before running. Especially changes that delete data.

Never Connect to Production

When passing DATABASE_URL, use local or staging. Add “never connect to production DB” to CLAUDE.md.

Conclusion

• Auto-generate table designs from requirements
• Analyze and improve legacy DB structures
• Automate migrations end to end
• Get index optimization recommendations
• Auto-generate realistic seed data
• Generate repository pattern query helpers
• Output ER diagrams in Mermaid format

Claude Code as your DB design partner boosts both design quality and dev speed. See Prisma ORM Guide. Official docs at Anthropic Claude Code.