Skip to content

INSERT

Jump to bottom Edit New page
Oxford Harrison edited this page Nov 19, 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', '[email protected]')`
);
// (b): Object-based syntax (1)
const result = await client.database('public').table('users').insert({
    data: {
        name: 'Jane',
        email: '[email protected]'
    }
});
// (c): Object-based syntax (2)
const result = await client.database('public').table('users').insert({
    columns: ['name', 'email']
    values: [
        ['Jane', '[email protected]']
    ]
});

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 [email protected]\nBeauty and the Beast...',
        '[email protected]'
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
        '[email protected]'
    )`
);
// (b): Function-based syntax (1)
const result = await client.database('public').table('books').insert({
    data: [
        { title: 'Beauty and the Beast', content: '(C) 2024 [email protected]\nBeauty and the Beast...', author: { email: '[email protected]' } },
        { title: 'The Secrets of Midnight Garden', content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...', author: { email: '[email protected]' } }
    ]
});
// (c): Object-based syntax (2)
const result = await client.database('public').table('books').insert({
    columns: [ 'title', 'content', { author: 'email' } ],
    values: [
        ['Beauty and the Beast', '(C) 2024 [email protected]\nBeauty and the Beast...', ['[email protected]']],
        ['The Secrets of Midnight Garden', '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...', ['[email protected]']]
    ]
});

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 [email protected]\nBeauty and the Beast...',
        (
            'John Doe',
            '[email protected]'
        )
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
        (
            'Alice Blue',
            '[email protected]'
        )
    ) RETURNING *`
);
// (b): Object-based syntax (1)
const result = await client.database('public').table('books').insert({
    data: [
        { title: 'Beauty and the Beast', content: '(C) 2024 [email protected]\nBeauty and the Beast...', author: {
            name: 'John Doe',
            email: '[email protected]'
        } },
        { title: 'The Secrets of Midnight Garden', content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...', author: {
            name: 'Alice Blue',
            email: '[email protected]'
        } }
    ],
    returning: '*'
});
// (c): Object-based syntax (2)
const result = await client.database('public').table('books').insert({
    columns: [ 'title', 'content', { author: ['name', 'email'] } ],
    values: [
        ['Beauty and the Beast', '(C) 2024 [email protected]\nBeauty and the Beast...', [
            'John Doe',
            '[email protected]'
        ]],
        ['The Secrets of Midnight Garden', '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...', [
            'Alice Blue',
            '[email protected]'
        ]]
    ],
    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',
        '[email protected]',
        (
            'Beauty and the Beast',
            '(C) 2024 [email protected]\nBeauty and the Beast...'
        )
    ), (
        'Alice Blue',
        '[email protected]',
        (
            'The Secrets of Midnight Garden',
            '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'
        )
    ) RETURNING *`
);
// (b): Object-based syntax (1)
const result = await client.database('public').table('users').insert({
    data: [
        { name: 'John Doe', email: '[email protected]', books: [
            { title: 'Beauty and the Beast', content: '(C) 2024 [email protected]\nBeauty and the Beast...' }
        ] },
        { name: 'Alice Blue', email: '[email protected]', books: [
            { title: 'The Secrets of Midnight Garden', content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...' }
        ] }
    ],
    returning: '*'
});

Note

This syntax uses the second table name (books) as key with assumption that the implied table is located within the same schema (public) as the base table.

It also makes the assumption that there is only one foreign key in the second table (books) referencing the base table (users).

An error is thrown where any of these assumptions fails.

// (c): Object-based syntax (2)
const result = await client.database('public').table('users').insert({
    columns: ['name', 'email', { books: ['title', 'content'] }],
    values: [
        ['John Doe', '[email protected]', [
            ['Beauty and the Beast', '(C) 2024 [email protected]\nBeauty and the Beast...']
        ]],
        ['Alice Blue', '[email protected]', [
            ['The Secrets of Midnight Garden', '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...']
        ]]
    ],
    returning: '*'
});

Tip

This syntax lets you work at a more granular level. While the above, by default, makes the same assumptions as in the first syntax, you are able to:

  1. explicitly specify the relevant foreign key using an explicit path expression, potentially supporting any number of references from second table to base table:
{
    columns: ['name', 'email', { rpath: [{ lpath: ['author', 'books'] }, { columns: ['title', 'content'] }] }],
    explicitPaths: true,
    values: [...]
}
  1. extend the previous to optionally qualify the second table name:
{
    columns: ['name', 'email', { rpath: [{ lpath: ['author', ['public', 'books']] }, { columns: ['title', 'content'] }] }],
    explicitPaths: true,
    values: [...]
}

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',
        '[email protected]',
        VALUES (
            (
                'Beauty and the Beast - Part 1',
                '(C) 2024 [email protected]\nBeauty and the Beast...'
            ), (
                'Beauty and the Beast - Part 2',
                '(C) 2024 [email protected]\nBeauty and the Beast...'
            )
        )
    ), (
        'Alice Blue',
        '[email protected]',
        VALUES (
            (
                'The Secrets of Midnight Garden - Part 1',
                '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'
            ), (
                'The Secrets of Midnight Garden - Part 2',
                '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'
            )
        )
    ) RETURNING *`
);
// (b): Object-based syntax (1)
const result = await client.database('public').table('users').insert({
    data: [
        { name: 'John Doe', email: '[email protected]', books: [
            { title: 'Beauty and the Beast - Part 1', content: '(C) 2024 [email protected]\nBeauty and the Beast...' },
            { title: 'Beauty and the Beast - Part 2', content: '(C) 2024 [email protected]\nBeauty and the Beast...' }
        ] },
        { name: 'Alice Blue', email: '[email protected]', books: [
            { title: 'The Secrets of Midnight Garden - Part 1', content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...' },
            { title: 'The Secrets of Midnight Garden - Part 2', content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...' }
        ] }
    ],
    returning: '*'
});
// (c): Object-based syntax (2)
const result = await client.database('public').table('users').insert({
    columns: ['name', 'email', { books: ['title', 'content'] } ],
    values: [
        ['John Doe', '[email protected]', [
            ['Beauty and the Beast - Part 1', '(C) 2024 [email protected]\nBeauty and the Beast...'],
            ['Beauty and the Beast - Part 2', '(C) 2024 [email protected]\nBeauty and the Beast...']
        ]],
        ['Alice Blue', '[email protected]', [
            ['The Secrets of Midnight Garden - Part 1', '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'],
            ['The Secrets of Midnight Garden - Part 2', '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...']
        ]]
    ],
    returning: '*'
});
Add a custom footer
Add a custom sidebar
Clone this wiki locally