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 experienceIf 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! // (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 sugarsGo 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) // (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', 'jd@example.com', 'admin' ),
( 'Alice Blue', 'ab@example.com', 'guest' )`
);
console.log(result); |
Progressive enhancementWhile 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) // (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 inferenceWhereas 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) 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`,
['johndoe@example.com']
); |
Automatic schema versioningWhile 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 ( 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 migrationWhereas 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 Declare your project's DB structure:
[
{
"name": "database_1",
"tables": []
},
{
"name": "database_2",
"tables": []
}
]
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:
|
Postgres / Supabase / Neon / etcInstall and connect the 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 const connectionParams = { connectionString: process.env.SUPABASE_CONNECTION_STRING };
|
MySQL / mariadbInstall and connect the 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' });
|
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 |
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.
To report bugs or request features, please submit an issue.
MIT. (See LICENSE)
