Options
All
  • Public
  • Public/Protected
  • All
Menu

Class QueryBuilder<T>

A fluid SQL query builder that is semi-typesafe and supports selecting, inserting, updating and deleting. Obtain an instance using the static methods or one of the methods on wrapped models.

Type parameters

  • T

Hierarchy

Index

Constructors

Properties

Methods

Constructors

Protected constructor

Properties

aggregateFunction

aggregateFunction: string | null = null

The aggregate function for this query. If this is set, it is assumed that this query selects of format SELECT fun(columns) AS aggregate.

columns

columns: string[] = ["*"]

The columns we are currently retrieving.

groups

groups: string[] = []

All the GROUP BY columns for the query.

isDistinct

isDistinct: boolean = false

If we are only looking for distinct values.

joins

joins: JoinClause[] = []

All joins for the current query.

limitCount

limitCount: number = -1

The LIMIT clause for the query, or -1 if not applicable.

modelConstructor

modelConstructor: Function | null

The constructor of the model we are currently querying. If this is non-null, we are still operating on the full model (no narrowed down views) which means that once we have fetched the objects we can assign this prototype to ensure that the returned instance supports any user-defined methods (along with defaults such as save).

orders

orders: object[] = []

All the ORDER BY clauses for the query.

table

table: string

Which table we are currently querying.

wheres

wheres: WhereQueryClause[] = []

All where clauses for the current query.

Methods

aggregate

  • aggregate(fn: string, column?: undefined | string): Promise<number>

  • Runs the specified aggregate function and returns the result.

    Parameters

    • fn: string
    • Optional column: undefined | string

    Returns Promise<number>

all

  • all(): Promise<T[]>

avg

  • avg(column?: undefined | string): Promise<number>

  • Returns the average of the specified column, or the current column by default.

    Parameters

    • Optional column: undefined | string

    Returns Promise<number>

count

  • count(): Promise<number>

Protected createNew

  • createNew(): this

  • Used as a helper function to create a new instance of ourselves. Mainly used in nested where/on clauses. Must be overridden by subclasses.

    Returns this

delete

  • delete(): Promise<void>

  • Deletes all rows matching the current query.

    Returns Promise<void>

distinct

  • distinct(value?: boolean): this

  • Marks this query as looking for distinct values. Use distinct(false) to undo.

    Parameters

    • Default value value: boolean = true

    Returns this

exists

  • exists(): Promise<boolean>

  • Checks if any rows exist that match the current query. Alias for count > 0.

    Returns Promise<boolean>

find

  • find(id: number): Promise<T | undefined>

  • Finds the first row with the specified ID. Alias for where("id", id).first()

    Parameters

    • id: number

    Returns Promise<T | undefined>

first

  • first(): Promise<T | undefined>

  • Returns the first row matching this query.

    Returns Promise<T | undefined>

from

  • Changes the table this query is currently acting on to the table associated with the specified model.

    Type parameters

    Parameters

    • model: M

    Returns QueryBuilder<A>

  • Changes the table this query is currently acting on to the specified table. An optional type parameter can be used to set the scheme of the table so that further operations are as type-safe as possible.

    Type parameters

    • M

    Parameters

    • table: string

    Returns QueryBuilder<M>

get

  • get(): Promise<T[]>

groupBy

  • groupBy<K>(...groups: K[]): this

  • Marks the query to group the results by the provided column names.

    Type parameters

    • K: keyof T

    Parameters

    • Rest ...groups: K[]

    Returns this

insert

  • insert(...entries: Partial<T>[]): Promise<void>

  • Inserts the specified entries in the current table.

    Parameters

    • Rest ...entries: Partial<T>[]

    Returns Promise<void>

insertAndGetId

  • insertAndGetId(entry: Partial<T>): Promise<number>

  • Essentially the same as insert(), but returns the ID of the inserted row and only supports inserting a single row at a time.

    Parameters

    • entry: Partial<T>

    Returns Promise<number>

join

  • Adds a new INNER JOIN with the specified table on the specified columns and operators. This operation loses type-safety since it is impossible to determine the return type of the join statically.

    Parameters

    • table: string
    • first: string
    • operator: Operator
    • second: string
    • Optional type: JoinType

    Returns QueryBuilder<KeyedDatabaseResult>

  • Adds a new nested INNER JOIN with the specified table. The handler receives a JoinClause which it can use to build the new join.

    Parameters

    • table: string
    • handler: function

    Returns QueryBuilder<KeyedDatabaseResult>

  • Adds a new INNER JOIN with the specified model and the specified columns and operator. This operation is type safe and returns a union of the current fields and the fields of the specified model.

    Type parameters

    Parameters

    • model: M
    • first: string
    • operator: Operator
    • second: string
    • Optional type: JoinType

    Returns QueryBuilder<T & A>

  • Adds a new nested INNER JOIN with the specified model. The handler receives a JoinClause which it can use to build the new join. This operation is type safe and returns a union of the current fields and the fields of the specified model.

    Type parameters

    Parameters

    Returns QueryBuilder<T & A>

