Writes

The shortcuts that write: insert(), upsert(), update(), deletes() and truncate(). For each, its parameters, every option with the SQL it adds and its effect on the type, and what it throws.

What every write shares:

• It returns a SQLFragment: .run(pool) sends it, .compile() gives its text and values without sending anything.
• A value is sent as a parameter ($1), never written into the SQL; a sql fragment is SQL.
• Rows come back through to_jsonb(), as a table's JSONSelectable: a timestamptz is a string (The catalog).
• An error from Postgres rejects .run() with a DatabaseError (isDatabaseError()). The errors below are thrown by pgcast as it builds the query, before anything is sent.

insert()

db.insert(table, values, options?)

Insert one row or several, and return them as Postgres stored them, defaults and serials filled in.

• table: a table of the schema.
• values: an Insertable, or an array of them. A sql fragment is written as SQL (db.sql`now()`), db.Default as DEFAULT; an undefined value is NULL (An undefined value).
  • raw(), cols() and vals() are no values here: they throw while compiling, before any query (raw() gives the message), and pgcast's type refuses them, where Zapatos 6.6.1's took them. For SQL, write a sql fragment; in an array or a jsonb object, they are part of the value, sent as JSON.
  • db.parent() throws "The 'parent' table alias has no meaning here": an INSERT has no parent query. The type refuses it.
  • A column typed any, as a zapatos/custom placeholder is, passes the type, not the run time. Zapatos 6.6.1 sent each as one parameter, as JSON: { tagline: db.raw("'x'") } stored {"value":"'x'"}, db.parent('id') {"value":"id"} (#259).
• options: returning and extras (Options).

TSYou write Open in playground

const films = await db.insert('films', [{
  studio_id: 1001,
  title: 'The Tidewater Ledger',
  release_year: 2031,
  runtime_minutes: 117,
  tagline: 'Every debt comes in with the tide.',
}, {
  studio_id: 1002,
  title: 'Moths over Harrowgate',
  release_year: 2031,
  runtime_minutes: 95,
}]).run(pool);

SQLPostgres runs

INSERT INTO "films" ("release_year", "runtime_minutes", "studio_id", "tagline", "title")
VALUES ($1, $2, $3, $4, $5), ($6, $7, $8, DEFAULT, $9)
RETURNING to_jsonb("films".*) AS result

values [2031, 117, 1001, "Every debt comes in with the tide.", "The Tidewater Ledger", 2031, 95, 1002, "Moths over Harrowgate"]

::TypeScript knows

const films: films.JSONSelectable[]
// that is
const films: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
}[]

• One row gives one result; an array gives an array, in the order Postgres returns them, in practice the order given.
• The columns are the keys of every row; a key missing from a row is DEFAULT in that row, as tagline above.
• An empty array is a no-op: .run(pool) resolves with [] and sends nothing, unless forced with .run(pool, true). Its SQL, INSERT INTO "films" SELECT null WHERE false, is marked as a no-op.
• Postgres takes at most 65,535 parameters in a query: split a bigger insert (Batches).

Options

returning and extras are the same for upsert(), update() and deletes() (ReturningOptionsForTable).

TSYou write Open in playground

const film = await db.insert('films', {
  studio_id: 1000,
  title: 'Winter at Fennick Lock',
  release_year: 2032,
  runtime_minutes: 108,
}, {
  returning: ['id'],
  extras: {
    name: 'title',
    shouted: db.sql<s.films.SQL, string>`upper(${'title'})`,
  },
}).run(pool);

SQLPostgres runs

INSERT INTO "films" ("release_year", "runtime_minutes", "studio_id", "title")
VALUES ($1, $2, $3, $4)
RETURNING jsonb_build_object($5::text, "id") ||
  jsonb_build_object($6::text, "title", $7::text, upper("title")) AS result

values [2032, 108, 1000, "Winter at Fennick Lock", "id", "name", "shouted"]

