Skip to content

Magic Paths

Jump to bottom Edit New page
Oxford Harrison edited this page Nov 9, 2024 · 10 revisions

Introducing Magic Paths

💥 Express relationships graphically! You shouldn't always have to write JOINS!

Meet Linked QL's magic path operators, a syntax extension to SQL, that lets you connect to columns on other tables without writing a single JOIN clause. Linked QL uses heuristics on your DB structure to figure out the details and the relevant JOINS behind the scenes.

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 a scenario showing a typical schema and an example 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

✨ Now, that translates to about 50% code, plus whole namespacing exercise, having been eliminated! Yet, no questions asked about your schema, and none of the usual upfront relationship mapping!

Taking things further, you are able to chain these operators to any level for your multi-level relationships:

-- Linked QL
SELECT * FROM books
WHERE author ~> role ~> codename = 'admin'

and for the different forms of relationships out there (one-to-many, many-to-one, many-to-many), path operators can go in any direction:

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

Plus, with Linked QL being a superset of SQL, you can combine the new magic together with the old LEFT JOIN/RIGHT JOIN/etc clauses with zero implications:

-- Linked QL
SELECT users.* FROM users, some_other_table.id
LEFT JOIN some_other_table USING some_other_condition
WHERE author <~ books ~> title = 'Beauty and the Beast'

giving you just the right tool for the job in every scenario: the regular JOINS for whatever calls for them; magic paths for when the very JOINS are an overkill!

✨ We think this will make a lot of your tooling and manual work around SQL obsolete and your codebase saner! You essentially get back SQL - and with it, a dose of magic!

Example 1:

// A basic query with JSON formatting
const result = await client.query(
    `SELECT
        name,
        { email, phone AS mobile } AS contact1,
        [ email, phone ] AS contact2
    FROM users`
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        contact1: {
            email: 'johndoed@example.com',
            mobile: '(555) 123-4567'
        },
        contact2: [ 'johndoed@example.com', '(555) 123-4567' ]
    },
    {
        name: 'Alice Blue',
        contact1: {
            email: 'aliceblue@example.com',
            mobile: '(888) 123-4567'
        },
        contact2: [ 'aliceblue@example.com', '(888) 123-4567' ]
    }
]

Example 2:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    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
);
// A basic JOIN using magic paths | MANY-TO-ONE
const result = await client.query(
    `SELECT
        title,
        content,
        author ~> name AS author_name
    FROM books
    WHERE author ~> role = $1`,
    ['admin']
);
console.log(result);
Console 
[
    {
        title: 'Beauty and the Beast',
        content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        author_name: 'John Doe'
    },
    {
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        author_name: 'Alice Blue'
    }
]

Example 3:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    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
);
// Same query but structured via JSON formatting | MANY-TO-ONE
const result = await client.query(
    `SELECT
        title,
        content,
        author: { name, email } AS author
    FROM books
    WHERE author ~> role = $1`,
    ['admin']
);
console.log(result);
Console 
[
    {
        title: 'Beauty and the Beast',
        content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        author: {
            name: 'John Doe',
            email: 'johndoed@example.com'
        }
    },
    {
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        author: {
            name: 'Alice Blue',
            email: 'aliceblue@example.com'
        }
    }
]

