import { kysely } from "https://deno.land/x/kysely_postgrs_js_dialect@v0.27.4/mod.ts";
const { InsertQueryBuilder } = kysely;
Sets the values to insert for an 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
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 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',
}))