::TypeScript knows

const film: db.ReturningTypeForTable<"films", "id"[], {
  name: "title";
  shouted: db.SQLFragment<string, never>;
}>
// that is
const film: {
  id: number;
  name: string;
  shouted: string;
}

Option	SQL it adds	Effect on the type
none	RETURNING to_jsonb("films".*) AS result	films.JSONSelectable
returning: ['id']	jsonb_build_object($5::text, "id") in place of to_jsonb("films".*)	Pick<films.JSONSelectable, 'id'>
extras: { name: 'title', shouted: … }, as above	|| jsonb_build_object($6::text, "title", $7::text, upper("title"))	adds name, of the column's type, and shouted, of the fragment's RunResult: string, from db.sql<s.films.SQL, string>

• A readonly array in returning (['id'] as const) gives the same SQL and the same type as a mutable one (columns).
• Both need strictNullChecks for their types.

upsert()

db.upsert(table, values, conflictTarget, options?)

INSERT … ON CONFLICT … DO UPDATE: insert each row, or update the existing row it conflicts with.

• table, values: as for insert(); raw(), cols(), vals() and parent() throw there too, on a conflicting row or a new one.
• conflictTarget: the arbiter of the conflict: a column, an array of columns, or the name of a unique or primary-key constraint wrapped in constraint().
• options: updateColumns, noNullUpdateColumns, updateValues and reportAction (Options); returning and extras, as for insert().

TSYou write Open in playground

const rating = await db.upsert('ratings', {
  film_id: 1002,
  stars: 4,
}, ['film_id', 'stars'], {
  updateValues: { votes: db.sql`${'ratings'}.${'votes'} + 1` },
}).run(pool);

SQLPostgres runs

INSERT INTO "ratings" ("film_id", "stars")
VALUES ($1, $2)
ON CONFLICT ("film_id", "stars") DO UPDATE SET ("film_id", "stars", "votes") = ROW(EXCLUDED."film_id", EXCLUDED."stars", "ratings"."votes" + 1)
RETURNING to_jsonb("ratings".*) ||
  jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result

values [1002, 4]

::TypeScript knows

const rating: db.UpsertReturnableForTable<"ratings", db.ColumnsOption<"ratings">, db.ExtrasOption<"ratings">, "suppress" | undefined>
// that is
const rating: {
  film_id: number;
  stars: number;
  votes: number;
  $action: "INSERT" | "UPDATE";
}

• The update sets every column inserted, from EXCLUDED, and each key of updateValues.
• Each result has $action, 'INSERT' or 'UPDATE' (UpsertAction).
• One row gives one result; an array gives an array, without the rows that do nothing (doNothing).
• An empty array is a no-op, as for insert().

Options

Each SQL replaces the DO UPDATE SET … of the plain upsert, on ratings (on films for noNullUpdateColumns), or adds to it:

Option	SQL it adds	Effect on the type
none	DO UPDATE SET ("film_id", "stars", "votes") = ROW(EXCLUDED."film_id", EXCLUDED."stars", EXCLUDED."votes")	ratings.JSONSelectable & UpsertAction
updateColumns: 'votes'	DO UPDATE SET ("votes") = ROW(EXCLUDED."votes")	none
updateColumns: db.doNothing	DO NOTHING	| undefined, for one row
noNullUpdateColumns: 'tagline'	CASE WHEN EXCLUDED."tagline" IS NULL THEN "films"."tagline" ELSE EXCLUDED."tagline" END in place of EXCLUDED."tagline"	none
updateValues: { votes: 10 }	ROW(EXCLUDED."film_id", EXCLUDED."stars", $3): $3 in place of EXCLUDED."votes"	none
updateValues: { votes: db.sql`${'ratings'}.${'votes'} + 1` }	"ratings"."votes" + 1 in place of EXCLUDED."votes"	none
reportAction: 'suppress'	no || jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END)	no UpsertAction

