database-prisma

Database Prisma

The schema is the contract. Migrations are the history. Queries must be predictable under load.

Modeling

• Model entities and relationships in schema.prisma with clear names.
• Use @id, @unique, @relation and onDelete consciously (Cascade only when correct).
• Prefer Prisma enums for closed sets.
• Timestamps: createdAt / updatedAt by default on mutable entities.
• Normalize enough; denormalize only with a measured read reason.

Migrations

• Always generate a migration (prisma migrate) instead of just db push in production.
• Review the generated SQL before applying.
• Migrations must be forward-safe; avoid destructive rewrites without a data plan.
• On deploy: migrate → release app (or the project's documented strategy).

Efficient queries

• Select only the necessary fields (select / include lean).
• Avoid N+1: use include/select appropriately or batch queries.
• Pagination with cursor or limited skip/take.
• Indexes for frequent filters/sorting (@@index, composite unique).
• Transactions ($transaction) for atomic multi-table writes.

Integration

• One PrismaClient per process (singleton in dev hot-reload).
• Do not expose the client directly to the UI; encapsulate it in repositories/services.
• Validate input before building where/data.
• Handle Prisma errors (P2002 unique, P2025 not found) as domain.

Soft delete and multi-tenant

• Soft delete: consistent filter across all reads.
• Multi-tenant: always scope by tenantId/orgId in queries; never trust only the client.

Anti-patterns

• findMany without a limit
• include deep “just in case”
• Business logic scattered in middleware callbacks without clarity
• Changing production with db push
• Missing indexes on filtered foreign keys

Completion criteria

• Coherent schema and relations
• Reviewed migration
• Queries without obvious N+1
• Indexes for hot paths
• DB errors mapped to the API/domain