modern-database-orchestration
Type-safe ORM patterns (Prisma, Drizzle), migration safety protocols, schema evolution workflows, and seeding strategies. Use when designing schemas, writing migrations, or integrating ORMs into a pro
SKILL.mdskills/modern-database-orchestration/SKILL.md
GitHubModern Database Orchestration
Purpose
Provide specialized, ORM-aware database engineering patterns that go beyond the raw SQL standards in rules/database.md. This skill bridges the gap between schema design theory and the practical reality of type-safe ORMs, migration pipelines, and production data evolution.
When to Use This Skill
Use when:
- Designing or modifying a database schema using Prisma, Drizzle, or TypeORM
- Writing or reviewing migration files
- Setting up database seeding for development or testing
- Debugging ORM query generation or performance issues
- Planning a schema evolution that requires zero-downtime deployment
Do NOT use when:
- Writing raw SQL queries without an ORM (use
rules/database.mddirectly) - The task is purely frontend with no data layer involvement
Phase 1: ORM Selection & Setup
Prisma (Recommended Default)
Best for: Greenfield projects, teams wanting strong type safety with minimal SQL knowledge.
npx prisma init # scaffolds schema.prisma + .env
npx prisma db push # rapid prototyping (no migration file)
npx prisma migrate dev # production-path migration
npx prisma generate # regenerate client after schema changes
Drizzle (Alternative)
Best for: Teams wanting SQL-like syntax with full type inference and no code generation step.
npx drizzle-kit generate # generate migration SQL from schema
npx drizzle-kit migrate # apply migrations
npx drizzle-kit push # push schema directly (prototyping)
Selection Criteria
| Factor | Prisma | Drizzle |
|---|---|---|
| Type safety | ✅ Generated types | ✅ Inferred types |
| Raw SQL access | Limited (via $queryRaw) |
Native SQL-like API |
| Migration tooling | Built-in, opinionated | Flexible, SQL-native |
| Learning curve | Low | Medium |
| Bundle size | Larger (engine binary) | Minimal |
| Edge runtime | Requires adapter | Native support |
Phase 2: Schema Design Workflow
Schema-First Development
- Define the domain model in the ORM schema file before writing any application code
- Run type generation to get compile-time safety across the entire data layer
- Write the data access layer against the generated types
- Never access the database directly from routes or controllers — always go through a repository/service layer
Prisma Schema Patterns
model User {
id String @id @default(cuid())
email String @unique
name String?
role Role @default(USER)
posts Post[]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([email])
@@map("users")
}
enum Role {
USER
ADMIN
}
Drizzle Schema Patterns
export const users = pgTable('users', {
id: text('id').primaryKey().$defaultFn(() => createId()),
email: text('email').notNull().unique(),
name: text('name'),
role: text('role', { enum: ['USER', 'ADMIN'] }).default('USER').notNull(),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});
Phase 3: Migration Safety Protocol
Development Migrations
- Always use
migrate dev(Prisma) ordrizzle-kit generate+drizzle-kit migrate(Drizzle) - Review the generated SQL before applying — never blindly accept
- Name migrations descriptively:
add_user_role_column, notmigration_001
Production Migration Rules
- Never run
migrate resetordb pushagainst production — these are Tier 4 blocked commands - Additive-only migrations: Add columns as nullable or with defaults. Never add a
NOT NULLcolumn without a default in a single step - Two-phase column removal:
- Phase 1: Deploy code that stops reading/writing the column
- Phase 2: Drop the column in a subsequent migration
- Two-phase rename:
- Phase 1: Add new column, backfill data, deploy code using new column
- Phase 2: Drop old column
- Index creation: Always use
CREATE INDEX CONCURRENTLYon large production tables (Postgres) - Test migrations: Run against a clone of production data before deploying
Migration Rollback Strategy
- Every migration should have a documented rollback path
- For Prisma: maintain manual
down.sqlfiles for critical migrations - For Drizzle: generate reverse SQL alongside forward migrations
- Never rely on ORM auto-rollback in production
Phase 4: Seeding & Test Data
Seed Architecture
// prisma/seed.ts or drizzle/seed.ts
async function seed() {
// 1. Clear existing data (dev only, NEVER in production)
await db.delete(posts);
await db.delete(users);
// 2. Create base entities
const admin = await db.insert(users).values({
email: 'admin@example.com',
role: 'ADMIN',
}).returning();
// 3. Create dependent entities
await db.insert(posts).values([
{ title: 'First Post', authorId: admin[0].id },
]);
}
Seeding Rules
- Seeds must be idempotent — running twice should not create duplicates
- Use fixed IDs for reference data (roles, categories) to enable stable foreign keys in tests
- Never seed with real user data or PII
- Separate reference data seeds (always needed) from sample data seeds (dev only)
Phase 5: Query Optimization
ORM-Specific Pitfalls
- N+1 queries: Always use
include(Prisma) orwith(Drizzle) for related data - Select only what you need: Use
selectto limit returned fields on large tables - Pagination: Always use cursor-based pagination for large datasets, not offset-based
- Transaction boundaries: Wrap multi-table mutations in explicit transactions
- Connection pooling: Use PgBouncer or built-in pooling for serverless environments
Debugging Queries
- Prisma: Enable query logging with
log: ['query']in the client constructor - Drizzle: Use
.toSQL()to inspect generated queries before execution - Always check
EXPLAIN ANALYZEfor slow queries
Output Format / Delivery
- Schema definitions and migration files
- Data access layer implementations
- Seed scripts
- Query optimization recommendations with before/after
EXPLAINoutput
Behavior Rules
- Schema is the source of truth. Application code conforms to the schema, not the other way around.
- Generated types are sacred. Never use
anyor type assertions to bypass ORM-generated types. - Migrations are immutable. Once a migration is committed, it is never modified — create a new migration instead.
- Connection strings are secrets. Never log, commit, or hardcode database URLs.
Safety Guardrails
- ORM Hallucination Patch (inherited from backend-dev-guidelines): Cross-reference every ORM method against the actual ORM documentation. Do not invent API methods.
- Destructive Migration Patch: Any migration containing
DROP,DELETE,TRUNCATE, orALTER COLUMN ... TYPEmust be flagged with an explicit warning before generation. - Raw Query Injection Patch: When using
$queryRaworsql.raw(), always use parameterized queries. Never interpolate user input into SQL strings. - Self-Rejection Clause: If the active project's ORM version or schema cannot be verified, ABORT OUTPUT and emit: "REJECTED: Cannot verify ORM schema — load the schema file before generating queries."
Maintenance Notes
- Created 2026-04-30 as part of the Engineering Rigor gap analysis.
- Complements
rules/database.md(raw SQL standards) with ORM-specific operational patterns. - Complements
backend-dev-guidelines(general backend) with specialized data-layer depth.