• updateColumns takes a column or an array; noNullUpdateColumns a column, an array, or db.all for every column. Batches has a panel of updateColumns: a booking keeps its time.
• updateValues is one set of values for every row updated. Its keys are always updated, even when updateColumns leaves them out, or is doNothing (then the SQL is DO UPDATE SET, not DO NOTHING).
• In updateValues, a plain value is a parameter. A value that holds a fragment, param(), raw(), cols(), vals(), parent(), Default or self, at any depth of its arrays and plain objects, is compiled as in a sql template.
• To read a column there, name the table for the existing row, ${'ratings'}.${'votes'}, or EXCLUDED for the new one, db.sql`EXCLUDED.${'votes'}`.
• There, db.self, or a fragment or condition that uses it (db.sql`${db.self} + 1`, conditions.add()), throws "The 'self' column alias has no meaning here", as in Zapatos 6.6.1; db.parent(), alone or in a fragment, throws "The 'parent' table alias has no meaning here".

Security

Zapatos 6.6.1 wrote a plain string in updateValues as an identifier: { tagline: 'happy' } put "happy" in the ROW(…), and a string from a user could read other columns or tables.

pgcast sends it as a parameter, $3 in its place, as its type and update() always said. To read a column there, write a fragment, as in the table (Security).

With noNullUpdateColumns, a NULL leaves the existing value. Film 1002 comes back from a feed with no tagline, and keeps its own:

TSYou write Open in playground

const film = await db.upsert('films', {
  id: 1002,
  studio_id: 1000,
  title: "The Cartographer's Silence",
  release_year: 2008,
  runtime_minutes: 131,
  tagline: null,
}, 'id', { noNullUpdateColumns: 'tagline' }).run(pool);

SQLPostgres runs

INSERT INTO "films" ("id", "release_year", "runtime_minutes", "studio_id", "tagline", "title")
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT ("id") DO UPDATE SET ("id", "studio_id", "title", "release_year", "runtime_minutes", "tagline") = ROW(EXCLUDED."id", EXCLUDED."studio_id", EXCLUDED."title", EXCLUDED."release_year", EXCLUDED."runtime_minutes", CASE WHEN EXCLUDED."tagline" IS NULL THEN "films"."tagline" ELSE EXCLUDED."tagline" END)
RETURNING to_jsonb("films".*) ||
  jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result

values [1002, 2008, 131, 1000, null, "The Cartographer's Silence"]

::TypeScript knows

const film: db.UpsertReturnableForTable<"films", db.ColumnsOption<"films">, db.ExtrasOption<"films">, "suppress" | undefined>
// that is
const film: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
  $action: "INSERT" | "UPDATE";
}

• The update still sets every column inserted, "id" included; only "tagline" keeps the existing value when the new one is NULL.
• film is the updated row: $action is 'UPDATE', and tagline is still 'Every map leaves something out.'.

With reportAction: 'suppress', the result has no $action:

TSYou write Open in playground

const rating = await db.upsert('ratings', {
  film_id: 1002,
  stars: 4,
  votes: 2,
}, ['film_id', 'stars'], { reportAction: 'suppress' }).run(pool);

SQLPostgres runs

INSERT INTO "ratings" ("film_id", "stars", "votes")
VALUES ($1, $2, $3)
ON CONFLICT ("film_id", "stars") DO UPDATE SET ("film_id", "stars", "votes") = ROW(EXCLUDED."film_id", EXCLUDED."stars", EXCLUDED."votes")
RETURNING to_jsonb("ratings".*) AS result

values [1002, 4, 2]

::TypeScript knows

const rating: ratings.JSONSelectable
// that is
const rating: {
  film_id: number;
  stars: number;
  votes: number;
}

With strictNullChecks off, the type keeps $action, which is then undefined.

Rows with different keys

In several rows without updateColumns, a key missing from one row is inserted as DEFAULT, then set from EXCLUDED on conflict: that row's existing value is lost.

