@asaidimu/query

@asaidimu/query is a flexible and type-safe query builder for constructing complex queries in JavaScript and TypeScript. It provides a fluent API for defining filters, sorting, pagination, projections, aggregations, and more. Ideal for building dynamic queries for databases, APIs, or other data sources.

Features

• Fluent API: Chainable methods for building queries.
• Type-Safe: Built with TypeScript for type safety and autocompletion.
• Flexible Filtering: Supports logical operators (and, or, not, etc.) and comparison operators (eq, lt, gt, etc.).
• Pagination: Supports both offset-based and cursor-based pagination.
• Projections: Include, exclude, or compute fields in query results.
• Aggregations: Group and aggregate data with operations like sum, avg, count, etc.
• Window Functions: Advanced analytics with window functions like row_number, rank, etc.
• Query Hints: Optimize queries with hints like index, force_index, etc.

Installation

Install the package using npm:

npm install @asaidimu/query

Or using yarn:

yarn add @asaidimu/query

Usage

Basic Example

import { QueryBuilder } from '@asaidimu/query';

// Create a query
const query = new QueryBuilder()
  .where({ field: 'age', operator: 'gt', value: 18 })
  .orderBy('name', 'asc')
  .offset(0, 10)
  .include(['name', 'age'])
  .build();

console.log(query);

Output:

{
  "filters": { "field": "age", "operator": "gt", "value": 18 },
  "sort": [{ "field": "name", "direction": "asc" }],
  "pagination": { "type": "offset", "offset": 0, "limit": 10 },
  "projection": { "include": ["name", "age"] }
}

Advanced Example

import { QueryBuilder } from '@asaidimu/query';

// Create a complex query
const query = new QueryBuilder()
  .where({
    operator: 'and',
    conditions: [
      { field: 'age', operator: 'gt', value: 18 },
      { field: 'status', operator: 'eq', value: 'active' },
    ],
  })
  .orderBy('name', 'asc')
  .cursor('abc123', 20, 'forward')
  .computed(
    { function: 'CONCAT', arguments: ['firstName', 'lastName'] },
    'fullName'
  )
  .aggregate(['department'], [
    { operation: 'sum', field: 'salary', alias: 'totalSalary' },
  ])
  .hint({ type: 'index', index: 'name_index' })
  .build();

console.log(query);

Output:

{
  "filters": {
    "operator": "and",
    "conditions": [
      { "field": "age", "operator": "gt", "value": 18 },
      { "field": "status", "operator": "eq", "value": "active" }
    ]
  },
  "sort": [{ "field": "name", "direction": "asc" }],
  "pagination": { "type": "cursor", "cursor": "abc123", "limit": 20, "direction": "forward" },
  "projection": {
    "computed": [
      {
        "type": "computed",
        "expression": { "function": "CONCAT", "arguments": ["firstName", "lastName"] },
        "alias": "fullName"
      }
    ]
  },
  "aggregations": {
    "groupBy": ["department"],
    "metrics": [{ "operation": "sum", "field": "salary", "alias": "totalSalary" }]
  },
  "hints": [{ "type": "index", "index": "name_index" }]
}

API Documentation

QueryBuilder<T, F>

The main class for building queries.

Methods

• where(filter: QueryFilter<T, F>): QueryBuilder<T, F>
  Adds a filter condition or group to the query.

• orderBy(field: keyof T & string, direction: SortDirection): QueryBuilder<T, F>
  Adds a sorting configuration to the query.

• offset(offset: number, limit: number): QueryBuilder<T, F>
  Adds offset-based pagination to the query.

• cursor(cursor: string | undefined, limit: number, direction: 'forward' | 'backward'): QueryBuilder<T, F>
  Adds cursor-based pagination to the query.

• include(fields: (keyof T & string)[]): QueryBuilder<T, F>
  Includes specific fields in the query results.

• exclude(fields: (keyof T & string)[]): QueryBuilder<T, F>
  Excludes specific fields from the query results.

• computed(expression: FunctionCall<F>, alias: string): QueryBuilder<T, F>
  Adds a computed field to the query results.

• case(conditions: CaseCondition<T, F>[], elseValue: FilterValue<T, F>, alias: string): QueryBuilder<T, F>
  Adds a case expression to the query results.

• join<R = any>(relation: keyof T & string, query: QueryDSL<R, F>, alias?: string): QueryBuilder<T, F>
  Adds a join configuration to the query.

• aggregate(groupBy: (keyof T & string)[], metrics: AggregationMetric<T>[]): QueryBuilder<T, F>
  Adds an aggregation configuration to the query.

• window(func: WindowFunction): QueryBuilder<T, F>
  Adds a window function to the query.

• hint(hint: QueryHint): QueryBuilder<T, F>
  Adds a query hint for optimization.

• build(): QueryDSL<T, F>
  Builds and returns the final query.

Type Definitions

The package includes TypeScript type definitions for all query components, including:

• QueryDSL: The main query structure.
• QueryFilter: Filter conditions or groups.
• SortDirection: Sorting direction (asc or desc).
• PaginationOptions: Pagination options (offset or cursor-based).
• ProjectionConfiguration: Field projections and computed fields.
• AggregationConfiguration: Aggregation configurations.
• WindowFunction: Window functions for advanced analytics.
• QueryHint: Query hints for optimization.