@mixxtor/adonisjs-lucid-extensions

Useful query builder extensions (macros) for AdonisJS Lucid ORM. Adds convenient methods like exists(), pluck(), find(), getCount(), search(), lazy(), chunk() and more to both DatabaseQueryBuilder and ModelQueryBuilder.

Installation

npm install @mixxtor/adonisjs-lucid-extensions

node ace configure @mixxtor/adonisjs-lucid-extensions

This will register the provider in your adonisrc.ts automatically.

Requirements

• AdonisJS v6+ (or v7+)
• Lucid ORM v21+ (or v22+)
• Node.js >= 20.11.0

Usage

Once installed and configured, all macros are available directly on query builders — no extra imports needed.

DatabaseQueryBuilder

exists()

Check if any records match the query. Returns a boolean instead of fetching rows.

const hasUsers = await db.from('users').where('is_active', true).exists()
// true

ModelQueryBuilder

All model macros also work on relation query builders (e.g., hasMany, belongsTo, manyToMany).

whereEqual(fields)

Add WHERE clauses for multiple columns at once. Undefined values are skipped.

const users = await User.query().whereEqual({
  role: 'admin',
  is_active: true,
  department: undefined, // skipped
})

whereUid(uid)

Shorthand WHERE by the model's primary key.

const query = User.query().whereUid(42)
// WHERE "id" = 42

find(uid)

Find a record by its primary key. Returns null if not found.

const user = await User.query().find(42)

findOrFail(uid)

Find a record by its primary key or throw E_ROW_NOT_FOUND.

const user = await User.query().findOrFail(42)

exists()

Check if any records match the query.

const hasAdmins = await User.query().where('role', 'admin').exists()
// true

except(modelOrId)

Exclude a specific record from the query results. Accepts a model instance, number, or string.

const currentUser = await User.find(1)
const otherUsers = await User.query().except(currentUser)

// Or by ID directly
const otherUsers = await User.query().except(1)

last()

Get the last record ordered by created_at descending.

const latestPost = await Post.query().last()

getCount(column?)

Count matching records. Supports a single column, multiple columns with aliases, or defaults to *.

// Total count
const total = await User.query().getCount()
// 150

// Count specific column
const count = await User.query().getCount('email')
// 148

// Multiple counts with aliases
const counts = await User.query().getCount({
  activeCount: 'is_active',
  verifiedCount: 'email_verified_at',
})
// { activeCount: 120, verifiedCount: 95 }

pluck(column, key?)

Extract column values from query results. Supports three call signatures:

// 1. Array of values
const names = await User.query().pluck('name')
// ['John', 'Jane', 'Bob']

// 2. Object keyed by another column
const nameById = await User.query().pluck('name', 'id')
// { 1: 'John', 2: 'Jane', 3: 'Bob' }

// 3. Transform each value with a function
const emails = await User.query().pluck('name', (name) => name.toLowerCase())
// ['john', 'jane', 'bob']

updateOrFail(data)

Update records or throw E_ROW_NOT_FOUND if no rows are affected.

await User.query().where('id', 42).updateOrFail({ name: 'New Name' })

deleteOrFail()

Delete records or throw E_ROW_NOT_FOUND if no rows are affected.

await User.query().where('id', 42).deleteOrFail()

paginateUsing(request)

Paginate using page and limit from the HTTP request query string.

// GET /users?page=2&limit=20
export default class UsersController {
  async index({ request }: HttpContext) {
    return await User.query().paginateUsing(request)
  }
}

Defaults: page = 1, limit = 15.

search(query, vectorColumn?)

Perform a PostgreSQL full-text search using plainto_tsquery.

const results = await Post.query().search('adonisjs tutorial')
// WHERE "search_vector" @@ plainto_tsquery('adonisjs tutorial')

Custom vector column:

const results = await Post.query().search('adonisjs', 'title_vector')

rank(vectorColumn?)

Add a relevance rank column to search results. Must be chained after search().

const results = await Post.query()
  .search('adonisjs tutorial')
  .rank()
  .select('*')
// Adds: ts_rank_cd("search_vector", plainto_tsquery(...)) AS rank
// Ordered by rank DESC

lazy(size?, column?)

Iterate over large datasets using keyset-based pagination. Returns an AsyncGenerator that yields one record at a time — memory-efficient for processing millions of rows.

for await (const user of User.query().where('is_active', true).lazy(100)) {
  await processUser(user)
}

• size — batch size per query (default: 100)
• column — column to paginate by (default: 'id', must be indexed)

chunk(size?)

Iterate over results in batches. Returns an AsyncGenerator that yields arrays.

for await (const batch of User.query().chunk(500)) {
  console.log(`Processing ${batch.length} users`)
  await Promise.all(batch.map(processBatch))
}

• size — records per chunk (default: 100)

Complete API Reference

| Target | Method | Description | |---|---|---| | DatabaseQueryBuilder | exists() | Check if records exist | | ModelQueryBuilder | whereEqual(fields) | WHERE for multiple fields | | | whereUid(uid) | WHERE by primary key | | | find(uid) | Find by primary key | | | findOrFail(uid) | Find or throw | | | exists() | Check existence | | | except(modelOrId) | Exclude from results | | | last() | Last record by created_at | | | getCount(column?) | Count records | | | pluck(column, key?) | Extract column values | | | updateOrFail(data) | Update or throw | | | deleteOrFail() | Delete or throw | | | paginateUsing(request) | Paginate from request | | | search(query, vectorColumn?) | PostgreSQL full-text search | | | rank(vectorColumn?) | Rank search results | | | lazy(size?, column?) | Keyset-based lazy iteration | | | chunk(size?) | Chunked batch iteration |

License

MIT