@linked-db/linked-ql

Linked QL

Get insanely productive with SQL!

Follow • Sponsor

LinkedQL is a robust database query client and next-generation migration wizard that makes database interactions and schema management a breeze! With the release of v0.3, we're redefining modern database abstraction and rethinking the traditional ORM.

Linked QL is JS-based and database-agnostic—supporting PostgreSQL, MySQL and mariadb (on the backend), and IndexedDB (in the browser)!

---

SELECT • INSERT • UPSERT • UPDATE • DELETE • CREATE • RENAME • ALTER • DROP

Docs • LANG • API • CLI • Migrations

What we're doing differently?
Not an ORM like Prisma or Drizzle, and yet, not an ordinary database query client! Here's a breif tour:
A SQL-native experience If you miss the art and power of SQL, then you'll love Linked QL! While SQL as a language may have come to be the exception in the database tooling ecosystem, it is the default in Linked QL! That is a go-ahead to, in fact, #usethelanguage whenever it feels inclined! └ Preview: // (1): A basic query with parameters const result = await client.query( `SELECT name, email FROM users WHERE role = $1`, ['admin'] ); console.log(result); // (2): A basic DDL query const result = await client.query( `CREATE TABLE users ( id int primary key generated always as identity, name varchar, email varchar, phone varchar, role varchar, created_time timestamp )` ); console.log(result);
Powerful syntax sugars Go ahead and model structures and traverse relationships like they were plain JSON objects—right within the language! Meet Linked QL's set of syntax extensions to SQL that do the hard work, cut your query in half, and even save you multiple round trips! (See ➞ JSON Sugars, Magic Paths, Upserts) └ Preview: // (1): JSON Sugars const result = await client.query( `SELECT name, email, { email, phone AS mobile } AS format1, [ email, phone ] AS format2 FROM users` ); console.log(result); // (2): Magic Paths const result = await client.query( `SELECT title, content, author ~> name AS author_name FROM books WHERE author ~> role = $1`, ['admin'] ); console.log(result); // (3): Upsert const result = await client.query( `UPSERT INTO public.users ( name, email, role ) VALUES ( 'John Doe', '[e-mail]', 'admin' ), ( 'Alice Blue', '[e-mail]', 'guest' )` ); console.log(result);
Progressive enhancement While the typical ORM often imposes a high level of abstraction where that's not desired, Linked QL offers a SQL-by-default, progressive enhancement workflow that lets you think from the ground up! And at whatever part of that spectrum you find a sweet spot, you also get the same powerful set of features that Linked QL has to offer! (See ➞ Examples) └ Preview: // (a): SQL const result = await client.query( `SELECT name, email FROM users WHERE role = $1 OR role = $2`, ['admin', 'contributor'] ); // (b): Object-Based Query Builder const result = await client.database('public').table('users').select({ fields: [ 'name', 'email' ], where: { some: [ { eq: ['role', { binding: 'admin' }] }, { eq: ['role', { binding: 'contributor' }] } ] } }); // (c): Function-Based Query Builder const result = await client.database('public').table('users').select({ fields: [ 'name', 'email' ], where: (q) => q.some( (r) => r.eq('role', (s) => s.binding('admin')), (r) => r.eq('role', (s) => s.binding('contributor')), ) });
Automatic schema inference Whereas the typical ORM requires you to feed them with your database schema (case in point: Drizzle), Linked QL automatically infers it and magically maintains 100% schema-awareness throughout (without necessarily looking again)! You get a whole lot of manual work entirely taken out of the equation! (See ➞ Automatic Schema Inference) └ Preview: Simply plug to your database and play: // Import pg and LinkedQl import pg from 'pg'; import { SQLClient } from '@linked-db/linked-ql/sql'; // Connect to your database const connectionParams = { connectionString: process.env.SUPABASE_CONNECTION_STRING } const pgClient = new pg.Client(connectionParams); await pgClient.connect(); // Use LinkedQl as a wrapper over that const client = new SQLClient(pgClient, { dialect: 'postgres' }); Query structures on the fly... without the upfront schema work: const result = await client.query( `SELECT access_token, user_id: { email, phone, role } AS user, last_active FROM auth.users WHERE user_id ~> email = $1`, ['[e-mail]'] );
Automatic schema versioning While the typical database has no concept of versioning, Linked QL comes with it to your database, and along with it a powerful rollback (and rollforward) mechanism! On each DDL operation you make against your database (CREATE, ALTER, DROP), you get a savepoint automatically created for you and a seamless rollback path you can take anytime! (See ➞ Automatic Schema Versioning) └ Preview: Perform a DDL operation and obtain a reference to the automatically created savepoint: // (a): Using the "RETURNING" clause at DDL execution time const savepoint = await client.query( `CREATE TABLE public.users ( id int, name varchar ) RETURNING SAVEPOINT`, { desc: 'Create users table' } ); // (b): Or using the database.savepoint() API at any time const savepoint = await client.database('public').savepoint(); Either way, see what you got there: // (a): Some important details about the referenced point in time console.log(savepoint.versionTag()); // 1 console.log(savepoint.commitDesc()); // Create users table console.log(savepoint.commitDate()); // 2024-07-17T22:40:56.786Z // (b): Your rollback path console.log(savepoint.reverseSQL()); // "DROP TABLE public.users CASCADE" // (c): Your rollback magic wand button await savepoint.rollback({ desc: 'Users table no more necessary' });
Diff-based migration Whereas schema evolution remains a drag in the database tooling ecosystem, it comes as a particularly nifty experience in Linked QL! As against the conventional script-based migrations approach, Linked QL follows a diff-based approach that lets you manage your entire DB structure declaratively out of a single schema.json (or schema.yml) file! (See ➞ Migrations) └ Preview: Declare your project's DB structure: ./database/schema.json [ { "name": "database_1", "tables": [] }, { "name": "database_2", "tables": [] } ] For an existing DB, usa a command to generate your DB structure: npx linkedql generate. Extend your database with tables and columns. Remove existing ibjects or edit them in-place. Then, use a command to commit your changes to your DB: npx linkedql commit
And we've got a few things in the radar: extensive TypeScript support (something we love about Prisma); Linked QL Realtime—a realtime data API for offline-first applications.

