Query Builder

Provides an advanced tool for dynamic SQL query construction, designed to facilitate integration into applications that require sophisticated filtering and search capabilities.

Introduction

The @barrerajulio/api-query-builder library offers a robust solution for developers needing to dynamically and efficiently construct SQL filters. This module enables the creation of complex search conditions using simple and expressive syntax, applicable in the where parameter for various operations.

Usage examples:

where=eq(field,value)
where=and(eq(field,value),eq(field1,value1))
where=or(eq(field,value),eq(field1,value1))

Table Of Contents

• Query Builder
  • Introduction
  • Table Of Contents
  • Features
  • Requirements
  • Installation
    • Clone The Repository
    • Navigate To The Project Directory
    • Install Dependencies
  • Usage
    • TypeORM integration
  • Supported Operators
    • And
    • Or
    • Between
    • Eq
    • Ilike
    • In
    • IsNull
    • Like
    • Lt
    • Lte
    • Mt
    • Mte
    • Not
  • Advanced Example
  • Development
  • Contributions
  • License

Features

• Dynamic Construction: Programmatically generates SQL queries, dynamically adapting conditions based on application requirements.
• Multiple Operator Compatibility: Support for a wide range of logical and comparative operators (AND, OR, NOT, etc.).
• Intuitive Syntax: User interface designed to be easy to understand and use, facilitating the creation of complex search conditions.
• Extensibility: Ability to combine and nest operators to build advanced queries, maintaining flexibility in their definition.

Requirements

• Node.js: Recommended compatible versions (>=14.0.0).
• TypeScript: It is preferable to use TypeScript to take advantage of static typing.

Installation

Follow the steps below to install and configure the library in your project:

Clone The Repository

git clone https://github.com/barrerajulio/api-query-builder.git

Navigate To The Project Directory

cd api-query-builder

Install Dependencies

Use npm or yarn to install the project's dependencies:

npm install @barrerajulio/api-query-builder

or

yarn add @barrerajulio/api-query-builder

Usage

To use the library in your project, first import the Query class and use it to build your queries:

import { Query } from '@barrerajulio/api-query-builder';

const query = new Query(qb => qb.eq('field', 'value'));
console.log(`Query: ${query.build()}`);

This example demonstrates how to construct a basic query. You can expand this query using the various supported operators.

TypeORM integration

If you need to materialize the parsed expression into TypeORM constructs, import the optional TypeORM bundle. It lives under a separate entry point so the core package remains frontend friendly:

import { QueryBuilder, FindWhereQueryBuilder } from '@barrerajulio/api-query-builder/typeorm';

const builder = new QueryBuilder();
const brackets = builder.build('and(eq(user.name,Foo),eq(user.status,ACTIVE))', {
    allowedFields: ['user.name', 'user.status'],
});

const findWhere = new FindWhereQueryBuilder();
const conditions = findWhere.build('or(eq(id,1),eq(id,2))', {
    allowedFields: ['id'],
});

To keep the optional dependency tree small, TypeORM is declared as an optional peer dependency. Install it alongside this package in backend projects:

yarn add typeorm @barrerajulio/api-query-builder

Supported Operators

Below are the operators you can use to build queries:

And

The and operator allows you to combine multiple conditions, ensuring that all must be met for the filter to be valid.

Example:

new Query(
    qb => qb.and(
        qb.eq('field1', 'value1'),
        qb.eq('field2', 'value2')
    )
);

Or

The or operator allows any of the combined conditions to be met.

Example:

new Query(
    qb => qb.or(
        qb.mt('field1', 10),
        qb.mte('field2', 20)
    )
);

Between

This operator is useful for comparing ranges, such as dates or numeric values.

Example:

new Query(
    qb => qb.between('field1', 10, 20)
);

Eq

Compares the value of a field to a specific value.

Example:

new Query(
    qb => qb.eq('field1', 'value1')
);

Ilike

Performs a partial comparison, ignoring case sensitivity.

Example:

new Query(
    qb => qb.ilike('field1', '%value1%')
);

In

Checks if a field matches any of the values in a list.

Example:

new Query(
    qb => qb.in('field1', 'value1', 'value2', 'value3')
);

IsNull

Validates that the field is null.

Example:

new Query(
    qb => qb.isNull('field1')
);

Like

Performs a partial comparison, considering case sensitivity.

Example:

new Query(
    qb => qb.like('field1', '%value1%')
);

Lt

Compares if a field is less than a given value.

Example:

new Query(
    qb => qb.lt('field1', 10)
);

Lte

Compares if a field is less than or equal to a given value.

Example:

new Query(
    qb => qb.lte('field1', 10)
);

Mt

Compares if a field is greater than a given value.

Example:

new Query(
    qb => qb.mt('field1', 10)
);

Mte

Compares if a field is greater than or equal to a given value.

Example:

new Query(
    qb => qb.mte('field1', 10)
);

Not

Inverts the result of a given condition.

Example:

new Query(
    qb => qb.not(qb.eq('field1', 'value1'))
);

Advanced Example

The library allows queries to be constructed iteratively, offering great flexibility for complex scenarios.

const { Query } = require('@barrerajulio/api-query-builder');

let query = new Query(qb => 
    qb.and(
        qb.eq('field1', 'value1'),
        qb.or(
            qb.eq('field2', 'value2'),
            qb.and(
                qb.eq('field3', 'value3'),
                qb.eq('field4', 'value4')
            )
        )
    )
);

const isPrivate = true;

if (isPrivate) {
    query = query.and(qb => qb.eq('field5', 'value5'));
}

This example demonstrates how to construct an initial query and then modify it based on external conditions, enabling the creation of adaptive and contextual queries.

Development

If you want to contribute to the development of this project, follow these steps:

1. Fork the repository.
2. Create a new branch (feature/new-functionality).
3. Implement your changes.
4. Submit a pull request for review.

Contributions

Contributions are welcome. Please follow the guidelines below to collaborate effectively:

1. Fork the repository if you are not an internal collaborator.
2. Create a new branch (feature/new-functionality).
3. Implement your changes and make descriptive commits.
4. Submit a pull request detailing the changes and their purposes.

License

This project is under a private license. For more details, see the LICENSE file included in the repository.

All rights reserved