@linked-db/linked-ql
Advanced tools
A query client that extends standard SQL with new syntax sugars and enables auto-versioning capabilities on any database
âď¸ Save the overhead working with SQL and structured data - from the time and effort spent figuring out relational queries to the labour managing schemas! Try a modern, simplistic take on SQL and databases in general!
Linked QL is a DB query client that simplfies how you interact with your database and manage your schemas.
đĽ Takes the ORM and friends out of the way and let's you just write SQL, but SQL that you will actually enjoy. (Linked QL extends standard SQL with new syntax sugars that let you write relational queries in less than half the code and without a single JOIN clause.)
âĄď¸ Takes the process out of schema management and lets you just ALTER away your DB, but in a safety net. (Linked QL extends your DB behind the scenes to automatically version each edit you make and have them kept as "savepoints" that you can always rollback to.)
đĽ Brings the "schema-as-code" paradigm to its true meaning and essentially lets you have your entire DB structure go in a single schema.json file that you edit in-place, as against the "hundreds of migration files" story. (Linked QL essentially rewrites that story.)
It comes as a small library and is usable over your DB of choice - from the server-side Postgres and MySQL, to the client-side IndexedDB, to the plain JSON object!
Jump to sections and features:
Install Linked QL:
npm install @linked-db/linked-ql
Obtain the Linked QL client for your target database:
For SQL databases, install the regular SQL client you use for your DB. (Typically, pg for Postgres, mysql2 for MySQL databases.)
Given a Postgres DB, install the pg client:
npm install pg
Use Linked QL as a wrapper over that:
// Import pg and LinkedQl
import pg from 'pg';
import LinkedQl from '@linked-db/linked-ql/sql';
// Connect pg
const pgClient = new pg.Client({
host: 'localhost',
port: 5432,
});
await pgClient.connect();
// Use LinkedQl as a wrapper over that
const client = new LinkedQl(pgClient, { dialect: 'postgres' });
For the client-side IndexedDB, import and instantiate the IDB client. (Coming soon)
// Import IDB as LinkedQl
import LinkedQl from '@linked-db/linked-ql/idb';
// Create an instance.
const client = new LinkedQl;
To work with Linked QL's in-memory object database, import and instantiate the ODB client. (Coming soon)
// Import ODB as LinkedQl
import LinkedQl from '@linked-db/linked-ql/odb';
// Create an instance.
const LinkedQlClient = new LinkedQl;
All client instances above implement the same interface:
client.query('SELECT fname, lname FROM users WHERE role = $1', { values: ['admin'] }).then(result => {
console.log(result);
});
const result = await client.query('SELECT fname, lname FROM users WHERE role = $1', { values: ['admin'] });
console.log(result);
Other APIs are covered just ahead in the API section.
đĽ Express relationships graphically.
JOINS can be good but can be a mess as they almost always obfuscate your entire query! But what if you didn't have to write JOINS to express certain relationships?
Meet Linked QL's magic path operators, a syntax extension to SQL, that lets you connect to columns on other tables without writing a single JOIN. Linked QL uses heuristics on your DB structure to figure out the details and the relevant JOINs behind the scenes.
Where you normally would write...
-- Regular SQL
SELECT title, users.fname AS author_name FROM posts
LEFT JOIN users ON users.id = posts.author
Linked QL lets you draw a path to express the relationship:
-- Linked QL
SELECT title, author ~> fname AS author_name FROM posts
And here's a scenario showing a typical schema and an example query each:
-- The users table
CREATE TABLE users (
id int primary key generated always as identity,
title varchar,
name varchar,
role int references roles (id),
created_time timestamp
);
-- The books table
CREATE TABLE books (
id int primary key generated always as identity,
title varchar,
content varchar,
author int references users (id),
created_time timestamp
);
-- Regular SQL
SELECT book.id, book.title, content, book.created_time, user.id AS author_id, user.title AS author_title, user.name AS author_name
FROM books AS book LEFT JOIN users AS user ON user.id = book.author
-- Linked QL
SELECT id, title, content, created_time, author ~> id, author ~> title, author ~> name
FROM books
⨠PRO: About 50% code, and whole namespacing exercise, now eliminated; all with zero upfront setup!
Taking that further, paths can be multi-level:
-- Linked QL
SELECT * FROM books
WHERE author ~> role ~> codename = 'admin'
and they can also be used to express incoming references:
-- Linked QL
SELECT * FROM users
WHERE author <~ books ~> title = 'Beauty and the Beast'
âĄď¸ Create, Alter, and Drop schemas without needing to worry about versioning.
Databases have historically lacked the concept of versioning, and that has seen all of the engineering work pushed down to the client application. If you've ever had to adopt a special process for defining and managing your schemas, wherein changes are handled through serially-named files within your application, each written as an UP/DOWN pair of actions, and in all supported by tooling...
app
ââ migrations
â â
â ââ 20240523_1759_create_users_table_and_drop_accounts_table
â â â
â â ââ up.sql
â â â CREATE TABLE users (id INT, first_n...);
â â â DROP TABLE accounts;
â â ââ down.sql
â â DROP TABLE users;
â â CREATE TABLE accounts (id INT, first_name VAR...);
â â
â ââ 20240523_1760_add_last_login_to_users_table_and_rename_order_status_table
â â â
â â ââ up.sql
â â â ALTER TABLE users ADD COLUMN last_lo...;
â â â ALTER TABLE order_status RENAME TO o...;
â â ââ down.sql
â â ALTER TABLE users DROP COLUMN last_login;
â â ALTER TABLE order_tracking RENAME TO order_status;
â â
â ââ +256 more...
then you've faced the problem that this defeciency in databases creates! But what if databases magically got to do the heavy lifting?
Meet Linked QL's little addition to your database that does exactly that and lets you just alter your DB however you may but in the safety net of some behind-the-scenes magic that snapshots your schema before each alteration! Meet Automatic Schema Savepoints and Rollbacks!
Linked QL:
// Alter schema
const savepoint = await client.query('CREATE TABLE public.users (id int, name varchar)', {
description: 'Create users table',
});
// Inspect the automatic savepoint created for you
console.log(savepoint.description); // Create users table
console.log(savepoint.versionTag); // 1
console.log(savepoint.savepointDate); // 2024-07-17T22:40:56.786Z
(More details in the Savepoint API.)
⨠PRO: Whole engineering work now essentially moved over to the DB where it rightly belongs; all with zero upfront setup!
Taking that further, you get a nifty rollback button should you want to:
// Rollback all associated changes (Gets the users table dropped)
await savepoint.rollback();
and you can go many levels back:
// Rollback to public@3
let savepoint;
while(savepoint = await client.database('public').savepoint()) {
await savepoint.rollback();
if (savepoint.versionTag === 3) break;
}
and you can "undo" a rollback, or in other words, roll forward to a point in time:
// "Undo" the last rollback (Gets the users table re-created)
let savepoint = await client.database('public').savepoint({ direction: 'forward' });
await savepoint.rollback();
You essentially are able to go back in time or forward in time as randomly as iteration may demand.
schema.jsonđĽ Have your entire DB structure live in a single schema.json file that you edit in-place!
With schema versioning now over to the database, much of the old conventions and formalities should now be irrelevant. We found that we could essentially streamline the whole "database" footprint from spanning hundreds of migration files to fitting into a single schema.json (or schema.yml) file!
schema.json[
{
"name": "database_1",
"tables": [] // Table objects
},
{
"name": "database_2",
"tables": [] // Table objects
}
]
An example table object:
{
"name": "users",
"columns": [], // Column objects (minimum of 1)
"constraints": [], // Constraint objects
"indexes": [] // Index objects
}
An example column object:
{
"name": "id",
"type": "int",
"primaryKey": true,
"identity": true
}
{
"name": "full_name",
"type": ["varchar","101"],
"generated": "(first_name || ' ' || last_name)"
}
{
"name": "email",
"type": ["varchar","50"],
"uniqueKey": true,
"notNull": true,
"check": "(email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')"
}
{
"name": "parent",
"type": "int",
"notNull": true,
"references": {
"targetTable": "users",
"targetColumns": ["id"],
"matchRull": "full",
"updateRule": "cascade",
"deleteRule": "restrict"
}
}
An example constraint object:
{
"type": "PRIMARY_KEY",
"columns": ["id"],
"name": "constraint_name"
}
{
"type": "UNIQUE_KEY",
"columns": ["email"]
}
{
"type": "FOREIGN_KEY",
"columns": ["parent"],
"targetTable": "users",
"targetColumns": ["id"],
"matchRull": "full",
"updateRule": "cascade",
"deleteRule": "restrict"
}
{
"type": "CHECK",
"expr": "(email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')"
}
An example index object:
{
"type": "FULLTEXT",
"columns": ["full_name"]
}
{
"type": "SPATIAL",
"columns": ["full_name"]
}
Now, if you had that somewhere in your application, say at ./database/schema.json, Linked QL could help keep it in sync both ways with your database:
linkedql migrategit pull, or at the click of a command: linkedql refreshđĽ You also get to see a version indicator on each database object in your schema essentially incrementing on each migrate operation (whether by you or by colleague), and decrementing on each rollback operation (whether by you or by colleague).
Thanks to a DB-native schema version control system, no need to maintain past states, or risk losing them; the DB now becomes the absolute source of truth for both itself and its client applications, as against the other way around. (You may want to see how that brings us to true "Schema as Code" in practice.)
To setup:
Make a directory within your application for database concerns. Linked QL will look in ./database, but you will be able to point to your preferred location when running Linked QL commands.
Have a driver.js file in that directory that has a default export function that returns a Linked QL instance. This will be imported and used by Linked QL to interact with your database. This could look something like:
import pg from 'pg';
import SQLClient from '@linked-db/linked-ql/sql';
const pgClient = new pg.Client({
host: 'localhost',
port: 5432,
});
await pgClient.connect();
const sqlClient = new SQLClient(pgClient, { dialect: 'postgres' });
export default function() {
return sqlClient;
}
Have your DB structure defined in a schema.json file in that directory. (See schema.json above for a guide.)
You can always extend your schema with new objects, and you can always drop objects or edit them in-place. For an existing database, table, column, constraint, or index, names may be changed, but not in-place! A "rename" operation is done with the addition of a temporary $name attribute:
{
"name": "old_name",
"$name": "new_name"
}
The old name being in place is needed to find the target during migration. The temporary $name attribute automatically disappears after new name has been picked up by Linked QL at next linkedql migrate.
To run:
linkedql migrate to walk through your staged local changes and interactively perform a migration on your database.linkedql rollback to walk through the latest savepoint at each database and interactively perform a rollback.linkedql leaderboard to just view the latest savepoint at each database.(More details in the Linked QL CLI section.)
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 us 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.
Client APIClient is the top-level object for the individual database kinds in Linked QL. Each instance implements the following interface:
client.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.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, a number, in all other cases, indicating number of rows processed by the query.â˝ď¸ 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 ge 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():CREATE DATABASE operation.
client.createDatabase(createSpec: string | { name: string, tables?: Array }, options?: Options): Promise<Savepoint>âď¸ Spec:
createSpec (string | { name: string, tables?: Array }): the database name, or an object corresponding to the database object in schema.json.options (Options, optional): as described in query().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 the table object 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():ALTER DATABASE operation.
client.alterDatabase(alterSpec: string | { name: string, tables?: string[] }, callback: (schema: DatabaseSchema) => void, options?: Options): Promise<Savepoint>âď¸ Spec:
alterSpec (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 ((schema: DatabaseSchema) => void): a function that is called with the requested schema. This can be async. Received object is a DatabaseSchema instance.options (Options, optional): as described in query().Savepoint instance.â˝ď¸ Usage:
Specify database by name:
const savepoint = await client.alterDatabase('database_1', schema => {
schema.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'] }, schema => {
schema.name('database_1_new');
schema.table('table_1').column('column_1').name('column_1_new');
schema.table('table_1').column('column_2').type('varchar');
}, { description: 'Renaming for testing purposes' });
client.dropDatabase():DROP DATABASE operation.
client.dropDatabase(dbName: string, options?: Options): Promise<Savepoint>âď¸ Spec:
dbName (string): the database name.options (Options, optional): as described in query().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():client.hasDatabase(dbName: string): Promise<Boolean>âď¸ Spec:
dbName (string): the database name.â˝ď¸ Usage:
const exists = await client.hasDatabase('database_1');
client.describeDatabase():client.describeDatabase(dbName: string): Promise<{ name: string, tables: Array }>âď¸ Spec:
dbName (string): the database name.â˝ď¸ Usage:
const schema = await client.describeDatabase('database_1');
console.log(schema.name);
console.log(schema.tables);
client.databases():client.databases(): Promise<Array<string>>âď¸ Spec:
â˝ď¸ Usage:
const databases = await client.databases();
console.log(databases); // ['public', 'database_1', ...]
client.database():Database instance.
client.database(dbName: string): Databaseâď¸ Spec:
dbName (string): the database name.Database instance.â˝ď¸ Usage:
const database = client.database('database_1');
Database APIDatabase is the API for database-level operations. This object is obtained via client.database()
database.name:database.name: (string, readonly)â˝ď¸ Usage:
const database = client.database('test_db');
console.log(database.name); // test_db
database.createTable():CREATE TABLE operation.
database.createTable(createSpec: { name: string, columns: Array, constraints?: Array, indexes?: Array }, options?: Options): Promise<Savepoint>âď¸ Spec:
createSpec ({ name: string, columns: Array, constraints?: Array, indexes?: Array }): an object corresponding to the table object in schema.json.options (Options, optional): as described in query().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():ALTER TABLE operation.
database.alterTable(tblName: string, callback: (schema: TableSchema) => void, options?: Options): Promise<Savepoint>âď¸ Spec:
tblName (string): the table name.callback ((schema: TableSchema) => void): a function that is called with the requested table schema. This can be async. Received object is a TableSchema instance.options (Options, optional): as described in query().Savepoint instance.â˝ď¸ Usage:
const savepoint = await database.alterTable('table_1', schema => {
schema.name('table_1_new');
schema.column('column_1').type('int');
schema.column('column_2').drop();
}, { description: 'Renaming for testing purposes' });
database.dropTable():DROP TABLE operation.
database.dropTable(tblName: string, options?: Options): Promise<Savepoint>âď¸ Spec:
tblName (string): the table name.options (Options, optional): as described in query().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():database.hasTable(tblName: string): Promise<Boolean>âď¸ Spec:
tblName (string): the table name.â˝ď¸ Usage:
const exists = await database.hasTable('table_1');
database.describeTable():database.describeTable(tblName: string): Promise<{ name: string, columns: Array, constraints: Array, indexes: Array }>âď¸ Spec:
tblName (string): the table name.â˝ď¸ Usage:
const schema = await database.describeTable('table_1');
console.log(schema.name);
console.log(schema.columns);
database.tables():database.tables(): Promise<Array<string>>âď¸ Spec:
â˝ď¸ Usage:
const tables = await database.tables();
console.log(tables); // ['table_1', 'table_2', ...]
database.table():Table instance.
database.table(tblName: string): Tableâď¸ Spec:
tblName (string): the table name.Table instance.â˝ď¸ Usage:
const table = database.table('table_1');
database.savepoint():database.savepoint(options?: { direction: string }): Savepointâď¸ Spec:
options ({ direction: string }, optional): extra paramters for the method.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
Table APITable is the API for table-level operations. This object is obtained via database.table()
table.name:table.name: (string, readonly)â˝ď¸ Usage:
const table = client.database('test_db').table('table_1');
console.log(table.name); // table_1
table.count():table.count(expr?: string | Function = *): Promise<number>âď¸ Spec:
expr (string | Function = *, optional): a string denoting column name, or a function that recieves a Field object with which to build an expression. Defaults to *.â˝ď¸ Usage:
const rowCount = await table.count();
// Number of rows where column_1 isn't null
const rowCount = await table.count('column_1');
table.select():SELECT query.
table.select(fields?: (string | Function)[] = *, where?: number | object | Function | true): Promise<Array<object>>table.select(where?: number | object | Function): Promise<Array<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 Field object with which to build an expression.)where (number | object | Function | true, optional): a number targeting the primary key value of the target row, or an object specifying some column name/column value conditions, or a function that recieves an Assertion object with which to build the conditions, or the value true denoting all records. Defaults to true.â˝ď¸ Usage:
// Select all fields (*) from all records
const result = await table.select();
// Select specified fields from the record having primary key value of 4
const result = await table.select(['first_name', 'last_name', 'email'], 4);
// Select record by primary key value, ommiting fields (implying all fields)
const result = await table.select(4);
// Select record by some column name/column value conditions, ommiting fields (implying all fields)
const result = await table.select({ first_name: 'John', last_name: 'Doe' });
table.insert():INSERT operation. (With automatic parameter binding.)
table.insert(payload: object | object[], returnList?: (string | Function)[]): Promise<Array<object> | number>table.insert(columns: string[], values: any[][], returnList?: (string | Function)[]): Promise<Array<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.returnList (((string | Function)[] | false), optional): a list of fields, corresponding to a select list, specifying data to be returned from the just inserted row. (Equivalent to Postgres' RETURNING clause, but supported for other DB kinds in Linked QL.)returnList was provided, an array of the processed row(s).â˝ď¸ Usage:
// Insert single entry
await table.insert({ first_name: 'John', last_name: 'Doe', email: 'johndoe@example.com'});
// Insert multiple entries
await table.insert([
{ first_name: 'John', last_name: 'Doe', email: 'johndoe@example.com'},
{ first_name: 'James', last_name: 'Clerk', email: 'jamesclerk@example.com'},
]);
// Insert multiple entries another way
await table.insert(['first_name', 'last_name', 'email'], [
['John', 'Doe', 'johndoe@example.com'],
['James', 'Clerk', 'jamesclerk@example.com'],
]);
// Insert single entry, obtaining inserted rows - which is itself streamlined to just the "id" column
const insertedRows = await table.insert({ first_name: 'John', last_name: 'Doe', email: 'johndoe@example.com'}, ['id']);
table.upsert():UPSERT operation. (With automatic parameter binding.)
table.upsert(payload: object | object[], returnList?: (string | Function)[]): Promise<Array<object> | number>table.upsert(columns: string[], values: any[][], returnList?: (string | Function)[]): Promise<Array<object> | number>âď¸ Spec:
payload (object | object[]): as described in insert().columns (string[]): as described in insert().values (any[][]): as described in insert().returnList ((string | Function)[], optional): as described in insert().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():UPDATE operation. (With automatic parameter binding.)
table.update(where: number | object | Function | true, payload: object, returnList?: (string | Function)[]): Promise<Array<object> | number>âď¸ Spec:
where (number | object | Function | true): as described in select().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.returnList ((string | Function)[], optional): as described in insert().insert().â˝ď¸ Usage:
// Update the record having primary key value of 4
await table.update(4, { first_name: 'John', last_name: 'Doe' });
// Update the record having specified email value, obtaining the updated rows
const updatedRows = await table.update({ email: 'johndoe@example.com' }, { first_name: 'John', last_name: 'Doe' }, ['*']);
// Update all records
await table.update(true, { updated_at: new Date });
table.delete():DELETE operation. (With automatic parameter binding.)
table.delete(where: number | object | Function | true, returnList?: (string | Function)[]): Promise<Array<object> | number>âď¸ Spec:
where (number | object | Function | true): as described in select().returnList ((string | Function)[], optional): as described in insert().insert().â˝ď¸ Usage:
// Delete the record having primary key value of 4
await table.delete(4);
// Delete the record having specified email, obtaining the deleted row
const deletedRow = await table.delete({ email: 'johndoe@example.com' });
// Delete all records
await table.delete(true);
Savepoint APISavepoint is an object representation of a database's savepoint. This object is obtained either via database.savepoint() or via a CREATE, ALTER, or DROP operation.
savepoint.id:savepoint.id: (UUID, readonly)â˝ď¸ Usage:
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.id); // f740d66a-df5f-4a34-a281-8ef3ba6fe754
savepoint.databaseTag: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: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: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:savepoint.cursor: (string, readonly)â˝ď¸ Usage:
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.cursor); // 1/2
savepoint.description: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: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: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: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
await savepoint.rollback();
console.log(savepoint.rollbackEffect); // DROP
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
Compare with that of rolling back table-level operations - which always just has an ALTER effect:
// 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: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():savepoint.isNextPointInTime(): Promise<boolean>âď¸ Spec:
â˝ď¸ 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():savepoint.rollback(): Promise<boolean>âď¸ Spec:
â˝ď¸ 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():savepoint.toJson(): objectâď¸ Spec:
{ 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():savepoint.schema(): objectâď¸ Spec:
â˝ď¸ 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():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.â˝ď¸ 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
Linked QL migrations are a small addition to Linked QL. And it comes ready-to-use, via the linkedql command, upon Linked QL's installation. (No extra setup is required.)
The linkedql command comes as part of your local Linked QL installation and not as a global package, and that means you'll need the npx prefix to run the commands below. E.g.
npx linkedql migrate
In each case, you can use the --dir flag to point Linked QL to your "database" directory:
npx linkedql migrate --dir="./src/database-stuff"
(Relative paths will resolve against your current working directory (CWD).)
Use the --db flag to run given command for a specific database out of the list of databases:
npx linkedql migrate --db=database_1
Use the flag --auto to turn off prompts and just take the "sensible-default" action:
npx linkedql migrate --auto
linkedql migrateInteractively run new migrations. Linked QL looks through your local schema and compares with your active DB structure to see what's new. It works interactively by default and you're able to preview each SQL query to be run.
đš Usage:
npx linkedql migrate
npx linkedql migrate --db=database_1
Use the --desc flag to provide the description for your new changes:
npx linkedql migrate --desc="Initial DB creation"
Use the flag --quiet to turn off SQL previews:
npx linkedql migrate --quiet
linkedql rollbackInteractively perform a rollback. Linked QL looks for the next savepoint at each database and initiates a rollback. It works interactively by default and you're able to preview each SQL query to be run.
đš Usage:
npx linkedql rollback
npx linkedql rollback --db=database_1
Use the --direction flag to specify either a "backward" rollback (the default) or a "forward" rollback if already at a certain rollback state:
npx linkedql rollback --direction=forward
Use the flag --quiet to turn off SQL previews:
npx linkedql migrate --quiet
linkedql leaderboardView the latest savepoint at each database. Linked QL displays details about the next savepoint at each database.
đš Usage:
npx linkedql leaderboard
npx linkedql leaderboard --db=database_1
Use the flag --direction to specify either a "back in time" lookup (the default) or "forward in time" lookup if already at a certain rollback state:
npx linkedql leaderboard --direction=forward
linkedql refreshRefresh local schema file. Linked QL regenerates the schema from current DB structure for each database it has managed and refreshes local copy.
đš Usage:
npx linkedql refresh
npx linkedql refresh --db=database_1
linkedql forgetPermanently erase savepoint histories. Linked QL deletes the savepoint history of all databases or specified database. This is irreversible.
đš Usage:
npx linkedql forget
npx linkedql forget --db=database_1
đŁ And that's a wrap!
Much of that could happen sooner with your support! If you'd like to help out, please consider a sponsorship. PRs are also always welcome.
To report bugs or request features, please submit an issue to this repository.
MIT.
FAQs
A query client that extends standard SQL with new syntax sugars and enables auto-versioning capabilities on any database
The npm package @linked-db/linked-ql receives a total of 6 weekly downloads. As such, @linked-db/linked-ql popularity was classified as not popular.
We found that @linked-db/linked-ql demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Newly introduced telemetry in devenv 1.4 sparked a backlash over privacy concerns, leading to the removal of its AI-powered feature after strong community pushback.
Security News
TC39 met in Seattle and advanced 9 JavaScript proposals, including three to Stage 4, introducing new features and enhancements for a future ECMAScript release.
Security News
Deno 2.2 enhances Node.js compatibility, improves dependency management, adds OpenTelemetry support, and expands linting and task automation for developers.