Getting Started
Install Linked QL: npm install @linked-db/linked-ql
Postgres / Supabase / Neon / etc Install and connect the pg client. (Or another postgres client of your choice.) Use Linked QL as a wrapper over that. npm install pg // Import pg and LinkedQl import pg from 'pg'; import { SQLClient } from '@linked-db/linked-ql/sql'; // Connect pg const connectionParams = { host: 'localhost', port: 5432, }; const pgClient = new pg.Client(connectParams); await pgClient.connect(); // Use LinkedQl as a wrapper over that const client = new SQLClient(pgClient, { dialect: 'postgres' }); For Supabase/Neon/etc., simply update connectionParams to use the connectionString for your remote DB: const connectionParams = { connectionString: process.env.SUPABASE_CONNECTION_STRING }; Note that your postgres database must be v15.x or higher.
MySQL / mariadb Install and connect the mariadb client. (Or, where applicable, the mysql/mysql2.) Use Linked QL as a wrapper over that. npm install mariadb // Import mariadb and LinkedQl import mariadb from 'mariadb'; import { SQLClient } from '@linked-db/linked-ql/sql'; // Connect pg const myConnection = await mariadb.createConnection({ host: '127.0.0.1', user: 'root', port: 3306, multipleStatements: true, // Required bitOneIsBoolean: true, // The default, but required trace: true, // Recommended }); // Use LinkedQl as a wrapper over that const client = new SQLClient(myConnection, { dialect: 'mysql' }); Note that your mariadb database must be v10.5.2 or higher. (MySQL v8 comparably.) In addition, Linked QL needs to be able to run multiple statements in one query. The multipleStatements connector parameter above is thus required. We also needed to have the bitOneIsBoolean parameter in place.
Indexed DB (Coming soon) // Import the IDB Client import { IDBClient } from '@linked-db/linked-ql/idb'; // Create an instance. const client = new IDBClient;
In-Mem DB (Coming soon) // Import the ODB Client import { ODBClient } from '@linked-db/linked-ql/odb'; // Create an instance. const client = new ODBClient;
All client instances above implement the same interface! The primary query interface therein is the client.query() method. For a quick list of examples, see here.

Docs • LANG • API • CLI • Migrations

SELECT • INSERT • UPSERT • UPDATE • DELETE • CREATE • RENAME • ALTER • DROP

---

[!IMPORTANT]

Note that this a fast-evolving project and a few things around here might change before v1! Note too that support for MySQL isn't yet on par with that of PostgreSQL.

Issues

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

License

MIT. (See LICENSE)