Query Builders

Query builders are initialized by calling the appropriate client method. Most builder methods return the builder instance, so method calls can be chained easily. The underlying query will not be executed until execute is called on the builder instance.

FindManyBuilder

Returns multiple records matching the provided criteria. Example:

const customers = await Customer.findMany().orderBy([{ lastName: 'ASC' }])

execute

public async execute<TRecord>(): Promise<TRecord[]>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): FindManyBuilder

Sets the transaction to be used by the query.

select

public select(...fields: ModelField[]): FindManyBuilder

Sets the SELECT clause for the query.

selectAll

public selectAll(): FindManyBuilder

Sets the SELECT clause for the query to all possible fields.

addSelect

public addSelect(...fields: ModelField[]): FindManyBuilder

Adds on to the SELECT clause for the query.

load

public load(

name: string,

getBuilder?: (builder: FindBuilder) => FindBuilder

): FindManyBuilder

public load(

name: string,

as: string,

getBuilder?: (builder: FindBuilder) => FindBuilder

): FindManyBuilder

Eager loads the specified model relation. An optional alias can be provided to return the related model or models under a different property name. The getBuilder parameter is a function that's passed a fresh FindOneBuilder or FindManyBuilder instance for the associated model and should return the same kind of Builder instance.

loadPaginated

public loadPaginated(

name: string,

getBuilder?: (builder: PaginatedBuilder) => PaginatedBuilder

): FindManyBuilder

public loadPaginated(

name: string,

as: string,

getBuilder?: (builder: PaginatedBuilder) => PaginatedBuilder

): FindManyBuilder

Eager loads a paginated relation. An optional alias can be provided to return the related models under a different property name. The getBuilder parameter is a function that's passed a fresh PaginateBuilder instance for the associated model and should return the same kind of PaginateBuilder instance.

resolveInfo

public resolveInfo(info: GraphQLResolveInfo): FindManyBuilder

Modifies the query based on the passed in GraphQLResolveInfo object. The selection set will determine what columns should be selected and which related models should be joined. The where, orderBy, limit and offset arguments, if they exist on the field and were provided, will be used to set the corresponding clauses in the query.

where

public where(where: Where): FindManyBuilder

Sets the WHERE clause for the query.

mergeWhere

public mergeWhere(where: Where): FindManyBuilder

Deep merges the provided object with the existing WHERE options for the query.

orderBy

public orderBy(orderBy: OrderBy[]): FindManyBuilder

Sets the ORDER BY clause for the query.

limit

public limit(value: number): FindManyBuilder

Sets the LIMIT for the query.

offset

public offset(value: number): FindManyBuilder

Sets the OFFSET for the query.

FindOneBuilder

Returns a single record matching the provided criteria, or null if one cannot be found. Example:

const customer = await Customer.findOne()

.where({ lastName: { equal: 'Giroux' } })

execute

public async execute<TRecord>(): Promise<TRecord | null>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): FindOneBuilder

Sets the transaction to be used by the query.

select

public select(...fields: ModelField[]): FindOneBuilder

Sets the SELECT clause for the query.

selectAll

public selectAll(): FindOneBuilder

Sets the SELECT clause for the query to all possible fields.

addSelect

public addSelect(...fields: ModelField[]): FindOneBuilder

Adds on to the SELECT clause for the query.

load

public load(

name: string,

getBuilder?: (builder: FindBuilder) => FindBuilder

): FindOneBuilder

public load(

name: string,

as: string,

getBuilder?: (builder: FindBuilder) => FindBuilder

): FindOneBuilder

Eager loads the specified model relation. An optional alias can be provided to return the related model or models under a different property name. The getBuilder parameter is a function that's passed a fresh FindOneBuilder or FindManyBuilder instance for the associated model and should return the same kind of Builder instance.

loadPaginated

public loadPaginated(

name: string,

getBuilder?: (builder: PaginatedBuilder) => PaginatedBuilder

): FindOneBuilder

public loadPaginated(

name: string,

as: string,

getBuilder?: (builder: PaginatedBuilder) => PaginatedBuilder

): FindOneBuilder

Eager loads a paginated relation. An optional alias can be provided to return the related models under a different property name. The getBuilder parameter is a function that's passed a fresh PaginateBuilder instance for the associated model and should return the same kind of PaginateBuilder instance.

resolveInfo

public resolveInfo(info: GraphQLResolveInfo): FindOneBuilder

Modifies the query based on the passed in GraphQLResolveInfo object. The selection set will determine what columns should be selected and which related models should be joined. The where, orderBy, limit and offset arguments, if they exist on the field and were provided, will be used to set the corresponding clauses in the query.

