# Database SQL

> SQL database standards using Prisma + PostgreSQL — schema design, migrations, query patterns, transactions, indexing, what to avoid. Load when writing or reviewing any database schema, Prisma queries, or migration files. Use when this capability is needed.

- Skill: `tomevault-io/database-sql-2` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/database-sql-2`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/database-sql-2/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/database-sql-2

---


Full standards in [sql.md](sql.md). Always-on summary:

**Stack:** PostgreSQL + Prisma ORM

**Schema rules:**
- Every table has `id` (cuid/uuid), `createdAt`, `updatedAt`
- Soft deletes with `deletedAt DateTime?` — never hard delete user data
- Foreign keys always explicit with `onDelete` behaviour defined
- snake_case for column names (`@@map`), PascalCase for Prisma models

**Query rules:**
- Never raw SQL unless Prisma cannot express it — use `$queryRaw` with tagged templates only
- Always select only needed fields — `findMany({ select: { id: true, name: true } })` — never `findMany(` without `select:` on large tables
- N+1 is never acceptable — use `include` or `select` with nested relations
- Wrap multi-step operations in `prisma.$transaction()`

**Safe migrations — expand-contract pattern** (required for any rename, removal, or type change on a live table):
1. **Expand** — add new column (nullable), dual-write in app, deploy
2. **Migrate** — backfill existing rows in batches (≤1000 rows/query), add NOT NULL with `NOT VALID` + `VALIDATE` to avoid table lock
3. **Contract** — remove old column after confirming zero references in running code

```bash
# Never drop a column without deploying the app change first
# Always diff before applying to production
npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma --script
grep -E 'DROP COLUMN|NOT NULL|ALTER TYPE' prisma/migrations/*/migration.sql   # flag for review
```

**Never:**
- Store passwords in plaintext (use bcrypt/argon2)
- Store tokens or secrets in the database without hashing
- Delete rows that have audit/compliance value — use soft deletes
- Run migrations in application startup code
- Use `deleteMany` without a `where` clause
- Apply a breaking schema change (rename/drop/type change) in a single deploy — always use expand-contract


**Related skills — apply together:**
- `error-handling` — Prisma P2002/P2025 map to ConflictError/NotFoundError in errorHandler
- `typescript-patterns` — type repository return values and Zod-inferred input types
- `api-conventions` — cursor pagination contract used in repositories matches the API response shape
- `security` — never raw SQL with string interpolation; always parameterized via Prisma

---
> Source: [manikumarkv/devrunway-claude-plugin](https://github.com/manikumarkv/devrunway-claude-plugin) — distributed by [TomeVault](https://tomevault.io).
<!-- tomevault:4.0:skill_md:2026-05-22 -->