Skip to content
Saasfly Docs

Kysely Insert

This content is not available in your language yet.

Short and simple examples of how to write insert queries.

Single row

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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)

Result

INSERT INTO
  "person" ("first_name", "last_name", "age")
VALUES
  ($1, $2, $3)


-- Parameters
-- [1] Jennifer
-- [2] Aniston
-- [3] 40

Multiple rows

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

INSERT INTO
  "person" ("first_name", "last_name", "age")
VALUES
  ($1, $2, $3),
  ($4, $5, $6)


-- Parameters
-- [1] Jennifer
-- [2] Aniston
-- [3] 40
-- [4] Arnold
-- [5] Schwarzenegger
-- [6] 70

Returning data

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

INSERT INTO
  "person" ("first_name", "last_name", "age")
VALUES
  ($1, $2, $3)
RETURNING
  "id",
  "first_name" AS "name"


-- Parameters
-- [1] Jennifer
-- [2] Aniston
-- [3] 40

Complex values

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

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


-- Parameters
-- [1] Jennifer
-- [2] Ani
-- [3] ston

Insert subquery

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

INSERT INTO
  "person" ("first_name", "last_name", "age")
SELECT
  "pet"."name",
  $1 AS "last_name",
  7 AS "age"
FROM
  "pet"


-- Parameters
-- [1] Petson