Class InsertQueryBuilder<DB, TB, O>

Asserts that query's output row type equals the given type T.

This method can be used to simplify excessively complex types to make typescript happy and much faster.

Kysely uses complex type magic to achieve its type safety. This complexity is sometimes too much for typescript and you get errors like this:

error TS2589: Type instantiation is excessively deep and possibly infinite.

In these case you can often use this method to help typescript a little bit. When you use this method to assert the output type of a query, Kysely can drop the complex output type that consists of multiple nested helper types and replace it with the simple asserted type.

Using this method doesn't reduce type safety at all. You have to pass in a type that is structurally equal to the current type.

Examples

const result = await db
  .with('new_person', (qb) => qb
    .insertInto('person')
    .values(person)
    .returning('id')
    .$assertType<{ id: string }>()
  )
  .with('new_pet', (qb) => qb
    .insertInto('pet')
    .values({ owner_id: (eb) => eb.selectFrom('new_person').select('id'), ...pet })
    .returning(['name as pet_name', 'species'])
    .$assertType<{ pet_name: string, species: Species }>()
  )
  .selectFrom(['new_person', 'new_pet'])
  .selectAll()
  .executeTakeFirstOrThrow()

Simply calls the given function passing this as the only argument.

If you want to conditionally call a method on this, see the $if method.

Examples

The next example uses a helper function log to log a query:

function log<T extends Compilable>(qb: T): T {
  console.log(qb.compile())
  return qb
}

db.updateTable('person')
  .set(values)
  .$call(log)
  .execute()

Change the output type of the query.

You should only use this method as the last resort if the types don't support your use case.

Call func(this) if condition is true.

This method is especially handy with optional selects. Any returning or returningAll method calls add columns as optional fields to the output type when called inside the func callback. This is because we can't know if those selections were actually made before running the code.

You can also call any other methods inside the callback.

Examples

async function insertPerson(values: InsertablePerson, returnLastName: boolean) {
  return await db
    .insertInto('person')
    .values(values)
    .returning(['id', 'first_name'])
    .$if(returnLastName, (qb) => qb.returning('last_name'))
    .executeTakeFirstOrThrow()
}

Any selections added inside the if callback will be added as optional fields to the output type since we can't know if the selections were actually made before running the code. In the example above the return type of the insertPerson function is:

{
  id: number
  first_name: string
  last_name?: string
}

Deprecated

Use $assertType instead.

Deprecated

Use $call instead

Deprecated

Use $castTo instead.

Sets the columns to insert.

The values method sets both the columns and the values and this method is not needed. But if you are using the expression method, you can use this method to set the columns to insert.

Examples

db.insertInto('person')
  .columns(['first_name'])
  .expression((eb) => eb.selectFrom('pet').select('pet.name'))

The generated SQL (PostgreSQL):

insert into "person" ("first_name")
select "pet"."name" from "pet"

Executes the query and returns an array of rows.

Also see the executeTakeFirst and executeTakeFirstOrThrow methods.

Executes the query and returns the first result or undefined if the query returned no result.

Executes the query and returns the first result or throws if the query returned no result.

By default an instance of NoResultError is thrown, but you can provide a custom error class as the only argument to throw a different error.

Executes query with explain statement before insert keyword.

const explained = await db
 .insertInto('person')
 .values(values)
 .explain('json')

The generated SQL (MySQL):

explain format=json insert into `person` (`id`, `first_name`, `last_name`) values (?, ?, ?) (?, ?, ?)

Insert an arbitrary expression. For example the result of a select query.

Examples

db.insertInto('person')
  .columns(['first_name'])
  .expression((eb) => eb.selectFrom('pet').select('pet.name'))

The generated SQL (PostgreSQL):

insert into "person" ("first_name")
select "pet"."name" from "pet"

Deprecated

Use $if instead

Changes an insert into query to an insert ignore into query.

If you use the ignore modifier, ignorable errors that occur while executing the insert statement are ignored. For example, without ignore, a row that duplicates an existing unique index or primary key value in the table causes a duplicate-key error and the statement is aborted. With ignore, the row is discarded and no error occurs.

This is only supported on some dialects like MySQL. On most dialects you should use the onConflict method.

Examples

await db.insertInto('person')
  .ignore()
  .values(values)
  .execute()

Adds an on conflict clause to the query.

on conflict is only supported by some dialects like PostgreSQL and SQLite. On MySQL you can use ignore and onDuplicateKeyUpdate to achieve similar results.

Examples

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .column('name')
    .doUpdateSet({ species: 'hamster' })
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict ("name")
do update set "species" = $3

