API

The Linked QL API

Here's for a quick overview of the Linked QL API:

Here we talk about the client.query() method in more detail along with other Linked QL APIs that essentially let you do the same things possible with client.query(), but this time, programmatically.

As an example of one of these APIs, a CREATE DATABASE operation...

const savepoint = await client.query('CREATE DATABASE IF NOT EXISTS database_1');

could be programmatically achieved as:

const savepoint = await client.createDatabase('database_1', { ifNotExists: true });

That said, while the createDatabase() method is associated with the base Client object, the different programmatic query APIs in Linked QL are actually organized into three hierarchical scopes:

the top-level scope (represented by the Client interface), featuring methods such as: createDatabase(), alterDatabase(), dropDatabase(), hasDatabase(), describeDatabase()
the database-level scope (represented by a certain Database interface), featuring methods such as: createTable(), alterTable(), dropTable(), hasTable(), describeTable()
the table-level scope (represented by a certain Table interface), featuring methods such as: select(), insert(), upsert(), update(), delete()

Each object provides a way to narrow in to the next; e.g. from the top-level scope to a database scope...

const database_1 = client.database('database_1');

and from there to a table scope:

const table_1 = database.table('table_1');

These APIs at play would look something like:

// Create database "database_1"
await client.createDatabase('database_1', { ifNotExists: true });

// Enter "database_1" and create a table
await client.database('database_1').createTable({
    name: 'table_1', columns: [
        { name: 'column_1', type: 'int', identity: true, primaryKey: true },
        { name: 'column_2', type: 'varchar' },
        { name: 'column_3', type: 'varchar' },
    ]
});

// Enter "table_1" and insert data
await client.database('database_1').table('table_1').insert({
    column_2: 'Column 2 test content',
    column_3: 'Column 3 test content',
});

These APIs and more are what's covered in this section.

Click on a definition to expand.

The `Client` API

Client is the top-level object for the individual database kinds in Linked QL. Each instance implements the following interface:

See content

client.query()
client.createDatabase()
client.alterDatabase()
client.dropDatabase()
client.hasDatabase()
client.describeDatabase()
client.databases()
client.database()

`client.query()`:

Run any SQL query.

client.query(sql: string, options?: Options): Promise<Savepoint | Array<object>>

⚙️ Spec:

sql (string): an SQL query.
options (Options, optional): extra parameters for the query.
Return value: a Savepoint instance when it's a CREATE, ALTER, or DROP operation, an array (the result set) when it's a SELECT query or when it's an INSERT, UPDATE, or DELETE operation that has a RETURNING clause, but a number (indicating number of rows processed by the query) when not having a RETURNING clause. Null in all other cases.

⚽️ Usage:

Run a CREATE, ALTER, or DROP operation and get back a reference to the savepoint associated with it:

const savepoint = await client.query('ALTER TABLE users RENAME TO accounts');
console.log(savepoint.versionTag); // number

await savepoint.rollback(); // true

or a SELECT query, and get back a result set:

const rows = await client.query('SELECT * FROM users WHERE id = 4');
console.log(rows.length); // 1

or an INSERT, UPDATE, or DELETE operation with a RETURNING clause, and get back a result set:

const rows = await client.query('INSERT INTO users SET name = \'John Doe\' RETURNING id');
console.log(rows.length); // 1

or an INSERT, UPDATE, or DELETE operation without a RETURNING clause, and ge back a number indicating the number of rows processed by the query:

const rowCount = await client.query('INSERT INTO users SET name = \'John Doe\'');
console.log(rowCount); // 1

Some additional parameters via options:

dialect (string, optional): the SQL dialect in use: postgres (the default) or mysql. (Details soon as to how this is treated by Linked QL.)

// Unlock certain dialect-specific clauses or conventions
const rows = await client.query('ALTER TABLE users MODIFY COLUMN id int', { dialect: 'mysql' });

values ((string | number | boolean | null | Date | object | any[])[], optional): the values for parameters in the query.
```
const rows = await client.query('SELECT * FROM users WHERE id = $1', { values: [4] });
```
description (string, optional): the description for a CREATE, ALTER, DROP operation and for the underlying savepoint they create.
```
const savepoint = await client.query('DROP DATABASE test', { description: 'No longer needed' });
```
noCreateSavepoint (boolean, optional): a flag to disable savepoint creation on a CREATE, ALTER, DROP operation.
```
await client.query('DROP DATABASE test', { noCreateSavepoint: true });
```

`client.createDatabase()`:

Dynamically run a CREATE DATABASE operation.

client.createDatabase(databaseNameOrJson: string | DatabaseSchemaSpec, options?: Options): Promise<Savepoint>

⚙️ Spec:

databaseNameOrJson (string | DatabaseSchemaSpec): the database name, or an object specifying the intended database structure to create.
options (Options, optional): as described in query().
Return value: a Savepoint instance.

⚽️ Usage:

Specify database by name:

const savepoint = await client.createDatabase('database_1', { description: 'Just testing database creation' });

or by a schema object, with an optional list of tables to be created along with it. (Each listed table corresponding to TableSchemaSpec (in schema.json).):

const savepoint = await client.createDatabase({
    name: 'database_1',
    tables: [{
        name: 'table_1'
        columns: [{ name: 'column_1', type: 'int' }, { name: 'column_2', type: 'time' }]
    }]
}, { description: 'Just testing database creation' });

Some additional parameters via options:

ifNotExists (boolean, optional): a flag to conditionally create the database.

const savepoint = await client.createDatabase('database_1', { ifNotExists: true, description: 'Just testing database creation' });

`client.alterDatabase()`:

Dynamically run an ALTER DATABASE operation.

client.alterDatabase(databaseNameOrJson: string | { name: string, tables?: string[] }, callback: (databaseSchemaApi: DatabaseSchemaAPI) => void, options?: Options): Promise<Savepoint>

⚙️ Spec:

databaseNameOrJson (string | { name: string, tables?: string[] }): the database name, or an object with the name and, optionally, a list of tables to be altered along with it.
callback ((databaseSchemaApi: DatabaseSchemaAPI) => void): a function that is called with the requested schema. This can be async.
options (Options, optional): as described in query().
Return value: a Savepoint instance.

⚽️ Usage:

Specify database by name:

const savepoint = await client.alterDatabase('database_1', databaseSchemaApi => {
    databaseSchemaApi.name('database_1_new');
}, { description: 'Renaming for testing purposes' });

or by an object, with an optional list of tables to be altered along with it:

