SelectQueryBuilder
§Methods
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.
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('first_and_last', (qb) => qb
.selectFrom('person')
.select(['first_name', 'last_name'])
.$assertType<{ first_name: string, last_name: string }>()
)
.with('age', (qb) => qb
.selectFrom('person')
.select('age')
.$assertType<{ age: number }>()
)
.selectFrom(['first_and_last', 'age'])
.selectAll()
.executeTakeFirstOrThrow()
Changes the output type from an object to a tuple.
This doesn't affect the generated SQL in any way. This function is just a necessary evil when you need to convert a query's output record type to a tuple type. Typescript doesn't currently offer tools to do this automatically (without insane hackery).
The returned object can no longer be executed. It can only be used as a subquery.
Examples
const result = await db
.selectFrom('person')
.selectAll('person')
.where(({ eb, refTuple, selectFrom }) => eb(
refTuple('first_name', 'last_name'),
'in',
selectFrom('pet')
.select(['name', 'species'])
.where('pet.species', '!=', 'cat')
.$asTuple('name', 'species')
))
The generated SQL(PostgreSQL):
select
"person".*
from
"person"
where
("first_name", "last_name")
in
(
select "name", "species"
from "pet"
where "pet"."species" != $1
)
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 {@link $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.selectFrom('person')
.selectAll()
.$call(log)
.execute()
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 SelectQueryBuilder
with a new output type.
Call func(this)
if condition
is true.
NOTE: This method has an impact on typescript performance and it should only be used
when necessary. Remember that you can call most methods like where
conditionally
like this:
let query = db.selectFrom('person').selectAll()
if (firstName) {
query = query.where('first_name', '=', firstName)
}
if (lastName) {
query = query.where('last_name', '=', lastName)
}
const result = await query.execute()
This method is mainly useful with optional selects. Any select
or selectAll
method called inside the callback add optional fields to the result type. This is
because we can't know if those selections were actually made before running the code.
Also see this recipe
Examples
async function getPerson(id: number, withLastName: boolean) {
return await db
.selectFrom('person')
.select(['id', 'first_name'])
.$if(withLastName, (qb) => qb.select('last_name'))
.where('id', '=', id)
.executeTakeFirstOrThrow()
}
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 getPerson
function is:
{
id: number
first_name: string
last_name?: string
}
You can also call any other methods inside the callback:
db.selectFrom('person')
.select('person.id')
.$if(filterByFirstName, (qb) => qb.where('first_name', '=', firstName))
.$if(filterByPetCount, (qb) => qb
.innerJoin('pet', 'pet.owner_id', 'person.id')
.having((eb) => eb.fn.count('pet.id'), '>', petCountLimit)
.groupBy('person.id')
)
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 {@link where}, {@link having} or {@link JoinQueryBuilder.on}.
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.selectFrom('person')
.where('nullable_column', 'is not', null)
.selectAll()
.executeTakeFirstOrThrow()
if (person.nullable_column) {
functionThatExpectsPersonWithNonNullValue(person)
}
Into this:
const person = await db.selectFrom('person')
.where('nullable_column', 'is not', null)
.selectAll()
.$narrowType<{ nullable_column: string }>()
.executeTakeFirstOrThrow()
functionThatExpectsPersonWithNonNullValue(person)
Giving the explicit narrowed type (string
in the example above) works fine for
simple types. If the type is complex, for example a JSON column or a subquery,
you can use the special NotNull
type to make the column not null.
import { NotNull } from 'kysely'
const person = await db.selectFrom('person')
.where('nullable_column', 'is not', null)
.selectAll()
.$narrowType<{ nullable_column: NotNull }>()
.executeTakeFirstOrThrow()
functionThatExpectsPersonWithNonNullValue(person)
Gives an alias for the query. This method is only useful for sub queries.
Examples
const pets = await db.selectFrom('pet')
.selectAll('pet')
.select(
(qb) => qb.selectFrom('person')
.select('first_name')
.whereRef('pet.owner_id', '=', 'person.id')
.as('owner_first_name')
)
.execute()
pets[0].owner_first_name
Clears limit clause from the query.
Examples
db.selectFrom('person')
.selectAll()
.limit(10)
.clearLimit()
The generated SQL(PostgreSQL):
select * from "person"
Clears offset clause from the query.
Examples
db.selectFrom('person')
.selectAll()
.limit(10)
.offset(20)
.clearOffset()
The generated SQL(PostgreSQL):
select * from "person" limit 10
Clears all order by
clauses from the query.
Examples
db.selectFrom('person')
.selectAll()
.orderBy('id')
.clearOrderBy()
The generated SQL(PostgreSQL):
select * from "person"
Clears all select clauses from the query.
Examples
db.selectFrom('person')
.select(['id', 'first_name'])
.clearSelect()
.select(['id', 'gender'])
The generated SQL(PostgreSQL):
select "id", "gender" from "person"
Clears all where expressions from the query.
Examples
db.selectFrom('person')
.selectAll()
.where('id','=',42)
.clearWhere()
The generated SQL(PostgreSQL):
select * from "person"
Makes the selection distinct.
Examples
const persons = await db.selectFrom('person')
.select('first_name')
.distinct()
.execute()
The generated SQL (PostgreSQL):
select distinct "first_name" from "person"
Adds distinct on
expressions to the select clause.
Examples
const persons = await db.selectFrom('person')
.innerJoin('pet', 'pet.owner_id', 'person.id')
.where('pet.name', '=', 'Doggo')
.distinctOn('person.id')
.selectAll('person')
.execute()
The generated SQL (PostgreSQL):
select distinct on ("person"."id") "person".*
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
where "pet"."name" = $1
Combines another select query or raw expression to this query using except
.
The output row type of the combined query must match this
query.
Examples
db.selectFrom('person')
.select(['id', 'first_name as name'])
.except(db.selectFrom('pet').select(['id', 'name']))
.orderBy('name')
You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:
db.selectFrom('person')
.select(['id', 'first_name as name'])
.except((eb) => eb.parens(
eb.selectFrom('pet').select(['id', 'name'])
))
.orderBy('name')
Combines another select query or raw expression to this query using except all
.
The output row type of the combined query must match this
query.
Examples
db.selectFrom('person')
.select(['id', 'first_name as name'])
.exceptAll(db.selectFrom('pet').select(['id', 'name']))
.orderBy('name')
You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:
db.selectFrom('person')
.select(['id', 'first_name as name'])
.exceptAll((eb) => eb.parens(
eb.selectFrom('pet').select(['id', 'name'])
))
.orderBy('name')
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 {@link NoResultError} is thrown, but you can provide a custom error class, or callback 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')
The generated SQL (MySQL):
explain format=json select * from `person` where `gender` = ?
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`)
The generated SQL (PostgreSQL):
explain (analyze, format json) select * from "person" where "gender" = $1
Adds a fetch
clause to the query.
This clause is only supported by some dialects like PostgreSQL or MS SQL Server.
Examples
return await db
.selectFrom('person')
.select('first_name')
.orderBy('first_name')
.offset(0)
.fetch(10)
.execute()
The generated SQL (MS SQL Server):
select "first_name"
from "person"
order by "first_name"
offset 0 rows
fetch next 10 rows only
Adds the for no key update
modifier to a select query on supported databases.
Adds the for update
modifier to a select query on supported databases.
Just like {@link innerJoin} but adds a full join instead of an inner join.
Adds a group by
clause to the query.
Examples
import { sql } from 'kysely'
await db
.selectFrom('person')
.select([
'first_name',
sql`max(id)`.as('max_id')
])
.groupBy('first_name')
.execute()
The generated SQL (PostgreSQL):
select "first_name", max(id)
from "person"
group by "first_name"
groupBy
also accepts an array:
import { sql } from 'kysely'
await db
.selectFrom('person')
.select([
'first_name',
'last_name',
sql`max(id)`.as('max_id')
])
.groupBy([
'first_name',
'last_name'
])
.execute()
The generated SQL (PostgreSQL):
select "first_name", "last_name", max(id)
from "person"
group by "first_name", "last_name"
The group by expressions can also be subqueries or raw sql expressions:
import { sql } from 'kysely'
await db
.selectFrom('person')
.select([
'first_name',
'last_name',
sql`max(id)`.as('max_id')
])
.groupBy([
sql`concat(first_name, last_name)`,
(qb) => qb.selectFrom('pet').select('id').limit(1)
])
.execute()
dynamic.ref
can be used to refer to columns not known at
compile time:
async function someQuery(groupBy: string) {
const { ref } = db.dynamic
return await db
.selectFrom('person')
.select('first_name')
.groupBy(ref(groupBy))
.execute()
}
someQuery('first_name')
The generated SQL (PostgreSQL):
select "first_name"
from "person"
group by "first_name"
Just like where but adds a having
statement
instead of a where
statement.
Just like whereRef but adds a having
statement
instead of a where
statement.
Joins another table to the query using an inner join.
Examples
Simple inner joins can be done 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 as pet_name'])
.execute()
The generated SQL (PostgreSQL):
select "person"."id", "pet"."name" as "pet_name"
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
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()
The generated SQL (PostgreSQL):
select *
from "person"
inner join "pet" as "p"
on "p"."owner_id" = "person"."id"
where "p".name" = $1
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
etc.).
You can do all the same things with the
on
method that you can with the corresponding where
method (like OR expressions for example).
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')
.on((eb) => eb.or([eb("person.age", ">", 18), eb("person.age", "<", 100)]))
)
.selectAll()
.execute()
The generated SQL (PostgreSQL):
select *
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
and "pet"."name" = $1
You can join a subquery by providing two callbacks:
const result = await db.selectFrom('person')
.innerJoin(
(eb) => eb
.selectFrom('pet')
.select(['owner_id as owner', 'name'])
.where('name', '=', 'Doggo')
.as('doggos'),
(join) => join
.onRef('doggos.owner', '=', 'person.id'),
)
.selectAll('doggos')
.execute()
The generated SQL (PostgreSQL):
select "doggos".*
from "person"
inner join (
select "owner_id" as "owner", "name"
from "pet"
where "name" = $1
) as "doggos"
on "doggos"."owner" = "person"."id"
Just like {@link innerJoin} but adds a lateral join instead of an inner join.
Examples
db.selectFrom('person')
.innerJoinLateral(
(eb) =>
eb.selectFrom('pet')
.select('name')
.whereRef('pet.owner_id', '=', 'person.id')
.as('p'),
(join) => join.onTrue()
)
.select(['first_name', 'p.name'])
.orderBy('first_name')
Combines another select query or raw expression to this query using intersect
.
The output row type of the combined query must match this
query.
Examples
db.selectFrom('person')
.select(['id', 'first_name as name'])
.intersect(db.selectFrom('pet').select(['id', 'name']))
.orderBy('name')
You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:
db.selectFrom('person')
.select(['id', 'first_name as name'])
.intersect((eb) => eb.parens(
eb.selectFrom('pet').select(['id', 'name'])
))
.orderBy('name')
Combines another select query or raw expression to this query using intersect all
.
The output row type of the combined query must match this
query.
Examples
db.selectFrom('person')
.select(['id', 'first_name as name'])
.intersectAll(db.selectFrom('pet').select(['id', 'name']))
.orderBy('name')
You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:
db.selectFrom('person')
.select(['id', 'first_name as name'])
.intersectAll((eb) => eb.parens(
eb.selectFrom('pet').select(['id', 'name'])
))
.orderBy('name')
Just like {@link innerJoin} but adds a left join instead of an inner join.
Just like {@link innerJoin} but adds a lateral left join instead of an inner join.
Examples
db.selectFrom('person')
.leftJoinLateral(
(eb) =>
eb.selectFrom('pet')
.select('name')
.whereRef('pet.owner_id', '=', 'person.id')
.as('p'),
(join) => join.onTrue()
)
.select(['first_name', 'p.name'])
.orderBy('first_name')
Adds a limit clause to the query.
Examples
Select the first 10 rows of the result:
return await db
.selectFrom('person')
.select('first_name')
.limit(10)
.execute()
The generated SQL (PostgreSQL):
select "first_name" from "person" limit $1
Select rows from index 10 to index 19 of the result:
return await db
.selectFrom('person')
.select('first_name')
.limit(10)
.offset(10)
.execute()
The generated SQL (PostgreSQL):
select "first_name" from "person" limit $1 offset $2
This can be used to add any additional SQL to the end of the query.
Also see {@link forUpdate}, {@link forShare}, {@link forKeyShare}, {@link forNoKeyUpdate} {@link skipLocked} and {@link noWait}.
Examples
db.selectFrom('person')
.select('first_name')
.modifyEnd(sql`for update`)
.execute()
The generated SQL (PostgreSQL):
select "first_name"
from "person"
for update
This can be used to add any additional SQL to the front of the query after the select
keyword.
Examples
db.selectFrom('person')
.modifyFront(sql`sql_no_cache`)
.select('first_name')
.execute()
The generated SQL (MySQL):
select sql_no_cache `first_name`
from `person`
Adds the nowait
modifier to a select query on supported databases.
Adds an offset
clause to the query.
Examples
Select rows from index 10 to index 19 of the result:
return await db
.selectFrom('person')
.select('first_name')
.limit(10)
.offset(10)
.execute()
The generated SQL (PostgreSQL):
select "first_name" from "person" limit $1 offset $2
Adds an order by
clause to the query.
orderBy
calls are additive. Meaning, additional orderBy
calls append to
the existing order by clause.
In a single call you can add a single column/expression or multiple columns/expressions.
Single column/expression calls can have 1-2 arguments. The first argument is
the expression to order by (optionally including the direction) while the second
optional argument is the direction (asc
or desc
).
Examples
Single column/expression per call:
await db
.selectFrom('person')
.select('person.first_name as fn')
.orderBy('id')
.orderBy('fn desc')
.execute()
await db
.selectFrom('person')
.select('person.first_name as fn')
.orderBy('id')
.orderBy('fn', 'desc')
.execute()
The generated SQL (PostgreSQL):
select "person"."first_name" as "fn"
from "person"
order by "id" asc, "fn" desc
Multiple columns/expressions per call:
await db
.selectFrom('person')
.select('person.first_name as fn')
.orderBy(['id', 'fn desc'])
.execute()
The order by expression can also be a raw sql expression or a subquery in addition to column references:
import { sql } from 'kysely'
await db
.selectFrom('person')
.selectAll()
.orderBy((eb) => eb.selectFrom('pet')
.select('pet.name')
.whereRef('pet.owner_id', '=', 'person.id')
.limit(1)
)
.orderBy(
sql`concat(first_name, last_name)`
)
.execute()
The generated SQL (PostgreSQL):
select *
from "person"
order by
( select "pet"."name"
from "pet"
where "pet"."owner_id" = "person"."id"
limit 1
) asc,
concat(first_name, last_name) asc
dynamic.ref
can be used to refer to columns not known at
compile time:
async function someQuery(orderBy: string) {
const { ref } = db.dynamic
return await db
.selectFrom('person')
.select('person.first_name as fn')
.orderBy(ref(orderBy))
.execute()
}
someQuery('fn')
The generated SQL (PostgreSQL):
select "person"."first_name" as "fn"
from "person"
order by "fn" asc
Just like {@link innerJoin} but adds a right join instead of an inner join.
Adds a select statement to the query.
When a column (or any expression) is selected, Kysely adds its type to the return type of the query. Kysely is smart enough to parse the selection names and types from aliased columns, subqueries, raw expressions etc.
Kysely only allows you to select columns and expressions that exist and would produce valid SQL. However, Kysely is not perfect and there may be cases where the type inference doesn't work and you need to override it. You can always use the {@link Kysely.dynamic | dynamic} module and the {@link sql} tag to override the types.
Select calls are additive. Calling select('id').select('first_name')
is the
same as calling select(['id', 'first_name'])
.
To select all columns of the query or specific tables see the {@link selectAll} method.
See the {@link $if} method if you are looking for a way to add selections based on a runtime condition.
Examples
Select a single column:
const persons = await db
.selectFrom('person')
.select('id')
.where('first_name', '=', 'Arnold')
.execute()
The generated SQL (PostgreSQL):
select "id" from "person" where "first_name" = $1
Select a single column and specify a table:
const persons = await db
.selectFrom(['person', 'pet'])
.select('person.id')
.execute()
The generated SQL (PostgreSQL):
select "person"."id" from "person", "pet"
Select multiple columns:
const persons = await db
.selectFrom('person')
.select(['person.id', 'first_name'])
.execute()
The generated SQL (PostgreSQL):
select "person"."id", "first_name" from "person"
You can give an alias for selections and tables by appending as the_alias
to the name:
const persons = await db
.selectFrom('person as p')
.select([
'first_name as fn',
'p.last_name as ln'
])
.execute()
The generated SQL (PostgreSQL):
select
"first_name" as "fn",
"p"."last_name" as "ln"
from "person" as "p"
You can select arbitrary expression including subqueries and raw sql snippets.
When you do that, you need to give a name for the selections using the as
method:
import { sql } from 'kysely'
const persons = await db.selectFrom('person')
.select(({ eb, selectFrom, or }) => [
// Select a correlated subquery
selectFrom('pet')
.whereRef('person.id', '=', 'pet.owner_id')
.select('pet.name')
.orderBy('pet.name')
.limit(1)
.as('first_pet_name'),
// Build and select an expression using
// the expression builder
or([
eb('first_name', '=', 'Jennifer'),
eb('first_name', '=', 'Arnold')
]).as('is_jennifer_or_arnold'),
// Select a raw sql expression
sql<string>`concat(first_name, ' ', last_name)`.as('full_name')
])
.execute()
The generated SQL (PostgreSQL):
select
(
select "pet"."name"
from "pet"
where "person"."id" = "pet"."owner_id"
order by "pet"."name"
limit $1
) as "pet_name",
("first_name" = $2 or "first_name" = $3) as "jennifer_or_arnold",
concat(first_name, ' ', last_name) as "full_name"
from "person"
In case you use the {@link sql} tag you need to specify the type of the expression
(in this example string
).
All the examples above assume you know the column names at compile time.
While it's better to build your code like that (that way you also know
the types) sometimes it's not possible or you just prefer to write more
dynamic code.
In this example, we use the dynamic
module's methods to add selections
dynamically:
const { ref } = db.dynamic
// Some column name provided by the user. Value not known at compile time.
const columnFromUserInput = req.query.select;
// A type that lists all possible values `columnFromUserInput` can have.
// You can use `keyof Person` if any column of an interface is allowed.
type PossibleColumns = 'last_name' | 'first_name' | 'birth_date'
const spersons = await db
.selectFrom('person')
.select([
ref<PossibleColumns>(columnFromUserInput)
'id'
])
.execute()
// The resulting type contains all `PossibleColumns` as optional fields
// because we cannot know which field was actually selected before
// running the code.
const lastName: string | undefined = persons[0].last_name
const firstName: string | undefined = persons[0].first_name
const birthDate: string | undefined = persons[0].birth_date
// The result type also contains the compile time selection `id`.
persons[0].id
Adds a select *
or select table.*
clause to the query.
Examples
The selectAll
method generates SELECT *
:
const persons = await db
.selectFrom('person')
.selectAll()
.execute()
The generated SQL (PostgreSQL):
select * from "person"
Select all columns of a table:
const persons = await db
.selectFrom('person')
.selectAll('person')
.execute()
The generated SQL (PostgreSQL):
select "person".* from "person"
Select all columns of multiple tables:
const personsPets = await db
.selectFrom(['person', 'pet'])
.selectAll(['person', 'pet'])
.execute()
The generated SQL (PostgreSQL):
select "person".*, "pet".* from "person", "pet"
Adds the skip locked
modifier to a select query on supported databases.
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
}
}
Creates the OperationNode that describes how to compile this expression into SQL.
If you are creating a custom expression, it's often easiest to use the {@link sql} template tag to build the node:
class SomeExpression<T> implements Expression<T> {
toOperationNode(): OperationNode {
return sql`some sql here`.toOperationNode()
}
}
Adds a top
clause to the query.
This clause is only supported by some dialects like MS SQL Server.
Examples
Select 10 biggest ages:
return await db
.selectFrom('person')
.select('age')
.top(10)
.orderBy('age desc')
.execute()
The generated SQL (MS SQL Server):
select top(10) "age" from "person" order by "age" desc
Select 10% first rows:
return await db
.selectFrom('person')
.selectAll()
.top(10, 'percent')
.execute()
The generated SQL (MS SQL Server):
select top(10) percent * from "person"
Combines another select query or raw expression to this query using union
.
The output row type of the combined query must match this
query.
Examples
db.selectFrom('person')
.select(['id', 'first_name as name'])
.union(db.selectFrom('pet').select(['id', 'name']))
.orderBy('name')
You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:
db.selectFrom('person')
.select(['id', 'first_name as name'])
.union((eb) => eb.parens(
eb.selectFrom('pet').select(['id', 'name'])
))
.orderBy('name')
Combines another select query or raw expression to this query using union all
.
The output row type of the combined query must match this
query.
Examples
db.selectFrom('person')
.select(['id', 'first_name as name'])
.unionAll(db.selectFrom('pet').select(['id', 'name']))
.orderBy('name')
You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:
db.selectFrom('person')
.select(['id', 'first_name as name'])
.unionAll((eb) => eb.parens(
eb.selectFrom('pet').select(['id', 'name'])
))
.orderBy('name')
Adds a where
expression to the query.
Calling this method multiple times will combine the expressions using and
.
Also see {@link whereRef}
Examples
where
method calls are combined with AND
:
const person = await db
.selectFrom('person')
.selectAll()
.where('first_name', '=', 'Jennifer')
.where('age', '>', 40)
.executeTakeFirst()
The generated SQL (PostgreSQL):
select * from "person" where "first_name" = $1 and "age" > $2
Operator can be any supported operator or if the typings don't support it you can always use:
sql`your operator`
Find multiple items using a list of identifiers:
const persons = await db
.selectFrom('person')
.selectAll()
.where('id', 'in', ['1', '2', '3'])
.execute()
The generated SQL (PostgreSQL):
select * from "person" where "id" in ($1, $2, $3)
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()
The generated SQL (PostgreSQL):
select *
from "person"
where (
"first_name" = $1
and "last_name" = "first_name"
)
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()
The generated SQL (PostgreSQL):
select *
from "person"
where (
("first_name" = $1 or "first_name" = $2)
and
("last_name" = $3 or "last_name" = $4)
)
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()
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 {@link 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()
The generated SQL (PostgreSQL):
select *
from "person"
where (
select "pet"."name"
from "pet"
where "pet"."owner_id" = "person"."id"
limit $1
) = $2
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()
The generated SQL (PostgreSQL):
select * from "person" where "id" in ($1, $2, $3)
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()
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"
)
)
If everything else fails, you can always use the {@link 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()
The generated SQL (PostgreSQL):
select * from "person"
where coalesce(first_name, last_name) like $1
In all examples above the columns were known at compile time (except for the raw {@link 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 {@link 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()
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')
The generated SQL (PostgreSQL):
select * from "person", "pet" where "person"."first_name" = "pet"."name"
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()
The generated SQL (PostgreSQL):
select "person".*, (
select "name"
from "pet"
where "pet"."owner_id" = "person"."id"
limit $1
) as "pet_name"
from "person"
Returns a copy of this SelectQueryBuilder instance with the given plugin installed.