Serene

Raw SQL wasn't the problem. It was just too raw.

Serene is a lightweight review tool for teams that want to keep raw SQL and native database drivers.

SQL injection is not a reason to avoid raw SQL. SQLi prevention does not require an ORM: fixed SQL with parameterized runtime values provides the same fundamental protection as parameterized ORM queries.

The problem is that raw SQL code does not always make that distinction obvious. Some paths are fixed and safely parameterized; others are dynamic, unresolved, or deserve closer inspection.

Serene makes the boundary explicit. Recognized construction can skip repeated construction-specific investigation, while raw or unresolved paths remain visible for additional scrutiny.

Serene is not an ORM, query builder, mapper, driver wrapper, or SQL parser.

Install

Current release: 0.7.0 (v0.7.0). Serene is not published to the npm registry; install the tagged GitHub release directly:

npm install github:mk3008/serene#v0.7.0

Use github:mk3008/serene only when you intentionally want the latest main instead of a pinned release.

Node.js 22+. The runtime has zero dependencies. Full documentation stays in the tagged GitHub repository rather than being copied into node_modules.

Quick start

Write normal SQL, bind values separately, and keep using your driver.

import { sql, bind } from '@mk3008/serene';

const findUser = sql`
  SELECT id, name
  FROM users
  WHERE id = :id
`;

const query = bind(findUser, { id }, 'indexed');
await pool.query(query.text, query.values);

sql accepts fixed template literals only. Runtime values stay outside the SQL text.

If your driver already supports named parameters, keep its native SQL unchanged:

const findUser = sql`
  SELECT [id], [name]
  FROM [users]
  WHERE id = @id
`;

const query = bind(findUser, { id });

query.names.forEach((name, i) => request.input(name, query.values[i]));
await request.query(query.text);

No Serene-specific parameter dialect is required when the driver already has one.

For drivers that use ? placeholders, use the same named authoring style and bind with anonymous:

const findUser = sql`SELECT id, name FROM users WHERE id = :id`;
const query = bind(findUser, { id }, 'anonymous');

Externally stored SQL

External SQL can reuse binding mechanics while remaining explicitly review-required:

import { externalSql, bindExternal, review } from '@mk3008/serene';

const statement = externalSql(storedSqlText);
const query = bindExternal(statement, { id }, 'indexed');
review(statement); // { level: 'review-required', code: 'EXTERNAL_SQL' }
review(query);     // { level: 'review-required', code: 'EXTERNAL_SQL' }
await pool.query(query.text, query.values);

ExternalSql has a separate identity from Sql. It cannot be used with bind, orderBy or materializeTemp; bindExternal shares bind's parameter validation, passthrough/positional modes, scanner conventions and snapshots. Applications own SQL trust and review, including any deployment hash/revision checks. Successful binding never approves the SQL. Source audit retains this boundary, including in --actionable-only output, and --strict fails on it. See the external SQL contract.

What Serene adds

• A visible SQL boundary — fixed SQL is created from a literal sql template.
• Value separation — bound values are never rendered into SQL text.
• Native driver usage — Serene does not own connections, execution, transactions, or mapping.
• Review triage — recognized construction can be treated as ordinary; unresolved or dynamic construction stays visible for additional review.
• Controlled sorting — runtime input can select from finite, source-defined ORDER BY choices without accepting arbitrary SQL fragments.
• Bounded TEMP materialization — PostgreSQL can wrap an existing Serene Sql in a fixed TEMP CTAS operation without opening a general SQL-fragment API.

Serene does not prove that a query is correct, authorized, fast, or free of every SQL vulnerability. Its job is narrower: make SQL construction easier to classify during review.

Using Serene with ORMs

Serene is native SQL / native driver first, but it can coexist with an ORM when the ORM provides a transparent parameterized raw-SQL path that preserves the SQL text and bound values.

Use the ORM where it helps, and Serene for raw SQL. Serene does not adapt SQL to ORM-specific query languages or formatting rules.

Verified coexistence paths include Kysely, TypeORM, and Prisma 7. See the ORM coexistence research for details and limitations.

Audit SQL paths

Run the audit on your source directory:

npx --no-install serene-audit src

Repository-wide:

npx --no-install serene-audit .

Show only paths that need additional review:

npx --no-install serene-audit --actionable-only src

If the project does not already use a compatible TypeScript version:

npm install --save-dev typescript@^5.9.3

Level	Meaning
ordinary	Recognized Serene construction
review-required	Raw or unresolved SQL path; inspect it
violation	A detected Serene boundary violation

Independent content review

Construction level and SQL-content review are separate axes. Recognized ordinary SQL can still carry reviewSignals.

