Skip to content
Saasfly Docs

Kysely Update

Short and simple examples of how to write update 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
  .updateTable('person')
  .set({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .where('id', '=', '1')
  .executeTakeFirst()


console.log(result.numUpdatedRows)

Result

UPDATE "person"
SET
  "first_name" = $1,
  "last_name" = $2
WHERE
  "id" = $3


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

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

const result = await db
  .updateTable('person')
  .set((eb) => ({
    age: eb('age', '+', 1),
    first_name: eb.selectFrom('pet').select('name').limit(1),
    last_name: 'updated',
  }))
  .where('id', '=', '1')
  .executeTakeFirst()


console.log(result.numUpdatedRows)

Result

UPDATE "person"
SET
  "age" = "age" + $1,
  "first_name" = (
    SELECT
      "name"
    FROM
      "pet"
    LIMIT
      $2
  ),
  "last_name" = $3
WHERE
  "id" = $4


-- Parameters
-- [1] 1
-- [2] 1
-- [3] updated
-- [4] 1