where

public where(where: Where): FindOneBuilder

Sets the WHERE clause for the query.

mergeWhere

public mergeWhere(where: Where): FindOneBuilder

Deep merges the provided object with the existing WHERE options for the query.

orderBy

public orderBy(orderBy: OrderBy[]): FindOneBuilder

Sets the ORDER BY clause for the query.

limit

public limit(value: number): FindOneBuilder

Sets the LIMIT for the query.

offset

public offset(value: number): FindOneBuilder

Sets the OFFSET for the query.

FindByIdBuilder

Returns a single record matching the provided primary key value, or null if one cannot be found. Example:

const customer = await Customer.findById(12)

execute

public async execute<TRecord>(): Promise<TRecord | null>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): FindByIdBuilder

Sets the transaction to be used by the query.

select

public select(...fields: ModelField[]): FindByIdBuilder

Sets the SELECT clause for the query.

selectAll

public selectAll(): FindByIdBuilder

Sets the SELECT clause for the query to all possible fields.

addSelect

public addSelect(...fields: ModelField[]): FindByIdBuilder

Adds on to the SELECT clause for the query.

load

public load(

name: string,

getBuilder?: (builder: FindBuilder) => FindBuilder

): FindByIdBuilder

public load(

name: string,

as: string,

getBuilder?: (builder: FindBuilder) => FindBuilder

): FindByIdBuilder

Eager loads the specified model relation. An optional alias can be provided to return the related model or models under a different property name. The getBuilder parameter is a function that's passed a fresh FindOneBuilder or FindManyBuilder instance for the associated model and should return the same kind of Builder instance.

loadPaginated

public loadPaginated(

name: string,

getBuilder?: (builder: PaginatedBuilder) => PaginatedBuilder

): FindByIdBuilder

public loadPaginated(

name: string,

as: string,

getBuilder?: (builder: PaginatedBuilder) => PaginatedBuilder

): FindByIdBuilder

Eager loads a paginated relation. An optional alias can be provided to return the related models under a different property name. The getBuilder parameter is a function that's passed a fresh PaginateBuilder instance for the associated model and should return the same kind of PaginateBuilder instance.

resolveInfo

public resolveInfo(info: GraphQLResolveInfo): FindByIdBuilder

Modifies the query based on the passed in GraphQLResolveInfo object. The selection set will determine what columns should be selected and which related models should be joined. The where, orderBy, limit and offset arguments, if they exist on the field and were provided, will be used to set the corresponding clauses in the query.

CreateOneBuilder

Creates a single record and returns the primary key value of the created record, if supported by the dialect. In MySQL and SQLite, the auto-incremented id value (based on LAST_INSERT_ID) is returned, if available. Example:

const id = await Customer.createOne({

firstName: 'Marc-Andre',

lastName: 'Giroux',

})

execute

public async execute(): Promise<string | number>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): CreateOneBuilder

Sets the transaction to be used by the query.

CreateManyBuilder

Creates multiple records and returns the primary key value of the created records, if supported by the dialect. In MySQL and SQLite, the auto-incremented id value (based on LAST_INSERT_ID) is returned, if available. Example:

const ids = await Customer.createMany([

{ firstName: 'Marc-Andre', lastName: 'Giroux' },

{ firstName: 'Lee', lastName: 'Byron' },

])

execute

public async execute(): Promise<(string | number)[]>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): CreateManyBuilder

Sets the transaction to be used by the query.

UpdateManyBuilder

Updates multiple records based on the provided criteria. Returns the number of affected records. Example:

const numberUpdated = await Customer.updateMany({ isSubscribed: true })

execute

public async execute(): Promise<number>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): UpdateManyBuilder

Sets the transaction to be used by the query.

where

public where(where: Where): UpdateManyBuilder

Sets the WHERE clause for the query.

mergeWhere

public mergeWhere(where: Where): UpdateManyBuilder

Deep merges the provided object with the existing WHERE options for the query.

orderBy

public orderBy(orderBy: OrderBy[]): UpdateManyBuilder

Sets the ORDER BY clause for the query.

limit

public limit(value: number): UpdateManyBuilder

Sets the LIMIT for the query.

offset

public offset(value: number): UpdateManyBuilder

Sets the OFFSET for the query.

UpdateByIdBuilder

Updates a single records based on the provided primary key value. Returns a boolean indicating whether the record was found. Example:

const wasUpdated = await Customer.updateById(12, { isSubscribed: true })

execute

public async execute(): Promise<boolean>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): UpdateByIdBuilder

