Libraries
    Preparing search index...

    Interface VariantDbSet<TEntity, K>

    A DbSet-like handle scoped to a single polymorphic variant (one branch of a discriminated union entity). All reads are automatically pre-filtered by the discriminator; all writes apply the correct STI / CTI logic.

    Obtain via DbSet.ofVariant('key') — analogous to EF Core's Set<DerivedType>().

    interface VariantDbSet<TEntity extends Entity<any, any, any>, K extends string> {
        andWhere(
            column: ColumnRef<EntitySchema<TEntity>>,
            operator: string,
            value: any,
        ): this;
        andWhere(column: ColumnRef<EntitySchema<TEntity>>, value: any): this;
        andWhere(record: Record<string, any>): this;
        andWhere(callback: (builder: QueryBuilder) => void): this;
        andWhere(raw: Raw): this;
        apply(fn: (builder: QueryBuilder) => void): this;
        avg(column: Raw<any> | ColumnRef<EntitySchema<TEntity>>): this;
        bulkInsert(
            rows: InferType<
                ReturnType<EntitySchema<TEntity>["makeAllPropsOptional"]>,
            >[],
            opts?: {
                chunkSize?: number;
                conflictColumns?: ColumnRef<EntitySchema<TEntity>>[];
                onConflict?: "ignore" | "merge";
            },
        ): Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>[]>;
        bulkUpdate(
            updates: readonly {
                set: Partial<InferType<TLocalSchema>>;
                where: Partial<InferType<TLocalSchema>>;
            }[],
        ): Promise<number>;
        bulkUpsert(
            rows: InferType<
                ReturnType<EntitySchema<TEntity>["makeAllPropsOptional"]>,
            >[],
            opts: {
                chunkSize?: number;
                conflictColumns: ColumnRef<EntitySchema<TEntity>>[];
            },
        ): Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>[]>;
        count(column?: Raw<any> | ColumnRef<EntitySchema<TEntity>>): this;
        countDistinct(column?: Raw<any> | ColumnRef<EntitySchema<TEntity>>): this;
        delete(): Promise<void>;
        distinct(...columns: (Raw<any> | ColumnRef<EntitySchema<TEntity>>)[]): this;
        execute(): Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>[]>;
        find(
            pk: PrimaryKeyValueOf<EntitySchema<TEntity>>,
        ): Promise<VariantResult<TEntity, K> | undefined>;
        findMany(
            pks: readonly PrimaryKeyValueOf<EntitySchema<TEntity>>[],
        ): Promise<VariantResult<TEntity, K>[]>;
        findOrFail(
            pk: PrimaryKeyValueOf<EntitySchema<TEntity>>,
        ): Promise<VariantResult<TEntity, K>>;
        first(): Promise<
            ExtractBranch<EntityResultByVariant<TEntity>, K>
            | undefined,
        >;
        groupBy(...columns: (Raw<any> | ColumnRef<EntitySchema<TEntity>>)[]): this;
        groupByRaw(sql: string, ...bindings: any[]): this;
        hardDelete(): Promise<number>;
        having(
            column: Raw<any> | ColumnRef<EntitySchema<TEntity>>,
            operator: string,
            value: any,
        ): this;
        havingRaw(sql: string, ...bindings: any[]): this;
        include<R extends string>(
            sel: (t: RelKeyTree<TEntity>) => R,
            customize?: (q: SchemaQueryBuilder<any, any>) => void,
        ): VariantDbSet<TEntity, K>;
        insert(
            payload: VariantInsertPayload<TEntity, K>,
        ): Promise<VariantResult<TEntity, K>>;
        insertMany(
            data: InferType<
                ReturnType<EntitySchema<TEntity>["makeAllPropsOptional"]>,
            >[],
        ): Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>[]>;
        joinMany<
            TForeignSchema extends
                ObjectSchemaBuilder<any, any, any, any, any, any, any>,
            TFieldName extends string,
        >(
            spec: JoinManySpec<EntitySchema<TEntity>, TForeignSchema, TFieldName>,
        ): SchemaQueryBuilder<
            EntitySchema<TEntity>,
            WithJoinedMany<
                ExtractBranch<EntityResultByVariant<TEntity>, K>,
                TFieldName,
                TForeignSchema,
            >,
        >;
        joinOne<
            TForeignSchema extends
                ObjectSchemaBuilder<any, any, any, any, any, any, any>,
            TFieldName extends string,
            TRequired extends boolean = true,
        >(
            spec: JoinOneSpec<
                EntitySchema<TEntity>,
                TForeignSchema,
                TFieldName,
                TRequired,
            >,
        ): SchemaQueryBuilder<
            EntitySchema<TEntity>,
            WithJoinedOne<
                ExtractBranch<EntityResultByVariant<TEntity>, K>,
                TFieldName,
                TForeignSchema,
                TRequired,
            >,
        >;
        limit(n: number): this;
        max(column: Raw<any> | ColumnRef<EntitySchema<TEntity>>): this;
        min(column: Raw<any> | ColumnRef<EntitySchema<TEntity>>): this;
        offset(n: number): this;
        onConflict(
            ...conflictColumns: ColumnRef<EntitySchema<TEntity>>[],
        ): OnConflictBuilder<
            EntitySchema<TEntity>,
            ExtractBranch<EntityResultByVariant<TEntity>, K>,
        >;
        onlyDeleted(): this;
        orderBy(
            column: Raw<any> | ColumnRef<EntitySchema<TEntity>>,
            direction?: "asc" | "desc",
        ): this;
        orderByRaw(sql: string, ...bindings: any[]): this;
        orWhere(
            column: ColumnRef<EntitySchema<TEntity>>,
            operator: string,
            value: any,
        ): this;
        orWhere(column: ColumnRef<EntitySchema<TEntity>>, value: any): this;
        orWhere(record: Record<string, any>): this;
        orWhere(callback: (builder: QueryBuilder) => void): this;
        orWhere(raw: Raw): this;
        orWhereIn(
            column: ColumnRef<EntitySchema<TEntity>>,
            values: readonly any[] | QueryBuilder<any, any>,
        ): this;
        orWhereNotIn(
            column: ColumnRef<EntitySchema<TEntity>>,
            values: readonly any[] | QueryBuilder<any, any>,
        ): this;
        orWhereNotNull(column: ColumnRef<EntitySchema<TEntity>>): this;
        orWhereNull(column: ColumnRef<EntitySchema<TEntity>>): this;
        paginate(
            opts: { page: number; pageSize: number },
        ): Promise<
            PaginationResult<ExtractBranch<EntityResultByVariant<TEntity>, K>>,
        >;
        paginateAfter(
            opts: {
                column?: ColumnRef<EntitySchema<TEntity>>;
                cursor?: any;
                direction?: "asc" | "desc";
                limit: number;
            },
        ): Promise<
            CursorPaginationResult<
                ExtractBranch<EntityResultByVariant<TEntity>, K>,
            >,
        >;
        pluck<K extends string>(
            column: ColumnRef<EntitySchema<TEntity>>,
        ): Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>[K][]>;
        projected<K extends string>(
            name: K,
        ): SchemaQueryBuilder<
            EntitySchema<TEntity>,
            Pick<
                ExtractBranch<EntityResultByVariant<TEntity>, K>,
                ProjectionKeysOf<EntitySchema<TEntity>, K> & keyof ExtractBranch<
                    EntityResultByVariant<TEntity>,
                    K,
                >,
            >,
        >;
        restore(): Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>[]>;
        scoped<K extends never>(name: K): this;
        select(...columns: (Raw<any> | ColumnRef<EntitySchema<TEntity>>)[]): this;
        select<TSel extends SelectSelector<EntitySchema<TEntity>>>(
            selector: TSel,
        ): SchemaQueryBuilder<
            EntitySchema<TEntity>,
            SelectProjection<ReturnType<TSel>>,
        >;
        selectRaw(sql: string, bindings?: any[]): this;
        selectVariants(keys: string[]): this;
        sum(column: Raw<any> | ColumnRef<EntitySchema<TEntity>>): this;
        then<
            TReturn1 = ExtractBranch<EntityResultByVariant<TEntity>, K>[],
            TReturn2 = never,
        >(
            onfulfilled?:
                | (
                    (
                        value: ExtractBranch<EntityResultByVariant<TEntity>, K>[],
                    ) => TReturn1 | PromiseLike<TReturn1>
                )
                | null,
            onrejected?: ((reason: any) => TReturn2 | PromiseLike<TReturn2>) | null,
        ): Promise<TReturn1 | TReturn2>;
        toKnexQuery(): QueryBuilder;
        toQuery(): string;
        toString(): string;
        transacting(
            trx: Transaction,
        ): SchemaQueryBuilder<
            EntitySchema<TEntity>,
            ExtractBranch<EntityResultByVariant<TEntity>, K>,
        >;
        unscoped(): this;
        update(patch: VariantUpdatePayload<TEntity, K>): Promise<void>;
        upsert(
            data: InferType<
                ReturnType<EntitySchema<TEntity>["makeAllPropsOptional"]>,
            >,
            opts: {
                conflictColumns: ColumnRef<EntitySchema<TEntity>>[];
                updateColumns?: ColumnRef<EntitySchema<TEntity>>[];
            },
        ): Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>>;
        where(
            column: ColumnRef<EntitySchema<TEntity>>,
            operator: string,
            value: any,
        ): this;
        where(column: ColumnRef<EntitySchema<TEntity>>, value: any): this;
        where(raw: Raw, operator: string, value: any): this;
        where(callback: (builder: QueryBuilder) => void): this;
        where(record: Record<string, any>): this;
        where(raw: Raw): this;
        whereBetween(
            column: ColumnRef<EntitySchema<TEntity>>,
            range: readonly [any, any],
        ): this;
        whereExists(
            callback: QueryBuilder<any, any> | QueryCallback<any, unknown[]>,
        ): this;
        whereILike(column: ColumnRef<EntitySchema<TEntity>>, value: string): this;
        whereIn(
            column: ColumnRef<EntitySchema<TEntity>>,
            values: readonly any[] | QueryBuilder<any, any>,
        ): this;
        whereJsonPath(
            column: ColumnRef<EntitySchema<TEntity>>,
            path: string,
            operator?: string,
            value?: any,
        ): this;
        whereLike(column: ColumnRef<EntitySchema<TEntity>>, value: string): this;
        whereNot(
            column: ColumnRef<EntitySchema<TEntity>>,
            operator: string,
            value: any,
        ): this;
        whereNot(column: ColumnRef<EntitySchema<TEntity>>, value: any): this;
        whereNot(record: Record<string, any>): this;
        whereNot(callback: (builder: QueryBuilder) => void): this;
        whereNot(raw: Raw): this;
        whereNotBetween(
            column: ColumnRef<EntitySchema<TEntity>>,
            range: readonly [any, any],
        ): this;
        whereNotExists(
            callback: QueryBuilder<any, any> | QueryCallback<any, unknown[]>,
        ): this;
        whereNotIn(
            column: ColumnRef<EntitySchema<TEntity>>,
            values: readonly any[] | QueryBuilder<any, any>,
        ): this;
        whereNotNull(column: ColumnRef<EntitySchema<TEntity>>): this;
        whereNull(column: ColumnRef<EntitySchema<TEntity>>): this;
        whereRaw(sql: string, ...bindings: any[]): this;
        whereVariant(
            key: string,
            column: string,
            operator: string,
            value: any,
        ): this;
        withDeleted(): this;
        withTransaction(trx: Transaction): VariantDbSet<TEntity, K>;
    }

    Type Parameters

    • TEntity extends Entity<any, any, any>
    • K extends string

    Hierarchy

    Index

    Methods

    andWhere apply avg bulkInsert bulkUpdate bulkUpsert count countDistinct delete distinct execute find findMany findOrFail first groupBy groupByRaw hardDelete having havingRaw include insert insertMany joinMany joinOne limit max min offset onConflict onlyDeleted orderBy orderByRaw orWhere orWhereIn orWhereNotIn orWhereNotNull orWhereNull paginate paginateAfter pluck projected restore scoped select selectRaw selectVariants sum then toKnexQuery toQuery toString transacting unscoped update upsert where whereBetween whereExists whereILike whereIn whereJsonPath whereLike whereNot whereNotBetween whereNotExists whereNotIn whereNotNull whereNull whereRaw whereVariant withDeleted withTransaction

    Methods

    • Alias for where — explicitly adds an AND WHERE clause. Identical to calling .where() when no logical-OR grouping is needed.

      Parameters

      Returns this

      this for chaining.

    • Alias for where — explicitly adds an AND WHERE clause. Identical to calling .where() when no logical-OR grouping is needed.

      Parameters

      Returns this

      this for chaining.

    • Alias for where — explicitly adds an AND WHERE clause. Identical to calling .where() when no logical-OR grouping is needed.

      Parameters

      • record: Record<string, any>

      Returns this

      this for chaining.

    • Alias for where — explicitly adds an AND WHERE clause. Identical to calling .where() when no logical-OR grouping is needed.

      Parameters

      • callback: (builder: QueryBuilder) => void

      Returns this

      this for chaining.

    • Alias for where — explicitly adds an AND WHERE clause. Identical to calling .where() when no logical-OR grouping is needed.

      Parameters

      • raw: Raw

      Returns this

      this for chaining.

    • Escape hatch: apply any Knex method to the underlying base query.

      Use this when you need a Knex feature not exposed by this API (e.g. forUpdate(), CTEs, join(), union()).

      Parameters

      • fn: (builder: QueryBuilder) => void

        A callback that receives the raw Knex.QueryBuilder and may mutate it in place.

      Returns this

      this for chaining.

      query(db, UserSchema).apply(qb => qb.forUpdate().noWait());

    • Bulk-insert many rows in chunks. The default chunk size of 500 keeps comfortably below Postgres' parameter-count limit of 65535. The chunk size also auto-shrinks when the number of bindings per row would exceed that ceiling.

      When opts.onConflict is supplied each chunk is wrapped in an INSERT ... ON CONFLICT clause:

      • 'ignore' — ON CONFLICT (...) DO NOTHING
      • 'merge' — ON CONFLICT (...) DO UPDATE SET ... (uses conflictColumns as the conflict target and updates every inserted column).

      beforeInsert / afterInsert hooks fire per row, identically to insertMany.

      Parameters

      Returns Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>[]>

      The inserted rows (excluding rows skipped by 'ignore').

    • Bulk-update many rows in a single SQL statement using a CASE expression keyed on the entity's primary key.

      Each entry's where clause must fully match the entity's primary key columns (single or composite). Updates that touch different columns are coalesced into one statement; rows whose PK appears in updates but whose set does not contain a given column retain their existing value.

      Parameters

      Returns Promise<number>

      The number of rows affected.

    • Add a COUNT(*) or COUNT(column) aggregate to the select list.

      Parameters

      Returns this

      this for chaining.

    • Add a COUNT(DISTINCT column) aggregate to the select list.

      Parameters

      Returns this

      this for chaining.

    • Delete rows matched by the current WHERE clause (CTI: atomic).

      Returns Promise<void>

    • Add DISTINCT to the select clause. Duplicate rows are eliminated.

      Parameters

      Returns this

      this for chaining.

    • Execute the query and return all matching rows, mapped back to schema property names.

      Returns Promise<ExtractBranch<EntityResultByVariant<TEntity>, K>[]>

      A promise that resolves to an array of result objects typed as TResult[].

      const users = await query(db, UserSchema).execute();

    • Execute the query and return only the first row, or undefined if no rows match.

      Returns Promise<ExtractBranch<EntityResultByVariant<TEntity>, K> | undefined>

      const user = await query(db, UserSchema).where(t => t.id, id).first();
      if (user) { /* ... */ }

    • Add a GROUP BY clause.

      Parameters

      Returns this

      this for chaining.

    • Add a raw GROUP BY expression.

      Parameters

      • sql: string
      • ...bindings: any[]

      Returns this

      this for chaining.

    • Permanently delete rows matching the current WHERE clause, bypassing the soft-delete mechanism.

      Returns Promise<number>

      The number of rows deleted.

    • Add a HAVING column operator value clause (used with GROUP BY).

      Parameters

      Returns this

      this for chaining.

    • Add a raw HAVING expression.

      Parameters

      • sql: string
      • ...bindings: any[]

      Returns this

      this for chaining.

    • Eager-load a collection of related rows (one-to-many relationship).

      Related rows are fetched via a single CTE + jsonb_agg query. The collection is attached to each result row under the field name specified by spec.as. Supports limit, offset, and orderBy per-parent using a row_number() window function to avoid fetching the full relation before slicing.

      Type Parameters

      • TForeignSchema extends ObjectSchemaBuilder<any, any, any, any, any, any, any>
      • TFieldName extends string

      Parameters

      • spec: JoinManySpec<EntitySchema<TEntity>, TForeignSchema, TFieldName>

        Join specification. Key fields:

        • foreignSchema — the ObjectSchemaBuilder of the related table.
        • localColumn — the primary/unique key on the local table.
        • foreignColumn — the column on the foreign table that references localColumn.
        • as — the property name to attach the array under.
        • limit / offset — optional pagination per parent row.
        • orderBy — optional { column, direction } for the sub-collection.
        • foreignQuery — optional pre-filtered Knex.QueryBuilder.

      Returns SchemaQueryBuilder<
          EntitySchema<TEntity>,
          WithJoinedMany<
              ExtractBranch<EntityResultByVariant<TEntity>, K>,
              TFieldName,
              TForeignSchema,
          >,
      >

      this (with an updated TResult type that includes the new field) for chaining.

      const UserSchema = object({
          id:   number(),
          name: string(),
      }).hasTableName('users');

      const PostSchema = object({
          id:       number(),
          title:    string(),
          authorId: number(),
      }).hasTableName('posts');

      const users = await query(db, UserSchema)
          .joinMany({
              foreignSchema: PostSchema,
              localColumn:   t => t.id,
              foreignColumn: t => t.authorId,
              as:            'posts',
              limit:         5,
              orderBy:       { column: t => t.id, direction: 'desc' },
          });
      // users[0].posts — typed as Array<{ id: number; title: string; authorId: number }>

    • Eager-load a single related row (one-to-one / many-to-one relationship).

      The related rows are fetched using a single CTE + jsonb_agg — no N+1 queries. The related object is attached to each result row under the field name specified by spec.as.

      Type Parameters

      • TForeignSchema extends ObjectSchemaBuilder<any, any, any, any, any, any, any>
      • TFieldName extends string
      • TRequired extends boolean = true

      Parameters

      • spec: JoinOneSpec<EntitySchema<TEntity>, TForeignSchema, TFieldName, TRequired>

        Join specification. Key fields:

        • foreignSchema — the ObjectSchemaBuilder of the related table.
        • localColumn — the local column that holds the foreign-table reference.
        • foreignColumn — the primary/unique key on the foreign table.
        • as — the property name to attach the related object under.
        • required — if true (default), rows without a matching related record are excluded (inner join); if false, they are included with null (left join).
        • foreignQuery — optional pre-filtered Knex.QueryBuilder for the foreign table (e.g. to apply scopes).

      Returns SchemaQueryBuilder<
          EntitySchema<TEntity>,
          WithJoinedOne<
              ExtractBranch<EntityResultByVariant<TEntity>, K>,
              TFieldName,
              TForeignSchema,
              TRequired,
          >,
      >

      this (with an updated TResult type that includes the new field) for chaining.

      const PostSchema = object({
          id:       number(),
          title:    string(),
          authorId: number(),
      }).hasTableName('posts');

      const AuthorSchema = object({
          id:   number(),
          name: string(),
      }).hasTableName('authors');

      const posts = await query(db, PostSchema)
          .joinOne({
              foreignSchema: AuthorSchema,
              localColumn:   t => t.authorId,
              foreignColumn: t => t.id,
              as:            'author',
          });
      // posts[0].author.name — typed as string ✓

    • Limit the number of rows returned.

      Parameters

      • n: number

        Maximum number of rows.

      Returns this

      this for chaining.

    • Skip the first n rows in the result set (for cursor/offset pagination).

      Parameters

      • n: number

        Number of rows to skip.

      Returns this

      this for chaining.

    • Insert a row (or rows) with an ON CONFLICT clause.

      Returns a chainable object with .merge() and .ignore() methods.

      • .merge(updateData?) — updates the conflicting row with the provided fields (or all insert fields if omitted).
      • .ignore() — skips the insert when a conflict occurs (INSERT IGNORE).

      Parameters

      Returns OnConflictBuilder<
          EntitySchema<TEntity>,
          ExtractBranch<EntityResultByVariant<TEntity>, K>,
      >

      A chainable conflict builder.

      // Upsert: insert or update on conflict
      await query(db, UserSchema)
          .onConflict(t => t.email)
          .merge({ name: 'Bob' });

      // Insert and ignore on conflict
      await query(db, UserSchema)
          .onConflict(t => t.email)
          .ignore();

    • Return only soft-deleted rows (WHERE deleted_at IS NOT NULL).

      Returns this

      this for chaining.

    • Order the results by a column.

      Parameters

      • column: Raw<any> | ColumnRef<EntitySchema<TEntity>>

        Column reference or raw expression.

      • Optionaldirection: "asc" | "desc"

        'asc' (default) or 'desc'.

      Returns this

      this for chaining.

      query(db, UserSchema).orderBy(t => t.name).orderBy(t => t.createdAt, 'desc');

    • Order the results by a raw SQL expression.

      Parameters

      • sql: string

        Raw SQL (e.g. 'LOWER(name) ASC').

      • ...bindings: any[]

      Returns this

      this for chaining.

    • Add an OR WHERE clause. Use this to create alternative filter branches.

      Parameters

      Returns this

      this for chaining.

    • Add an OR WHERE clause. Use this to create alternative filter branches.

      Parameters

      Returns this

      this for chaining.

    • Add an OR WHERE clause. Use this to create alternative filter branches.

      Parameters

      • record: Record<string, any>

      Returns this

      this for chaining.

    • Add an OR WHERE clause. Use this to create alternative filter branches.

      Parameters

      • callback: (builder: QueryBuilder) => void

      Returns this

      this for chaining.

    • Add an OR WHERE clause. Use this to create alternative filter branches.

      Parameters

      • raw: Raw

      Returns this

      this for chaining.

    • Add an OR WHERE column IN (values) clause.

      Parameters

      Returns this

      this for chaining.

    • Add an OR WHERE column NOT IN (values) clause.

      Parameters

      Returns this

      this for chaining.

    • Add an OR WHERE column IS NOT NULL clause.

      Parameters

      Returns this

      this for chaining.

    • Execute an offset-based paginated query.

      Runs a count query and a data query in parallel. Returns the page data along with pagination metadata.

      Parameters

      • opts: { page: number; pageSize: number }

        { page, pageSize } — 1-based page number and page size.

      Returns Promise<PaginationResult<ExtractBranch<EntityResultByVariant<TEntity>, K>>>

      A PaginationResult with data, total count, and page info.

      const page = await query(db, Post)
          .where(t => t.status, 'published')
          .paginate({ page: 2, pageSize: 20 });
      // page.data, page.total, page.totalPages, page.hasNextPage, ...

    • Execute a cursor-based (keyset) paginated query.

      More efficient than offset pagination for large datasets. Fetches one extra row to determine whether more data exists.

      Parameters

      • opts: {
            column?: ColumnRef<EntitySchema<TEntity>>;
            cursor?: any;
            direction?: "asc" | "desc";
            limit: number;
        }

        { cursor, limit, column?, direction? }.

      Returns Promise<
          CursorPaginationResult<ExtractBranch<EntityResultByVariant<TEntity>, K>>,
      >

      A CursorPaginationResult with data, next cursor, and hasMore flag.

      const page = await query(db, Post)
          .orderBy(t => t.createdAt, 'desc')
          .paginateAfter({ cursor: lastCreatedAt, limit: 20 });

    • Apply a named projection defined on the schema via .projection(name, columns).

      Calling .projected() on the query builder does two things:

      1. Restricts the SQL SELECT clause to the columns registered under name (SQL column names are resolved via .hasColumnName()).
      2. Narrows the TypeScript result row type to Pick<Row, Keys> so accessing columns outside the projection is a compile-time error.

      The name parameter is constrained to the literal projection names registered on the schema — TypeScript will report an error for any unregistered name.

      Calling .projected() after .select(), any aggregate method (.count(), .min(), etc.), or a second .projected() call throws at runtime with a clear error message.

      Type Parameters

      • K extends string

      Parameters

      • name: K

        The projection name.

      Returns SchemaQueryBuilder<
          EntitySchema<TEntity>,
          Pick<
              ExtractBranch<EntityResultByVariant<TEntity>, K>,
              ProjectionKeysOf<EntitySchema<TEntity>, K> & keyof ExtractBranch<
                  EntityResultByVariant<TEntity>,
                  K,
              >,
          >,
      >

      A new builder whose result type is Pick<Row, ProjectionKeys>.

      const PostSchema = object({ id: number(), title: string(), body: string() })
        .hasTableName('posts')
        .projection('summary', 'id', 'title');

      const rows = await query(db, PostSchema)
        .scoped('published')
        .projected('summary');
      // rows: Array<Pick<Post, 'id' | 'title'>>
      // rows[0].body  // ← TS error: not in projection

      ddlExtension .projection() for schema-side definition.

    • Type Parameters

      • K extends never

      Parameters

      • name: K

      Returns this

    • Select specific columns instead of *. Each column reference is resolved to its SQL column name through the schema.

      Parameters

      Returns this

      this for chaining.

    • DTO projection: select multiple aliased columns at once via a descriptor record. Returns a query whose result rows match the shape of the selector's return value (each value typed as the inferred schema-property type).

      Type Parameters

      Parameters

      Returns SchemaQueryBuilder<EntitySchema<TEntity>, SelectProjection<ReturnType<TSel>>>

      const dtos = await query(db, UserSchema)
          .where(t => t.id, '>', 0)
          .select(t => ({ id: t.id, n: t.name }))
          .execute();
      // dtos: { id: number; n: string }[]

    • Add a raw SQL expression to the SELECT clause.

      Parameters

      • sql: string

        Raw SQL (e.g. '*, ts_rank(vector, query) AS rank').

      • Optionalbindings: any[]

        Optional parameter bindings.

      Returns this

      this for chaining.

    • Restrict the query to only return rows for the specified variant keys.

      Adds WHERE <discriminator> IN (...) to the query and skips the LEFT JOINs for excluded variants. This is more efficient than filtering after loading all variants.

      Only valid on a polymorphic schema (created via .withVariants()).

      Parameters

      • keys: string[]

        Discriminator values to include (e.g. ['image', 'document']).

      Returns this

      this for chaining.

      const images = await query(db, FileSchema).selectVariants(['image']);
      // images: Array<{ id; name; type: 'image'; width; height; format }>

    • Returns the underlying Knex query builder. Useful when passing this query as a foreignQuery in .joinOne() / .joinMany(), or any context that expects a raw Knex.QueryBuilder.

      Returns QueryBuilder

    • Return the raw SQL string that would be executed, for debugging. Does not execute the query against the database.

      Returns string

    • Alias for toQuery — returns the raw SQL string.

      Returns string

    • Bind this query builder to a Knex transaction.

      Returns a new builder that runs all operations — SELECT, INSERT, UPDATE, DELETE, and eager-loaded sub-queries — within the given transaction. The original builder is left unchanged.

      Use this when you already have a transaction obtained from knex.transaction() and want all operations performed by the returned builder to participate in that transaction.

      Parameters

      • trx: Transaction

        The Knex transaction obtained from knex.transaction().

      Returns SchemaQueryBuilder<
          EntitySchema<TEntity>,
          ExtractBranch<EntityResultByVariant<TEntity>, K>,
      >

      A new SchemaQueryBuilder bound to the transaction.

      async function createUser(
          data: InsertType<typeof UserSchema>,
          trx: Knex.Transaction
      ) {
          return query(db, UserSchema).transacting(trx).insert(data);
      }

      await db.transaction(async trx => {
          const user = await createUser({ name: 'Alice' }, trx);
          await query(db, PostSchema).transacting(trx).insert({ authorId: user.id, title: 'Hello' });
      });

    • Bypass the default scope (and soft-delete scope) for this query.

      Returns this

      this for chaining.

      await query(db, Post).unscoped().where(t => t.id, 1);

    • Add a WHERE column BETWEEN low AND high clause.

      Parameters

      Returns this

      this for chaining.

    • Add a WHERE EXISTS (subquery) clause.

      Parameters

      • callback: QueryBuilder<any, any> | QueryCallback<any, unknown[]>

        A Knex query callback or sub-query builder.

      Returns this

      this for chaining.

    • Add a case-insensitive WHERE column ILIKE value clause (PostgreSQL).

      Parameters

      Returns this

      this for chaining.

    • Add a WHERE column IN (values) clause.

      Parameters

      • column: ColumnRef<EntitySchema<TEntity>>

        Column reference (property accessor or string key).

      • values: readonly any[] | QueryBuilder<any, any>

        Array of values or a sub-query.

      Returns this

      this for chaining.

    • Add a PostgreSQL JSON path filter using @? / @@ operators or a path-based equality test via jsonb_path_query_first.

      Only supported on pg clients — throws at runtime on others.

      Parameters

      • column: ColumnRef<EntitySchema<TEntity>>

        Column reference for the jsonb column.

      • path: string

        Dot-separated property path (e.g. 'a.b.c') or a JSONPath expression string (e.g. '$.a.b ? (@ == 1)').

      • Optionaloperator: string

        Comparison operator (=, !=, <, <=, >, >=, @?, @@). Use @? / @@ for JSONPath existence / predicate tests.

      • Optionalvalue: any

        The right-hand side value. Ignored for @? and @@.

      Returns this

      this for chaining.

      // Filter rows where data->>'status' = 'active'
      query(db, Schema).whereJsonPath(t => t.data, 'status', '=', 'active');

      // JSONPath existence
      query(db, Schema).whereJsonPath(t => t.data, '$.tags[*] ? (@ == "sale")', '@?');

    • Add a case-sensitive WHERE column LIKE value clause.

      Parameters

      Returns this

      this for chaining.

    • Add a WHERE NOT clause — negates the condition.

      Parameters

      Returns this

      this for chaining.

    • Add a WHERE NOT clause — negates the condition.

      Parameters

      Returns this

      this for chaining.

    • Add a WHERE NOT clause — negates the condition.

      Parameters

      • record: Record<string, any>

      Returns this

      this for chaining.

    • Add a WHERE NOT clause — negates the condition.

      Parameters

      • callback: (builder: QueryBuilder) => void

      Returns this

      this for chaining.

    • Add a WHERE NOT clause — negates the condition.

      Parameters

      • raw: Raw

      Returns this

      this for chaining.

    • Add a WHERE column NOT BETWEEN low AND high clause.

      Parameters

      Returns this

      this for chaining.

    • Add a WHERE NOT EXISTS (subquery) clause.

      Parameters

      • callback: QueryBuilder<any, any> | QueryCallback<any, unknown[]>

        A Knex query callback or sub-query builder.

      Returns this

      this for chaining.

    • Add a WHERE column NOT IN (values) clause.

      Parameters

      Returns this

      this for chaining.

    • Add a raw WHERE clause. Useful for database-specific expressions.

      Parameters

      • sql: string

        Raw SQL string with optional :binding: or ? placeholders.

      • ...bindings: any[]

        Values for the placeholders.

      Returns this

      this for chaining.

    • Add a WHERE condition that applies only to rows matching a specific variant of a polymorphic schema. Rows for other variants pass through unaffected (the condition is ORed away for non-matching discriminator values).

      The column name is resolved against the variant's schema properties.

      Only valid on a polymorphic schema (created via .withVariants()).

      Parameters

      • key: string

        The discriminator value identifying the variant (e.g. 'image').

      • column: string

        Property key on the variant's schema (e.g. 'width').

      • operator: string

        SQL comparison operator ('=', '>', '<', 'like', etc.).

      • value: any

        The value to compare against.

      Returns this

      this for chaining.

      // Return all documents and only images wider than 1024 px
      const files = await query(db, FileSchema)
          .whereVariant('image', 'width', '>', 1024);

    • Include soft-deleted rows in the results.

      By default, schemas with .softDelete() automatically filter out rows where deleted_at IS NOT NULL. Call .withDeleted() to include them.

      Returns this

      this for chaining.

    Settings

    Member Visibility

    On This Page

    Methods