Example 4:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    parent int references users (id),
    created_time timestamp
);
// A multi-level JOIN using magic paths | MANY-TO-ONE
const result = await client.query(
    `SELECT
        name,
        email,
        parent ~> parent ~> name AS grand_parent
    FROM users
    LIMIT 2
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: 'johndoed@example.com',
        grand_parent: 'Some user 1'
    },
    {
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        grand_parent: 'Some user 2'
    }
]

Example 5:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    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
);
// A basic one-to-many JOIN using magic paths | ONE-TO-MANY
const result = await client.query(
    `SELECT
        name,
        email,
        author <~ books ~> title AS book_title
    FROM books
    WHERE author <~ books ~> content LIKE '%(C) 2024%'`,
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: 'johndoed@example.com',
        book_title: 'Beauty and the Beast - Part 1'
    },
    {
        name: 'John Doe',
        email: 'johndoed@example.com',
        book_title: 'Beauty and the Beast - Part 2'
    },
    {
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        books: 'The Secrets of Midnight Garden - Part 1'
    },
    {
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        books: 'The Secrets of Midnight Garden - Part 2'
    }
]

Example 6:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    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
);
// Same query but structured and aggregated via JSON formatting | ONE-TO-MANY
const result = await client.query(
    `SELECT
        name,
        email,
        author <~ books: { title, content }[] AS books
    FROM books
    WHERE author <~ books ~> content LIKE '%(C) 2024%'`,
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: 'johndoed@example.com',
        books: [
            {
                title: 'Beauty and the Beast - Part 1',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
            },
            {
                title: 'Beauty and the Beast - Part 2',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
            }
        ]
    },
    {
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        books: [
            {
                title: 'The Secrets of Midnight Garden - Part 1',
                content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
            },
            {
                title: 'The Secrets of Midnight Garden - Part 2',
                content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
            }
        ]
    }
]

Example 7:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    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
);
// A basic multi-dimensional INSERT | MANY-TO-ONE
// TIP: for each book entry CREATED, CREATE a user with specified email
const result = await client.query(
    `INSERT INTO books (
        title,
        content,
        author ~> email
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        'johndoed@example.com'
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        'aliceblue@example.com'
    )`
);
console.log(result); // true
// A basic multi-dimensional UPSERT | MANY-TO-ONE
// TIP: for each book entry CREATED or UPDATED, CREATE or UPDATE a user with specified email
const result = await client.query(
    `UPSERT INTO books (
        title,
        content,
        author ~> email
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        'johndoed@example.com'
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        'aliceblue@example.com'
    )`
);
console.log(result); // true
// A basic multi-dimensional UPDATE | MANY-TO-ONE
// TIP: for each book entry UPDATED, CREATE or UPDATE a user with specified email
const result = await client.query(
    `UPDATE books
    SET
        title = 'Beauty and the Beast',
        content = '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        author ~> email = 'johndoed@example.com'
    `
);
console.log(result); // true

Example 8:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    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
);
// A multi-dimensional INSERT
// TIP: for each book entry CREATED, CREATE a user with specified name and email, RETURNING entire tree
const result = await client.query(
    `INSERT INTO books (
        title,
        content,
        author: (
            name,
            email
        )
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        (
            'John Doe',
            'johndoed@example.com'
        )
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        (
            'Alice Blue',
            'aliceblue@example.com'
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        title: 'Beauty and the Beast',
        content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        author: {
            id: 1,
            name: 'John Doe',
            email: 'johndoed@example.com',
            phone: '(555) 123-4567',
            role: 'admin',
            created_time: '2024-11-06T18:22:46.709Z'
        },
        created_time: '2024-11-06T18:22:46.709Z'
    },
    {
        id: 2,
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        author: {
            id: 2,
            name: 'Alice Blue',
            email: 'aliceblue@example.com',
            phone: '(888) 123-4567',
            role: 'admin',
            created_time: '2024-11-06T18:22:46.709Z'
        },
        created_time: '2024-11-06T18:22:46.709Z'
    }
]

Example 9:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    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
);
// A multi-dimensional INSERT
// TIP: for each user CREATED, CREATE a book entry with specified title and content, RETURNING entire tree
const result = await client.query(
    `INSERT INTO users (
        name,
        email,
        author <~ books: (
            title,
            content
        )
    ) VALUES (
        'John Doe',
        'johndoed@example.com',
        (
            'Beauty and the Beast',
            '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
        )
    ), (
        'Alice Blue',
        'aliceblue@example.com',
        (
            'The Secrets of Midnight Garden',
            '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        name: 'John Doe',
        email: 'johndoed@example.com',
        phone: '(555) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'Beauty and the Beast',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    },
    {
        id: 1,
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        phone: '(888) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'The Secrets of Midnight Garden',
                content: '(C) 2024 johndoed@example.com\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    }
]

Example 10:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    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
);
// A multi-dimensional INSERT
// TIP: for each user CREATED, CREATE two book entries with specified titles and contents, RETURNING entire tree
const result = await client.query(
    `INSERT INTO users (
        name,
        email,
        author <~ books: (
            title,
            content
        )
    ) VALUES (
        'John Doe',
        'johndoed@example.com',
        VALUES (
            (
                'Beauty and the Beast - Part 1',
                '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
            ), (
                'Beauty and the Beast - Part 2',
                '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
            )
        )
    ), (
        'Alice Blue',
        'aliceblue@example.com',
        VALUES (
            (
                'The Secrets of Midnight Garden - Part 1',
                '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
            ), (
                'The Secrets of Midnight Garden - Part 2',
                '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
            )
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        name: 'John Doe',
        email: 'johndoed@example.com',
        phone: '(555) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'Beauty and the Beast - Part 1',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            },
            {
                id: 2,
                title: 'Beauty and the Beast - Part 2',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    },
    {
        id: 1,
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        phone: '(888) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'The Secrets of Midnight Garden - Part 1',
                content: '(C) 2024 johndoed@example.com\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            },
            {
                id: 2,
                title: 'The Secrets of Midnight Garden - Part 2',
                content: '(C) 2024 johndoed@example.com\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    }
]
Add a custom footer
Add a custom sidebar
Clone this wiki locally