Fragments

A fragment is a piece of SQL you build once and splice into statements through {name} slots. Splicing happens before inference, so the finished statement is checked as a whole and the fragment's parameters get their types from where they end up.

Slots

const byAuthor7 = sql.fragment("WHERE p.author_id = $1", 7);
const q = sql("SELECT p.id, p.title FROM posts p {byAuthor7} ORDER BY p.id", { byAuthor7 });
q.text; // "SELECT p.id, p.title FROM posts p WHERE p.author_id = $1 ORDER BY p.id"
// Query<{ id: string; title: string }, [number | null]>

Only {identifier} is a slot; braces inside string literals, such as '{1,2}', are left alone.

A fragment's own parameters are renumbered to follow the statement's. Arguments passed after the slot object belong to the outer statement:

const byAuthor = sql(
  "SELECT p.title FROM posts p WHERE p.author_id = $1 {more}",
  { more: sql.fragment(`AND p.title <> ${sql.param("draft")}`) },
  42,
);
byAuthor.text; // "SELECT p.title FROM posts p WHERE p.author_id = $1 AND p.title <> $2"
byAuthor.values; // [42, "draft"]

The values a fragment carries are type-checked once it is spliced, whether they were passed as arguments, :name objects or sql.param():

const wrong = sql.fragment("AND p.author_id = $1", "seven");
sql("SELECT p.id FROM posts p WHERE true {wrong}", { wrong });
// … SqlError<"parameter $1: a string value cannot be used as int4", 14>

Queries as subqueries

A whole query can be spliced as well, for example into IN (…):

const authors = sql("SELECT author_id FROM posts GROUP BY author_id HAVING count(*) >= $1::int4", 3);
const prolific = sql("SELECT email FROM users WHERE id IN ({authors})", { authors });

Identifiers and lists

sql.id(…) quotes an identifier (sql.id("schema", "table") gives "schema"."table"), and sql.join([…], separator) joins fragments, renumbering their parameters in order. Both keep their text as a literal type, so the statement is still fully typed:

const column = sql.id("display_name"); // "display_name", quoted
const both = sql.join([sql.fragment("u.id > $1", 10), sql.fragment(`u.email LIKE ${sql.param("%@example.com")}`)], " AND ");
const listed = sql("SELECT {column} FROM users u WHERE {both}", { column, both });
listed.text; // 'SELECT "display_name" FROM users u WHERE u.id > $1 AND u.email LIKE $2'
// Query<{ display_name: string | null }, [number, string]>

A name whose type is a union of literals gives a fragment whose text is a union. The statement is checked once for each member, and the row type is the union of their rows:

declare const shown: "email" | "display_name";
const pick = sql.id(shown); // Fragment<'"display_name"' | '"email"'>
const picked = sql("SELECT {pick} FROM users", { pick });
// RowType: { display_name: string | null } | { email: string }

function countRows<T extends RelationName<DB>>(table: T) {
  const t = sql.id(table); // every table and view of DB
  return sql("SELECT count(*) AS n FROM {t}", { t });
}

Inside a generic function, the name's type is not known until the call, so TypeSQL cannot check the statement there. A generic constrained to RelationName<DB> (the top-level tables and views) is widened to all of them: the statement is checked against every relation, and the row type is the union of all their rows. Any other generic or string name gives a dynamic fragment, for sql.as. To check against a narrower set, widen the name yourself: sql.id(name as "users" | "posts").

sql.join keeps the literal text only for a tuple written in place. Joining an array built at runtime gives a fragment whose text is not known at compile time; use sql.list for that.

Lists from runtime arrays

sql.list(items, separator = ", ") joins an array whose length and order are only known at runtime, such as user-chosen sort keys. Each element's text must be known at compile time (fragments picked from a constant table, sql.id over a union of names), so the statement stays checked:

const SORT = {
  title: { asc: sql.fragment("p.title"), desc: sql.fragment("p.title DESC") },
  author: { asc: sql.fragment("p.author_id"), desc: sql.fragment("p.author_id DESC") },
} as const;
declare const sortBy: { field: keyof typeof SORT; dir: "asc" | "desc" }[]; // chosen by the user
const order = sql.list([...sortBy.map(({ field, dir }) => SORT[field][dir]), sql.fragment("p.id")]);
const sorted = sql("SELECT p.id, p.title FROM posts p ORDER BY {order}", { order });
// Query<{ id: string; title: string }, []>: checked with each of the five elements once

TypeSQL does not try every combination. It checks the statement once, with each distinct element present once in the list, in TypeScript's union order. In a list position (ORDER BY, GROUP BY, SET, VALUES rows, IN (…), an AND chain) each element is checked on its own, so if that statement is valid, every count and order of the elements is valid too. Every element's columns are checked against the scope, and the values each element carries are checked against the types they get there.

• The query's text type (TextType) is that one-of-each statement, not the text sent at runtime. The runtime text repeats and renumbers elements as given.
• In a select list (SELECT {cols}), the row type counts every element as selected, even though at runtime only some may be.
• An empty array throws at runtime. Put a fixed element first ([base, ...rest]), as with the p.id tiebreaker above, or use sql.fragment("true") first in an AND chain.
• An element whose text is dynamic (sql.raw) makes the whole list dynamic.

Dynamic SQL

When the shape of a statement is only known at runtime (sql.raw(text), or sql.join over an array), TypeSQL cannot infer anything, and splicing such a fragment into sql(…) is a compile error. Use sql.as<Row>(…), which accepts the same arguments, skips inference and lets you state the row type:

declare const userChosenColumns: string[];
const raw = sql.raw(userChosenColumns.map((c) => `"${c.replaceAll('"', '""')}"`).join(", "));
const dynamic = sql.as<{ [column: string]: unknown }>("SELECT {raw} FROM users", { raw });

sql.raw text is sent as is; never put user input in it.