Writing data

INSERT, UPDATE and DELETE are checked against the schema like queries. With RETURNING, they have a row type; without it the row type is never, and adapters' execute() returns the affected row count.

INSERT

Value parameters take the column's type, including | null for nullable columns. Every NOT NULL column without a default must be listed:

const created = sql(
  "INSERT INTO users (email, display_name, mood) VALUES ($1, $2, $3) RETURNING id, created_at",
  "a@b.c",
  null,
  "happy",
);
// Query<{ id: number; created_at: Date }, [string, string | null, "happy" | "neutral" | "sad"]>

sql("INSERT INTO users (display_name) VALUES ($1)", "Ada");
// … SqlError<"null value in column \"email\" of relation \"users\" violates not-null constraint", 2>

JSON values for json / jsonb columns go through ${sql.json(value)}, which sends JSON text with any driver and checks the value against a Json<T> column.

Also supported: multi-row VALUES, DEFAULT and DEFAULT VALUES, INSERT … SELECT (its columns are checked against the target list), OVERRIDING … VALUE, and inserting into a view is an error. DEFAULT is only allowed as a whole VALUES item or SET value, and not for a NOT NULL column without a default (it would store NULL).

Optional values: sql.valueOrDefault

sql.valueOrDefault(value) is used like sql.param(value), but an undefined value sends DEFAULT instead of a parameter. The column's default stays in the database, so you don't copy it into a coalesce(…):

declare const mood: "happy" | "neutral" | "sad" | undefined; // optional input
const signup = sql(`
  INSERT INTO users (email, mood)
  VALUES (${sql.param("a@b.c")}, ${sql.valueOrDefault(mood)})
  RETURNING id, mood`);
signup.text; // "… VALUES ($1, $2) …", or "… VALUES ($1, DEFAULT) …" when mood is undefined

The value is checked like a parameter, without undefined. When it can be undefined, the statement is checked a second time with DEFAULT in its place, so using it outside a VALUES item or SET value, or for a NOT NULL column without a default, is an error:

sql(`INSERT INTO users (email) VALUES (${sql.valueOrDefault(maybeEmail)})`);
// … SqlError<"null value in column \"email\" of relation \"users\" violates not-null constraint
//            (sql.valueOrDefault() sends DEFAULT for undefined)", 4>

The second check runs once per statement, however many valueOrDefault values it has. Inside sql.fragment(…) only the value type is checked.

Partial updates: sql.set

sql.set(table, values) builds a SET list from an object, such as a PATCH body with optional keys. Keys must be columns of the table and values must fit them (null only for nullable columns, enum values for enums). undefined values are skipped:

declare const patch: { display_name?: string | null; mood?: "happy" | "neutral" | "sad" }; // e.g. a PATCH body
const set = sql.set("users", patch); // keys and values checked against users; undefined is skipped
const patched = sql("UPDATE users SET {set} WHERE id = $1 RETURNING id, display_name, mood", { set }, 7);
patched.text; // 'UPDATE users SET "display_name" = $2 WHERE id = $1 …' when only display_name is set

• Splice it into UPDATE … SET {set} or ON CONFLICT … DO UPDATE SET {set}; other assignments can follow (SET {set}, updated_at = now()).
• The statement is checked with every key of the input type assigned once, so the columns are checked again against the table being updated.
• Keys that are not columns are errors, also on a variable whose type has extra keys, because they would be sent.
• If no value is defined at runtime, sql.set throws: Postgres needs at least one assignment. Skip the UPDATE (or SELECT the row) when the input is empty.

Bulk inserts: sql.values

sql.values(table, rows) builds the column list and the VALUES rows from objects:

declare const imported: { email: string; display_name?: string | null }[];
const rows = sql.values("users", imported); // email is required; missing values are DEFAULT
const inserted = sql("INSERT INTO users {rows} ON CONFLICT (email) DO NOTHING RETURNING id", { rows });
inserted.text; // 'INSERT INTO users ("email", "display_name") VALUES ($1, $2), ($3, DEFAULT) …'

• NOT NULL columns without a default are required in every row; the others are optional.
• The columns are the keys that have a value in any row, in order of first appearance. A missing or undefined value is DEFAULT, so rows with different keys can be mixed.
• One row without any value becomes DEFAULT VALUES. An empty array throws, and so do several rows without values (Postgres cannot insert them with VALUES).
• The statement is checked with every key of the row types once.

The table name is only used for typing, and schema tables are written "schema.table": sql.values("auth.sessions", rows).

JSON columns

sql.set and sql.values pass their values to the driver as parameters, so a JSON value goes through sql.json(value) there too: it is sent as JSON text by both drivers and checked against a Json<T> column. A plain object also works as it is; an array without sql.json() is an error, because node-postgres would send it as a Postgres array:

const patch = sql.set("portfolio", { history: sql.json(history), settings: { theme: "dark" } });
const patched = sql("UPDATE portfolio SET {patch} WHERE id = $1", { patch }, 1);

const rows = sql.values("portfolio", [{ name: "a", history: sql.json(history) }, { name: "b" }]);
const created = sql("INSERT INTO portfolio {rows} RETURNING id", { rows });
// sql.set("portfolio", { history }) is an error: an array for jsonb must be passed as sql.json(value)

UPDATE

SET columns must exist and values take their types. UPDATE … FROM joins more tables, and RETURNING can use all of them:

const renamed = sql("UPDATE users SET display_name = $1 WHERE id = $2", "Ada", 7);
// RowType<typeof renamed> is never: no RETURNING. Run it with execute() to get the row count.
const touched = sql(`
  UPDATE posts p SET title = upper(p.title)
  FROM users u
  WHERE u.id = p.author_id AND u.mood = 'sad'
  RETURNING p.id, u.email`);
// { id: string; email: string }

Upserts

ON CONFLICT … DO NOTHING and DO UPDATE SET … WHERE … are supported; EXCLUDED refers to the row that was proposed for insertion:

const upsert = sql(`
  INSERT INTO users (email, mood) VALUES ($1, $2)
  ON CONFLICT (email) DO UPDATE SET mood = EXCLUDED.mood
  RETURNING id`, "a@b.c", "neutral");

DELETE

const removed = sql("DELETE FROM posts p USING users u WHERE u.id = p.author_id AND u.email = $1 RETURNING p.id, p.title", "a@b.c");
// { id: string; title: string }

RETURNING * expands to the target table's columns, or to every table in FROM/USING, as in Postgres.

Data-modifying CTEs

Statements with RETURNING can feed a WITH clause:

const moved = sql(`
  WITH archived AS (
    DELETE FROM posts WHERE author_id = $1 RETURNING id
  )
  SELECT count(*) AS n FROM archived`, 7);
// { n: string }