Review area	Examples
Procedural logic (elevated)	DO blocks, routine/trigger definitions, explicit CALL / EXEC / EXECUTE
Persistent definitions (elevated)	Recognized CREATE / ALTER of tables, views, functions, procedures and triggers
Broad/destructive	DROP, TRUNCATE, RENAME, SELECT / UPDATE / DELETE without an apparent WHERE
Operational/unclear	temporary tables, data-modifying CTEs, CTE bodies outside the lightweight recognizer

Signals keep SQL visible for review without changing construction level or causing --strict to fail. Persistent-DDL, procedural-container and explicit-execution candidates carry priority: "elevated"; other signals, including TEMP creation and bounded ALTER TABLE pg_temp.<name>, remain advisory. See SQL-content review for details.

The audit is conservative and file-local. See audit coverage for details.

Use the audit with AI agents

Copy this into the repository's root AGENTS.md (or equivalent instruction file):

Use `@mk3008/serene` as the default construction path for executable raw SQL; if
it cannot preserve needed SQL behavior, keep the exception explicit for additional
review. For SQL-construction review, use the installed `serene-audit` and keep
unresolved paths in review. Use ordinary results to skip redundant
construction-provenance review, not SQL meaning/binding, authorization, or
business-behavior checks.

Then keep individual prompts focused on the actual task; they do not need to mention Serene each time. See the AI adoption guide for details and evidence.

Filter source before AI delivery

Serene does not install itself into an AI agent. Call the filter in the host/tool layer before returning source-bearing results to the model:

import { filterConstructionSource, filterConstructionDiff } from '@mk3008/serene/filter';

const modelRead = filterConstructionSource(pinnedSource, {
  ...currentSource,
  ranges: readRanges,
});

const modelDiff = filterConstructionDiff(pinnedPair, {
  ...currentPair,
  changes: pairedEdits,
});

For PR or commit review, the host can start from a normal Git diff:

git diff --unified=0 BASE...HEAD

Convert those diff hunks into pairedEdits, then pass them to filterConstructionDiff. Serene deliberately does not parse or run git diff itself.

Send modelRead / modelDiff to the AI instead of the unfiltered source response. See the source filter and diff filter for the host contract and limits.

Dynamic ORDER BY without dynamic SQL

import { sql, sort, orderBy, bind } from '@mk3008/serene';

const base = sql`SELECT id, name, created_at FROM users`;

const query = orderBy(base, {
  name: sort`name ASC`,
  newest: sort`created_at DESC`,
}, input.sort);

const bound = bind(query);

Runtime input chooses a reviewed key, not a SQL fragment.

PostgreSQL TEMP materialization

Reuse a Serene SQL body in a fixed CREATE TEMPORARY TABLE ... ON COMMIT DROP AS wrapper, then bind values normally:

import { sql, materializeTemp, bind } from '@mk3008/serene';

const source = sql`SELECT id FROM users WHERE tenant_id = :tenantId`;
const snapshot = materializeTemp(source, 'user_snapshot');
const query = bind(snapshot, { tenantId }, 'indexed');
// Inside an explicit transaction on this same checked-out connection:
await client.query(query.text, query.values);
// Consume pg_temp.user_snapshot before committing or rolling back.

The name must be a literal at the call site for ordinary source classification. Only a single ASCII name of 1–63 characters is accepted; Serene quotes it and preserves case. Schema paths and arbitrary fragments are unsupported. The body must be an unbound Serene Sql without statement terminators outside recognized quotes/comments, including a trailing semicolon. SQL validity and CTAS suitability remain review responsibilities. This is a PostgreSQL contract, not a cross-database TEMP abstraction. TEMP content suggestions remain visible even for ordinary construction. See the security contract.

Optional search conditions without dynamic SQL

Optional filters do not necessarily require building SQL strings. When the set of filters is known, normal fixed SQL can express any combination:

const searchUsers = sql`
  SELECT id, name, status, created_at
  FROM users
  WHERE (:status IS NULL OR status = :status)
    AND (:createdFrom IS NULL OR created_at >= :createdFrom)
    AND (:createdTo IS NULL OR created_at < :createdTo)
`;

const query = bind(searchUsers, {
  status,
  createdFrom,
  createdTo,
}, 'indexed');

Bind null for filters that are not used. The SQL text stays fixed and the values stay bound; no Serene-specific optional-filter feature is required.

If users can change the query structure itself—for example, adding arbitrary joins or grouping—that is a different problem and may still require dynamic SQL and additional review.

Documentation

• Design — goals, boundaries, and why Serene stays small.
• Security contract — guarantees, non-guarantees, and review responsibilities.
• Review coverage — what source inspection detects, refers, or may miss.
• Binding verification — detailed parameter behavior and regression evidence.
• Pre-exposure filter — host API for filtering construction-only source before AI delivery.
• Diff filter — paired base/head filtering for PR and commit changes.
• Evaluation map — current research questions, AI-review evidence, and limits.
• AI adoption guide — optional repository policy for installed Serene and AI review.

Development

npm ci
npm run check
npm pack

License: MIT.