| name | database-query-safety |
| description | Use when the user writes raw SQL, Drizzle/Prisma/Kysely queries, ORM `findMany` / `findUnique` calls with user input, or anything constructing a query from request data. Covers parameterization, N+1 detection, missing indexes, SELECT *, query plans, and unbounded result sets. |
Database Query Safety
Two questions before any new query ships: "Can a user smuggle SQL into this?" and "What does this look like at 100x the current row count?"
When to use
- Writing raw SQL,
db.$queryRaw, sql\`` template literals
- Writing ORM queries (Drizzle / Prisma / Kysely / TypeORM) that include user input
- Adding a list/feed/search endpoint
- Touching a query inside a hot loop
- Reviewing a query before launch or under a perf incident
When to skip
- Reading a static fixture or seed
- One-off scripts run by you against a dev DB
The rules
1. Parameterize. Never concatenate user input into SQL.
db.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`)
db.$queryRaw`SELECT * FROM users WHERE email = ${email}`
db.select().from(users).where(eq(users.email, email))
db.selectFrom('users').where('email', '=', email).selectAll().execute()
ORMs parameterize by default. The unsafe escape hatches ($queryRawUnsafe, sql.raw, string concat) are where injection lives. If you need dynamic identifiers (table/column names from input), allowlist them — never interpolate.
2. Solve N+1 before it ships, not after.
const posts = await db.post.findMany()
for (const post of posts) {
post.author = await db.user.findUnique({ where: { id: post.authorId } })
}
const posts = await db.post.findMany({ include: { author: true } })
In Drizzle:
db.query.posts.findMany({ with: { author: true } })
How to spot: any DB call inside a for / map / forEach over rows you just fetched. Catch it locally with a query log (PRISMA_LOG_LEVEL=query, Drizzle's logger: true); catch it in CI by snapshotting query counts in tests for hot paths.
3. Always cap LIMIT. Always.
db.message.findMany({ where: { channelId } })
db.message.findMany({ where: { channelId }, take: 100, orderBy: { createdAt: 'desc' } })
Every list endpoint has a max page size (e.g. 100). Even "internal" queries should have a LIMIT — internal data grows. See api-route-hardening for cursor pagination.
4. Index every column you filter or sort on.
The query is fine when there are 1k rows. It's unusable at 1M. Indexes fix this; they're cheap to add, painful to add late under load.
CREATE INDEX idx_message_user_created ON message (userId, createdAt DESC);
Read the query plan for any non-trivial query: EXPLAIN ANALYZE. If you see Seq Scan on a table over a few thousand rows, you need an index.
Composite index order matters: put the equality filter first, the sort/range filter second.
5. Don't SELECT * over wide rows in hot paths.
If your users table has a 50KB metadata JSON column and you findMany for a user list, every row drags 50KB across the wire. Project only the columns you need.
db.user.findMany({ select: { id: true, name: true, avatarUrl: true } })
db.select({ id: users.id, name: users.name }).from(users)
6. Soft-deletes need partial indexes.
If you "soft-delete" with deletedAt IS NOT NULL, every index needs to either include deletedAt or be partial:
CREATE INDEX idx_user_email_active ON users(email) WHERE deletedAt IS NULL;
Otherwise lookups slow down forever as deleted rows accumulate.
7. Beware the silent full-table scan.
These all cause it:
- Filtering on a function:
WHERE LOWER(email) = ? (without a functional index)
- Leading wildcard
LIKE '%foo'
- Implicit type coercion:
WHERE id = '123' when id is bigint
OR across columns without each branch being indexed
For text search at scale, use full-text search (Postgres tsvector + GIN) or a search index (Meilisearch, Typesense, OpenSearch). Don't LIKE '%foo%' on a 10M-row table.
8. Connection pool: small and serverful.
Serverless functions can each open a connection. 100 concurrent invocations × 5 connections each = 500 — your Postgres dies at 100. Use a pooler (PgBouncer, Supabase pooler, Prisma Accelerate, Neon's serverless driver) and keep the per-instance pool tiny (connection_limit=1 for serverless).
9. Long queries hold locks. Watch what blocks what.
Updates and deletes take row locks; ALTER TABLE takes table locks (see migrations-and-schema). A "harmless" report query at peak traffic can pile up behind a lock. Set a statement_timeout per role:
ALTER ROLE app SET statement_timeout = '5s';
ALTER ROLE analytics SET statement_timeout = '60s';
Slow query gets killed instead of holding the system hostage.
10. Aggregate with the database, not in the app.
const orders = await db.order.findMany({ where: { userId } })
const total = orders.reduce((s, o) => s + o.amountCents, 0)
const { _sum } = await db.order.aggregate({
where: { userId },
_sum: { amountCents: true },
})
War story
A "vibe-coded" project-tracker shipped a /api/dashboard endpoint that listed all of a user's tasks, their assignees, their projects, and each project's owner — four findMany calls in nested loops. Worked great with seed data. First customer with 800 tasks across 30 projects: dashboard took 14 seconds, hit the platform timeout, and 502'd. Fix was three lines of include. A query log on day one would have caught it before launch.
Quick checklist
