db-query-dsl

Type-safe DSL for describing database queries using universal TypeScript types. It lets you define filtering, sorting, projection, and pagination in a consistent way and adapt these options to a specific driver/ORM (Mongoose, Prisma, Knex, etc.) through your own adapter.

Installation

pnpm add @bee4boop/db-query-dsl
# or
npm i @bee4boop/db-query-dsl
# or
yarn add @bee4boop/db-query-dsl

Quick start

import type {
  QueryOptions,
  Where,
  SortOptions,
  SelectOptions,
  QueryAdapter,
} from '@bee4boop/db-query-dsl';

// Your domain type
type User = {
  id: string;
  email: string;
  name: string;
  age: number;
  status: 'active' | 'pending' | 'deleted';
  createdAt: Date;
  profile?: { city?: string; country?: string };
  tags: string[];
};

// Universal query options
const options: QueryOptions<User> = {
  where: {
    // logical operator keys — 'and' | 'or' | 'not'
    and: [
      { status: 'active' },
      { age: { gte: 18 } },
      { email: { regex: /@gmail\.com$/i } },
    ],
    // can be combined with top-level fields
    profile: { country: 'USA' },
  },
  sort: { createdAt: 'desc', age: 'asc' },
  select: { email: true, name: true, createdAt: true },
  pagination: { page: 1, perPage: 20 },
};

// You write an adapter for your driver/ORM
declare const adapter: QueryAdapter<User, unknown>;
const dbQuery = adapter.adapt(/* baseQuery */, options);

Concept

• Where — type-safe MongoDB-like filters: field-level comparison operators and object-level logical operators.
• SortOptions — field sorting, including nested objects.
• SelectOptions — field projection, supports nesting.
• PaginationOptions — limit/offset or page/perPage.
• QueryOptions — aggregates where/sort/select/pagination.
• QueryAdapter<TDomain, Q> — adapter interface that converts universal QueryOptions<T> into a concrete query Q for your DB/ORM.

Note: logical operators are expressed as string keys 'and' | 'or' | 'not'. The LogicalOperator enum is used only at the type level.

Examples

Filters Where<T>

type Product = {
  name: string;
  price: number;
  rating?: number;
  tags: string[];
  createdAt: Date;
};

// Primitives and comparison operators
const where1: Where<Product> = {
  price: { gte: 500, lte: 2000 },
};

// Logical operators
const where2: Where<Product> = {
  or: [{ rating: { gte: 4.5 } }, { tags: { all: ['premium', 'pro'] } }],
};

// Regex and nested objects
const where3: Where<Product> = {
  name: { regex: /laptop/i },
};

Supported field-level operators (FieldOps<V>):

• eq/ne: equal / not equal
• in/nin: in / not in a set
• gt/gte/lt/lte: numeric comparisons
• exists: field existence
• all: all elements are present (arrays)
• regex: regular expression (string | RegExp)

Logical operators (LogicalOps<T>):

• and: array of conditions, all must pass
• or: array of conditions, at least one passes
• not: negates a nested condition

Sorting SortOptions<T>

const sort1: SortOptions<Product> = { price: 'asc', rating: 'desc' };
const sort2: SortOptions<Product> = { createdAt: -1 }; // numeric direction is allowed

Projection SelectOptions<T>

const select1: SelectOptions<Product> = { name: true, price: true };
// Nested selection if the field is an object
type User = {
  profile: { firstName: string; lastName: string; avatar?: string };
};
const select2: SelectOptions<User> = {
  profile: { firstName: 1, lastName: 1, avatar: 0 },
};

Pagination PaginationOptions

{ limit: 10, offset: 20 }
{ page: 2, perPage: 50 }

Full QueryOptions<T>

import type { QueryOptions } from '@bee4boop/db-query-dsl';

type Post = {
  title: string;
  content: string;
  author: { name: string; email: string };
  tags: string[];
  createdAt: Date;
};

const query: QueryOptions<Post> = {
  where: {
    and: [
      { title: { regex: /typescript/i } },
      { tags: { all: ['ts', 'tips'] } },
    ],
    author: { name: { regex: /^john/i } },
  },
  sort: { createdAt: 'desc' },
  select: { title: true, author: { name: 1, email: 1 } },
  pagination: { limit: 10, offset: 0 },
};

Adapter example

An adapter is a “bridge” between the universal types and a specific DB/ORM. You implement the translation according to your needs.

Mongoose sketch

import type {
  QueryAdapter,
  QueryOptions,
  Where,
  SortOptions,
  SelectOptions,
} from '@bee4boop/db-query-dsl';
import type { FilterQuery, Query } from 'mongoose';