leftJoin

  • Adds a new LEFT JOIN with the specified table on the specified columns and operators. This operation loses type-safety since it is impossible to determine the return type of the join statically.

    Parameters

    • table: string
    • first: string
    • operator: Operator
    • second: string

    Returns QueryBuilder<KeyedDatabaseResult>

  • Adds a new nested LEFT JOIN with the specified table. The handler receives a JoinClause which it can use to build the new join.

    Parameters

    • table: string
    • handler: function

    Returns QueryBuilder<KeyedDatabaseResult>

  • Adds a new LEFT JOIN with the specified model and the specified columns and operator. This operation is type safe and returns a union of the current fields and the fields of the specified model.

    Type parameters

    Parameters

    • model: M
    • first: string
    • operator: Operator
    • second: string

    Returns QueryBuilder<T & A>

  • Adds a new nested LEFT JOIN with the specified model. The handler receives a JoinClause which it can use to build the new join. This operation is type safe and returns a union of the current fields and the fields of the specified model.

    Type parameters

    Parameters

    Returns QueryBuilder<T & A>

limit

  • limit(count: number): this

  • Limits the amount of rows this query targets to the specified amount. Must be positive non-null.

    Parameters

    • count: number

    Returns this

max

  • max(column?: undefined | string): Promise<number>

  • Returns the maximum value of the specified column, or the current column by default.

    Parameters

    • Optional column: undefined | string

    Returns Promise<number>

min

  • min(column?: undefined | string): Promise<number>

  • Returns the minimum value of the specified column, or the current column by default.

    Parameters

    • Optional column: undefined | string

    Returns Promise<number>

orWhere

  • orWhere(handler: function): this
  • orWhere<K>(column: K, value: T[K]): this
  • orWhere<K>(column: K, operator: Operator, value: T[K]): this

  • Creates a new nested WHERE OR clause. The specified callback receives a nested query builder that can be used to build the nested queries.

    Parameters

    • handler: function
        • (builder: this): any

        • Parameters

          • builder: this

          Returns any

    Returns this

  • Adds a new WHERE OR clause for the specified column, requiring that it matches the specified variable. This is an alias for where(column, "=", value).

    Type parameters

    • K: keyof T

    Parameters

    • column: K
    • value: T[K]

    Returns this

  • Adds a new WHERE OR clause for the specified column, operator and value. Use where if you want to add a WHERE AND clause instead.

    Type parameters

    • K: keyof T

    Parameters

    • column: K
    • operator: Operator
    • value: T[K]

    Returns this

orWhereColumn

  • orWhereColumn<K1, K2>(first: K1, operator: Operator, second: K2): this

  • Adds a new WHERE OR clause comparing two columns of the current query. This uses OR, use whereColumn if you want AND instead.

    Type parameters

    • K1: keyof T

    • K2: keyof T

    Parameters

    • first: K1
    • operator: Operator
    • second: K2

    Returns this

orWhereNested

  • orWhereNested(handler: function): this

  • Adds a new nested WHERE OR query. The handler receives a new QueryBuilder that can be used to enter the where clauses of the nested where.

    Parameters

    Returns this

orWhereNotNull

  • orWhereNotNull<K>(column: K): this

  • Adds a new WHERE OR clause asserting that the specified column is NOT NULL.

    Type parameters

    • K: keyof T

    Parameters

    • column: K

    Returns this

orWhereNull

  • orWhereNull<K>(column: K): this

  • Adds a new WHERE OR clause asserting that the specified column is NULL.

    Type parameters

    • K: keyof T

    Parameters

    • column: K

    Returns this

orWhereRaw

  • Adds a new raw WHERE OR clause for the current query. You can use ? as a substitute for arguments to securely bind parameters.

    Parameters

    Returns this

orderBy

  • Marks the query to order the results by the specified column in the specified direction, or ASC (ascending) by default.

    Type parameters

    • K: keyof T

    Parameters

    Returns this

orderByAsc

  • orderByAsc<K>(column: K): this

  • Marks the query to order the results by the specified column in ascending direction.

    Type parameters

    • K: keyof T

    Parameters

    • column: K

    Returns this

orderByDesc

  • orderByDesc<K>(column: K): this

  • Marks the query to order the results by the specified column in descending direction.

    Type parameters

    • K: keyof T

    Parameters

    • column: K

    Returns this