• [{ film_id: 1002, stars: 5, votes: 3 }, { film_id: 1002, stars: 4 }] sends ($4, $5, DEFAULT), so an existing count of 4-star votes goes back to 1.
• It runs, as in Zapatos 6.6.1, and warns once per process: PGCAST_DEP001.
• A key of updateValues, or a column of an array or string conflictTarget, does not count: new rows without their serial id, upserted on 'id', neither warn nor throw. With constraint(), every key counts.
• Rows with the same keys, one row, and any updateColumns never warn.

Opt-in

With upsertRequiresSameKeys: true (Config), such an upsert throws before any query: "rows have different keys: pass updateColumns, or give every row the same keys". Without it, pgcast warns as PGCAST_DEP001; it becomes the default in 7.0 (Versions).

constraint()

db.constraint(name)

Wrap the name of a unique or primary-key constraint of the table, for the conflictTarget of upsert(): ON CONFLICT ON CONSTRAINT "ratings_pkey", in place of ON CONFLICT ("film_id", "stars"). The names come from the schema, so one the table doesn't have fails to compile:

db.upsert('ratings', { film_id: 1002, stars: 4 }, db.constraint('ratings_pkey'));
db.upsert('ratings', { film_id: 1002, stars: 4 }, db.constraint('ratings_stars_key'));
Argument of type '"ratings_stars_key"' is not assignable to parameter of type '"ratings_pkey"'.

• The type takes every unique index of the table, and Postgres only a constraint. An index made with CREATE UNIQUE INDEX, which is no constraint, type-checks, and Postgres refuses it: 42704, constraint "…" for table "…" does not exist.
• For such an index, pass its columns as the conflictTarget: 'code' for a unique index on code.

The type does not check that a constraint belongs to the upsert's table, and Postgres refuses another table's: 42704. Only inside the call does constraint() take the table's names, as above. Held in a variable, const k = db.constraint('tags_pkey') takes any table's name, so db.upsert('ratings', …, k) compiles. Give the table, db.constraint<'ratings'>('ratings_pkey'), and the type checks the name against it:

const k = db.constraint('tags_pkey');
db.upsert('ratings', { film_id: 1002, stars: 4 }, k);  // compiles, then 42704
// @ts-expect-error tags_pkey is not a constraint of ratings
db.constraint<'ratings'>('tags_pkey');

Constraint

The class that constraint() returns, its constraint name in value: new db.Constraint<'ratings'>('ratings_pkey') is the same.

doNothing

An empty array, for the updateColumns of upsert(): a row that conflicts is left as it is, and returns nothing.

TSYou write Open in playground

const tag = await db.upsert('tags', {
  film_id: 1003,
  tag: 'coastal',
}, ['film_id', 'tag'], {
  updateColumns: db.doNothing,
}).run(pool);

SQLPostgres runs

INSERT INTO "tags" ("film_id", "tag")
VALUES ($1, $2)
ON CONFLICT ("film_id", "tag") DO NOTHING
RETURNING to_jsonb("tags".*) ||
  jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result

values [1003, "coastal"]

::TypeScript knows

const tag: db.UpsertReturnableForTable<"tags", db.ColumnsOption<"tags">, db.ExtrasOption<"tags">, "suppress" | undefined> | undefined
// that is
const tag: {
  film_id: number;
  tag: string;
  $action: "INSERT" | "UPDATE";
} | undefined

• One row: undefined when it conflicts, and the type says so.
• An array: the rows inserted only; [] when every row conflicts.

UpsertAction

{ $action: 'INSERT' | 'UPDATE' }, added to each result of upsert(): 'INSERT' for a new row, 'UPDATE' for an updated one, read from the row's xmax. reportAction: 'suppress' leaves it out of the SQL and of the type.

UpsertReturnableForTable

