SQL Helper

The @alchemify/sql package provides a tagged template helper for building parameterized SQL queries. It ensures values are always passed as $N placeholders, preventing SQL injection in frontend code that talks to the thin proxy.

Installation

Already included in the monorepo as packages/sql/. Import from @alchemify/sql:

import { sql, joinSQL, raw } from "@alchemify/sql";

API

sql (tagged template)

Builds a parameterized query. Plain values become $N placeholders. SQL fragments (from sql, joinSQL, or raw) are inlined with placeholders renumbered.

const userId = "abc-123";
const q = sql`SELECT * FROM users WHERE id = ${userId}`;
// q.sql    → "SELECT * FROM users WHERE id = $1"
// q.params → ["abc-123"]

Compose fragments by nesting:

const where = sql`status = ${"active"} AND age > ${18}`;
const q = sql`SELECT * FROM users WHERE ${where} LIMIT ${10}`;
// q.sql    → "SELECT * FROM users WHERE status = $1 AND age > $2 LIMIT $3"
// q.params → ["active", 18, 10]

joinSQL(chunks, sep?)

Joins an array of SQL fragments with a plain-string separator (default: " "). Renumbers all placeholders.

const conditions = [
  sql`name = ${"alice"}`,
  sql`age > ${21}`,
];
const where = joinSQL(conditions, " AND ");
// where.sql    → "name = $1 AND age > $2"
// where.params → ["alice", 21]

raw(text)

Wraps a raw SQL string as a fragment with no parameters. Never call with user input — the text is inserted verbatim.

const order = raw("ORDER BY created_at DESC");
const q = sql`SELECT * FROM logs ${order}`;
// q.sql    → "SELECT * FROM logs ORDER BY created_at DESC"
// q.params → []

The SQL type

All three functions return the same shape:

type SQL = { readonly sql: string; readonly params: readonly unknown[] };

This matches what the thin proxy expects at POST /sql: { sql, params }.

Usage with the proxy

const q = sql`SELECT * FROM products WHERE price < ${maxPrice}`;

const res = await fetch("/sql", {
  method: "POST",
  headers: {
    "Content-Type": "application/json",
    Authorization: `Bearer ${token}`,
  },
  body: JSON.stringify(q),
});

Known limitations

$N renumbering uses a regex replace (/\$(\d+)/g). This means $N tokens inside dollar-quoted strings, string literals, or SQL comments would be mangled if they appear in raw() text or fragments composed via joinSQL. In practice this is not an issue — the sql tagged template produces its own $N tokens and never renumbers caller-provided literal text.