Insert Statement

Insert(tableName, input) builds an INSERT INTO ... VALUES (...) statement.

import { Insert } from '@sqb/builder';

const query = Insert('customers', { given_name: 'John', family_name: 'Doe' });
query.generate().sql;
// insert into customers (given_name, family_name) values ('John', 'Doe')

Like Select, Insert is dual-callable (Insert(...) or new Insert(...)) and extends the shared query base — see Insert class for the full member list, and the Query / ReturningQuery base classes for .generate(), .values(), .comment() and .returning().

Arguments

Insert(tableName: string | Raw, input: Record<string, any> | Select | Raw): Insert

• tableName — a table name string or a Raw fragment. Passing anything else (including null/undefined) throws a TypeError.
• input — a plain object of column/value pairs, a Select (for INSERT ... SELECT), or a Raw fragment. Passing an array or any other non-object value throws a TypeError.

Insert(null, { id: 1 });
// TypeError: String or Raw instance required as first argument (tableName) for Insert

Insert('customers', [1, 'aaa']);
// TypeError: Object or Select instance required as second argument (input) for Insert

Column values

Object values are serialized with the same value rules used everywhere else in the builder — strings are quoted, Dates are formatted, nested objects become JSON strings, and Param/Raw/sub-Select values are serialized as-is:

Insert('customers', { id: 1, name: 'aaa' }).generate().sql;
// insert into customers (id, name) values (1, 'aaa')

Insert('customers', { id: Param('id'), name: Param('name') })
  .generate({ params: { id: 1, name: 'Abc' } });
// sql: insert into customers (id, name) values (:id, :name)
// params: { id: 1, name: 'Abc' }

Reserved-word column names are escaped automatically:

Insert('customers', { id: 1, with: 'aaa' }).generate().sql;
// insert into customers (id, "with") values (1, 'aaa')

Insert ... Select

Pass a Select as input to build an INSERT INTO ... (columns) VALUES (SELECT ...) statement. Column names are taken from the sub-select's own column aliases/names:

Insert('customers', Select('id', 'the_name name').from('staging')).generate().sql;
// insert into customers (id, name) values (select id, the_name as name from staging)

values()

.values(obj) (inherited from Query) merges extra bind parameter values into the query, which is equivalent to passing params to .generate():

Insert('customers', { id: Param('id'), name: Param('name') })
  .values({ id: 1, name: 'Abc' })
  .generate().sql;
// insert into customers (id, name) values (:id, :name)

returning()

.returning(...columns) (inherited from ReturningQuery) appends a RETURNING clause and records the requested fields on the generate result:

const result = Insert('customers', { id: 1, name: 'aaa' })
  .returning('id', 'update as u1')
  .generate();

result.sql;
// insert into customers (id, name) values (1, 'aaa') returning id, "update" as u1
result.returningFields;
// [{ field: 'id', alias: undefined }, { field: 'update', alias: 'u1' }]

comment()

Insert('customers', { id: 1 }).comment('Seed row');

See Raw SQL and Parameters and Generating SQL per dialect for more on Param and .generate() options.