You can provide the name of the constraint instead of a column name:

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .constraint('pet_name_key')
    .doUpdateSet({ species: 'hamster' })
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict on constraint "pet_name_key"
do update set "species" = $3

You can also specify an expression as the conflict target in case the unique index is an expression index:

import { sql } from 'kysely'

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .expression(sql`lower(name)`)
    .doUpdateSet({ species: 'hamster' })
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict (lower(name))
do update set "species" = $3

You can add a filter for the update statement like this:

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .column('name')
    .doUpdateSet({ species: 'hamster' })
    .where('excluded.name', '!=', 'Catto'')
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict ("name")
do update set "species" = $3
where "excluded"."name" != $4

You can create an on conflict do nothing clauses like this:

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .column('name')
    .doNothing()
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict ("name") do nothing

You can refer to the columns of the virtual excluded table in a type-safe way using a callback and the ref method of ExpressionBuilder:

db.insertInto('person')
  .values(person)
  .onConflict(oc => oc
    .column('id')
    .doUpdateSet({
      first_name: (eb) => eb.ref('excluded.first_name'),
      last_name: (eb) => eb.ref('excluded.last_name')
    })
  )

Adds on duplicate key update to the query.

If you specify on duplicate key update, and a row is inserted that would cause a duplicate value in a unique index or primary key, an update of the old row occurs.

This is only implemented by some dialects like MySQL. On most dialects you should use onConflict instead.

Examples

await db
  .insertInto('person')
  .values(values)
  .onDuplicateKeyUpdate({ species: 'hamster' })

Allows you to return data from modified rows.

On supported databases like PostgreSQL, this method can be chained to insert, update and delete queries to return data.

Note that on SQLite you need to give aliases for the expressions to avoid this bug in SQLite. For example .returning('id as id').

Also see the returningAll method.

Examples

Return one column:

const { id } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning('id')
  .executeTakeFirst()

Return multiple columns:

const { id, first_name } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning(['id', 'last_name'])
  .executeTakeFirst()

Return arbitrary expressions:

import { sql } from 'kysely'

const { id, full_name, first_pet_id } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning([
    'id as id',
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name'),
    (qb) => qb.selectFrom('pets').select('pet.id').limit(1).as('first_pet_id')
  ])
  .executeTakeFirst()

Adds a returning * to an insert/update/delete query on databases that support returning such as PostgreSQL.

Sets the values to insert for an insert query.

This method takes an object whose keys are column names and values are values to insert. In addition to the column's type, the values can be raw sql snippets or select queries.

You must provide all fields you haven't explicitly marked as nullable or optional using Generated or ColumnType.

The return value of an insert query is an instance of InsertResult. The insertId field holds the auto incremented primary key if the database returned one.

On PostgreSQL and some other dialects, you need to call returning to get something out of the query.

Also see the expression method for inserting the result of a select query or any other expression.

Examples

Insert a row into person:

const result = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "last_name") values ($1, $2)

On dialects that support it (for example PostgreSQL) you can insert multiple rows by providing an array. Note that the return value is once again very dialect-specific. Some databases may only return the id of the last inserted row and some return nothing at all unless you call returning.

await db
  .insertInto('person')
  .values([{
    first_name: 'Jennifer',
    last_name: 'Aniston'
  }, {
    first_name: 'Arnold',
    last_name: 'Schwarzenegger',
  }])
  .execute()

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "last_name") values (($1, $2), ($3, $4))

On PostgreSQL you need to chain returning to the query to get the inserted row's columns (or any other expression) as the return value:

const row = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning('id')
  .executeTakeFirstOrThrow()

row.id

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "last_name") values ($1, $2) returning "id"

In addition to primitives, the values can also be raw sql expressions or select queries:

import { sql } from 'kysely'

const result = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: sql`${'Ani'} || ${'ston'}`,
    age: db.selectFrom('person').select(sql`avg(age)`),
  })
  .executeTakeFirst()

console.log(result.insertId)

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "last_name", "age")
values ($1, $2 || $3, (select avg(age) from "person"))

You can also use the callback version of subqueries or raw expressions:

db.with('jennifer', (db) => db
  .selectFrom('person')
  .where('first_name', '=', 'Jennifer')
  .select(['id', 'first_name', 'gender'])
  .limit(1)
).insertInto('pet').values({
  owner_id: (eb) => eb.selectFrom('jennifer').select('id'),
  name: (eb) => eb.selectFrom('jennifer').select('first_name'),
  species: 'cat',
})

Returns a copy of this InsertQueryBuilder instance with the given plugin installed.