Prisma relational

Prisma (Relational)

Purpose

Express the approved relational schema through Prisma and use its client well: schema-as-source, generated types, disciplined queries, and a clear line where raw SQL takes over. The schema design comes from relational-schema-design; this skill is its Prisma expression.

When to Use

• After Prisma + a relational DB were approved (database-selection).
• Not for schema design decisions (upstream) or Drizzle/Mongoose projects.

Inputs

• Approved schema design (entities, constraints, types, relations).
• Migration/environment workflow expectations (database-migrations).

Discovery Questions

• Does the schema use features Prisma models natively (enums, composite uniques, referential actions) vs ones needing raw SQL in migrations (partial indexes, CHECK constraints, triggers)?
• Where do generated types flow (service layer boundaries — ../../backend/backend-api-architecture says entities ≠ DTOs)?
• What query hot paths exist (drives select/include discipline)?

Responsibilities

• Express the design in schema.prisma: types, relations with explicit referential actions (onDelete), @@unique/@@index, enums — mapping every constraint the design demands; what Prisma's DSL can't express (CHECKs, partial indexes) goes into migration SQL deliberately, not dropped.
• Own the migration workflow with database-migrations: migrate dev in development, generated SQL reviewed like code, migrate deploy in environments; no db push beyond throwaway prototyping; drift detection in CI.
• Set client usage patterns:
  • select/include scoped to need — no default full-entity fetches leaking through APIs (../../backend/rest-api-design payload discipline);
  • relation loading strategy per hot path — avoid N+1 (batched include, or restructured queries);
  • $transaction for multi-write invariants (transactions owns the boundaries);
  • pagination via cursor patterns on stable keys;
  • singleton client with pool sizing matched to deployment (database-performance).
• Define the raw-SQL escape hatch policy: $queryRaw (parameterized only — database-security) for reporting/bulk/window queries where the client abstraction fights; typed and reviewed.
• Keep generated client/types out of domain contracts: repository/service boundary maps Prisma models → domain/DTO shapes.

Required Workflow

1. Translate the approved design into schema.prisma; list what needs raw-SQL migration additions.
2. Wire the migration workflow (dev/deploy/CI drift check).
3. Define client patterns (select scope, N+1 strategy, transactions, pagination).
4. Set the raw-SQL policy and pool config.
5. Verify constraints landed in SQL (inspect generated migrations) — not just in the DSL.

Decision Rules

• The generated migration SQL is the truth — review it; the DSL is shorthand.
• Any constraint Prisma can't express still ships (raw SQL in the migration) — the design isn't negotiable downward to the tool.
• db push never touches shared environments.
• N+1s hide behind lazy convenience: hot paths get explicit query shape review (database-performance).

Rules

• All raw queries parameterized; string interpolation into SQL is a defect.
• Migration files are immutable once applied beyond dev (database-migrations).
• Client version and engine pinned; upgrades follow ../../dependency-audit.

Anti-Patterns

• Designing the schema in Prisma DSL ad hoc, skipping the design skill.
• Dropping CHECK/partial-index requirements because the DSL lacks them.
• findMany with no select, no take — full rows, unbounded.
• Prisma models serialized straight out of controllers.
• Per-request new PrismaClient() exhausting connections.

Validation Checklist

• Design fully expressed (DSL + raw-SQL supplements listed).
• Migration workflow wired; generated SQL reviewed; CI drift check.
• Client patterns set: select scope, N+1 plan, transactions, pagination.
• Raw-SQL policy (parameterized, reviewed) recorded.
• Pool sizing matched to deployment.
• Models mapped to DTOs at the boundary.

Definition of Done

The approved schema fully expressed through Prisma (with deliberate raw-SQL supplements), a reviewed migration workflow, recorded client usage patterns that prevent N+1/overfetch/connection exhaustion, and a parameterized-only raw-SQL policy.

Related Skills

database-selection, relational-schema-design, database-migrations, transactions, indexing, database-performance, database-security, seed-data, ../../backend/backend-api-architecture.

Related Knowledge

../../../knowledge/ (hot paths, environment workflow).

Related References

../../../references/database/prisma/ (patterns, when populated).

Context Loading Guidance

• Requires: approved schema design, migration workflow expectations, hot paths.
• Does not require: re-deciding schema/database choices, app feature code.
• May load: database-migrations (workflow), transactions (boundaries).
• Stop when: schema expression + workflow + client patterns are recorded.

Token Efficiency Guidance

Work from the schema-design table; don't paste the whole schema.prisma into discussion — reference models by name and show only contested mappings.