Table API

Table is the API for table-level operations. This object is obtained via database.table()

See content

• table.name
• table.count()
• table.select()
• table.insert()
• table.upsert()
• table.update()
• table.delete()
• table.schema()

table.name:

The name associated with the Table instance.

table.name: (string, readonly)

⚽️ Usage:

const table = client.database('test_db').table('table_1');
console.log(table.name); // table_1

table.count():

Count total entries in table.

table.count(): Promise<number>

⚙️ Spec:

• Return value: number.

⚽️ Usage:

Same signature as table.select() but for performing a count query.

const rowCount = await table.count();

// Number of rows where column_1 isn't null
const rowCount = await table.count(['column_1']);

// Number of rows where column_1 isn't null and the extra conditions are also satisfied
const rowCount = await table.count(['column_1'], { where: { col1: 'val1' } });

// Number of rows where conditions are satisfied
const rowCount = await table.count({ where: { col1: 'val1' } });

table.select():

Dynamically run a SELECT query.

table.select(fields?: (string | Function)[] = *, modifiers?: object | Function | number | true): Promise<Array<object> | object>

table.select(modifiers?: object | Function | number | true): Promise<Array<object> | object>

⚙️ Spec:

• fields ((string | Function)[] = *, optional): an array of fields to select. (A field being either a column name string, or a function that recieves a new Field builder with which to build an expression.)
• modifiers (object | Function | number | true, optional): additional query modifiers. If a number, then the query's LIMIT clause implied. If a function, then a callback function implied to recieve the underlying query builder. If an object, then an object with any of the following properties:
  • where (string | number | object | Function, optional): the query's WHERE clause. If a string or number, a condition over primary key column implied. (With the primary key column name automatically figured.) If an object, an object of column name/column value conditions implied. If a function, a callback function implied to recieve the underlying Condition builder. If the value true, all records implied. Defaults to true.
  • limit (number, optional): the query's LIMIT clause.
• Return value: an array (the result set) for a multi-row SELECT operation (where modifiers.where isn't string or number), but an object for a single-row SELECT operation (where modifiers.where is string or number).

⚽️ Usage:

// Select all fields (*) from all records
const result = await table.select();

// Select specified fields and limit to 4 records
const result = await table.select(['first_name', 'last_name', 'email'], 4);

// Select first 4 records, ommiting fields (implying all fields)
const result = await table.select(4);

// Select record with primary key of 1. (This time returning single result object.)
const result = await table.select({ where: 1 });

// Select record by some column name/column value conditions
const result = await table.select({ where: { first_name: 'John', last_name: 'Doe' } });

table.insert():

Dynamically run an INSERT operation. (With automatic parameter binding.)

table.insert(payload: object | object[], modifiers?: object | Function): Promise<Array<object> | object | number>

table.insert(columns: string[], values: any[][], modifiers?: object | Function): Promise<Array<object> | object | number>

⚙️ Spec:

• payload (object | object[]): an object denoting a single entry, or an array of said objects denoting multiple entries. (An entry having the general form: { [key: string]: string | number | boolean | null | Date | object | any[] } where arrays and objects as values are automatically JSON-stringified.)
• columns (string[]): just column names (as against the key/value payload in the first call pattern).
• values (any[][]): a two-dimensional array of just values (as against the key/value payload in the first call pattern), denoting multiple entries.
• modifiers (object | Function, optional): additional modifiers for the query. If a function, then a callback function implied to recieve the underlying query builder. If an object, then an object with any of the following properties:
  • returning ((string | Function)[], optional): a list of fields, corresponding to a select list, specifying values to be returned from the just inserted row. (Equivalent to Postgres' RETURNING clause, but supported for other DB kinds in Linked QL.)
• Return value: where no modifiers.returning is specified, a number indicating number of rows processed by the query, otherwise an array (the result set) for a multi-row INSERT operation (where payload isn't an object), but an object for a single-row INSERT operation (where payload is an object is specified).

⚽️ Usage:

// Insert single entry
await table.insert({ first_name: 'John', last_name: 'Doe', email: '[email protected]'});

// Insert multiple entries
await table.insert([
    { first_name: 'John', last_name: 'Doe', email: '[email protected]'},
    { first_name: 'James', last_name: 'Clerk', email: '[email protected]'},
]);

// Insert multiple entries another way
await table.insert(['first_name', 'last_name', 'email'], [
    ['John', 'Doe', '[email protected]'],
    ['James', 'Clerk', '[email protected]'],
]);

// Insert single entry, obtaining inserted row - limited to just the "id" column
const insertedRows = await table.insert({ first_name: 'John', last_name: 'Doe', email: '[email protected]'}, { returning: ['id'] });

table.upsert():

Dynamically run an UPSERT operation. (With automatic parameter binding.)

table.upsert(payload: object | object[], returnList?: (string | Function)[]): Promise<Array<object> | object | number>

table.upsert(columns: string[], values: any[][], returnList?: (string | Function)[]): Promise<Array<object> | object | number>

⚙️ Spec:

• payload (object | object[]): as described in insert().
• columns (string[]): as described in insert().
• values (any[][]): as described in insert().
• modifiers (object | Function, optional): as described in insert().
• Return value: as described in insert().

⚽️ Usage:

An UPSERT operation is an INSERT operation that automatically converts to an UPDATE operation where given record already exists. API usage is same as insert() but as upsert().

table.update():

Dynamically run an UPDATE operation. (With automatic parameter binding.)

table.update(payload: object, modifiers: object | Function | number | true): Promise<Array<object> | object | number>

⚙️ Spec:

• payload (object): an object having the general form: { [key: string]: string | number | boolean | null | Date | object | any[] } where arrays and objects as values are automatically JSON-stringified.
• modifiers (object | Function | number | true): as described in select() including any of the following properties:
  • returning ((string | Function)[], optional): as described in insert()
• Return value: where modifiers.returning clause is specified, a number indicating number of rows processed by the query, otherwise an array (the result set) for a multi-row UPDATE operation (where modifiers.where isn't string or number), but an object for a single-row UPDATE operation (where modifiers.where is string or number).

⚽️ Usage:

// Update the record having primary key value of 4
await table.update({ first_name: 'John', last_name: 'Doe' }, { where: 4 });

// Update the record having specified email value, obtaining the updated rows
const updatedRows = await table.update({ first_name: 'John', last_name: 'Doe' }, { where: { email: '[email protected]' }, returning: ['*'] });

// Update all records
await table.update({ updated_at: new Date }, { where: true });

table.delete():

Dynamically run a DELETE operation. (With automatic parameter binding.)

table.delete(modifiers: object | Function | number | true): Promise<Array<object> | object | number>

⚙️ Spec:

• modifiers (object | Function | number | true): as described in select() including any of the following properties:
  • returning ((string | Function)[], optional): as described in insert()
• Return value: where modifiers.returning clause is specified, a number indicating number of rows processed by the query, otherwise an array (the result set) for a multi-row DELETE operation (where modifiers.where isn't string or number), but an object for a single-row DELETE operation (where modifiers.where is string or number).

⚽️ Usage:

// Delete the record having primary key value of 4
await table.delete({ where: 4 });

// Delete the record having specified email, obtaining the deleted row
const deletedRow = await table.delete({ where: { email: '[email protected]' } });

// Delete all records
await table.delete(true);

table.schema():

Get the schema structure for a table. (From v0.12.0)

table.schema(): Promise<TableSchemaAPI>

⚙️ Spec:

• Return value: a TableSchemaAPI instance; the requested schema.

⚽️ Usage:

const schema = await table.schema();
console.log(schema.name());
console.log(schema.columns());