@linked-db/linked-ql

Linked QL

A query client that extends standard SQL with new syntax sugars and enables auto-versioning capabilities on any database; 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:

• Basic Usage
• Magic Paths
• Auto-Versioning
• API

Basic Usage

Install Linked QL:

npm install @linked-db/linked-ql

Obtain the Linked QL client for your target database:

1. 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' });
   

2. For the client-side IndexedDB, import and instantiate the IDB client.

   // Import IDB as LinkedQl
   import LinkedQl from '@linked-db/linked-ql/idb';
   
   // Create an instance.
   const client = new LinkedQl;
   

3. To work with Linked QL's in-memory object storage, import and instantiate the ODB client.

   // Import ODB as LinkedQl
   import LinkedQl from '@linked-db/linked-ql';
   
   // 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', { params: ['admin'] }).then(result => {
    console.log(result);
});

const result = await client.query('SELECT fname, lname FROM users WHERE role = $1', { params: ['admin'] });
console.log(result);

Other APIs are covered just ahead in the API section.

Introducing Magic Paths

Express relationships graphically. Meet the magic path operators, a syntax extension to SQL, that let you connect to columns on other tables without writing a JOIN. Linked QL uses heuristics on your DB structure to figure out the details and the relevant JOINs.

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

Here's another instance showing an example schema and a typical 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: Whole namespacing exercise is now eliminated; 70% less code; all with zero upfront setup!

Additionally, paths can be multi-level:

-- Linked QL
SELECT ..., author ~> role ~> name 
FROM books

and they can also be used to express incoming references:

-- Linked QL
SELECT ..., author <~ books ~> title 
FROM users

DOCS coming soon.

Introducing Auto-Versioning

Create, Drop, Alter schemas without needing to worry about schema versioning. Linked QL automatically adds auto-versioning capabilities to your database. Meet Schema Savepoints and Rollbacks.

Where you normally would maintain a history of schema files (i.e. migration files) within your application, with a naming convention that, among other things, must carry a sense of versioning...

app
 ├── migrations
  ├── 20240523_1759_create_users_table_and_drop_accounts_table.sql
  ├── 20240523_1760_add_last_login_to_users_table_and_add_index_on_order_status_table.sql
  ├── ...

Linked QL lets you just alter your DB however you may, this time, with automatic savepoints happening within your DB as you go:

// Alter schema
await client.query('CREATE TABLE users (...)', {
    savepointDesc: 'Create users table',
});

// Inspect the automatic savepoint created for you
const savepoint = await client.database('public').savepoint();
console.log(savepoint.savepoint_desc); // Create users table

PRO: DB versioning concerns are now taken out of the client application - to the DB itself; all with zero upfront setup!

Now, when it's time to rollback? A magic wand button:

// Rollback all associated changes
await savepoint.rollback();

and you can go many levels back:

// Rollback to a point in time
let savepoint;
while(savepoint = await client.database('public').savepoint()) {
    await savepoint.rollback();
    if (savepoint.id === '...') break;
}

and you can "redo" a rollback; i.e. roll forward:

let savepoint = await client.database('public').savepoint({ direction: 'forward' });
await savepoint.rollback();

DOCS coming soon.

API

Coming soon.

Issues

To report bugs or request features, please submit an issue to this repository.

License

MIT.