This content is not available in your language yet.
Short and simple examples of how to write insert queries.
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 }}
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)
INSERT INTO "person" ("first_name", "last_name", "age")VALUES ($1, $2, $3) -- Parameters-- [1] Jennifer-- [2] Aniston-- [3] 40
INSERT INTO `person` (`first_name`, `last_name`, `age`)VALUES (?, ?, ?) -- Parameters-- [1] Jennifer-- [2] Aniston-- [3] 40
INSERT INTO "person" ("first_name", "last_name", "age")VALUES (?, ?, ?) -- Parameters-- [1] Jennifer-- [2] Aniston-- [3] 40
await db .insertInto('person') .values([{ first_name: 'Jennifer', last_name: 'Aniston', age: 40, }, { first_name: 'Arnold', last_name: 'Schwarzenegger', age: 70, }]) .execute()
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
INSERT INTO `person` (`first_name`, `last_name`, `age`)VALUES (?, ?, ?), (?, ?, ?) -- Parameters-- [1] Jennifer-- [2] Aniston-- [3] 40-- [4] Arnold-- [5] Schwarzenegger-- [6] 70
INSERT INTO "person" ("first_name", "last_name", "age")VALUES (?, ?, ?), (?, ?, ?) -- Parameters-- [1] Jennifer-- [2] Aniston-- [3] 40-- [4] Arnold-- [5] Schwarzenegger-- [6] 70
const result = await db .insertInto('person') .values({ first_name: 'Jennifer', last_name: 'Aniston', age: 40, }) .returning(['id', 'first_name as name']) .executeTakeFirstOrThrow()
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
INSERT INTO `person` (`first_name`, `last_name`, `age`)VALUES (?, ?, ?) returning `id`, `first_name` AS `name` -- Parameters-- [1] Jennifer-- [2] Aniston-- [3] 40
INSERT INTO "person" ("first_name", "last_name", "age")VALUES (?, ?, ?) RETURNING "id", "first_name" AS "name" -- Parameters-- [1] Jennifer-- [2] Aniston-- [3] 40
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()
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 INTO `person` (`first_name`, `last_name`, `middle_name`, `age`)VALUES ( ?, CONCAT(?, ?), `first_name`, ( SELECT AVG(`age`) AS `avg_age` FROM `person` ) ) -- Parameters-- [1] Jennifer-- [2] Ani-- [3] ston
INSERT INTO "person" ("first_name", "last_name", "middle_name", "age")VALUES ( ?, concat (?, ?), "first_name", ( SELECT AVG("age") AS "avg_age" FROM "person" ) ) -- Parameters-- [1] Jennifer-- [2] Ani-- [3] ston
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()
INSERT INTO "person" ("first_name", "last_name", "age")SELECT "pet"."name", $1 AS "last_name", 7 AS "age"FROM "pet" -- Parameters-- [1] Petson
INSERT INTO `person` (`first_name`, `last_name`, `age`)SELECT `pet`.`name`, ? AS `last_name`, 7 AS `age`FROM `pet` -- Parameters-- [1] Petson
INSERT INTO "person" ("first_name", "last_name", "age")SELECT "pet"."name", ? AS "last_name", 7 AS "age"FROM "pet" -- Parameters-- [1] Petson