Package Exports

fluent-query-builder

Readme

fluent-query-builder

A type-safe, DB-agnostic query builder for TypeScript, inspired by LINQ in C# with Entity Framework.

Overview

fluent-query-builder provides a fluent, type-safe API for building database queries that works across different database providers. The core query builder is database-agnostic, while provider-specific translators convert queries to the appropriate format (e.g., Prisma, TypeORM, Mongoose).

Key Features

Type-safe: Built with TypeScript for full type safety
DB-agnostic: Core query builder works with any database provider
Fluent API: Chainable methods for intuitive query construction
Provider support: Extensible architecture for multiple database providers
Immutable: All operations return new instances, ensuring predictable behavior

Installation

npm install fluent-query-builder

Prerequisites: TypeScript 5.0+ and Node.js 18+

Quick Start

import { QueryBuilder, ConditionOperator, SortDirection } from "fluent-query-builder";
import { PrismaTranslator } from "fluent-query-builder";
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Build a query
const query = QueryBuilder.create()
  .where("name", ConditionOperator.EQ, "John")
  .where("age", ConditionOperator.GTE, 18)
  .sortBy("createdAt", SortDirection.DESC)
  .paginate(1, 10)
  .include("posts");

// Translate to Prisma format
const prismaQuery = PrismaTranslator.translate(query);

// Execute with Prisma
const users = await prisma.user.findMany(prismaQuery);

Usage Guide

Basic Filtering with `where()`

The where() method is the simplest way to add filter conditions. Each call adds an AND condition.

import { QueryBuilder, ConditionOperator } from "fluent-query-builder";

// Simple equality check
const query1 = QueryBuilder.create().where("email", ConditionOperator.EQ, "user@example.com");

// Multiple conditions (AND by default)
const query2 = QueryBuilder.create()
  .where("status", ConditionOperator.EQ, "active")
  .where("age", ConditionOperator.GTE, 18);

// Using different operators
const query3 = QueryBuilder.create()
  .where("name", ConditionOperator.LIKE, "John%")
  .where("age", ConditionOperator.IN, [18, 19, 20])
  .where("deletedAt", ConditionOperator.IS_NULL);

// Nested field filtering with dot notation for relations
const query4 = QueryBuilder.create()
  .where("profile.email", ConditionOperator.EQ, "user@example.com")
  .where("profile.age", ConditionOperator.GTE, 18);

Complex Filtering with `filter()`

Use filter() for complex nested conditions with explicit logical operators (AND, OR, NOT).

import { QueryBuilder, ConditionOperator, LogicalOperator } from "fluent-query-builder";

// OR condition
const query1 = QueryBuilder.create().filter({
  operator: LogicalOperator.OR,
  conditions: [
    { field: "status", operator: ConditionOperator.EQ, value: "active" },
    { field: "status", operator: ConditionOperator.EQ, value: "pending" },
  ],
});

// Nested conditions
const query2 = QueryBuilder.create().filter({
  operator: LogicalOperator.AND,
  conditions: [
    { field: "status", operator: ConditionOperator.EQ, value: "active" },
    {
      operator: LogicalOperator.OR,
      conditions: [
        { field: "role", operator: ConditionOperator.EQ, value: "admin" },
        { field: "role", operator: ConditionOperator.EQ, value: "moderator" },
      ],
    },
  ],
});

// Nested field filtering with relations
const query3 = QueryBuilder.create().filter({
  operator: LogicalOperator.OR,
  conditions: [
    { field: "base.email", operator: ConditionOperator.ILIKE, value: "%search%" },
    { field: "base.username", operator: ConditionOperator.ILIKE, value: "%search%" },
  ],
});

// Deep nested relations with multiple levels
const query4 = QueryBuilder.create()
  .where("user.profile.email", ConditionOperator.EQ, "user@example.com")
  .where("user.profile.age", ConditionOperator.GTE, 18);

Sorting

Add one or more sort orders to your query.

import { QueryBuilder, SortDirection } from "fluent-query-builder";

// Single sort
const query1 = QueryBuilder.create().sortBy("createdAt", SortDirection.DESC);

// Multiple sorts
const query2 = QueryBuilder.create()
  .sortBy("name", SortDirection.ASC)
  .sortBy("createdAt", SortDirection.DESC);

// Nested field sorting with dot notation for relations
const query3 = QueryBuilder.create()
  .sortBy("profile.name", SortDirection.ASC)
  .sortBy("profile.createdAt", SortDirection.DESC);

// Deep nested relations with multiple levels
const query4 = QueryBuilder.create().sortBy("user.profile.email", SortDirection.ASC);

Pagination

Control the number of results and which page to return.

import { QueryBuilder } from "fluent-query-builder";

// Page 1, 10 items per page (default)
const query1 = QueryBuilder.create();

