AdonisJS Query Builder

A powerful and flexible query builder for AdonisJS applications, inspired by Spatie's Laravel Query Builder. Provides advanced filtering, sorting, pagination, and relationship handling for your API endpoints.

🚀 Features

• 🔍 Advanced Filtering: 14 filter operators (eq, ne, gt, gte, lt, lte, like, ilike, in, not_in, between, not_between, null, not_null)
• 📊 Sorting: Configurable sortable fields with validation
• 🔗 Relationship Includes: Preload relationships with validation
• 📋 Field Selection: Select specific fields with N+1 prevention
• 📄 Pagination: Optimized pagination with performance considerations
• 🎯 Relationship Filtering: Filter by relationship existence
• 🌐 Scopes Support: Apply model scopes dynamically
• 🛡️ Security: SQL injection protection and input validation
• 🔧 Fluent API: Chainable methods for clean code
• 📝 Decorator Pattern: Easy model configuration
• 🎨 TypeScript: Full type safety with no any types

📦 Installation

npm i @thienbd/adonisjs-query-builder

📖 Quick Start

Basic Usage

import { QueryBuilder } from "@thienbd/adonisjs-query-builder";
import User from "#models/user";

export class UsersController {
  async index({ request, response }: HttpContext) {
    const users = await QueryBuilder.fromRequest(
      User.query(),
      { request, response },
      {
        allowedFilters: ["email", "status", "createdAt"],
        allowedSorts: ["email", "status", "createdAt"],
        allowedIncludes: ["posts"],
        allowedFields: ["id", "email", "status", "createdAt"],
      }
    )
      .apply()
      .getWithPagination();

    return response.json({
      success: true,
      data: users.data,
      meta: users.meta,
    });
  }
}

Manual Usage

import { QueryBuilder } from "@thienbd/adonisjs-query-builder";
import User from "#models/user";

// Create QueryBuilder manually with request parameters
const queryBuilder = new QueryBuilder(
  User.query(),
  {
    filter: { status: "active", age: { gte: 18 } },
    sort: "createdAt",
    order: "desc",
    include: "posts,profile",
    fields: "id,name,email",
    page: 1,
    limit: 20,
  },
  {
    allowedFilters: ["status", "age", "name"],
    allowedSorts: ["createdAt", "name"],
    allowedIncludes: ["posts", "profile"],
    allowedFields: ["id", "name", "email", "status"],
  }
);

const result = await queryBuilder.apply().getWithPagination();

Model Decorator

import { QueryBuilderOptions } from "@thienbd/adonisjs-query-builder";
import { BaseModel } from "@adonisjs/lucid/orm";

@QueryBuilderOptions({
  allowedFilters: ["email", "status", "createdAt"],
  allowedSorts: ["email", "status", "createdAt"],
  allowedIncludes: ["posts"],
  allowedFields: ["id", "email", "status", "createdAt"],
  defaultSort: "createdAt",
})
export default class User extends BaseModel {
  @column()
  declare email: string;

  @column()
  declare status: string;

  @column.dateTime()
  declare createdAt: DateTime;

  @hasMany(() => Post)
  declare posts: Post[];
}

🔧 API Examples

Filtering

# Basic filters
GET /users?filter[status]=active
GET /users?filter[email][email protected]

# Advanced operators
GET /users?filter[name][like]=john
GET /users?filter[status][in]=active,pending
GET /users?filter[id][between]=1,100
GET /users?filter[createdAt][gte]=2023-01-01

Sorting

GET /users?sort=name&order=asc
GET /users?sort=createdAt&order=desc

Field Selection

GET /users?fields=id,name,email

Relationship Includes

GET /users?include=posts
GET /users?include=posts,profile

Pagination

GET /users?page=2&limit=10

Scopes

GET /users?scopes[active]=true&scopes[verified]=true

Combined Query

GET /users?filter[status]=active&sort=createdAt&include=posts&fields=id,email,status&page=1&limit=15

📋 Available Filter Operators

| Operator | Description | Example | | ------------- | ----------------------- | --------------------------------------- | | eq | Equals | filter[name]=John | | ne | Not equals | filter[name][ne]=John | | gt | Greater than | filter[age][gt]=18 | | gte | Greater than or equal | filter[age][gte]=18 | | lt | Less than | filter[age][lt]=65 | | lte | Less than or equal | filter[age][lte]=65 | | like | Like (case-sensitive) | filter[name][like]=John | | ilike | Like (case-insensitive) | filter[name][ilike]=john | | in | In array | filter[status][in]=active,pending | | not_in | Not in array | filter[status][not_in]=deleted,banned | | between | Between values | filter[age][between]=18,65 | | not_between | Not between values | filter[age][not_between]=18,65 | | null | Is null | filter[deletedAt][null] | | not_null | Is not null | filter[createdAt][not_null] |