pluck

  • pluck<K>(column: K): Promise<T[K][]>

  • Returns only the specified column of all rows this query currently targets.

    Type parameters

    • K: keyof T

    Parameters

    • column: K

    Returns Promise<T[K][]>

rightJoin

  • Adds a new RIGHT JOIN with the specified table on the specified columns and operators. This operation loses type-safety since it is impossible to determine the return type of the join statically.

    Parameters

    • table: string
    • first: string
    • operator: Operator
    • second: string

    Returns QueryBuilder<KeyedDatabaseResult>

  • Adds a new nested RIGHT JOIN with the specified table. The handler receives a JoinClause which it can use to build the new join.

    Parameters

    • table: string
    • handler: function

    Returns QueryBuilder<KeyedDatabaseResult>

  • Adds a new RIGHT JOIN with the specified model and the specified columns and operator. This operation is type safe and returns a union of the current fields and the fields of the specified model.

    Type parameters

    Parameters

    • model: M
    • first: string
    • operator: Operator
    • second: string

    Returns QueryBuilder<T & A>

  • Adds a new nested RIGHT JOIN with the specified model. The handler receives a JoinClause which it can use to build the new join. This operation is type safe and returns a union of the current fields and the fields of the specified model.

    Type parameters

    Parameters

    Returns QueryBuilder<T & A>

select

  • Narrows this query down to the specified fields. This erases type- safety since raw SQL queries can be used. Do not use with untrusted input.

    Parameters

    • Rest ...fields: string[]

    Returns QueryBuilder<KeyedDatabaseResult>

sum

  • sum(column?: undefined | string): Promise<number>

  • Returns the sum of the specified column, or the current column by default.

    Parameters

    • Optional column: undefined | string

    Returns Promise<number>

update

  • update(values: Partial<T>): Promise<void>

  • Updates the specified values for all rows matching the current query.

    Parameters

    • values: Partial<T>

    Returns Promise<void>

value

  • value<K>(column: K): Promise<T[K]>

  • Returns the specified column of the first result returned by this query.

    Type parameters

    • K: keyof T

    Parameters

    • column: K

    Returns Promise<T[K]>

where

  • where(handler: function): this
  • where<K>(column: K, value: T[K]): this
  • where<K>(column: K, operator: Operator, value: T[K], boolean?: QueryBoolean): this

  • Creates a new nested where clause. The specified callback receives a nested query builder that can be used to build the nested queries.

    Parameters

    • handler: function
        • (builder: this): any

        • Parameters

          • builder: this

          Returns any

    Returns this

  • Adds a new where clause for the specified column, requiring that it matches the specified variable. This is an alias for where(column, "=", value).

    Type parameters

    • K: keyof T

    Parameters

    • column: K
    • value: T[K]

    Returns this

  • Adds a new where clause for the specified column, operator and value. Optionally you can provide a QueryBoolean of "OR" instead of "AND", but it is recommended that you use orWhere instead.

    Type parameters

    • K: keyof T

    Parameters

    Returns this

whereColumn

  • Adds a new where clause comparing two columns of the current query. This uses AND as boolean by default, use orWhereColumn if you want OR instead.

    Type parameters

    • K1: keyof T

    • K2: keyof T

    Parameters

    Returns this

whereNested

  • whereNested(handler: function, boolean?: QueryBoolean): this

  • Adds a new nested where query. The handler receives a new QueryBuilder that can be used to enter the where clauses of the nested where.

    Parameters

    Returns this

whereNotNull

  • whereNotNull<K>(column: K): this

  • Adds a new where clause asserting that the specified column is NOT NULL.

    Type parameters

    • K: keyof T

    Parameters

    • column: K

    Returns this

whereNull

  • whereNull<K>(column: K, boolean?: QueryBoolean, negate?: boolean): this

  • Adds a new where clause asserting that the specified column is NULL.

    Type parameters

    • K: keyof T

    Parameters

    • column: K
    • Default value boolean: QueryBoolean = "AND"
    • Default value negate: boolean = false

    Returns this

whereRaw

  • Adds a new raw where clause for the current query. You can use ? as a substitute for arguments to securely bind parameters.

    Parameters

    Returns this

Static execute

  • execute(sql: string, args?: any[]): Promise<void>

  • Executes the specified raw query.

    Parameters

    • sql: string
    • Optional args: any[]

    Returns Promise<void>

Static model

Static table

  • Creates a new QueryBuilder referencing the specified table.

    Type parameters

    • T: any

    Parameters

    • table: string

    Returns QueryBuilder<T>

Generated using TypeDoc