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:

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);

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', '[email protected]', 'admin' ),
        ( 'Alice Blue', '[email protected]', 'guest' )`
);
console.log(result);

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

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`,
    ['[email protected]']
);

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

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

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.

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.

// Import the IDB Client
import { IDBClient } from '@linked-db/linked-ql/idb';

// Create an instance.
const client = new IDBClient;

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