Sets the values to insert for an {@link Kysely.insertInto | 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 {@link InsertResult.insertId | 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 {@link expression} method for inserting the result of a select query or any other expression.

Examples

Insert a single row:

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

// `insertId` is only available on dialects that
// automatically return the id of the inserted row
// such as MySQL and SQLite. On PostgreSQL, for example,
// you need to add a `returning` clause to the query to
// get anything out. See the "returning data" example.
console.log(result.insertId)

The generated SQL (MySQL):

insert into `person` (`first_name`, `last_name`, `age`) values (?, ?, ?)

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',
    age: 40,
  }, {
    first_name: 'Arnold',
    last_name: 'Schwarzenegger',
    age: 70,
  }])
  .execute()

The generated SQL (PostgreSQL):

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

On supported dialects like PostgreSQL you need to chain returning to the query to get the inserted row's columns (or any other expression) as the return value. returning works just like select. Refer to select method's examples and documentation for more info.

const result = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston',
    age: 40,
  })
  .returning(['id', 'first_name as name'])
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "last_name", "age") values ($1, $2, $3) returning "id", "first_name" as "name"

In addition to primitives, the values can also be arbitrary expressions. You can build the expressions by using a callback and calling the methods on the expression builder passed to it:

import { sql } from 'kysely'

const ani = "Ani"
const ston = "ston"

const result = await db
  .insertInto('person')
  .values(({ ref, selectFrom, fn }) => ({
    first_name: 'Jennifer',
    last_name: sql`concat(${ani}, ${ston})`,
    middle_name: ref('first_name'),
    age: selectFrom('person')
      .select(fn.avg<number>('age')
      .as('avg_age')),
  }))
  .executeTakeFirst()

The generated SQL (PostgreSQL):

insert into "person" (
  "first_name",
  "last_name",
  "middle_name",
  "age"
)
values (
  $1,
  concat($2, $3),
  "first_name",
  (select avg("age") as "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((eb) => ({
  owner_id: eb.selectFrom('jennifer').select('id'),
  name: eb.selectFrom('jennifer').select('first_name'),
  species: 'cat',
}))