🛡️ Security Features

This package is designed with security in mind:

• Whitelist-based Validation: Only allows explicitly configured filters, sorts, includes, and scopes
• SQL Injection Protection:
  • Sanitizes LIKE/ILIKE operators (escapes %, _, \ characters)
  • Uses parameterized queries via Lucid ORM
  • Validates operators against a strict whitelist
• Input Validation: Rejects invalid operators and field names with descriptive errors
• No Dynamic Field Names: Field names must be whitelisted, preventing column injection

Security Test Coverage

The package includes 17 security-focused tests covering:

• LIKE/ILIKE wildcard injection (%, _)
• SQL comment injection (--)
• UNION injection attempts
• Null byte injection
• Unicode injection
• Operator injection
• Field name injection

⚡ Performance Considerations

• Optimized Pagination: Uses fresh query for count to avoid conflicts
• N+1 Prevention: Automatically skips field selection when includes are present
• Query Optimization: Efficient query building with minimal overhead

📝 Response Format

{
  "success": true,
  "data": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "[email protected]"
    }
  ],
  "meta": {
    "currentPage": 1,
    "perPage": 15,
    "total": 100,
    "lastPage": 7
  }
}

🔨 Advanced Usage

Custom Filters

const config = {
  allowedFilters: ["name", "email", "status"],
  filterMapping: {
    status: (query, value) => {
      if (value === "active") {
        query.where("status", "active").whereNotNull("email_verified_at");
      } else {
        query.where("status", value);
      }
    },
  },
};

Relationship Filters

const config = {
  relationshipFilters: {
    posts: (query, hasPosts) => {
      if (hasPosts) {
        query.has("posts");
      } else {
        query.doesntHave("posts");
      }
    },
  },
};

Model Scopes

export default class User extends BaseModel {
  static active(query) {
    query.where("status", "active");
  }

  static verified(query) {
    query.whereNotNull("email_verified_at");
  }
}

🧪 Testing

npm test

Test Coverage

The package has comprehensive test coverage with 71 tests across multiple categories:

| Category | Tests | Description | | -------------------- | ----- | -------------------------------------- | | Basic | 3 | Instance creation, SQL output, cloning | | Filters | 20 | All 14 operators + error handling | | Sorting | 5 | Asc/desc, defaults, validation | | Includes | 4 | Single/multiple, validation | | Fields | 4 | Selection, filtering, N+1 prevention | | Apply All | 2 | Combined operations, chaining | | Edge Cases | 4 | Empty config/request handling | | SQL Injection | 17 | Security vulnerability tests | | Relationship Filters | 4 | Custom handlers | | Scopes | 4 | Scope application, validation | | Filter Mapping | 3 | Custom filter logic | | Helper Functions | 2 | Config utilities | | ILIKE/Not Between | 2 | Additional operators |

� API Reference

QueryBuilder Methods

| Method | Description | | ---------------------------- | ----------------------------------------------------------------- | | apply() | Apply all operations (filters, sorting, includes, fields, scopes) | | applyFilters() | Apply only filters | | applySorting() | Apply only sorting | | applyIncludes() | Apply only relationship includes | | applyFields() | Apply only field selection | | applyScopes() | Apply only scopes | | applyRelationshipFilters() | Apply only relationship filters | | get() | Execute query and return results | | first() | Execute query and return first result | | paginate(page, limit) | Execute query with pagination | | getWithPagination() | Execute query with pagination from request | | clone() | Clone the QueryBuilder instance | | toSql() | Get the SQL string |

Static Methods

| Method | Description | | ---------------------------------------------- | ------------------------------------- | | QueryBuilder.for(model, request, config) | Create QueryBuilder for a model | | QueryBuilder.fromRequest(query, ctx, config) | Create QueryBuilder from HTTP context |

Helper Functions

| Function | Description | | ---------------------------------------- | --------------------- | | getModelQueryBuilderConfig(model) | Get config from model | | createQueryBuilderModel(model, config) | Add config to model |

�🔧 Development

# Install dependencies
npm install

# Build the package
npm run build

# Run tests
npm test

# Lint code
npm run lint

# Type check
npm run typecheck

# Format code
npm run format

📄 License

This package is open-sourced software licensed under the MIT license.

🤝 Contributing

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

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'feat: add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

📞 Support

If you encounter any issues or have questions, please file an issue.

🔗 Links

• NPM Package
• GitHub Repository
• Issues

🙏 Acknowledgments

• Inspired by Spatie's Laravel Query Builder
• Built for AdonisJS framework