@linked-db/linked-ql

Linked QL

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 minimalistic 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 query relationships without writing a JOIN.)

• takes the process out of schema management and lets you just alter away like it's nothing. (Linked QL extends your DB behind the scene to automatically version each edit you make and have them kept as "savepoints" that you can always rollback to.)

• takes schema-as-code to a whole new level where you can have your entire database structure live in a schema.json file - similar to having package.json. (Linked QL gives you schema-as-code without the overhead of 100s of migration files that must be managed manually.)

Linked QL 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:

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

Setup

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. (Coming soon)

   // 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. (Coming soon)

   // 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

And here's another scenario 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 * FROM books
WHERE author ~> role ~> name = 'admin'

and they can also be used to express incoming references:

-- Linked QL
SELECT * FROM users
WHERE author <~ books ~> title = 'Beauty and the Beast'

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.

TODO

There's a lot here:

• Write detailed docs.
• Upgrade support for MySQL.
• Implement support for IndexedDB and in-mem.
• Write detailed tests.

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.

Issues

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

License

MIT.