Class DeleteQueryBuilder<DB, TB, O>

Asserts that query's output row type equals the given type T.

This method can be used to simplify excessively complex types to make typescript happy and much faster.

Kysely uses complex type magic to achieve its type safety. This complexity is sometimes too much for typescript and you get errors like this:

error TS2589: Type instantiation is excessively deep and possibly infinite.
Copy

In these case you can often use this method to help typescript a little bit. When you use this method to assert the output type of a query, Kysely can drop the complex output type that consists of multiple nested helper types and replace it with the simple asserted type.

Using this method doesn't reduce type safety at all. You have to pass in a type that is structurally equal to the current type.

Examples

const result = await db
  .with('deleted_person', (qb) => qb
    .deleteFrom('person')
    .where('id', '=', person.id)
    .returning('first_name')
    .$assertType<{ first_name: string }>()
  )
  .with('deleted_pet', (qb) => qb
    .deleteFrom('pet')
    .where('owner_id', '=', person.id)
    .returning(['name as pet_name', 'species'])
    .$assertType<{ pet_name: string, species: Species }>()
  )
  .selectFrom(['deleted_person', 'deleted_pet'])
  .selectAll()
  .executeTakeFirstOrThrow()
Copy

Simply calls the provided function passing this as the only argument. $call returns what the provided function returns.

If you want to conditionally call a method on this, see the $if method.

Examples

The next example uses a helper function log to log a query:

function log<T extends Compilable>(qb: T): T {
  console.log(qb.compile())
  return qb
}

db.deleteFrom('person')
  .$call(log)
  .execute()
Copy

Change the output type of the query.

This method call doesn't change the SQL in any way. This methods simply returns a copy of this DeleteQueryBuilder with a new output type.

Call func(this) if condition is true.

This method is especially handy with optional selects. Any returning or returningAll method calls add columns as optional fields to the output type when called inside the func callback. This is because we can't know if those selections were actually made before running the code.

You can also call any other methods inside the callback.

Examples

async function deletePerson(id: number, returnLastName: boolean) {
  return await db
    .deleteFrom('person')
    .where('id', '=', id)
    .returning(['id', 'first_name'])
    .$if(returnLastName, (qb) => qb.returning('last_name'))
    .executeTakeFirstOrThrow()
}
Copy

Any selections added inside the if callback will be added as optional fields to the output type since we can't know if the selections were actually made before running the code. In the example above the return type of the deletePerson function is:

{
  id: number
  first_name: string
  last_name?: string
}
Copy

Narrows (parts of) the output type of the query.

Kysely tries to be as type-safe as possible, but in some cases we have to make compromises for better maintainability and compilation performance. At present, Kysely doesn't narrow the output type of the query when using where and returning or returningAll.

This utility method is very useful for these situations, as it removes unncessary runtime assertion/guard code. Its input type is limited to the output type of the query, so you can't add a column that doesn't exist, or change a column's type to something that doesn't exist in its union type.

Examples

Turn this code:

const person = await db.deleteFrom('person')
  .where('id', '=', id)
  .where('nullable_column', 'is not', null)
  .returningAll()
  .executeTakeFirstOrThrow()

if (person.nullable_column) {
  functionThatExpectsPersonWithNonNullValue(person)
}
Copy

Into this:

const person = await db.deleteFrom('person')
  .where('id', '=', id)
  .where('nullable_column', 'is not', null)
  .returningAll()
  .$narrowType<{ nullable_column: string }>()
  .executeTakeFirstOrThrow()

functionThatExpectsPersonWithNonNullValue(person)
Copy

Clears the limit clause from the query.

Examples

db.deleteFrom('pet')
  .returningAll()
  .where('name', '=', 'Max')
  .limit(5)
  .clearLimit()
Copy

The generated SQL(PostgreSQL):

delete from "pet" where "name" = "Max" returning *
Copy

Clears the order by clause from the query.

Examples

db.deleteFrom('pet')
  .returningAll()
  .where('name', '=', 'Max')
  .orderBy('id')
  .clearOrderBy()
Copy

The generated SQL(PostgreSQL):

delete from "pet" where "name" = "Max" returning *
Copy

Clears all returning clauses from the query.

Examples

db.deleteFrom('pet')
  .returningAll()
  .where('name', '=', 'Max')
  .clearReturning()
Copy

The generated SQL(PostgreSQL):

delete from "pet" where "name" = "Max"
Copy

Clears all where expressions from the query.

Examples

db.selectFrom('person')
  .selectAll()
  .where('id','=',42)
  .clearWhere()
Copy

The generated SQL(PostgreSQL):