UpsertReturnableForTable<T, C, E, RA>: one row of what upsert() returns, the ReturningTypeForTable with UpsertAction, unless RA, the reportAction, is 'suppress': db.UpsertReturnableForTable<'ratings', undefined, undefined, undefined> is ratings.JSONSelectable & UpsertAction. It does not add the undefined of updateColumns: [] (doNothing). It needs strictNullChecks: without it, it is the whole JSONSelectable with UpsertAction, whatever C, E and RA.

update()

db.update(table, values, where, options?)

Update the rows that match, and return them. The arguments come in the order of the SQL: SET, then WHERE.

• table: a table of the schema.
• values: an Updatable, with at least one key. A sql fragment is written as SQL, where db.self is the column, and so is a condition such as conditions.add(). raw(), cols(), vals() and parent() throw, as for insert(). An undefined value is NULL (An undefined value).
• where: a Whereable, or a sql fragment (Conditions).
• options: returning and extras, as for insert().

TSYou write Open in playground

const screenings = await db.update('screenings', {
  starts_at: db.sql`${db.self} + interval '15 minutes'`,
  subtitled: true,
}, { id: 1002 }).run(pool);

SQLPostgres runs

UPDATE "screenings" SET ("starts_at", "subtitled") = ROW("starts_at" + interval '15 minutes', $1)
WHERE ("id" = $2)
RETURNING to_jsonb("screenings".*) AS result

values [true, 1002]

::TypeScript knows

const screenings: screenings.JSONSelectable[]
// that is
const screenings: {
  cinema_id: number;
  film_id: number;
  id: number;
  starts_at: db.TimestampTzString;
  subtitled: boolean | null;
}[]

• Always an array: every row updated, [] when none matches.
• SET (…) = ROW(…): ROW() keeps a single column valid since Postgres 10.
• values with no key throws: "nothing to update: an Updatable needs at least one column". Zapatos 6.6.1 sent SET () = ROW(), which Postgres refused (42601).

Every row

An empty Whereable, {}, compiles to WHERE TRUE: update() updates every row of the table, and deletes() deletes them all. An empty list in and(...filters) or not(or(...ids)) reaches every row too.

deletes()

db.deletes(table, where, options?)

Delete the rows that match, and return them. delete is a reserved word in JavaScript, hence the name.

• table: a table of the schema.
• where: a Whereable, or a sql fragment (Conditions); {} is every row, as for update().
• options: returning and extras, as for insert().

TSYou write Open in playground

const removed = await db.deletes('tags', {
  film_id: 1003,
}, { returning: ['tag'] }).run(pool);

SQLPostgres runs

DELETE
FROM "tags"
WHERE ("film_id" = $1)
RETURNING jsonb_build_object($2::text, "tag") AS result

values [1003, "tag"]

::TypeScript knows

const removed: db.JSONOnlyColsForTable<"tags", "tag"[]>[]
// that is
const removed: {
  tag: string;
}[]

• Always an array: every row deleted, [] when none matches.

DeleteSignatures

The interface of deletes()'s call signature, (table, where, options?) => SQLFragment<…[]>, for a function that wraps it and keeps its types. A wrapper typed with it gets the types of its parameters from it, with no cast, and its calls are checked and typed as those of deletes():

const loggedDeletes: db.DeleteSignatures = (table, where, options) => {
  const query = db.deletes(table, where, options);
  console.log(query.compile().text);
  return query;
};

const removed = loggedDeletes('tags', { film_id: 1003 }, { returning: ['tag'] });

truncate()

db.truncate(table, ...options)

Empty one table, or several in one statement.

• table: a table of the schema, or an array of them.
• options: 'CONTINUE IDENTITY' or 'RESTART IDENTITY', then 'RESTRICT' or 'CASCADE', each optional.

TSYou write Open in playground

const emptied = await db.truncate(['seat_bookings', 'ratings', 'tags'], 'RESTART IDENTITY').run(pool);

SQLPostgres runs

TRUNCATE "seat_bookings", "ratings", "tags" RESTART IDENTITY

