Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/kysely-org/kysely/llms.txt

Use this file to discover all available pages before exploring further.

The DeleteQueryBuilder is used to build and execute DELETE queries in Kysely. It provides a fluent API for deleting rows with type safety.

​
Basic Usage

​
Delete Rows

Delete rows from a table: 
const result = await db
  .deleteFrom('person')
  .where('id', '=', 1)
  .executeTakeFirst()

console.log(result.numDeletedRows)
The generated SQL (PostgreSQL): 
delete from "person" where "id" = $1

​
Delete with Multiple Conditions

await db
  .deleteFrom('person')
  .where('first_name', '=', 'Jennifer')
  .where('age', '>', 40)
  .execute()

​
Returning Data

On PostgreSQL and SQLite, you can use returning to get deleted rows: 
const deletedPerson = await db
  .deleteFrom('person')
  .where('id', '=', 1)
  .returning(['id', 'first_name'])
  .executeTakeFirst()
The generated SQL (PostgreSQL): 
delete from "person" where "id" = $1 returning "id", "first_name"
Return all columns: 
const deletedPerson = await db
  .deleteFrom('person')
  .where('id', '=', 1)
  .returningAll()
  .executeTakeFirst()

​
Using Clause (PostgreSQL, MySQL)

​
PostgreSQL: Using for Additional Tables

The using clause allows adding additional tables for filtering: 
await db
  .deleteFrom('pet')
  .using('person')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('person.first_name', '=', 'Bob')
  .executeTakeFirstOrThrow()
The generated SQL (PostgreSQL): 
delete from "pet"
using "person"
where "pet"."owner_id" = "person"."id"
  and "person"."first_name" = $1

​
MySQL: Using with Joins

On MySQL, the using clause allows using joins: 
await db
  .deleteFrom('pet')
  .using('pet')
  .leftJoin('person', 'person.id', 'pet.owner_id')
  .where('person.first_name', '=', 'Bob')
  .executeTakeFirstOrThrow()
The generated SQL (MySQL): 
delete from `pet`
using `pet`
left join `person` on `person`.`id` = `pet`.`owner_id`
where `person`.`first_name` = ?

​
Multiple Tables in Using

await db
  .deleteFrom('toy')
  .using(['pet', 'person'])
  .whereRef('toy.pet_id', '=', 'pet.id')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('person.first_name', '=', 'Bob')
  .returning('pet.name')
  .executeTakeFirstOrThrow()
The generated SQL (PostgreSQL): 
delete from "toy"
using "pet", "person"
where "toy"."pet_id" = "pet"."id"
  and "pet"."owner_id" = "person"."id"
  and "person"."first_name" = $1
returning "pet"."name"

​
Delete with Joins

You can join tables to delete based on related data: 
await db
  .deleteFrom('person')
  .using('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .where('pet.name', '=', 'Doggo')
  .execute()
See the joins documentation for more information on join methods.

​
Limit and Order By

​
Limit (MySQL)

Limit the number of deleted rows on MySQL: 
await db
  .deleteFrom('person')
  .where('age', '>', 18)
  .limit(5)
  .execute()

​
Order By (MySQL)

await db
  .deleteFrom('person')
  .orderBy('age', 'desc')
  .limit(1)
  .execute()

​
Top Clause (MS SQL Server)

Delete the first N rows: 
await db
  .deleteFrom('person')
  .top(5)
  .where('age', '>', 18)
  .executeTakeFirstOrThrow()
The generated SQL (MS SQL Server): 
delete top(5) from "person" where "age" > @1
Delete a percentage of rows: 
await db
  .deleteFrom('person')
  .top(50, 'percent')
  .where('age', '>', 18)
  .executeTakeFirstOrThrow()
The generated SQL (MS SQL Server): 
delete top(50) percent from "person" where "age" > @1

​
Where Conditions

All the standard where methods are available: 
// Simple where
await db
  .deleteFrom('person')
  .where('id', '=', 1)
  .execute()

// Multiple conditions
await db
  .deleteFrom('person')
  .where('first_name', '=', 'Jennifer')
  .where('age', '>', 40)
  .execute()

// Where with expression
await db
  .deleteFrom('person')
  .where((eb) => eb.or([
    eb('first_name', '=', 'Jennifer'),
    eb('first_name', '=', 'Arnold')
  ]))
  .execute()

// Where ref (comparing columns)
await db
  .deleteFrom('person')
  .whereRef('first_name', '=', 'last_name')
  .execute()
See the DeleteQueryBuilder documentation for more filtering options.

​
Clear Methods

Clear parts of the query: 
const query = db
  .deleteFrom('person')
  .where('id', '=', 1)

// Clear where conditions
const clearedQuery = query.clearWhere()

// Clear order by
const clearedOrderQuery = query.clearOrderBy()

​
API Reference

​
Main Methods

  • deleteFrom(table) - Specify the table to delete from
  • where(...) - Add WHERE conditions
  • whereRef(...) - Add WHERE conditions comparing columns
  • using(table) - Add USING clause for additional tables
  • innerJoin(), leftJoin(), rightJoin(), fullJoin() - Join tables
  • orderBy(...) - Order rows before deletion (MySQL)
  • limit(n) - Limit number of deleted rows (MySQL)
  • top(n) - Delete top N rows (MS SQL Server)
  • returning(...) - Return columns from deleted rows
  • returningAll() - Return all columns from deleted rows
  • clearWhere() - Clear WHERE conditions
  • clearOrderBy() - Clear ORDER BY clause
  • modifyEnd(modifier) - Add custom SQL
  • execute() - Execute the query
  • executeTakeFirst() - Execute and return first result
  • executeTakeFirstOrThrow() - Execute and return first result or throw

​
Result Object

The return value is an instance of DeleteResult: 
interface DeleteResult {
  /**
   * The number of rows deleted
   */
  numDeletedRows?: bigint | undefined
}

Build docs developers (and LLMs) love

Get started for freeTalk to us