const savepoint = await client.alterDatabase({ name: 'database_1', tables: ['table_1'] }, databaseSchemaApi => {
    databaseSchemaApi.name('database_1_new');
    databaseSchemaApi.table('table_1').column('column_1').name('column_1_new');
    databaseSchemaApi.table('table_1').column('column_2').type('varchar');
}, { description: 'Renaming for testing purposes' });

`client.dropDatabase()`:

Dynamically run a DROP DATABASE operation.

client.dropDatabase(databaseName: string, options?: Options): Promise<Savepoint>

⚙️ Spec:

databaseName (string): the database name.
options (Options, optional): as described in query().
Return value: a Savepoint instance.

⚽️ Usage:

const savepoint = await client.dropDatabase('database_1', { description: 'Dropping for testing purposes' });

Some additional parameters via options:

ifExists (boolean, optional): a flag to conditionally drop the database.

const savepoint = await client.dropDatabase('database_1', { ifExists: true, description: 'Dropping for testing purposes' });

cascade (boolean, optional): a flag to force-drop the database along with its dependents.

const savepoint = await client.dropDatabase('database_1', { cascade: true, description: 'Dropping for testing purposes' });

`client.hasDatabase()`:

Check if a database exists.

client.hasDatabase(databaseName: string): Promise<Boolean>

⚙️ Spec:

databaseName (string): the database name.
Return value: Boolean.

⚽️ Usage:

const exists = await client.hasDatabase('database_1');

`client.describeDatabase()`:

Get the schema structure for a database.

client.describeDatabase(databaseName: string): Promise<DatabaseSchemaSpec>

⚙️ Spec:

databaseName (string): the database name.
Return value: an object corresponding to DatabaseSchemaSpec; the requested schema.

⚽️ Usage:

const schema = await client.describeDatabase('database_1');
console.log(schema.name);
console.log(schema.tables);

`client.databases()`:

Get a list of available databases.

client.databases(): Promise<Array<string>>

⚙️ Spec:

Return value: an array of database names.

⚽️ Usage:

const databases = await client.databases();
console.log(databases); // ['public', 'database_1', ...]

`client.database()`:

Obtain a Database instance.

client.database(databaseName: string): Database

⚙️ Spec:

databaseName (string): the database name.
Return value: a Database instance.

⚽️ Usage:

const database = client.database('database_1');

The `Database` API

Database is the API for database-level operations. This object is obtained via client.database()

See content

database.name
database.createTable()
database.alterTable()
database.dropTable()
database.hasTable()
database.describeTable()
database.tables()
database.table()
database.savepoint()

`database.name`:

The name associated with the Database instance.

database.name: (string, readonly)

⚽️ Usage:

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

`database.createTable()`:

Dynamically run a CREATE TABLE operation.

database.createTable(tableJson: TableSchemaSpec, options?: Options): Promise<Savepoint>

⚙️ Spec:

tableJson (TableSchemaSpec): an object specifying the intended table structure to create.
options (Options, optional): as described in query().
Return value: a Savepoint instance.

⚽️ Usage:

const savepoint = await database.createTable({
    name: 'table_1'
    columns: [
        { name: 'column_1', type: 'int' }, 
        { name: 'column_2', type: 'time' }
    ]
}, { description: 'Just testing table creation' });

Some additional parameters via options:

ifNotExists (boolean, optional): a flag to conditionally create the table.

const savepoint = await database.createTable({
    name: 'table_1'
    columns: [ ... ]
}, { ifNotExists: true, description: 'Just testing table creation' });

`database.alterTable()`:

Dynamically run an ALTER TABLE operation.

database.alterTable(tableName: string, callback: (tableSchemaApi: TableSchemaAPI) => void, options?: Options): Promise<Savepoint>

⚙️ Spec:

tableName (string): the table name.
callback ((tableSchemaApi: TableSchemaAPI) => void): a function that is called with the requested table schema. This can be async.
options (Options, optional): as described in query().
Return value: a Savepoint instance.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    tableSchemaApi.name('table_1_new');
    tableSchemaApi.column('column_1').type('int');
    tableSchemaApi.column('column_2').drop();
}, { description: 'Renaming for testing purposes' });

`database.dropTable()`:

Dynamically run a DROP TABLE operation.

database.dropTable(tableName: string, options?: Options): Promise<Savepoint>

⚙️ Spec:

tableName (string): the table name.
options (Options, optional): as described in query().
Return value: a Savepoint instance.

⚽️ Usage:

const savepoint = await database.dropTable('table_1', { description: 'Dropping for testing purposes' });

Some additional parameters via options:

ifExists (boolean, optional): a flag to conditionally drop the table.

const savepoint = await database.dropTable('table_1', { ifExists: true, description: 'Dropping for testing purposes' });

cascade (boolean, optional): a flag to force-drop the table along with its dependents.

const savepoint = await database.dropTable('table_1', { cascade: true, description: 'Dropping for testing purposes' });

`database.hasTable()`:

Check if a table exists.

database.hasTable(tableName: string): Promise<Boolean>

⚙️ Spec:

tableName (string): the table name.
Return value: Boolean.

⚽️ Usage:

const exists = await database.hasTable('table_1');

`database.describeTable()`:

Get the schema structure for a table.

database.describeTable(tableName: string): Promise<TableSchemaSpec>

⚙️ Spec:

tableName (string): the table name.
Return value: an object corresponding to TableSchemaSpec; the requested schema.

⚽️ Usage:

const schema = await database.describeTable('table_1');
console.log(schema.name);
console.log(schema.columns);

`database.tables()`:

Get a list of available tables.

database.tables(): Promise<Array<string>>

⚙️ Spec:

Return value: an array of table names.

⚽️ Usage:

const tables = await database.tables();
console.log(tables); // ['table_1', 'table_2', ...]

`database.table()`:

Obtain a Table instance.

database.table(tableName: string): Table

⚙️ Spec:

tableName (string): the table name.
Return value: a Table instance.

⚽️ Usage:

const table = database.table('table_1');

`database.savepoint()`:

Obtain the next available savepoint for given database.

database.savepoint(options?: { direction: string }): Savepoint

⚙️ Spec:

options ({ direction: string }, optional): extra paramters for the method.
Return value: a Savepoint instance.

⚽️ Usage:

const savepoint = await database.savepoint();
console.log(savepoint.versionTag); // number

await savepoint.rollback(); // true

Some additional parameters via options:

direction (string, optional): the direction of lookup - either back in time: backward (the default), or forward in time: forward.

const savepoint = await database.savepoint({ direction: 'forward' });
console.log(savepoint.versionTag); // number

await savepoint.rollback(); // true

