prorm API Reference
    Preparing search index...

    Interface FindOptions

    The single options object every finder compiles into SQL. where, include, order, limit, locking and the rest behave identically whichever finder you call.

    interface FindOptions {
        where?: WhereOptions<any>;
        attributes?: AttributesOptions;
        withCount?: string[];
        schema?: string;
        tableName?: string;
        searchPath?: string | string[];
        using?: string;
        include?: IncludeOptions[] | Includeable[];
        order?: Order;
        limit?: number;
        offset?: number;
        group?:
            | string
            | string[]
            | [any, string][]
            | { model: ModelStatic<any>; as?: string }[];
        groupType?: "rollup" | "cube" | "grouping" | "none";
        groupingSets?: string[][];
        having?: HavingOptions<any>;
        raw?: boolean;
        transaction?: Transaction;
        lock?: LockOptions;
        benchmark?: boolean;
        logging?: boolean | ((sql: string, time?: number) => void);
        benchmarkMark?: string;
        paranoid?: boolean;
        unionType?: UnionType;
        union?: UnionQueryOptions[];
        subQuery?: boolean;
        subquery?: boolean;
        distinct?: boolean;
        cte?: CTEOptions[];
        stream?: boolean;
        streamBatchSize?: number;
        transform?: (record: any) => any;
        streamHighWatermark?: number;
    }

    Hierarchy (View Summary)

    Index
    where? attributes? withCount? schema? tableName? searchPath? using? include? order? limit? offset? group? groupType? groupingSets? having? raw? transaction? lock? benchmark? logging? benchmarkMark? paranoid? unionType? union? subQuery? subquery? distinct? cte? stream? streamBatchSize? transform? streamHighWatermark?
    where?: WhereOptions<any>
    attributes?: AttributesOptions
    withCount?: string[]

    Association aliases to count without loading their rows. Each sets <alias>Count on every returned row.

    const users = await User.findAll({ withCount: ['posts'] });
    users[0].postsCount; // number

    Resolved with one grouped query per association, so the statement count does not grow with the number of parent rows. Prefer this over a full include when you only need the size.

    schema?: string

    Schema to use for the main table

    tableName?: string

    Overrides the FROM target of the query, in place of the model's own table name. Primarily useful together with cte to select from a CTE result set instead of the model's base table

    // Select from the `org_chart` CTE instead of `employees`
    Employee.findAll({ cte: [...], tableName: 'org_chart' })
    searchPath?: string | string[]

    Search path for query resolution (PostgreSQL)

    using?: string

    Connection name to use for this query Allows querying a different database connection

    // Use analytics connection
    User.findAll({
      using: 'analytics'
    });
    include?: IncludeOptions[] | Includeable[]
    order?: Order
    limit?: number

    Number of records to return

    offset?: number

    Number of records to skip

    group?:
        | string
        | string[]
        | [any, string][]
        | { model: ModelStatic<any>; as?: string }[]

    GROUP BY clause - can be a string, array of strings, array of arrays with function expressions, or array of model objects

    group: 'status'
    group: ['status']
    group: ['userId', 'category']
    group: [['status', 'role']] // Array format for mixed columns and expressions
    group: [[prorm.fn('COUNT', 'id'), 'count']] // Function expressions with alias
    group: [{ model: User, as: 'author' }]
    groupType?: "rollup" | "cube" | "grouping" | "none"

    Type of grouping for advanced aggregation features

    • 'rollup': Generates ROLLUP(column, ...) for hierarchical aggregations
    • 'cube': Generates CUBE(column, ...) for multi-dimensional aggregations
    • 'grouping': Uses GROUPING SETS for custom grouping combinations
    • 'none': No special grouping type (default)
    groupType: 'rollup'
    groupType: 'cube'
    groupType: 'grouping'
    groupingSets?: string[][]

    GROUPING SETS - custom grouping combinations for advanced aggregation Each element is an array representing one grouping set

    groupingSets: [['status'], ['region'], []] // Group by status, region, and grand total
    groupingSets: [['a', 'b'], ['a'], []] // Group by (a,b), (a), and grand total
    having?: HavingOptions<any>

    HAVING clause - filter aggregated results Supports all WHERE operators plus aggregation-specific conditions

    having: { count: { [Op.gt]: 5 } }
    having: { count: { [Op.between]: [1, 10] } }
    having: { $or: [{ count: { [Op.lt]: 2 } }, { totalViews: { [Op.gt]: 1000 } }] }
    raw?: boolean
    transaction?: Transaction
    lock?: LockOptions

    Row-level locking options

    • true: FOR UPDATE (equivalent to 'UPDATE')
    • 'UPDATE': FOR UPDATE - prevents other transactions from modifying the rows
    • 'SHARE': FOR SHARE (PostgreSQL) / LOCK IN SHARE MODE (MySQL)
    • 'KEY SHARE': FOR KEY SHARE (PostgreSQL only)
    • { of: Model }: Lock only the specified table (PostgreSQL)
    // Lock all rows with FOR UPDATE
    User.findAll({ lock: 'UPDATE' })

    // Lock with transaction-level FOR UPDATE
    User.findAll({ lock: true, transaction: t })

    // Lock only the User table (PostgreSQL)
    User.findAll({ lock: { of: User } })
    benchmark?: boolean
    logging?: boolean | ((sql: string, time?: number) => void)
    benchmarkMark?: string
    paranoid?: boolean

    When true, includes soft-deleted records in query results. When false or undefined, excludes soft-deleted records (default behavior for paranoid models).

    false
    unionType?: UnionType

    UNION type for combining multiple queries

    'UNION'
    union?: UnionQueryOptions[]

    Array of union queries to combine with the main query

    subQuery?: boolean

    Subquery support - allows using literal SQL in where clauses Usage: { id: { [Op.in]: prorm.literal('(SELECT id FROM users)') } }

    subquery?: boolean

    Whether to use subquery for includes. When false, uses JOIN instead of IN (SELECT) for includes. When true (default), uses subquery IN pattern for includes.

    true

    // Use JOIN instead of IN (SELECT)
    const users = await User.findAll({
      include: [{ model: Post, where: { status: 'published' } }],
      subquery: false
    });

    // Use subquery IN pattern (default)
    const users = await User.findAll({
      include: [{ model: Post, where: { status: 'published' } }],
      subquery: true
    });
    distinct?: boolean

    When true, applies DISTINCT to the query to remove duplicate rows. Useful when using aggregate functions with includes that may cause duplicates.

    undefined

    // Count distinct users with posts
    const count = await User.count({ distinct: true, include: Post })
    cte?: CTEOptions[]

    Common Table Expressions (CTEs) to use in the query Supports both regular WITH and WITH RECURSIVE

    // Simple CTE
    cte: [{ name: 'active_users', query: 'SELECT * FROM users WHERE active = true' }]

    // Recursive CTE for hierarchy
    cte: [{
      name: 'org_chart',
      columns: ['id', 'name', 'manager_id'],
      query: `SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
              UNION ALL
              SELECT e.id, e.name, e.manager_id FROM employees e
              INNER JOIN org_chart o ON e.manager_id = o.id`,
      recursive: true
    }]
    stream?: boolean

    Enable streaming results for large datasets When true, returns a ReadableStream instead of waiting for all results

    false
    streamBatchSize?: number

    Batch size for streaming results

    1000
    transform?: (record: any) => any

    Transform function to apply to each record during streaming

    streamHighWatermark?: number

    High watermark for the stream (internal buffer size)

    1000

    Settings
    Member Visibility

    On This Page
    Properties