Last Updated: 3/20/2026

CRUD Operations

Kysely provides type-safe query builders for all CRUD operations: INSERT, UPDATE, and DELETE. Each operation returns a result object with metadata about what changed in the database.

INSERT

The insertInto method creates an insert query. It takes a table name and returns an InsertQueryBuilder:


const result = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston',
    age: 40,
  })
  .executeTakeFirst()
 
console.log(result.insertId)

Insert Result

The execute methods return an InsertResult object (see src/query-builder/insert-result.ts):


class InsertResult {
  readonly insertId: bigint | undefined
  readonly numInsertedOrUpdatedRows: bigint | undefined
}

insertId — The auto-incrementing primary key of the inserted row (MySQL, SQLite). On PostgreSQL this is undefined unless you use returning().
numInsertedOrUpdatedRows — The number of rows inserted. On MySQL with onDuplicateKeyUpdate, updated rows are counted twice.

Inserting Multiple Rows

Pass an array to values to insert multiple rows:


await db
  .insertInto('person')
  .values([
    { first_name: 'Jennifer', age: 40 },
    { first_name: 'Arnold', age: 50 },
  ])
  .execute()

Returning Inserted Data

On databases that support RETURNING (PostgreSQL, SQLite 3.35+), use the returning method to get back the inserted data:


const person = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    age: 40,
  })
  .returning(['id', 'first_name'])
  .executeTakeFirst()
 
// person: { id: number; first_name: string } | undefined
console.log(person?.id)

Use returningAll() to return all columns:


const person = await db
  .insertInto('person')
  .values({ first_name: 'Jennifer', age: 40 })
  .returningAll()
  .executeTakeFirstOrThrow()
 
// person: { id: number; first_name: string; last_name: string | null; age: number }

Upserts with onConflict

The onConflict method handles insert conflicts (PostgreSQL, SQLite). Use it to implement upsert logic:


await db
  .insertInto('person')
  .values({
    id: 1,
    first_name: 'Jennifer',
    age: 40,
  })
  .onConflict((oc) => oc
    .column('id')
    .doUpdateSet({
      first_name: 'Jennifer',
      age: 40,
    })
  )
  .execute()

The generated SQL (PostgreSQL):


insert into "person" ("id", "first_name", "age")
values ($1, $2, $3)
on conflict ("id")
do update set "first_name" = $4, "age" = $5

To reference the excluded (conflicting) row, use the expression builder:


await db
  .insertInto('person')
  .values({ id: 1, first_name: 'Jennifer', age: 40 })
  .onConflict((oc) => oc
    .column('id')
    .doUpdateSet((eb) => ({
      first_name: eb.ref('excluded.first_name'),
      age: eb.ref('excluded.age'),
    }))
  )
  .execute()

The excluded table is a virtual table that contains the row that would have been inserted. This pattern creates a true upsert: insert if the row doesn’t exist, update if it does.

You can also use doNothing() to silently ignore conflicts:


await db
  .insertInto('person')
  .values({ id: 1, first_name: 'Jennifer' })
  .onConflict((oc) => oc.column('id').doNothing())
  .execute()

The OnConflictBuilder class (see src/query-builder/on-conflict-builder.ts) provides methods for specifying the conflict target: column, columns, constraint, or expression.

MySQL Upserts

MySQL uses onDuplicateKeyUpdate instead of onConflict:


await db
  .insertInto('person')
  .values({ id: 1, first_name: 'Jennifer', age: 40 })
  .onDuplicateKeyUpdate({
    first_name: 'Jennifer',
    age: 40,
  })
  .execute()

The generated SQL (MySQL):


insert into `person` (`id`, `first_name`, `age`)
values (?, ?, ?)
on duplicate key update `first_name` = ?, `age` = ?

UPDATE

The updateTable method creates an update query. It takes a table name and returns an UpdateQueryBuilder:


const result = await db
  .updateTable('person')
  .set({
    first_name: 'Jennifer',
    age: 41,
  })
  .where('id', '=', 1)
  .executeTakeFirst()
 
console.log(result.numUpdatedRows)

Update Result

The execute methods return an UpdateResult object (see src/query-builder/update-result.ts):


class UpdateResult {
  readonly numUpdatedRows: bigint
  readonly numChangedRows?: bigint
}

numUpdatedRows — The number of rows matched by the WHERE clause (even if their values didn’t change).
numChangedRows — The number of rows whose values actually changed (MySQL only).