The `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.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);

The `Savepoint` API

Savepoint is the object representation of a database's savepoint. This object is obtained either via database.savepoint() or via a CREATE, ALTER, or DROP operation.

See content

savepoint.id
savepoint.databaseTag
savepoint.versionTag
savepoint.versionMax
savepoint.cursor
savepoint.description
savepoint.savepointDate
savepoint.rollbackDate
savepoint.rollbackEffect
savepoint.isNextPointInTime()
savepoint.rollback()
savepoint.toJSON()
savepoint.schema()
savepoint.name()

`savepoint.id`:

The UUID associated with the savepoint.

savepoint.id: (UUID, readonly)

⚽️ Usage:

const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.id); // f740d66a-df5f-4a34-a281-8ef3ba6fe754

`savepoint.databaseTag`:

The subject database's generic identifier that transcends name changes.

savepoint.databaseTag: (string, readonly)

⚽️ Usage:

Consider a database's generic identifier before and after a name change:

// Before name change
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.databaseTag); // db:18m6z

// Name change
await client.alterDatabase('test_db', schema => schema.name('test_db_new'));

// Now even after name change
const savepoint = await client.database('test_db_new').savepoint();
console.log(savepoint.databaseTag); // db:18m6z

`savepoint.versionTag`:

The savepoint's version tag.

savepoint.versionTag: (number, readonly)

⚽️ Usage:

// Version 1
const savepoint = await client.createDatabase({
    name: 'test_db',
    tables: [{
        name: 'test_tbl1',
        columns: [],
    }]
});
console.log(savepoint.versionTag); // 1

// Version 2
const savepoint = await client.database('test_db').createTable({
    name: 'test_tbl2',
    columns: [],
});
console.log(savepoint.versionTag); // 2

// Version 2 currently
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.versionTag); // 2

`savepoint.versionMax`:

The database's peak version regardless of its current rollback level.

savepoint.versionMax: (number, readonly)

⚽️ Usage:

const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.versionTag); // 2
console.log(savepoint.versionMax); // 2

await savepoint.rollback();

const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.versionTag); // 1
console.log(savepoint.versionMax); // 2

`savepoint.cursor`:

The savepoint's current level in the database's list of available savepoints.

savepoint.cursor: (string, readonly)

⚽️ Usage:

const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.cursor); // 1/2

`savepoint.description`:

The description for the changes associated with the savepoint.

savepoint.description: (string, readonly)

⚽️ Usage:

const savepoint = await client.database('test_db').createTable({
    name: 'test_tbl2',
    columns: [],
}, { description: 'Create test_tbl2' });
console.log(savepoint.description); // Create test_tbl2

const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.description); // Create test_tbl2

`savepoint.savepointDate`:

The savepoint's creation date.

savepoint.savepointDate: (Date, readonly)

⚽️ Usage:

const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.savepointDate); // 2024-07-20T15:31:06.096Z

`savepoint.rollbackDate`:

The savepoint's rollback date.

savepoint.rollbackDate: (Date, readonly)

⚽️ Usage:

const savepoint = await client.database('test_db').createTable({
    name: 'test_tbl2',
    columns: [],
}, { description: 'Create test_tbl2' });
console.log(savepoint.rollbackDate); // null

await savepoint.rollback();
console.log(savepoint.rollbackDate); // 2024-07-20T15:31:06.096Z

// Find the same savepoint with a forward lookup
const savepoint = await client.database('test_db').savepoint({ direction: 'forward' });
console.log(savepoint.rollbackDate); // 2024-07-20T15:31:06.096Z

`savepoint.rollbackEffect`:

A single-word summary of the effect that rolling back to this savepoint will have on subject DB.

savepoint.rollbackEffect: (string, readonly)

⚽️ Usage:

Will rolling back to given savepoint mean dropping or re-creating the subject database?:

For a create operation...

const savepoint = await client.createDatabase('test_db', { descripton: 'Create db' });

Rolling back will mean dropping the DB:

console.log(savepoint.descripton); // Create db
console.log(savepoint.rollbackEffect); // DROP

// Drop DB
console.log(savepoint.rollbackEffect); // DROP
await savepoint.rollback();

Having rolled back, rolling forward will mean a re-creation of the DB:

// Find the same savepoint with a forward lookup
const savepoint = await client.database('test_db').savepoint({ direction: 'forward' });
// Now rolling back will mean re-creating the DB
console.log(savepoint.descripton); // Create db
console.log(savepoint.rollbackEffect); // CREATE

But note that table-level create/drop operations always only have an ALTER effect on parent DB:

// Create table - which translates to a DB "alter" operation
const savepoint = await client.database('test_db').createTable({
    name: 'test_tbl2',
    columns: [],
}, { description: 'Create test_tbl2' });
// Rolling back will mean dropping the table - which will still translate to a DB "alter" operation
console.log(savepoint.descripton); // Create test_tbl2
console.log(savepoint.rollbackEffect); // ALTER

// Drop DB
await savepoint.rollback();
console.log(savepoint.rollbackEffect); // ALTER

// Find the same savepoint with a forward lookup
const savepoint = await client.database('test_db').savepoint({ direction: 'forward' });
// Now rolling back will mean re-creating the table - which will still translate to a DB "alter" operation
console.log(savepoint.descripton); // Create test_tbl2
console.log(savepoint.rollbackEffect); // ALTER

`savepoint.rollbackQuery`:

A query preview of the rollback.

savepoint.rollbackQuery: ({ toString(): string }, readonly)

⚽️ Usage:

You get a query instance that is toString()able:

For a create operation...

const savepoint = await client.createDatabase('test_db', { descripton: 'Create db' });

Rolling back will mean dropping the DB:

console.log(savepoint.rollbackQuery.toString()); // DROP SCHEMA test_db CASCADE

`savepoint.isNextPointInTime()`:

Check if the savepoint is the next actual point in time for the database.

savepoint.isNextPointInTime(): Promise<boolean>

⚙️ Spec:

Return value: boolean.

⚽️ Usage:

For a new operation, that would be true:

const dbCreationSavepoint = await client.createDatabase('test_db');
console.log(await dbCreationSavepoint.isNextPointInTime()); // true

But after having performed more operations, that wouldn't be:

const tblCreationSavepoint = await client.database('test_db').createTable({
    name: 'test_tbl',
    columns: [{
        name: 'id',
        type: 'int'
    }]
});
console.log(await tblCreationSavepoint.isNextPointInTime()); // true
console.log(await dbCreationSavepoint.isNextPointInTime()); // false

Rollback table creation and test dbCreationSavepoint's position again:

await tblCreationSavepoint.rollback();
console.log(await tblCreationSavepoint.isNextPointInTime()); // false
console.log(await dbCreationSavepoint.isNextPointInTime()); // true

`savepoint.rollback()`:

Rollback all changes associated with given savepoint.

savepoint.rollback(): Promise<boolean>

⚙️ Spec:

Return value: boolean.

⚽️ Usage:

Create database and rollback:

// Create DB
const savepoint = await client.createDatabase('test_db', { descripton: 'Create db' });
// Roll back - which means drop the DB
await savepoint.rollback();

Undo the rollback; i.e. roll forward:

// Find the same savepoint with a forward lookup
const savepoint = await client.database('test_db').savepoint({ direction: 'forward' });
// Roll back - which means re-create the DB
await savepoint.rollback();

`savepoint.toJSON()`:

Get a plain object representation of the savepoint.

savepoint.toJSON(): object

⚙️ Spec:

Return value: an object of the form { id: string, name: string, databaseTag: string, versionTag: number, versionMax: number, cursor: string, description: string, savepointDate: Date, rollbackDate: Date | null }.

⚽️ Usage:

const savepoint = await client.createDatabase('test_db', { descripton: 'Create db' });
console.log(savepoint.toJSON());

`savepoint.schema()`:

Get the subject DB's schema snapshot at this point in time.

savepoint.schema(): object

⚙️ Spec:

Return value: an object corresponding to DatabaseSchemaSpec (in schema.json).

⚽️ Usage:

const savepoint = await client.database('test_db').createTable({
    name: 'test_tbl',
    columns: [{
        name: 'id',
        type: 'int'
    }]
});
console.log(savepoint.schema());

const savepoint = await client.database('test_db').savepoint();
await savepoint.schema();

`savepoint.name()`:

Get the subject database's name.

savepoint.name(postRollback?: boolean): string

⚙️ Spec:

postRollback (boolean, optional): in case a name change was captured in the savepoint, whether to return the database's post-rollback name. Otherwise the database's active, pre-rollback name is returned.
Return value: the database name.

⚽️ Usage:

// Name change
const savepoint = await client.alterDatabase('test_db', schema => schema.name('test_db_new'));
// The database's active, pre-rollback name
console.log(savepoint.name()); // test_db_new
// The database's post-rollback name
console.log(savepoint.name(true)); // test_db

The `DatabaseSchemaAPI` API

DatabaseSchemaAPI is the programmatic interface to DatabaseSchemaSpec (in schema.json). This object is obtained via client.alterDatabase()'s callback function.

DatabaseSchemaAPI inherits from AbstractSchemaAPI.

See content

databaseSchemaApi.name()
databaseSchemaApi.table()

Inherited:
abstractSchemaApi.toJSON()
abstractSchemaApi.toString()
abstractSchemaApi.keep()
abstractSchemaApi.drop()

`databaseSchemaApi.name()`:

Set or get the database name. (Overrides abstractSchemaApi.name().)

databaseSchemaApi.name(name?: string): this

⚙️ Spec:

name (string, optional): when provided, sets the database name. When ommitted, gets the database name returned.
Return value: Identifier - the current database name, or this - the databaseSchemaApi instance.

⚽️ Usage:

Rename the database:

const savepoint = await client.alterDatabase('database_1', databaseSchemaApi => {
    // Inspect existing name
    console.log(databaseSchemaApi.name().toJSON()); // database_1
    // Rename
    databaseSchemaApi.name('new_database_1');
}, { description: 'Renaming for testing purposes' });

`databaseSchemaApi.table()`:

Add a table to the database or get an existing one.

databaseSchemaApi.table(tableNameOrJson: string | TableSchemaSpec): TableSchemaAPI

⚙️ Spec:

tableNameOrJson (string | TableSchemaSpec): when a string, the name of a table to get. When an object, an object that defines a new table to create.
Return value: TableSchemaAPI - the table schema requested or the one just added.

⚽️ Usage:

const savepoint = await client.alterDatabase('database_1', databaseSchemaApi => {
    // Drop existing table_1
    databaseSchemaApi.table('table_1').drop();
    // Add table_2
    databaseSchemaApi.table({
        name: 'table_2',
        columns: [],
    });
}, { description: 'Altering for testing purposes' });

The `TableSchemaAPI` API

TableSchemaAPI is the programmatic interface to TableSchemaSpec (in schema.json). This object is obtained via databaseSchemaApi.table() and database.alterTable()'s callback function.

TableSchemaAPI inherits from AbstractSchemaAPI.

See content

tableSchemaApi.name()
tableSchemaApi.column()
tableSchemaApi.primaryKey()
tableSchemaApi.constraint()
tableSchemaApi.index()

Inherited:
abstractSchemaApi.toJSON()
abstractSchemaApi.toString()
abstractSchemaApi.keep()
abstractSchemaApi.drop()

`tableSchemaApi.name()`:

Set or get the table name. (Overrides abstractSchemaApi.name().)

tableSchemaApi.name(name?: string | string[]): this

⚙️ Spec:

name (string | string[], optional): when provided, sets the table name. Accepts a two-part array for a fully-qualified table name. When ommitted, gets the table name returned.
Return value: Identifier - the current table name, or this - the tableSchemaApi instance.

⚽️ Usage:

Rename the table:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Inspect existing name
    console.log(tableSchemaApi.name().toJSON()); // table_1
    // Rename
    tableSchemaApi.name('new_table_1');
}, { description: 'Renaming for testing purposes' });

Rename the table - fully-qualified:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    tableSchemaApi.name(['database_1', 'new_table_1']);
}, { description: 'Renaming for testing purposes' });

Change the qualifier - moving the table to a different database:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    tableSchemaApi.name(['database_4', 'new_table_1']);
}, { description: 'Renaming for testing purposes' });

`tableSchemaApi.column()`:

Add a column to the table or get an existing one.

tableSchemaApi.column(columnNameOrJson: string | ColumnSchemaSpec): ColumnSchemaAPI

⚙️ Spec:

columnNameOrJson (string | ColumnSchemaSpec): when a string, the name of a column to get. When an object, an object that defines a new column to create.
Return value: ColumnSchemaAPI - the column requested or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Obtain existing column_1 and modify its type attribute
    tableSchemaApi.column('column_1').type('int');
    // Add column_2
    tableSchemaApi.column({
        name: 'column_2',
        type: ['varchar', 50],
    });
}, { description: 'Altering for testing purposes' });

`tableSchemaApi.primaryKey()`:

Add a Primary Key constraint to the table or get the existing one. (Translates to the SQL PRIMARY KEY constraint.)

tableSchemaApi.primaryKey(constraintJson?: TablePrimaryKeySchemaSpec): TablePrimaryKeySchemaAPI

⚙️ Spec:

constraintJson (TablePrimaryKeySchemaSpec, optional): when provided, an object that defines a new Primary Key to create, specifying the intended Primary Key column(s), and optionally, a constraint name. When ommitted, gets the PRIMARY_KEY instance on the table returned if exists.
Return value: TablePrimaryKeySchemaAPI - the existing PRIMARY_KEY instance requested or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // See if there's one set and undo that
    if (!tableSchemaApi.primaryKey()) {
        // Add a Primary Key constraint on columns 2 and 3
        tableSchemaApi.primaryKey({ columns: ['column_2', 'column_3'] });
    }
}, { description: 'Altering for testing purposes' });

`tableSchemaApi.constraint()`:

Add a Primary Key, Foreign Key, Unique Key, or Check constraint to the table or get an existing one. (Provides a unified way to set/get table constraints.)

tableSchemaApi.constraint(constraintNameOrJson: string | TableConstraintSchemaType): TableConstraintSchemaAPI

⚙️ Spec:

constraintNameOrJson (string | TableConstraintSchemaType): when a string, the name of a constraint to get. When an object, an object that defines a new constraint to create.
Return value: TableConstraintSchemaAPI - the constraint requested or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Obtain existing constraint_1 and modify its columns list
    tableSchemaApi.constraint('constraint_1').columns(['id', 'bio']);
    // Add constraint_2
    tableSchemaApi.constraint({
        type: 'PRIMARY_KEY',
        name: 'constraint_2',
        columns: ['id'],
    });
}, { description: 'Altering for testing purposes' });

Note that when a constraint name is ommitted, one is automatically generated for you:

// Add an anonymous constraint
const constraint = tableSchemaApi.constraint({
    type: 'PRIMARY_KEY',
    columns: ['id'],
});
// Inspect is auto-generated name
console.log(constraint.name()); // auto_name_25kjd

`tableSchemaApi.index()`:

Add a Fulltext or Spartial Index to the table or get an existing one.

tableSchemaApi.index(indexNameOrJson: string | IndexSchemaSpec): IndexSchema

⚙️ Spec:

indexNameOrJson (string | IndexSchemaSpec): when a string, the name of an index to get. When an object, an object that defines a new index to create.
Return value: IndexSchema - the index requested or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Obtain existing index_1 and modify its columns list
    tableSchemaApi.index('index_1').columns(['id', 'bio']);
    // Add index_2
    tableSchemaApi.index({
        type: 'FULLTEXT',
        name: 'index_2',
        columns: ['id'],
    });
}, { description: 'Altering for testing purposes' });

Note that when an index name is ommitted, one is automatically generated for you:

// Add an anonymous index
const index = tableSchemaApi.index({
    type: 'FULLTEXT',
    columns: ['id'],
});
// Inspect is auto-generated name
console.log(index.name()); // auto_name_4gkbc

Table Constraint Schema APIs

The getter/setter APIs to the various table-level constraints.

type TableConstraintSchemaAPI = TablePrimaryKeySchemaAPI | TableForeignKeySchemaAPI | TableUniqueKeySchemaAPI | TableCheckConstraintSchemaAPI

See details

interface TablePrimaryKeySchemaAPI extends PrimaryKeySchemaAPI {
    // Set/get the constraint columns
    columns(value?: string[]): Array;
}

Jump to PrimaryKeySchemaAPI

interface TableForeignKeySchemaAPI extends ForeignKeySchemaAPI {
    // Set/get the constraint columns
    columns(value?: string[]): Array;
}

Jump to ForeignKeySchemaAPI

interface TableUniqueKeySchemaAPI extends UniqueKeySchemaAPI {
    // Set/get the constraint columns
    columns(value?: string[]): Array;
}

Jump to UniqueKeySchemaAPI

interface TableCheckConstraintSchemaAPI extends CheckConstraintSchemaAPI {
    // Get the constraint columns
    columns(): Array;
}

Jump to CheckConstraintSchemaAPI

The `ColumnSchemaAPI` API

ColumnSchemaAPI is the programmatic interface to ColumnSchemaSpec (in schema.json). This object is obtained via tableSchemaApi.column().

ColumnSchemaAPI inherits from AbstractSchemaAPI.

See content

columnSchemaApi.type()
columnSchemaApi.primaryKey()
columnSchemaApi.foreignKey()
columnSchemaApi.uniqueKey()
columnSchemaApi.check()
columnSchemaApi.default()
columnSchemaApi.expression()
columnSchemaApi.identity()
columnSchemaApi.notNull()
columnSchemaApi.null()
columnSchemaApi.autoIncrement()
columnSchemaApi.onUpdate()
columnSchemaApi.constraint()

Inherited:
abstractSchemaApi.name()
abstractSchemaApi.toJSON()
abstractSchemaApi.toString()
abstractSchemaApi.keep()
abstractSchemaApi.drop()

`columnSchemaApi.type()`:

Set the column type or get the current value.

tableSchemaApi.type(typeJson?: string | string[]): ColumnTypeSchema

⚙️ Spec:

typeJson (string | string[], optional): when provided, sets the column type. Accepts a two-part array for a fully-qualified type. When ommitted, gets the current column type returned.
Return value:ColumnTypeSchema - the current column type, or this - the columnSchemaApi instance.

⚽️ Usage:

Obtain a column and change its type:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // New type
    tableSchemaApi.column('column_1').type(['varchar', 255]);
    // Current type as JSON
    console.log(tableSchemaApi.column('column_1').type().toJSON()); // ['varchar', 255]
    // Current type as SQL
    console.log(tableSchemaApi.column('column_1').type().toString()); // varchar(255)
}, { description: 'Altering for testing purposes' });

`columnSchemaApi.primaryKey()`:

Designate the column as Primary Key for the table or get the column's current PRIMARY_KEY instance. (Translates to the SQL PRIMARY KEY constraint.)

columnSchemaApi.primaryKey(constraintToggleOrJson?: boolean | PrimaryKeySchemaSpec): PrimaryKeySchemaAPI

⚙️ Spec:

constraintToggleOrJson (boolean | PrimaryKeySchemaSpec, optional): when a boolean, toggles the designation of the column as Primary Key for the table. When an object, an object that specifies a constraint name. When ommitted, gets the column's PRIMARY_KEY instance returned if exists.
Return value: PrimaryKeySchemaAPI - the existing PRIMARY_KEY instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').primaryKey()) {
        // Add a Primary Key constraint on column_1
        tableSchemaApi.column('column_1').primaryKey(true);
    }
});

Note that when a constraint name is ommitted, one is automatically generated for you:

// Inspect the auto-generated name
console.log(tableSchemaApi.column('column_1').primaryKey().name()); // auto_name_25kjd

`columnSchemaApi.foreignKey()`:

Add the FOREIGN_KEY constraint type to the column or get the column's current FOREIGN_KEY instance. (Translates to the SQL FOREIGN KEY constraint.)

columnSchemaApi.foreignKey(constraintJson?: ForeignKeySchemaSpec): ForeignKeySchemaAPI

⚙️ Spec:

constraintJson (ForeignKeySchemaSpec, optional): when provided, an object that defines a new Foreign Key to create, specifying, among other things, the target table and target columns, and optionally, a constraint name. When ommitted, gets the column's FOREIGN_KEY instance returned if exists.
Return value: ForeignKeySchemaAPI - the existing FOREIGN_KEY instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').foreignKey()) {
        // Add a Foreign Key constraint on column_1
        tableSchemaApi.column('column_1').foreignKey({
            targetTable: 'table_2',
            targetColumns: ['id'],
            updateRule: 'CASCADE',
        });
    }
});

Note that when a constraint name is ommitted, one is automatically generated for you:

// Inspect the auto-generated name
console.log(tableSchemaApi.column('column_1').foreignKey().name()); // auto_name_25kjd

`columnSchemaApi.uniqueKey()`:

Add the UNIQUE_KEY constraint type to the column or get the column's current UNIQUE_KEY instance. (Translates to the SQL UNIQUE constraint.)

columnSchemaApi.uniqueKey(constraintToggleOrJson?: boolean | UniqueKeySchemaSpec): UniqueKeySchemaAPI

⚙️ Spec:

constraintToggleOrJson (boolean | UniqueKeySchemaSpec, optional): when a boolean, toggles the existence of the UNIQUE_KEY constraint on the column. When an object, an object that defines a new constraint to create, specifying a constraint name. When ommitted, gets the column's UNIQUE_KEY instance returned if exists.
Return value: UniqueKeySchemaAPI - the existing UNIQUE_KEY instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').uniqueKey()) {
        // Add a Unique Key constraint on column_1
        tableSchemaApi.column('column_1').uniqueKey(true);
    }
});

Note that when a constraint name is ommitted, one is automatically generated for you:

// Inspect the auto-generated name
console.log(tableSchemaApi.column('column_1').uniqueKey().name()); // auto_name_25kjd

`columnSchemaApi.check()`:

Add the CHECK constraint type to the column or get the column's current CHECK constraint instance. (Translates to the SQL CHECK constraint.)

columnSchemaApi.check(constraintJson?: CheckConstaintSpec): CheckConstraintSchemaAPI

⚙️ Spec:

constraintJson (CheckConstraintSchemaSpec, optional): when provided, an object that defines a new constraint to create, specifying the intended SQL expression, and, optionally, a constraint name. When ommitted, gets the column's CHECK constraint instance returned if exists.
Return value: CheckConstraintSchemaAPI - the existing CHECK constraint instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').check()) {
        // Add a Check constraint on column_1
        tableSchemaApi.column('column_1').check({ expr: 'column_1 IS NOT NULL' });
    }
});

Note that when a constraint name is ommitted, one is automatically generated for you:

// Inspect the auto-generated name
console.log(tableSchemaApi.column('column_1').check().name()); // auto_name_25kjd

`columnSchemaApi.default()`:

Add the DEFAULT constraint type to the column or get the column's current DEFAULT constraint instance. (Translates to the SQL DEFAULT constraint.)

columnSchemaApi.default(constraintJson?: DefaultConstraintSchemaSpec): DefaultConstraintSchemaAPI

⚙️ Spec:

constraintJson (DefaultConstraintSchemaSpec, optional): when provided, an object that defines a new constraint to create, specifying the intended SQL expression, and, optionally, a constraint name. When ommitted, gets the column's DEFAULT constraint instance returned if exists.
Return value: DefaultConstraintSchemaAPI - the existing DEFAULT constraint instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').default()) {
        // Add a Default constraint on column_1
        tableSchemaApi.column('column_1').default({ expr: 'now()' });
    }
});

`columnSchemaApi.expression()`:

Add the EXPRESSION constraint type to the column or get the column's current EXPRESSION instance. (Translates to the SQL GENERATED COLUMN type.)

columnSchemaApi.expression(constraintJson?: ExpressionConstraintSchemaSpec): ExpressionConstraintSchemaAPI

⚙️ Spec:

constraintJson (ExpressionConstraintSchemaSpec, optional): when provided, an object that defines a new constraint to create, specifying the intended SQL expression, and, optionally, a constraint name. When ommitted, gets the column's EXPRESSION constraint instance returned if exists.
Return value: ExpressionConstraintSchemaAPI - the existing EXPRESSION constraint instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').expression()) {
        // Add an Expression constraint on column_1
        tableSchemaApi.column('column_1').expression({ expr: 'column_1 * 2', stored: true });
    }
});

`columnSchemaApi.identity()`:

Add the IDENTITY constraint type to the column or get the column's current IDENTITY constraint instance. (Translates to the SQL IDENTITY COLUMN type.)

columnSchemaApi.identity(constraintToggleOrJson?: boolean | IdentityConstraintSchemaSpec): IdentityConstraintSchemaAPI

⚙️ Spec:

constraintToggleOrJson (boolean | IdentityConstraintSchemaSpec, optional): when boolean, toggles the existence of the IDENTITY constraint on the column. When an object, an object that defines a new constraint to create, specifying an always rule. When ommitted, gets the column's IDENTITY constraint instance returned if exists.
Return value: IdentityConstraintSchemaAPI - the existing IDENTITY constraint instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').identity()) {
        // Add an Identity constraint on column_1
        tableSchemaApi.column('column_1').identity({ always: false });
    }
});

`columnSchemaApi.notNull()`:

Add the NOT_NULL constraint type to the column or get the column's current NOT_NULL constraint instance. (Translates to the SQL NOT NULL constraint.)

columnSchemaApi.notNull(constraintToggle?: boolean): NotNullConstraintSchemaAPIBuilder

⚙️ Spec:

constraintToggle (boolean, optional): when provided, toggles the existence of the NOT_NULL constraint on the column. When ommitted, gets the column's NOT_NULL constraint instance returned if exists.
Return value: NotNullConstraintSchemaAPIBuilder - the existing NOT_NULL constraint instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').notNull()) {
        // Add an notNull constraint on column_1
        tableSchemaApi.column('column_1').notNull(true);
    }
});

`columnSchemaApi.null()`:

Add the NULL constraint type to the column or get the column's current NULL constraint instance. (Translates to the SQL NULL constraint.)

columnSchemaApi.null(constraintToggle?: boolean): NullConstraintSchemaAPI

⚙️ Spec:

constraintToggle (boolean, optional): when provided, toggles the existence of the NULL constraint on the column. When ommitted, gets the column's NULL constraint instance returned if exists.
Return value: NullConstraintSchemaAPI - the existing NULL constraint instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').null()) {
        // Add an null constraint on column_1
        tableSchemaApi.column('column_1').null(true);
    }
});

`columnSchemaApi.autoIncrement()`:

Add the AUTO_INCREMENT constraint type to the column or get the column's current AUTO_INCREMENT constraint instance. (Translates to the MySQL-specific AUTO_INCREMENT constraint.)

columnSchemaApi.autoIncrement(constraintToggle?: boolean): AutoIncrementConstraintSchemaAPI

⚙️ Spec:

constraintToggle (boolean, optional): when provided, toggles the existence of the AUTO_INCREMENT constraint on the column. When ommitted, gets the column's AUTO_INCREMENT constraint instance returned if exists.
Return value: AutoIncrementConstraintSchemaAPI - the existing AUTO_INCREMENT constraint instance on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaAPI => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').autoIncrement()) {
        // Add an autoIncrement constraint on column_1
        tableSchemaApi.column('column_1').autoIncrement(true);
    }
});

`columnSchemaApi.onUpdate()`:

Add the ON_UPDATE clause to the column or get the column's current ON_UPDATE constraint instance. (Translates to the MySQL-specific ON UPDATE clause for timestamp/datetime columns.)

columnSchemaApi.onUpdate(constraintToggle?: OnUpdateClauseSpec): OnUpdateClauseSchemaAPI

⚙️ Spec:

constraintToggle (OnUpdateClauseSpec, optional): when provided, an object that defines a new constraint to create, specifying the intended SQL expression. When ommitted, gets the ON_UPDATE clause returned if exists.
Return value: OnUpdateClauseSchemaAPI - the existing ON_UPDATE clause on the column or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Be sure that this doesn't already exist on column_1
    if (!tableSchemaApi.column('column_1').onUpdate()) {
        // Add an autoIncrement constraint on column_1
        tableSchemaApi.column('column_1').onUpdate('CURRENT_TIMESTAMP');
    }
});

`columnSchemaApi.constraint()`:

Add a Primary Key, Foreign Key, Unique Key, Check, or other constraint, to the column or get an existing one. (Provides a unified way to set/get column constraints.)

columnSchemaApi.constraint(constraintType: string, constraintToggleOrJson?: boolean | object): ColumnConstraintSchemaAPI

columnSchemaApi.constraint(constraintJson: ColumnConstraintSchemaType): ColumnConstraintSchemaAPI

⚙️ Spec:

constraintType (string): One of PRIMARY_KEY, FOREIGN_KEY, UNIQUE_KEY, CHECK, DEFAULT, EXPRESSION, NOT_NULL, NULL, IDENTITY, AUTO_INCREMENT, ON_UPDATE. When provided as only argument, gets the existing constraint on the column returned. When in conjucntion with constraintToggleOrJson, gets the constraint added to the column.
constraintToggleOrJson (boolean | ColumnConstraintSchemaType, optional): as explained for constraintToggle/constraintJson in the individual constraint sections above.
constraintJson (ColumnConstraintSchemaType): as explained for constraintJson in the individual constraint sections above.
Return value: ColumnConstraintSchemaAPI - the constraint requested or the one just added.

⚽️ Usage:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    const col1 = tableSchemaApi.column('column_1');
    // See if we already have a PRIMARY_KEY constraint on the column. Create one if not
    if (!col1.constraint('PRIMARY_KEY')) {
        // Add PRIMARY_KEY
        col1.constraint('PRIMARY_KEY', true);
        // Or: col1.constraint({ type: 'PRIMARY_KEY' });
    }
});

Column Constraint Schema APIs

The getter/setter APIs to the various column-level constraints.

type ColumnConstraintSchemaAPI = PrimaryKeySchemaAPI | ForeignKeySchemaAPI | UniqueKeySchemaAPI | CheckConstraintSchemaAPI | DefaultConstraintSchemaAPI | ExpressionConstraintSchemaAPI | IdentityConstraintSchemaAPI | NotNullConstraintSchemaAPI | NullConstraintSchemaAPI | AutoIncrementConstraintSchemaAPI | OnUpdateClauseSchemaAPI

See details

interface PrimaryKeySchemaAPI extends AbstractSchemaAPI {}

interface ForeignKeySchemaAPI extends AbstractSchemaAPI {
    // Set/get the target table
    targetTable(value?: string | string[]): Identifier;
    // Set/get the target columns
    targetColumns(value?: string[]): Array;
    // Set/get the match rule
    matchRule(value?: string): string;
    // Set/get the update rule
    updateRule(value?: string | { rule: string, columns: string[] }): string | { rule: string, columns: string[] };
    // Set/get the delete rule
    deleteRule(value?: string | { rule: string, columns: string[] }): string | { rule: string, columns: string[] };
}

interface UniqueKeySchemaAPI extends AbstractSchemaAPI {}

interface CheckConstraintSchemaAPI extends AbstractSchemaAPI {
    // Set/get the SQL expression
    expr(value?: string): string;
}

interface DefaultConstraintSchemaAPI extends AbstractSchemaAPI {
    // Set/get the SQL expression
    expr(value?: string): string;
}

interface ExpressionConstraintSchemaAPI extends AbstractSchemaAPI {
    // Set/get the SQL expression
    expr(value?: string): string;
    // Set/get the "stored" false
    stored(value?: boolean): boolean;
}

interface IdentityConstraintSchemaAPI extends AbstractSchemaAPI {
    // Set/get the "always" rule
    always(value?: boolean): boolean;
}

interface NotNullConstraintSchemaAPI extends AbstractSchemaAPI {}

interface NullConstraintSchemaAPI extends AbstractSchemaAPI {}

interface AutoIncrementConstraintSchemaAPI extends AbstractSchemaAPI {}

interface OnUpdateClauseSchemaAPI extends AbstractSchemaAPI {
    // Set/get the SQL expression
    expr(value?: string): string;
}

Jump to AbstractSchemaAPI

The `AbstractSchemaAPI` API

AbstractSchema is a base class inheritted by all Schema APIs - e.g. DatabaseSchemaAPI, TableSchemaAPI, ColumnSchemaAPI.

See content

abstractSchemaApi.name()
abstractSchemaApi.toJSON()
abstractSchemaApi.toString()
abstractSchemaApi.keep()
abstractSchemaApi.drop()

`abstractSchemaApi.name()`:

Set or get the name the schema instance.

instance.name(value?: string): string | this

⚙️ Spec:

value (string, optional): when provided, the name of the schema instance. When ommitted, returns the current name.
Return value: string - the current name, or this - the schema instance.

⚽️ Usage:

Set or get the name of a ColumnSchemaAPI instance:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    // Get the name
    console.log(tableSchemaApi.column('column_1').name()); // column_1
    // Rename
    tableSchemaApi.column('column_2').name('new_column_2');
});

`abstractSchemaApi.toJSON()`:

Render the Schema instance to a JSON object.

instance.toJSON(): object

⚙️ Spec:

Return value: an object corresponding to the instance's JSON equivalent in schema.json.

⚽️ Usage:

Render a TableSchemaAPI to JSON:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    tableSchemaApi.column('column_1').primaryKey(true); // Designate existing column "column_1" as primary key
    tableSchemaApi.column('column_2'); // Drop index_2

    // Now inspect what you've done so far
    console.log(tableSchemaApi.toJSON());
});

`abstractSchemaApi.toString()`:

Render the Schema instance to SQL.

instance.toString(): string

⚙️ Spec:

Return value: an SQL representation of the instance.

⚽️ Usage:

Render a TableSchemaAPI to SQL:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    tableSchemaApi.column('column_1').primaryKey(true); // Designate existing column "column_1" as primary key
    tableSchemaApi.column('column_2'); // Drop index_2

    // Now inspect what you've done so far
    console.log(tableSchemaApi.toString());
});

`abstractSchemaApi.keep()`:

Specify whether to keep or drop the schema instance, or get the current keep status.

instance.keep(toggle?: boolean): this

⚙️ Spec:

toggle (boolean, optional): when provided, toggles the keep status of the schema. When ommitted returns the current keep status of the schema.
Return value: boolean - the current status, or this - the schema instance.

⚽️ Usage:

Drop a Column:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    tableSchemaApi.column('column_2').keep(false);
});

`abstractSchemaApi.drop()`:

Set the schema instance to the keep === false state.

instance.drop(): this

⚙️ Spec:

Return value: this - the schema instance.

⚽️ Usage:

Drop a Column:

const savepoint = await database.alterTable('table_1', tableSchemaApi => {
    tableSchemaApi.column('column_2').drop();
});

API

The Linked QL API

The Client API

client.query():

client.createDatabase():

client.alterDatabase():

client.dropDatabase():

client.hasDatabase():

client.describeDatabase():

client.databases():

client.database():

The Database API

database.name:

database.createTable():

database.alterTable():

database.dropTable():

database.hasTable():

database.describeTable():

database.tables():

database.table():

database.savepoint():

The Table API

table.name:

table.count():

table.select():

table.insert():

table.upsert():

table.update():

table.delete():

The Savepoint API

savepoint.id:

savepoint.databaseTag:

savepoint.versionTag:

savepoint.versionMax:

savepoint.cursor:

savepoint.description:

savepoint.savepointDate:

savepoint.rollbackDate:

savepoint.rollbackEffect:

savepoint.rollbackQuery:

savepoint.isNextPointInTime():

savepoint.rollback():

savepoint.toJSON():

savepoint.schema():

savepoint.name():

The DatabaseSchemaAPI API

databaseSchemaApi.name():

databaseSchemaApi.table():

The TableSchemaAPI API

tableSchemaApi.name():

tableSchemaApi.column():

tableSchemaApi.primaryKey():

tableSchemaApi.constraint():

tableSchemaApi.index():

Table Constraint Schema APIs

The ColumnSchemaAPI API

columnSchemaApi.type():

columnSchemaApi.primaryKey():

columnSchemaApi.foreignKey():

columnSchemaApi.uniqueKey():

columnSchemaApi.check():

columnSchemaApi.default():

columnSchemaApi.expression():

columnSchemaApi.identity():

columnSchemaApi.notNull():

columnSchemaApi.null():

columnSchemaApi.autoIncrement():

columnSchemaApi.onUpdate():

columnSchemaApi.constraint():

Column Constraint Schema APIs

The AbstractSchemaAPI API

abstractSchemaApi.name():

abstractSchemaApi.toJSON():

abstractSchemaApi.toString():

abstractSchemaApi.keep():

abstractSchemaApi.drop():

Clone this wiki locally

The `Client` API

`client.query()`:

`client.createDatabase()`:

`client.alterDatabase()`:

`client.dropDatabase()`:

`client.hasDatabase()`:

`client.describeDatabase()`:

`client.databases()`:

`client.database()`:

The `Database` API

`database.name`:

`database.createTable()`:

`database.alterTable()`:

`database.dropTable()`:

`database.hasTable()`:

`database.describeTable()`:

`database.tables()`:

`database.table()`:

`database.savepoint()`:

The `Table` API

`table.name`:

`table.count()`:

`table.select()`:

`table.insert()`:

`table.upsert()`:

`table.update()`:

`table.delete()`:

The `Savepoint` API

`savepoint.id`:

`savepoint.databaseTag`:

`savepoint.versionTag`:

`savepoint.versionMax`:

`savepoint.cursor`:

`savepoint.description`:

`savepoint.savepointDate`:

`savepoint.rollbackDate`:

`savepoint.rollbackEffect`:

`savepoint.rollbackQuery`:

`savepoint.isNextPointInTime()`:

`savepoint.rollback()`:

`savepoint.toJSON()`:

`savepoint.schema()`:

`savepoint.name()`:

The `DatabaseSchemaAPI` API

`databaseSchemaApi.name()`:

`databaseSchemaApi.table()`:

The `TableSchemaAPI` API

`tableSchemaApi.name()`:

`tableSchemaApi.column()`:

`tableSchemaApi.primaryKey()`:

`tableSchemaApi.constraint()`:

`tableSchemaApi.index()`:

The `ColumnSchemaAPI` API

`columnSchemaApi.type()`:

`columnSchemaApi.primaryKey()`:

`columnSchemaApi.foreignKey()`:

`columnSchemaApi.uniqueKey()`:

`columnSchemaApi.check()`:

`columnSchemaApi.default()`:

`columnSchemaApi.expression()`:

`columnSchemaApi.identity()`:

`columnSchemaApi.notNull()`:

`columnSchemaApi.null()`:

`columnSchemaApi.autoIncrement()`:

`columnSchemaApi.onUpdate()`:

`columnSchemaApi.constraint()`:

The `AbstractSchemaAPI` API

`abstractSchemaApi.name()`:

`abstractSchemaApi.toJSON()`:

`abstractSchemaApi.toString()`:

`abstractSchemaApi.keep()`:

`abstractSchemaApi.drop()`: