Insert Queries

Learn how to insert data into your database using Kysely’s InsertQueryBuilder with type-safe insert statements.

The InsertQueryBuilder is used to build and execute INSERT queries in Kysely. It provides a fluent API for inserting single or multiple rows with type safety.

Basic Usage

Insert a Single Row

Insert a single row into a table:

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.
console.log(result.insertId)

The generated SQL (MySQL):

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

Insert Multiple Rows

On dialects that support it (for example PostgreSQL) you can insert multiple rows by providing an array:

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))

Returning Data

On supported dialects like PostgreSQL you need to chain returning to the query to get the inserted row’s columns:

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"

You can return all columns:

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

Complex Values

Values can be arbitrary expressions including subqueries and raw SQL:

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<string>`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")
)

Insert from Subquery

You can create an INSERT INTO SELECT FROM query using the expression method:

const result = await db.insertInto('person')
  .columns(['first_name', 'last_name', 'age'])
  .expression((eb) => eb
    .selectFrom('pet')
    .select((eb) => [
      'pet.name',
      eb.val('Petson').as('last_name'),
      eb.lit(7).as('age'),
    ])
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "last_name", "age")
select "pet"."name", $1 as "last_name", 7 as "age" from "pet"

Default Values

Insert default values:

await db.insertInto('person')
  .defaultValues()
  .execute()

The generated SQL (PostgreSQL):

insert into "person" default values

Conflict Handling

On Conflict (PostgreSQL)

Handle conflicts using onConflict:

// Do nothing on conflict
await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston',
    age: 40,
  })
  .onConflict((oc) => oc
    .column('email')
    .doNothing()
  )
  .execute()

// Update on conflict
await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston',
    age: 40,
  })
  .onConflict((oc) => oc
    .column('email')
    .doUpdateSet({
      first_name: 'Jennifer',
      last_name: 'Aniston',
    })
  )
  .execute()

Insert Ignore (MySQL)

Use ignore() to ignore duplicate key errors:

await db.insertInto('person')
  .ignore()
  .values({
    first_name: 'John',
    last_name: 'Doe',
    gender: 'female',
  })
  .execute()

The generated SQL (MySQL):

insert ignore into `person` (`first_name`, `last_name`, `gender`) values (?, ?, ?)

On Duplicate Key Update (MySQL)

Handle duplicates with update:

await db.insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston',
    age: 40,
  })
  .onDuplicateKeyUpdate({
    age: 41,
  })
  .execute()

SQLite Conflict Actions

SQLite supports various conflict actions:

// Insert or ignore
await db.insertInto('person')
  .orIgnore()
  .values({ first_name: 'John', last_name: 'Doe' })
  .execute()

// Insert or replace
await db.insertInto('person')
  .orReplace()
  .values({ first_name: 'John', last_name: 'Doe' })
  .execute()

// Insert or abort
await db.insertInto('person')
  .orAbort()
  .values({ first_name: 'John', last_name: 'Doe' })
  .execute()

// Insert or fail
await db.insertInto('person')
  .orFail()
  .values({ first_name: 'John', last_name: 'Doe' })
  .execute()

// Insert or rollback
await db.insertInto('person')
  .orRollback()
  .values({ first_name: 'John', last_name: 'Doe' })
  .execute()

API Reference

Main Methods

• insertInto(table) - Specify the table to insert into
• values(values) - Set the values to insert
• columns(columns) - Set the columns (used with expression)
• expression(expression) - Insert from a subquery or expression
• defaultValues() - Insert default values
• returning(columns) - Return columns from inserted rows (PostgreSQL, SQLite)
• returningAll() - Return all columns from inserted rows
• onConflict(callback) - Handle conflicts (PostgreSQL)
• onDuplicateKeyUpdate(updates) - Update on duplicate key (MySQL)
• ignore() - Insert ignore (MySQL)
• orIgnore(), orReplace(), orAbort(), orFail(), orRollback() - SQLite conflict actions
• modifyEnd(modifier) - Add custom SQL to the end
• execute() - Execute the query
• executeTakeFirst() - Execute and return first result
• executeTakeFirstOrThrow() - Execute and return first result or throw

Result Object

The return value is an instance of InsertResult:

interface InsertResult {
  /**
   * The auto incremented primary key (MySQL, SQLite)
   */
  insertId?: bigint | undefined

  /**
   * The number of rows inserted
   */
  numInsertedRows?: bigint | undefined
}