type AnyDoc = Record<string, unknown>;

export class MongooseQueryAdapter<TDomain extends AnyDoc>
  implements QueryAdapter<TDomain, Query<any, any>>
{
  adapt(baseQuery: Query<any, any>, options?: QueryOptions<TDomain>) {
    if (!options) return baseQuery;
    let q = baseQuery;
    q = this.applyWhere(q, options.where);
    q = this.applySort(q, options.sort);
    q = this.applySelect(q, options.select);
    q = this.applyPagination(
      q,
      options.pagination?.offset,
      options.pagination?.limit,
    );
    return q;
  }

  applyWhere(query: Query<any, any>, where?: Where<TDomain>) {
    if (!where) return query;
    // Convert universal Where<T> to Mongoose FilterQuery here
    const filter: FilterQuery<any> = this.toMongooseFilter(where);
    return query.find(filter);
  }

  applySort(query: Query<any, any>, sort?: SortOptions<TDomain>) {
    if (!sort) return query;
    return query.sort(sort as any);
  }

  applySelect(query: Query<any, any>, select?: SelectOptions<TDomain>) {
    if (!select) return query;
    return query.select(select as any);
  }

  applyPagination(query: Query<any, any>, offset?: number, limit?: number) {
    if (typeof offset === 'number') query = query.skip(offset);
    if (typeof limit === 'number') query = query.limit(limit);
    return query;
  }

  applyPopulate(query: Query<any, any>, populate?: string | string[]) {
    if (!populate) return query;
    if (Array.isArray(populate)) {
      populate.forEach((p) => (query = query.populate(p)));
      return query;
    }
    return query.populate(populate);
  }

  private toMongooseFilter(where: Where<TDomain>): FilterQuery<any> {
    // Implement Where<T> -> FilterQuery<any> translation according to your rules
    return where as unknown as FilterQuery<any>;
  }
}

Prisma sketch

import type {
  QueryAdapter,
  QueryOptions,
  Where,
  SortOptions,
  SelectOptions,
} from '@bee4boop/db-query-dsl';

export class PrismaAdapter<
  T,
  Q extends {
    where?: any;
    orderBy?: any;
    select?: any;
    skip?: number;
    take?: number;
  },
> implements QueryAdapter<T, Q>
{
  adapt(baseQuery: Q, options?: QueryOptions<T>): Q {
    if (!options) return baseQuery;
    const q = { ...baseQuery };
    if (options.where) q.where = this.toWhere(options.where);
    if (options.sort) q.orderBy = this.toOrderBy(options.sort);
    if (options.select) q.select = options.select as any;
    if (options.pagination?.offset !== undefined)
      q.skip = options.pagination.offset;
    if (options.pagination?.limit !== undefined)
      q.take = options.pagination.limit;
    return q;
  }
  applyWhere(q: Q, where?: Where<T>) {
    return this.adapt(q, { where } as any);
  }
  applySort(q: Q, sort?: SortOptions<T>) {
    return this.adapt(q, { sort } as any);
  }
  applySelect(q: Q, select?: SelectOptions<T>) {
    return this.adapt(q, { select } as any);
  }
  applyPagination(q: Q, offset?: number, limit?: number) {
    return this.adapt(q, { pagination: { offset, limit } } as any);
  }
  applyPopulate(q: Q) {
    return q;
  } // Prisma does not need populate

  private toWhere(where: Where<T>) {
    return where as any;
  }
  private toOrderBy(sort: SortOptions<T>) {
    return sort as any;
  }
}

API

• Types
  • FieldOps<V>
  • LogicalOps<T> — keys: 'and' | 'or' | 'not'
  • Where<T> — composition of LogicalOps<T> and fields of T
  • SortOrder — 'asc' | 'desc' | 1 | -1'
  • SortOptions<T>
  • SelectOptions<T>
• Interfaces
  • PaginationOptions — { limit?, offset?, page?, perPage? }
  • QueryOptions<T> — { where?, sort?, select?, pagination? }
  • QueryAdapter<TDomain, Q> — methods:
    • adapt(baseQuery: Q, options?: QueryOptions<TDomain>): Q
    • applyWhere(query: Q, where?: Where<TDomain>): Q
    • applySort(query: Q, sort?: SortOptions<TDomain>): Q
    • applySelect(query: Q, select?: SelectOptions<TDomain>): Q
    • applyPagination(query: Q, offset?: number, limit?: number): Q
    • applyPopulate(query: Q, populate?: string | string[]): Q

License

MIT © Vyacheslav D.

Contributing

See CONTRIBUTING.md for the developer workflow, CI/release process, and versioning guidelines.