values []

::TypeScript knows

const emptied: undefined

Option	SQL it adds	Effect on the type
none	TRUNCATE "tags"	undefined
'CONTINUE IDENTITY'	CONTINUE IDENTITY: sequences keep their values, as by default	none
'RESTART IDENTITY'	RESTART IDENTITY: the tables' sequences restart	none
'RESTRICT'	RESTRICT: Postgres refuses if a table not in the list references one of them, as by default	none
'CASCADE'	CASCADE: also empties the tables that reference them	none

With 'CASCADE', Postgres empties every table that references one of them, at any depth: films takes roles, screenings, ratings and tags with it, and seat_bookings through screenings, each named in a notice, truncate cascades to table "roles":

TSYou write Open in playground

const emptied = await db.truncate('films', 'CASCADE').run(pool);

SQLPostgres runs

TRUNCATE "films" CASCADE

values []

::TypeScript knows

const emptied: undefined

• .run() resolves with [], where the type says undefined.
• The type takes the options in that order, one of each kind. pgcast writes them as given: in JavaScript, or past the type with as any, truncate('tags', 'CASCADE', 'RESTART IDENTITY') sends TRUNCATE "tags" CASCADE RESTART IDENTITY, and Postgres refuses it (42601).
• Any other option throws: "Truncate option must be CONTINUE IDENTITY/RESTART IDENTITY/RESTRICT/CASCADE, not '…'". Zapatos 6.6.1 wrote it into the SQL as it was.

An undefined value

In the values of insert(), update() and upsert(), Zapatos 6.6.1 sends undefined as NULL: a NOT NULL DEFAULT column fails, and update() erases the column instead of leaving it.

In	By default, as in 6.6.1	With undefinedMeansMissing: true
insert()	NULL	DEFAULT: VALUES ($1, $2, $3, DEFAULT, $4)
update()	sets the column to NULL, and counts as a key	left out: SET ("title") = ROW($1); with no key left, it throws "nothing to update…"
upsert()	inserts NULL, then sets it from EXCLUDED	left out: not inserted, nor updated unless updateColumns names it (then from EXCLUDED, so an existing row takes the column's DEFAULT); in several rows, its cell is DEFAULT and counts as missing (Rows with different keys)

• By default, each sends the SQL of 6.6.1 and warns once per process: PGCAST_DEP002.
• An undefined in a where is unchanged.
• An undefined in updateValues is unchanged too, in both modes: its key is still updated, from EXCLUDED. So an existing row takes the value inserted, or the column's DEFAULT when the row doesn't insert it, with no warning: upsert('ratings', { film_id: 1002, stars: 4 }, ['film_id', 'stars'], { updateValues: { votes: undefined } }) sends EXCLUDED."votes", and an existing count goes back to 1.

Opt-in

undefinedMeansMissing: true (Config) reads an undefined value as a missing key, as JSON.stringify() does. Without it, pgcast warns as PGCAST_DEP002; it becomes the default in 7.0 (Versions).

ReturningOptionsForTable

{ returning?, extras? }: the options of insert(), upsert(), update() and deletes() that shape each result (Options).

ReturningTypeForTable

ReturningTypeForTable<T, C, E>: one row of what insert(), update() and deletes() return, for the table T, the returning columns C and the extras E (undefined for an option not given): the JSONSelectable, narrowed to C, with the keys of E.

insert() of one row returns it; insert() of an array, update() and deletes() return an array of it. It types a fake result, in a test, from the schema:

type NewFilm = db.ReturningTypeForTable<'films', ['id', 'title'], undefined>;

const inserted: NewFilm = { id: 1000, title: 'The Long Take' };

• Stable: type tests pin it to what .run() gives, here for db.insert('films', film, { returning: ['id', 'title'] }).
• C may be a readonly tuple, as returning may (Options).
• It needs strictNullChecks: without it, it is the whole JSONSelectable, whatever C and E.