Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 36 ยท Transactions and Raw SQL

TypedSQL and Raw SQL Safety

Write raw SQL in .sql files and let Prisma TypedSQL generate type-safe functions, plus a checklist for keeping hand-written SQL safe, tested and maintainable.

$queryRaw gives you freedom but little safety: the TypeScript type you attach is a guess, a renamed column breaks at runtime, and SQL buried in template strings gets no editor support. TypedSQL solves this by moving raw SQL into .sql files and generating typed functions from them. Prisma checks each file against your real database while generating, so mistakes show up at build time.

TypedSQL needs the Prisma CLI, a database and (when this was written) a preview feature flag, so everything here is read-only except for labelled stand-ins that mimic the generation step with plain Node. Check the Prisma docs for the current status of the feature in your version.

The workflow

  1. Enable the feature in the generator block:
PRISMA
generator client {
  provider        = "prisma-client"
  output          = "../src/generated"
  previewFeatures = ["typedSql"]
}
  1. Put SQL files in prisma/sql/. Each file is one query; the name becomes the function name:
SQL
-- prisma/sql/getActiveUsers.sql
-- @param {Int} $1:minPosts
SELECT u.id, u.email, COUNT(p.id) AS post_count
FROM "User" u
LEFT JOIN "Post" p ON p."authorId" = u.id
GROUP BY u.id, u.email
HAVING COUNT(p.id) >= $1
ORDER BY post_count DESC;

The -- @param comment names and types the parameter ($1 is the PostgreSQL placeholder; MySQL and SQLite use ?). Prisma can infer many types itself, and the comment is how you give a friendlier name or override.

  1. Generate:
Terminal
npx prisma generate --sql

Prisma sends each query to the database to learn its column names and types (it prepares the statement without running it) and writes TypeScript functions.

  1. Use them:
TypeScript
import { getActiveUsers } from './generated/sql';

const rows = await prisma.$queryRawTyped(getActiveUsers(2));
// rows: { id: number; email: string; post_count: bigint | null }[]

If the table changes so the query no longer works, prisma generate --sql fails and CI catches it before deployment. Aliases matter: AS post_count becomes the property name. Prisma also reports when two result columns have the same name or a column has no name.

flowchart LR
  A["prisma/sql/*.sql"] --> B["prisma generate --sql"]
  B --> C["prepare against database"]
  C --> D["typed functions"]
  D --> E["prisma.$queryRawTyped(fn(args))"]

The database itself is the type checker.

A stand-in for the generation step

The real generator asks the database about types. This stand-in parses the -- @param comment, prepares the statement on SQLite to prove it is valid, reads the result column names, and builds a function that checks argument counts. It is a teaching model, not Prisma.

Node.js Stand-in: generate a checked function from a .sql file

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The last line is the point of TypedSQL: a broken query is discovered during generation, not in production.

Limitations worth knowing

  • Needs a live database connection to generate.
  • Bound parameters only for values; identifiers still cannot be parameters.
  • Dynamic SQL (variable number of filters) does not fit; use Prisma.sql fragments with $queryRaw for that.
  • Result column types come from the database: COUNT is bigint, decimals are Decimal, and nullability is inferred conservatively.

A raw-SQL safety checklist

  1. Tagged templates, not strings. Never build SQL by concatenating request data. See raw queries.
  2. Allow-list identifiers. Sort columns and table names come from your own map.
  3. Cap and paginate. Raw reports can return millions of rows; always add LIMIT.
  4. Least privilege. The app's database user should not be able to DROP TABLE. Use a separate role for migrations.
  5. Test the SQL. Raw SQL bypasses Prisma's type safety, so cover it with integration tests against a real database (see testing Prisma code).
  6. Know the cost. Check slow queries with EXPLAIN (indexes and query plans).
  7. Handle types. Convert bigint, Decimal and dates before sending JSON.
Node.js Stand-in: handle bigint before JSON

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where do TypedSQL queries live?

    Each .sql file in prisma/sql becomes a generated function named after the file.
  2. 2. When does TypedSQL tell you a query is invalid?

    Generation asks the database to prepare each statement, so syntax errors and missing columns fail the build.
  3. 3. Which is NOT a good use of TypedSQL?

    TypedSQL files are static. For queries assembled from optional parts, use Prisma.sql fragments with $queryRaw.
  4. 4. Why convert bigint results before sending them as JSON?

    JSON.stringify cannot serialize BigInt; convert it to a number or string first.

Summary

  • TypedSQL turns .sql files in prisma/sql into typed functions via prisma generate --sql.
  • The database validates each query at generation time, so refactors fail early.
  • Use $queryRawTyped to run them; keep dynamic queries on $queryRaw with Prisma.sql.
  • Follow a checklist: bound values, allow-listed identifiers, limits, least privilege, tests and type conversion.
  • The generator here is shown as read-only Prisma commands plus a Node stand-in.

Next: Solving the N+1 Query Problem