query-module

Installation

npm i @rym-lib/query-module
npm i @rym-lib/query-module-driver-sequelize

Usage

import { QueryDriverSequelize } from '@rym-lib/query-module-driver-sequelize'

import { driver } from '~/my-query-driver'

type Data = {
  id: string
  name: string
  displayName: string
  email: string
  thumbnailUrl: string
}

const userQuery = defineQuery<Data, QueryDriverSequelize>(driver, {
  source: (builder) =>
    builder
      .column('id')
      .column('profile.name', 'name')
      .column('profile.display_name', 'displayName')
      .column('email')
      .column('profile.thumbnail_url', 'thumbnailUrl')
      .from('users', 'user')
      .leftJoin('user_profiles', 'profile', 'user.id = profile.user_id'),
  rules: {
    id: 'user.id',
    name: 'profile.name',
    displayName: 'profile.display_name',
    email: 'user.email',
  },
})

// filter by any column value.
// can operate eq, ne, contains, gt, gte, lt, lte, in
const userList = await userQuery.many({
  filter: {
    displayName: {
      contains: 'abc',
    },
  },
})
console.log(userList.items)

// lookup single row or null
const user = await userQuery.one({
  filter: {
    id: {
      eq: '12345',
    },
  },
})
console.log(user)

Docs

Methods

.many(params = {})

filter by params, and return matching rows array or empty array.

const rows = query.many()

.one(params = {})

finding a record by params, when missing result, return null.

const row = query.one({
  filter: {
    id: {
      eq: 'id_1234',
    },
  },
})

if (row === null) {
  throw new Error('row not found')
}

.count(params = {})

Count rows matching params and return a number. The method is only exposed when the query is created via defineQueryWithCount. Runners returned from the regular defineQuery do not have count() on their type.

import { defineQueryWithCount } from '@rym-lib/query-module'

const userQuery = defineQueryWithCount<Data, QueryDriverPrisma>(driver, {
  source: (builder) => builder.from('users'),
  rules: { status: 'status' },
})

const total = await userQuery.count()
const activeCount = await userQuery.count({
  filter: { status: { eq: 'active' } },
})

count() accepts the same shape as many() for API consistency, but only filter is honored — orderBy, take, and skip are ignored because they do not affect the resulting row count. Middleware preprocess runs (so default filters set there are applied) but postprocess does not, since the return value is a number rather than a QueryResultList.

defineQueryWithCount constrains its driver argument to QueryDriverWithCountInterface, so a driver that doesn't implement executeCount is rejected at compile time.

Finding parameters

The implementation of these parameters is left to the driver.

filter

Filter data by defined property names.

const result = await query.many({
  filter: {},
})

Support operators are

• eq (automatically handles raw SQL expressions)
• ne (automatically handles raw SQL expressions)
• contains
• not_contains
• lte
• lt
• gte
• gt
• in (automatically handles raw SQL expressions)

orderBy

Specify sort order.

const result = await query.many({
  orderBy: ['created_at:desc', 'name:asc'],
})

take

Specify take rows count.

const result = await query.many({
  take: 10,
})

if (rows.length <= 10) {
  // true
}

skip

Specify thr first item to be retrieved

const result = await query.many({
  skip: 10,
})

Drivers

• sequelize

Automatic Raw SQL Expression Support

The query module automatically detects and handles raw SQL expressions in filter conditions. You can use CASE-WHEN statements and other complex SQL expressions with standard operators:

const query = defineQuery<Data, QueryDriverPrisma>(driver, {
  source: (builder) =>
    builder
      .from('users', 'u')
      .leftJoin('user_profiles', 'p', 'u.id = p.user_id')
      .column('u.id')
      .column('u.name')
      .column('u.status')
      .column(
        unescape(`
        CASE
          WHEN u.status = 'active' THEN 'Active User'
          WHEN u.status = 'pending' THEN 'Pending User'
          ELSE 'Inactive User'
        END
        `),
        'status_display',
      )
      .column(
        unescape(`
        CASE
          WHEN p.category = 'premium' AND u.status = 'active' THEN 'gold'
          WHEN p.category = 'premium' THEN 'silver'
          ELSE 'bronze'
        END
        `),
        'user_tier',
      ),
  rules: {
    id: 'u.id',
    name: 'u.name',
    status: 'u.status',
    // Use CASE-WHEN expressions as filter targets
    status_display: unescape(`
      CASE
        WHEN u.status = 'active' THEN 'Active User'
        WHEN u.status = 'pending' THEN 'Pending User'
        ELSE 'Inactive User'
      END
    `),
    user_tier: unescape(`
      CASE
        WHEN p.category = 'premium' AND u.status = 'active' THEN 'gold'
        WHEN p.category = 'premium' THEN 'silver'
        ELSE 'bronze'
      END
    `),
  },
})

// Filter by CASE-WHEN result using standard operators
const activeUsers = await query.many({
  filter: {
    status_display: { eq: 'Active User' }, // Automatically handles raw SQL expression
  },
})

// Filter with multiple values
const premiumUsers = await query.many({
  filter: {
    user_tier: { in: ['gold', 'silver'] }, // Automatically handles raw SQL expression
  },
})

// Combine with regular filters
const activeBasicUsers = await query.many({
  filter: {
    status_display: { eq: 'Active User' }, // Raw SQL expression
    name: { contains: 'John' }, // Regular field filter
  },
})

Automatic Detection

The query module automatically detects raw SQL expressions by checking for SQL keywords such as:

• CASE, WHEN, THEN, ELSE, END
• Function names like CONCAT, COALESCE, SUBSTRING, LENGTH
• Aggregate functions like COUNT, SUM, AVG, MAX, MIN
• String functions like UPPER, LOWER, TRIM
• Operators and parentheses (, ), +, -, *, /

When a raw SQL expression is detected, the field is automatically wrapped in parentheses for safe SQL generation. Regular field names are handled normally without additional wrapping.

Middleware

• pagination