Skip to content

INSERT

Jump to bottom Edit New page
Oxford Harrison edited this page Nov 18, 2024 · 12 revisions

DOCS • LANG

The INSERT statement.

See APIS ➞ client.query(), table.insert()

Section Description
Basic Insert Run a basic INSERT operation.
The COLUMNS/VALUES Clause -
The SET Clause -
The SELECT Clause -
The ON CONFLICT Clause -
The RETURNING Clause -
Multi-Dimensional Inserts Insert multi-dimensional data structures without doing the rough work.

Basic Insert

// (a): SQL syntax
const result = await client.query(
    `INSERT INTO public.users
        (name, email)
    VALUES ('Jane', 'jane@example.com')`
);
// (b): Object-based syntax
const result = await client.database('public').table('users').insert(
    { name: 'Jane', email: 'jane@example.com' }
);

The COLUMNS/VALUES Clause

The SELECT Clause

The SET Clause

The ON CONFLICT Clause

The RETURNING Clause

Multi-Dimensional Inserts

While you could insert relational data individually and manually create the relevant associations, Linked QL supports special path operators that let you express multi-dimensional data graphically. (See ➞ Magic Paths.) Or if you want, you could simply pass in your raw multi-dimensional data and Linked QL will do a neat multi-dimensional insert for you.

Example 1:

For each book entry created, create a user with the specified email—associated as author:

// (a): SQL syntax
const result = await client.query(
    `INSERT INTO public.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'
    )`
);
// (b): Object-based syntax (1)
const result = await client.database('public').table('books').insert(
    [ 'title', 'content', { rpath: ['author', 'email']} ],
    [
        ['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']]
    ]
);
// (c): Function-based syntax (2)
const result = await client.database('public').table('books').insert(
    [
        { title: 'Beauty and the Beast', content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...', author: { email: 'johndoed@example.com' } },
        { title: 'The Secrets of Midnight Garden', content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...', author: { email: 'aliceblue@example.com' } }
    ]
);

Example 2:

For each book entry created, create a user with the specified name and email—associated as author—returning entire tree:

// (a): SQL syntax
const result = await client.query(
    `INSERT INTO public.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 *`
);
// (b): Object-based syntax (1)
const result = await client.database('public').table('books').insert(
    [ 'title', 'content', { rpath: ['author', { columns: ['name', 'email'] }]} ],
    [
        ['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: '*' }
);
// (c): Object-based syntax (2)
const result = await client.database('public').table('books').insert(
    [
        { 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'
        } }
    ],
    { returning: '*' }
);

Example 3:

For each user created, create an associated book entry with the specified title and content, returning entire tree:

// (a): SQL syntax
const result = await client.query(
    `INSERT INTO public.users (
        name,
        email,
        author <~ public.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 *`
);
// (b): Object-based syntax (1)
const result = await client.database('public').table('users').insert(
    ['name', 'email', { rpath: [{ lpath: ['author', ['public', 'books']] }, { columns: ['title', 'content'] }] } ],
    [
        ['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: '*' }
);
// (c): Object-based syntax (2)
const result = await client.database('public').table('users').insert(
    [
        { name: 'John Doe', email: 'johndoed@example.com', books: [
            { title: 'Beauty and the Beast', content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...' }
        ] },
        { name: 'Alice Blue', email: 'aliceblue@example.com', books: [
            { title: 'The Secrets of Midnight Garden', content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...' }
        ] }
    ],
    { returning: '*' }
);

Tip

Notice that object-based syntax (2) uses the second table name (books) as key. In contrast to object-based syntax (1) which lets you optionally qualify the table name, this assumes that the specified table is located within the same schema (public in this case) as the base table.

Another assumption made is that there is only one foreign key in the second table (books) referencing the base table (users)—i.e. author. This is in contrast to object-based syntax (1) which carries the specific foreign key in the path syntax, potentially supporting any number of such references.

An error is thrown where any of these assumptions fails.

Example 4:

Extend the previous to create two books each, instead of one:

// (a): SQL syntax
const result = await client.query(
    `INSERT INTO public.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 *`
);
// (b): Object-based syntax (1)
const result = await client.database('public').table('users').insert(
    ['name', 'email', { rpath: [{ lpath: ['author', 'books'] }, { columns: ['title', 'content'] }] } ],
    [
        ['John Doe', 'johndoed@example.com', [
            ['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', [
            ['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: '*' }
);
// (c): Object-based syntax (2)
const result = await client.database('public').table('users').insert(
    [
        { 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...' }
        ] }
    ],
    { returning: '*' }
);
Add a custom footer
Add a custom sidebar
Clone this wiki locally