The set Method

The set method specifies which columns to update. It accepts an object:


await db
  .updateTable('person')
  .set({
    first_name: 'Jennifer',
    last_name: 'Aniston',
  })
  .where('id', '=', 1)
  .execute()

You can use the expression builder to reference other columns or build complex expressions:


await db
  .updateTable('person')
  .set((eb) => ({
    age: eb('age', '+', 1),
  }))
  .where('id', '=', 1)
  .execute()

This increments the age column by 1.

Conditional Updates

Use where to filter which rows to update:


await db
  .updateTable('person')
  .set({ age: 41 })
  .where('first_name', '=', 'Jennifer')
  .where('last_name', '=', 'Aniston')
  .execute()

Always use a WHERE clause unless you intentionally want to update every row. An update without a WHERE clause modifies the entire table.

Returning Updated Data

Like inserts, updates support returning on databases that allow it:


const updatedPerson = await db
  .updateTable('person')
  .set({ age: 41 })
  .where('id', '=', 1)
  .returning(['id', 'first_name', 'age'])
  .executeTakeFirst()
 
console.log(updatedPerson?.age) // 41

DELETE

The deleteFrom method creates a delete query. It takes a table name and returns a DeleteQueryBuilder:


const result = await db
  .deleteFrom('person')
  .where('id', '=', 1)
  .executeTakeFirst()
 
console.log(result.numDeletedRows)

Delete Result

The execute methods return a DeleteResult object (see src/query-builder/delete-result.ts):


class DeleteResult {
  readonly numDeletedRows: bigint
}

numDeletedRows — The number of rows deleted.

Conditional Deletes

Use where to filter which rows to delete:


await db
  .deleteFrom('person')
  .where('age', '<', 18)
  .execute()

Always use a WHERE clause unless you intentionally want to delete every row. A delete without a WHERE clause empties the entire table.

Returning Deleted Data

Like inserts and updates, deletes support returning:


const deletedPeople = await db
  .deleteFrom('person')
  .where('age', '<', 18)
  .returningAll()
  .execute()
 
console.log(`Deleted ${deletedPeople.length} people`)

Execution Methods

All query builders provide three execution methods:

execute()

Returns a promise of an array of results. For INSERT/UPDATE/DELETE, the array contains one result object per statement executed:


const results = await db
  .insertInto('person')
  .values({ first_name: 'Jennifer' })
  .execute()
 
// results: InsertResult[]
console.log(results[0].insertId)

executeTakeFirst()

Returns the first result or undefined:


const result = await db
  .updateTable('person')
  .set({ age: 41 })
  .where('id', '=', 1)
  .executeTakeFirst()
 
// result: UpdateResult | undefined
if (result) {
  console.log(result.numUpdatedRows)
}

executeTakeFirstOrThrow()

Returns the first result or throws a NoResultError:


const result = await db
  .deleteFrom('person')
  .where('id', '=', 1)
  .executeTakeFirstOrThrow()
 
// result: DeleteResult (throws if no rows matched)
console.log(result.numDeletedRows)

You can provide a custom error constructor:


class PersonNotFoundError extends Error {
  constructor() {
    super('Person not found')
  }
}
 
await db
  .deleteFrom('person')
  .where('id', '=', 1)
  .executeTakeFirstOrThrow(PersonNotFoundError)

Type Safety in Mutations

Kysely validates mutation payloads against your database schema. The values and set methods only accept columns that exist in the table, with the correct types:


// ✅ Valid
await db.insertInto('person').values({
  first_name: 'Jennifer', // string
  age: 40,                // number
})
 
// ❌ Type error: 'email' doesn't exist on 'person'
await db.insertInto('person').values({
  email: 'jennifer@example.com',
})
 
// ❌ Type error: 'age' expects number, got string
await db.insertInto('person').values({
  first_name: 'Jennifer',
  age: 'forty',
})

For inserts, Kysely uses the Insertable<T> type (see src/util/column-type.ts). This makes Generated columns optional and enforces the insert type from ColumnType.

For updates, Kysely uses the Updateable<T> type, which makes all columns optional and enforces the update type from ColumnType.

What’s Next

Transactions: Group multiple queries into atomic units with automatic or manual commit/rollback, isolation levels, and savepoints.
Expressions: Build complex WHERE conditions, subqueries, and computed values using the expression builder.