select * from "person"
Copy

Executes the query and returns an array of rows.

Also see the executeTakeFirst and executeTakeFirstOrThrow methods.

Executes the query and returns the first result or undefined if the query returned no result.

Executes the query and returns the first result or throws if the query returned no result.

By default an instance of NoResultError is thrown, but you can provide a custom error class, or callback as the only argument to throw a different error.

Executes query with explain statement before the main query.

const explained = await db
 .selectFrom('person')
 .where('gender', '=', 'female')
 .selectAll()
 .explain('json')
Copy

The generated SQL (MySQL):

explain format=json select * from `person` where `gender` = ?
Copy

You can also execute explain analyze statements.

import { sql } from 'kysely'

const explained = await db
 .selectFrom('person')
 .where('gender', '=', 'female')
 .selectAll()
 .explain('json', sql`analyze`)
Copy

The generated SQL (PostgreSQL):

explain (analyze, format json) select * from "person" where "gender" = $1
Copy

Just like innerJoin but adds a full join instead of an inner join.

Joins another table to the query using an inner join.

Examples

Simple usage by providing a table name and two columns to join:

const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  // `select` needs to come after the call to `innerJoin` so
  // that you can select from the joined table.
  .select('person.id', 'pet.name')
  .execute()

result[0].id
result[0].name
Copy

The generated SQL (PostgreSQL):

select "person"."id", "pet"."name"
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
Copy

You can give an alias for the joined table like this:

await db.selectFrom('person')
  .innerJoin('pet as p', 'p.owner_id', 'person.id')
  .where('p.name', '=', 'Doggo')
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join "pet" as "p"
on "p"."owner_id" = "person"."id"
where "p".name" = $1
Copy

You can provide a function as the second argument to get a join builder for creating more complex joins. The join builder has a bunch of on* methods for building the on clause of the join. There's basically an equivalent for every where method (on, onRef, onExists etc.). You can do all the same things with the on method that you can with the corresponding where method. See the where method documentation for more examples.

await db.selectFrom('person')
  .innerJoin(
    'pet',
    (join) => join
      .onRef('pet.owner_id', '=', 'person.id')
      .on('pet.name', '=', 'Doggo')
  )
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
and "pet"."name" = $1
Copy

You can join a subquery by providing a select query (or a callback) as the first argument:

await db.selectFrom('person')
  .innerJoin(
    qb.selectFrom('pet')
      .select(['owner_id', 'name'])
      .where('name', '=', 'Doggo')
      .as('doggos'),
    'doggos.owner_id',
    'person.id',
  )
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join (
  select "owner_id", "name"
  from "pet"
  where "name" = $1
) as "doggos"
on "doggos"."owner_id" = "person"."id"
Copy

Just like innerJoin but adds a left join instead of an inner join.

Adds a limit clause to the query.

A limit clause in a delete query is only supported by some dialects like MySQL.

Examples

Delete 5 oldest items in a table:

await db
  .deleteFrom('pet')
  .orderBy('created_at')
  .limit(5)
  .execute()
Copy

Adds an order by clause to the query.

orderBy calls are additive. To order by multiple columns, call orderBy multiple times.

The first argument is the expression to order by and the second is the order (asc or desc).

An order by clause in a delete query is only supported by some dialects like MySQL.

See orderBy for more examples.

Examples

Delete 5 oldest items in a table:

await db
  .deleteFrom('pet')
  .orderBy('created_at')
  .limit(5)
  .execute()
Copy

The generated SQL (MySQL):

delete from `pet`
order by `created_at`
limit ?
Copy

Allows you to return data from modified rows.

On supported databases like PostgreSQL, this method can be chained to insert, update and delete queries to return data.

Note that on SQLite you need to give aliases for the expressions to avoid this bug in SQLite. For example .returning('id as id').

Also see the returningAll method.

Examples

Return one column:

const { id } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning('id')
  .executeTakeFirst()
Copy

Return multiple columns:

const { id, first_name } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning(['id', 'last_name'])
  .executeTakeFirst()
Copy

Return arbitrary expressions:

import { sql } from 'kysely'

const { id, full_name, first_pet_id } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning((eb) => [
    'id as id',
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name'),
    eb.selectFrom('pets').select('pet.id').limit(1).as('first_pet_id')
  ])
  .executeTakeFirst()
Copy

Adds returning * or returning table.* clause to the query.

Examples

Return all columns.

const pets = await db
  .deleteFrom('pet')
  .returningAll()
  .execute()
Copy

The generated SQL (PostgreSQL)

delete from "pet" returning *
Copy

Return all columns from all tables