// Page 2, 20 items per page
const query2 = QueryBuilder.create().paginate(2, 20);

Including Relations

Include related data in your query results.

import { QueryBuilder } from "fluent-query-builder";

const query = QueryBuilder.create().include("posts").include("profile").include("comments");

Complete Example with Prisma

import { QueryBuilder, ConditionOperator, SortDirection } from "fluent-query-builder";
import { PrismaTranslator } from "fluent-query-builder";
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function getActiveUsers() {
  // Build the query
  const query = QueryBuilder.create()
    .where("status", ConditionOperator.EQ, "active")
    .where("age", ConditionOperator.GTE, 18)
    .sortBy("createdAt", SortDirection.DESC)
    .paginate(1, 10)
    .include("posts")
    .include("profile");

  // Translate to Prisma format
  const prismaQuery = PrismaTranslator.translate(query);

  // Execute the query
  return await prisma.user.findMany(prismaQuery);
}

API Reference

QueryBuilder

The main class for building queries. All methods return a new QueryBuilder instance.

`QueryBuilder.create(): QueryBuilder`

Creates a new QueryBuilder instance with default pagination (page: 1, limit: 10).

const query = QueryBuilder.create();

`where(field: string, operator: ConditionOperator, value: unknown): QueryBuilder`

Adds a simple filter condition. Multiple where() calls are combined with AND. Supports nested fields using dot notation (e.g., "profile.email", "user.profile.name").

query.where("email", ConditionOperator.EQ, "user@example.com");
query.where("profile.email", ConditionOperator.EQ, "user@example.com");

`filter(filter: Filter | FilterGroup): QueryBuilder`

Adds a filter or filter group for complex nested conditions with explicit logical operators.

query.filter({
  operator: LogicalOperator.AND,
  conditions: [
    { field: "status", operator: ConditionOperator.EQ, value: "active" },
    { field: "age", operator: ConditionOperator.GTE, value: 18 },
  ],
});

`sortBy(field: string, direction: SortDirection): QueryBuilder`

Adds a sort order. Can be called multiple times for multi-field sorting. Supports nested fields using dot notation (e.g., "profile.name", "user.profile.email").

query.sortBy("name", SortDirection.ASC);
query.sortBy("profile.name", SortDirection.ASC);

`include(include: Include): QueryBuilder`

Adds a relation to include. Can be called multiple times.

query.include("posts");

`paginate(page: number, limit: number): QueryBuilder`

Sets pagination parameters. Throws an error if page < 1 or limit is not between 1 and 1000.

query.paginate(2, 20);

Getters

getFilters(): (Filter | FilterGroup)[] - Returns all filters
getSorts(): Sort[] - Returns all sort orders
getIncludes(): Include[] - Returns all includes
getPagination(): Pagination - Returns pagination settings
getOffset(): number - Returns calculated offset for pagination
getLimit(): number - Returns the limit value

Condition Operators

The ConditionOperator enum provides the following operators:

EQ - Equals
NE - Not equals
GT - Greater than
GTE - Greater than or equal
LT - Less than
LTE - Less than or equal
IN - In array
NOT_IN - Not in array
LIKE - Pattern match (case-sensitive)
ILIKE - Pattern match (case-insensitive)
BETWEEN - Between two values (array of 2 elements)
IS_NULL - Is null
IS_NOT_NULL - Is not null

Logical Operators

The LogicalOperator enum provides:

AND - Logical AND
OR - Logical OR
NOT - Logical NOT

Sort Direction

The SortDirection enum provides:

ASC - Ascending
DESC - Descending

Types

Filter - Single filter condition
FilterGroup - Group of filters with logical operator
Sort - Sort configuration
Include - Relation name (string)
Pagination - Pagination configuration
PaginatedResult<T> - Paginated result with metadata

PrismaTranslator

Translates QueryBuilder instances to Prisma query clauses.

`translate(query: QueryBuilder): PrismaQueryClauses`

Translates a complete query builder to Prisma query clauses.

const prismaQuery = PrismaTranslator.translate(query);
// Returns: { where, orderBy, include, skip, take }

Individual Translators

translateWhere(filters: (Filter | FilterGroup)[]): GenericPrismaWhere
translateOrderBy(sorts: Sort[]): GenericPrismaOrderBy
translateInclude(includes: Include[]): GenericPrismaInclude

Supported Database Providers

Prisma

Full support for Prisma ORM. Use PrismaTranslator to convert queries to Prisma format.

import { QueryBuilder, ConditionOperator } from "fluent-query-builder";
import { PrismaTranslator } from "fluent-query-builder";

const query = QueryBuilder.create().where("status", ConditionOperator.EQ, "active").paginate(1, 10);

const prismaQuery = PrismaTranslator.translate(query);
const results = await prisma.user.findMany(prismaQuery);

Coming Soon

TypeORM
Mongoose

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.