Hoppa till innehåll
Saasfly Docs

Kysely Insert

Det här innehållet är inte tillgängligt på ditt språk än.

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