const result = ctx.db
  .deleteFrom('toy')
  .using(['pet', 'person'])
  .whereRef('toy.pet_id', '=', 'pet.id')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('person.first_name', '=', 'Zoro')
  .returningAll()
  .execute()
Copy

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 *
Copy

Return all columns from a single table.

const result = ctx.db
  .deleteFrom('toy')
  .using(['pet', 'person'])
  .whereRef('toy.pet_id', '=', 'pet.id')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('person.first_name', '=', 'Itachi')
  .returningAll('pet')
  .execute()
Copy

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".*
Copy

Return all columns from multiple tables.

const result = ctx.db
  .deleteFrom('toy')
  .using(['pet', 'person'])
  .whereRef('toy.pet_id', '=', 'pet.id')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('person.first_name', '=', 'Luffy')
  .returningAll(['toy', 'pet'])
  .execute()
Copy

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 "toy".*, "pet".*
Copy

Just like innerJoin but adds a right join instead of an inner join.

Executes the query and streams the rows.

The optional argument chunkSize defines how many rows to fetch from the database at a time. It only affects some dialects like PostgreSQL that support it.

Examples

const stream = db.
  .selectFrom('person')
  .select(['first_name', 'last_name'])
  .where('gender', '=', 'other')
  .stream()

for await (const person of stream) {
  console.log(person.first_name)

  if (person.last_name === 'Something') {
    // Breaking or returning before the stream has ended will release
    // the database connection and invalidate the stream.
    break
  }
}
Copy

Changes a delete from query into a delete top from query.

top clause is only supported by some dialects like MS SQL Server.

Examples

Delete the first 5 rows:

await db
  .deleteFrom('person')
  .top(5)
  .where('age', '>', 18)
  .executeTakeFirstOrThrow()
Copy

The generated SQL (MS SQL Server):

delete top(5) from "person" where "age" > @1
Copy

Delete the first 50% of rows:

await db
  .deleteFrom('person')
  .top(50, 'percent')
  .where('age', '>', 18)
  .executeTakeFirstOrThrow()
Copy

The generated SQL (MS SQL Server):

delete top(50) percent from "person" where "age" > @1
Copy

Adds a using clause to the query.

This clause allows adding additional tables to the query for filtering/returning only. Usually a non-standard syntactic-sugar alternative to a where with a sub-query.

Examples:

await db
  .deleteFrom('pet')
  .using('person')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('person.first_name', '=', 'Bob')
  .executeTakeFirstOrThrow()
Copy

The generated SQL (PostgreSQL):

delete from "pet"
using "person"
where "pet"."owner_id" = "person"."id"
  and "person"."first_name" = $1
Copy

On supported databases such as MySQL, this clause allows using joins, but requires at least one of the tables after the from keyword to be also named after the using keyword. See also innerJoin, leftJoin, rightJoin and fullJoin.

await db
  .deleteFrom('pet')
  .using('pet')
  .leftJoin('person', 'person.id', 'pet.owner_id')
  .where('person.first_name', '=', 'Bob')
  .executeTakeFirstOrThrow()
Copy

The generated SQL (MySQL):

delete from `pet`
using `pet`
left join `person` on `person`.`id` = `pet`.`owner_id`
where `person`.`first_name` = ?
Copy

You can also chain multiple invocations of this method, or pass an array to a single invocation to name multiple tables.

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()
Copy

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"
Copy

Adds a where expression to the query.

Calling this method multiple times will combine the expressions using and.

Also see whereRef

Examples

where method calls are combined with AND:

const person = await db
  .selectFrom('person')
  .selectAll()
  .where('first_name', '=', 'Jennifer')
  .where('age', '>', 40)
  .executeTakeFirst()
Copy

The generated SQL (PostgreSQL):

select * from "person" where "first_name" = $1 and "age" > $2
Copy

Operator can be any supported operator or if the typings don't support it you can always use:

sql`your operator`
Copy

Find multiple items using a list of identifiers:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where('id', 'in', ['1', '2', '3'])
  .execute()
Copy

The generated SQL (PostgreSQL):

select * from "person" where "id" in ($1, $2, $3)
Copy

You can use the and function to create a simple equality filter using an object

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where((eb) => eb.and({
    first_name: 'Jennifer',
    last_name: eb.ref('first_name')
  }))
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
where (
  "first_name" = $1
  and "last_name" = "first_name"
)
Copy

To combine conditions using OR, you can use the expression builder. There are two ways to create OR expressions. Both are shown in this example:

const persons = await db
  .selectFrom('person')
  .selectAll()
  // 1. Using the `or` method on the expression builder:
  .where((eb) => eb.or([
    eb('first_name', '=', 'Jennifer'),
    eb('first_name', '=', 'Sylvester')
  ]))
  // 2. Chaining expressions using the `or` method on the
  // created expressions:
  .where((eb) =>
    eb('last_name', '=', 'Aniston').or('last_name', '=', 'Stallone')
  )
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
where (
  ("first_name" = $1 or "first_name" = $2)
  and
  ("last_name" = $3 or "last_name" = $4)
)
Copy

You can add expressions conditionally like this:

import { Expression, SqlBool } from 'kysely'

const firstName: string | undefined = 'Jennifer'
const lastName: string | undefined = 'Aniston'
const under18 = true
const over60 = true

let query = db
  .selectFrom('person')
  .selectAll()

if (firstName) {
  // The query builder is immutable. Remember to reassign
  // the result back to the query variable.
  query = query.where('first_name', '=', firstName)
}

if (lastName) {
  query = query.where('last_name', '=', lastName)
}

if (under18 || over60) {
  // Conditional OR expressions can be added like this.
  query = query.where((eb) => {
    const ors: Expression<SqlBool>[] = []

    if (under18) {
      ors.push(eb('age', '<', 18))
    }

    if (over60) {
      ors.push(eb('age', '>', 60))
    }

    return eb.or(ors)
  })
}

const persons = await query.execute()
Copy

Both the first and third argument can also be arbitrary expressions like subqueries. An expression can defined by passing a function and calling the methods of the ExpressionBuilder passed to the callback:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(
    (qb) => qb.selectFrom('pet')
      .select('pet.name')
      .whereRef('pet.owner_id', '=', 'person.id')
      .limit(1),
    '=',
    'Fluffy'
  )
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
where (
  select "pet"."name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
  limit $1
) = $2
Copy

A where in query can be built by using the in operator and an array of values. The values in the array can also be expressions:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where('person.id', 'in', [100, 200, 300])
  .execute()
Copy

The generated SQL (PostgreSQL):

select * from "person" where "id" in ($1, $2, $3)
Copy

For complex where expressions you can pass in a single callback and use the ExpressionBuilder to build your expression:

const firstName = 'Jennifer'
const maxAge = 60

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .where(({ eb, or, and, not, exists, selectFrom }) => and([
    or([
      eb('first_name', '=', firstName),
      eb('age', '<', maxAge)
    ]),
    not(exists(
      selectFrom('pet')
        .select('pet.id')
        .whereRef('pet.owner_id', '=', 'person.id')
    ))
  ]))
  .execute()
Copy

The generated SQL (PostgreSQL):

select "person".*
from "person"
where (
  (
    "first_name" = $1
    or "age" < $2
  )
  and not exists (
    select "pet"."id" from "pet" where "pet"."owner_id" = "person"."id"
  )
)
Copy

If everything else fails, you can always use the sql tag as any of the arguments, including the operator:

import { sql } from 'kysely'

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(
    sql`coalesce(first_name, last_name)`,
    'like',
    '%' + name + '%',
  )
  .execute()
Copy

The generated SQL (PostgreSQL):

select * from "person"
where coalesce(first_name, last_name) like $1
Copy

In all examples above the columns were known at compile time (except for the raw sql expressions). By default kysely only allows you to refer to columns that exist in the database and can be referred to in the current query and context.

Sometimes you may want to refer to columns that come from the user input and thus are not available at compile time.

You have two options, the sql tag or db.dynamic. The example below uses both:

import { sql } from 'kysely'
const { ref } = db.dynamic

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(ref(columnFromUserInput), '=', 1)
  .where(sql.id(columnFromUserInput), '=', 2)
  .execute()
Copy

Adds a where clause where both sides of the operator are references to columns.

The normal where method treats the right hand side argument as a value by default. whereRef treats it as a column reference. This method is expecially useful with joins and correlated subqueries.

Examples

Usage with a join:

db.selectFrom(['person', 'pet'])
  .selectAll()
  .whereRef('person.first_name', '=', 'pet.name')
Copy

The generated SQL (PostgreSQL):

select * from "person", "pet" where "person"."first_name" = "pet"."name"
Copy

Usage in a subquery:

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .select((eb) => eb
    .selectFrom('pet')
    .select('name')
    .whereRef('pet.owner_id', '=', 'person.id')
    .limit(1)
    .as('pet_name')
  )
  .execute()
Copy

The generated SQL (PostgreSQL):

select "person".*, (
  select "name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
  limit $1
) as "pet_name"
from "person"
Copy

Returns a copy of this DeleteQueryBuilder instance with the given plugin installed.