Sets the transaction to be used by the query.

DeleteManyBuilder

Deletes multiple records based on the provided criteria. Returns the number of affected records. Example:

const numberDeleted = await Customer.deleteMany()

.where({ lastPurchase: { lessThan: new Date() } })

execute

public async execute(): Promise<number>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): DeleteManyBuilder

Sets the transaction to be used by the query.

where

public where(where: Where): DeleteManyBuilder

Sets the WHERE clause for the query.

mergeWhere

public mergeWhere(where: Where): DeleteManyBuilder

Deep merges the provided object with the existing WHERE options for the query.

orderBy

public orderBy(orderBy: OrderBy[]): DeleteManyBuilder

Sets the ORDER BY clause for the query.

limit

public limit(value: number): DeleteManyBuilder

Sets the LIMIT for the query.

offset

public offset(value: number): DeleteManyBuilder

Sets the OFFSET for the query.

DeleteByIdBuilder

Deletes a single records based on the provided primary key value. Returns a boolean indicating whether the record was found. Example:

const wasDeleted = await Customer.deleteById(12)

execute

public async execute(): Promise<boolean>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): DeleteByIdBuilder

Sets the transaction to be used by the query.

PaginateBuilder

Returns a "page" object representing a paginated result. Can be used for both pagination or aggregation. Example:

const { aggregate: { count, sum: { balance } } } = await Customer.paginate()

.count()

.sum('balance')

const { results, hasMore } = await Customer.paginate()

.selectAll()

.limit(10)

.hasMore()

execute

public async execute(): Promise<Object>

Executes the query and returns a Promise with the result.

toQueryBuilder

public toQueryBuilder(): Knex.QueryBuilder

Transforms the query builder instance into an instance of a Knex query builder.

transaction

public transaction(transaction: Knex.Transaction | null): PaginateBuilder

Sets the transaction to be used by the query.

count

public min(field: string): PaginateBuilder

Adds the count to the aggregate result object.

min

public min(field: string): PaginateBuilder

Adds the minimum for the provided field to the aggregate result object.

max

public max(field: string): PaginateBuilder

Adds the maximum for the provided field to the aggregate result object.

avg

public avg(field: string): PaginateBuilder

Adds the average for the provided field to the aggregate result object.

sum

public sum(field: string): PaginateBuilder

Adds the sum for the provided field to the aggregate result object.

select

public select(...fields: ModelField[]): PaginatedBuilder

Sets the SELECT clause for the query.

selectAll

public selectAll(): PaginatedBuilder

Sets the SELECT clause for the query to all possible fields.

addSelect

public addSelect(...fields: ModelField[]): PaginatedBuilder

Adds on to the SELECT clause for the query.

load

public load(

name: string,

getBuilder?: (builder: FindBuilder) => FindBuilder

): PaginatedBuilder

public load(

name: string,

as: string,

getBuilder?: (builder: FindBuilder) => FindBuilder

): PaginatedBuilder

Eager loads the specified model relation. An optional alias can be provided to return the related model or models under a different property name. The getBuilder parameter is a function that's passed a fresh FindOneBuilder or FindManyBuilder instance for the associated model and should return the same kind of Builder instance.

loadPaginated

public loadPaginated(

name: string,

getBuilder?: (builder: PaginatedBuilder) => PaginatedBuilder

): PaginatedBuilder

public loadPaginated(

name: string,

as: string,

getBuilder?: (builder: PaginatedBuilder) => PaginatedBuilder

): PaginatedBuilder

Eager loads a paginated relation. An optional alias can be provided to return the related models under a different property name. The getBuilder parameter is a function that's passed a fresh PaginateBuilder instance for the associated model and should return the same kind of PaginateBuilder instance.

resolveInfo

public resolveInfo(info: GraphQLResolveInfo): PaginateBuilder

Modifies the query based on the passed in GraphQLResolveInfo object. The selection set will determine what fields to aggregate and return as part of the aggregate result object. The where, orderBy, limit and offset arguments, if they exist on the field and were provided, will be used to set the corresponding clauses in the query.

where

public where(where: Where): PaginateBuilder

Sets the WHERE clause for the query.

mergeWhere

public mergeWhere(where: Where): PaginateBuilder

Deep merges the provided object with the existing WHERE options for the query.

orderBy

public orderBy(orderBy: OrderBy[]): PaginateBuilder

Sets the ORDER BY clause for the query.

limit

public limit(value: number): PaginateBuilder

Sets the LIMIT for the query.

offset

public offset(value: number): PaginateBuilder

Sets the OFFSET for the query.