Skip to content

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