INSERT

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 (1)
const result = await client.database('public').table('users').insert({
    data: {
        name: 'Jane',
        email: 'jane@example.com'
    }
});

// (c): Object-based syntax (2)
const result = await client.database('public').table('users').insert({
    columns: ['name', 'email']
    values: [
        ['Jane', 'jane@example.com']
    ]
});

The COLUMNS/VALUES Clause

Examples coming soon.

The SELECT Clause

Examples coming soon.

The SET Clause

Examples coming soon.

The ON CONFLICT Clause

Examples coming soon.

The RETURNING Clause

Examples coming soon.

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): Function-based syntax (1)
const result = await client.database('public').table('books').insert({
    data: [
        { 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' } }
    ]
});

// (c): Object-based syntax (2)
const result = await client.database('public').table('books').insert({
    columns: [ 'title', 'content', { rpath: ['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']]
    ]
});

Tip

This syntax lets you be explicit about relationships using paths. But you can also opt in to shorthand paths:

{
    shorthands: true,
    columns: ['title', 'content', { author: 'email' }],
    values: [...]
}

And in fact, that can take the form:

{
    shorthands: true,
    columns: { title: true, content: true, author: 'email' },
    values: [...]
}

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({
    data: [
        { 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: '*'
});

// (c): Object-based syntax (2)
const result = await client.database('public').table('books').insert({
    columns: [ 'title', 'content', { rpath: ['author', { columns: ['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: '*'
});

Tip

As before, this syntax lets you be explicit about relationships using paths. But you can also opt in to shorthand paths:

{
    shorthands: true,
    columns: ['title', 'content', { author: ['name', 'email'] }],
    values: [...]
}

And in fact, that can take the form:

{
    shorthands: true,
    columns: { title: true, content: true, author: { name: true, email: true } },
    //OR: columns: { title: true, content: true, author: ['name', 'email'] },
    values: [...]
}

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({
    data: [
        { 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: '*'
});

Note

This syntax uses the second table name (books) as key with the 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', { rpath: [{ lpath: ['author', 'books'] }, { columns: ['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: '*'
});

Tip

As before, this syntax lets you be explicit about relationships using paths.

You're even able to qualify the implied table name where necessary:

{
    columns: ['name', 'email', { rpath: [{ lpath: ['author', ['public', 'books']] }, { columns: ['title', 'content'] }] }],
    values: [...]
}

But where the assumptions of the first syntax also hold, you can opt in to shorthand paths:

{
    shorthands: true,
    columns: ['name', 'email', { books: ['title', 'content'] }],
    values: [...]
}

And in fact, that can take the form:

{
    shorthands: true,
    columns: { name: true, email: true, books: { title: true, content: true } },
    //OR: columns: { name: true, email: true, books: ['title', 'content'] },
    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',
        '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({
    data: [
        { 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: '*'
});

// (c): Object-based syntax (2)
const result = await client.database('public').table('users').insert({
    shorthands: true,
    columns: ['name', 'email', { books: ['title', 'content'] } ],
    values: [
        ['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: '*'
});