AxiosLeo/node-orm-mysql / skills
orm-mysql-usage
VerifiedBuild MySQL queries, perform CRUD operations, and manage transactions using @axiosleo/orm-mysql. Use when writing database queries, building where conditions, inserting/updating/deleting rows, managing transactions, or working with the ORM query builder in this project.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 4.3 KB |
| crud-operations.md | markdown | 5.6 KB |
| pagination.md | markdown | 11.9 KB |
| query-building.md | markdown | 3.1 KB |
| transactions.md | markdown | 4.4 KB |
| where-conditions.md | markdown | 5.1 KB |
pagination.md
View on GitHub# Pagination
Best practices for building paginated list endpoints with `@axiosleo/orm-mysql`.
## Critical Rule
**For paginated queries, always reuse the SAME query builder for both `count()` and `select()`.**
NEVER construct a separate `countBuilder` and re-apply the same `where` conditions.
The `Query` instance returned by `db.table(...)` is a mutable object: each `where()`, `whereIn()`, `leftJoin()`, etc. mutates `this.options` and returns `this`. Calling `count()` on a builder does NOT consume or invalidate it -- you can chain `select()` afterwards on the very same instance.
## Anti-Pattern: Duplicating Conditions for COUNT
The following pattern is wrong. It is verbose, fragile, and a maintenance hazard: every time a filter is added or changed, both builders must be updated, and forgetting one of them silently desynchronizes `total` from the actual result set.
```javascript
// BAD -- do not write code like this
let queryBuilder = this.companyDB
.table(this.plansTable, 'pp')
.attr('pp.plan_no', 'pp.title', 'pp.plan_type', 'pp.status', 'pp.created_at')
.attr('u.name as created_by_name')
.leftJoin('users', 'pp.created_by=u.id', { alias: 'u' });
if (query.plan_no) {
queryBuilder = queryBuilder.where('pp.plan_no', 'like', `%${query.plan_no}%`);
}
if (query.status) {
queryBuilder = queryBuilder.where('pp.status', query.status);
}
if (query.plan_type) {
queryBuilder = queryBuilder.where('pp.plan_type', query.plan_type);
}
queryBuilder = queryBuilder.where('pp.disabled', 0);
const page = query.page || 1;
const pageSize = query.page_size || 10;
const offset = (page - 1) * pageSize;
// BAD: a second builder that re-declares the same table and re-applies every where
const countBuilder = this.companyDB
.table(this.plansTable, 'pp')
.where('pp.disabled', 0);
if (query.plan_no) {
countBuilder.where('pp.plan_no', 'like', `%${query.plan_no}%`);
}
if (query.status) {
countBuilder.where('pp.status', query.status);
}
if (query.plan_type) {
countBuilder.where('pp.plan_type', query.plan_type);
}
const total = await countBuilder.count();
const plans = await queryBuilder
.orderBy('pp.created_at', 'desc')
.limit(pageSize)
.offset(offset)
.select();
```
## Recommended Pattern: Reuse the Same Query Builder
Build the filters once on a single `queryBuilder`, then call `count()` followed by the chained `orderBy/limit/offset/select()` on the same instance.
```javascript
// GOOD
let queryBuilder = this.companyDB
.table(this.plansTable, 'pp')
.attr('pp.plan_no', 'pp.title', 'pp.plan_type', 'pp.status', 'pp.created_at')
.attr('u.name as created_by_name')
.leftJoin('users', 'pp.created_by=u.id', { alias: 'u' });
if (query.plan_no) {
queryBuilder = queryBuilder.where('pp.plan_no', 'like', `%${query.plan_no}%`);
}
if (query.status) {
queryBuilder = queryBuilder.where('pp.status', query.status);
}
if (query.plan_type) {
queryBuilder = queryBuilder.where('pp.plan_type', query.plan_type);
}
queryBuilder = queryBuilder.where('pp.disabled', 0);
const page = query.page || 1;
const pageSize = query.page_size || 10;
const offset = (page - 1) * pageSize;
const total = await queryBuilder.count();
const plans = await queryBuilder
.orderBy('pp.created_at', 'desc')
.limit(pageSize)
.offset(offset)
.select();
```
Note how the filter block (`if (query.xxx) { ... }`) appears exactly once. There is no second copy to keep in sync.
## Why This Is Safe
Three implementation details from this ORM make the single-builder pattern correct:
1. **`Query.options` is mutable, methods return `this`.** Every chain call (`where`, `whereIn`, `leftJoin`, `attr`, `orderBy`, `limit`, `offset`, ...) mutates `this.options` on the same instance. There is no copy-on-write.
2. **`count()` ignores `attrs`, `limit`, `offset`, and `orderBy`.** The COUNT SQL is built by `_countOperator` in `src/builder.js`, which only consumes `tables`, `joins`, `conditions`, `groupField`, and `having`. It emits `SELECT COUNT(*) AS count FROM ...` and never reads the `attr()` list, the limit/offset, or the order-by list. So having those configured on the builder does not affect the count.
3. **`select()` overwrites `operator`.** `count()` sets `this.options.operator = 'count'`. `select()` then sets `this.options.operator = 'select'`, restoring the correct execution path. The two calls can safely happen on the same instance, in either order.
## Recommended Order
Call `count()` BEFORE chaining `orderBy`/`limit`/`offset`. Even though `count()` would ignore them anyway, this ordering keeps the read intent obvious:
```javascript
// 1. assemble all where / join / attr
// 2. take the total
const total = await queryBuilder.count();
// 3. then add purely "presentation" concerns and fetch the page
const list = await queryBuilder
.orderBy('pp.created_at', 'desc')
.limit(pageSize)
.offset(offset)
.select();
```
## Complete Pagination Template
A drop-in template for a typical "list with filters" endpoint:
```javascript
async function listPlans(query) {
let q = db.table('procurement_plans', 'pp')
.attr('pp.plan_no', 'pp.title', 'pp.plan_type', 'pp.status', 'pp.created_at')
.attr('u.name as created_by_name')
.leftJoin('users', 'pp.created_by=u.id', { alias: 'u' })
.where('pp.disabled', 0);
if (query.plan_no) {
q = q.where('pp.plan_no', 'like', `%${query.plan_no}%`);
}
if (query.status) {
q = q.where('pp.status', query.status);
}
if (query.plan_type) {
q = q.where('pp.plan_type', query.plan_type);
}
const page = Number(query.page) || 1;
const pageSize = Number(query.page_size) || 10;
const offset = (page - 1) * pageSize;
const total = await q.count();
const list = await q
.orderBy('pp.created_at', 'desc')
.limit(pageSize)
.offset(offset)
.select();
return { list, total, page, page_size: pageSize };
}
```
## Multi-Keyword Fuzzy Search
A common pagination filter is "search by keyword across several columns", where the user may type multiple space-separated keywords (e.g. `"foo bar"`) and expect rows matching ANY keyword in ANY of the searchable columns.
This pattern combines `whereCondition()` (for the per-keyword `column1 LIKE ? OR column2 LIKE ?` group) with logical operators between groups. Two foot-guns make it easy to write SQL that compiles but returns wrong results:
1. **Consecutive `whereCondition()` calls join with `AND` by default.** No operator is auto-inserted "between" groups except the implicit `AND`. So writing one `whereCondition()` per keyword without anything between them means every keyword must match -- not what users expect from search.
2. **Inserting `whereOr()` at the top level introduces an operator-precedence bug.** SQL evaluates `AND` before `OR`, so `WHERE other_filters AND (k1 group) OR (k2 group)` lets the second keyword match rows that ignore `other_filters` entirely.
### Anti-Pattern A: All keywords required (silent AND)
```javascript
// BAD -- generates: ... AND (plan_no LIKE %k1% OR title LIKE %k1%) AND (plan_no LIKE %k2% OR title LIKE %k2%)
// User searches "foo bar" but no row contains both "foo" and "bar" anywhere -- empty result.
if (query.keyword && query.keyword.trim()) {
const keywords = query.keyword.trim().split(/\s+/).filter(Boolean);
for (const keyword of keywords) {
const keywordCondition = new QueryCondition();
keywordCondition
.where('pp.plan_no', 'like', `%${keyword}%`)
.whereOr()
.where('pp.title', 'like', `%${keyword}%`);
queryBuilder = queryBuilder.whereCondition(keywordCondition);
}
}
```
### Anti-Pattern B: Top-level `whereOr()` between groups (precedence bug)
```javascript
// BAD -- generates: ... AND (k1 group) OR (k2 group)
// AND binds tighter than OR, so disabled=0 / status / plan_type only constrain the first keyword group.
// The second keyword group OR's against everything else, returning soft-deleted or wrong-tenant rows.
keywords.forEach((keyword, index) => {
if (index !== 0) {
queryBuilder.whereOr();
}
const keywordCondition = new QueryCondition();
keywordCondition
.where('pp.plan_no', 'like', `%${keyword}%`)
.whereOr()
.where('pp.title', 'like', `%${keyword}%`);
queryBuilder = queryBuilder.whereCondition(keywordCondition);
});
```
### Recommended: Build a single `QueryCondition` and attach it once
Declare **one** `QueryCondition` outside the loop, push every keyword's `OR`-clauses into it, and attach the whole thing to the main builder with **one** `whereCondition()` call after the loop. All keyword `LIKE`s stay inside one parenthesized group, joined to the surrounding `AND` filters as a single safe sub-expression.
The diff from Anti-Pattern B is small and worth memorizing:
1. Move `const keywordCondition = new QueryCondition()` **out** of the loop.
2. Inside the loop, call `.whereOr()` on `keywordCondition` (the inner condition), **not** on `queryBuilder`.
3. Call `queryBuilder.whereCondition(keywordCondition)` **once**, after the loop, not on every iteration.
```javascript
// GOOD -- generates: ... AND (plan_no LIKE %k1% OR title LIKE %k1% OR plan_no LIKE %k2% OR title LIKE %k2%)
const { QueryCondition } = require('@axiosleo/orm-mysql');
if (query.keyword && query.keyword.trim()) {
const keywords = query.keyword.trim().split(/\s+/).filter(Boolean);
const keywordCondition = new QueryCondition();
keywords.forEach((keyword, index) => {
if (index !== 0) {
keywordCondition.whereOr();
}
keywordCondition
.where('pp.plan_no', 'like', `%${keyword}%`)
.whereOr()
.where('pp.title', 'like', `%${keyword}%`);
});
queryBuilder = queryBuilder.whereCondition(keywordCondition);
}
```
### Rules of Thumb
- One `whereCondition()` call -> one parenthesized group joined to the surrounding `WHERE` with `AND`. Safe and unambiguous.
- For "any-of-N" search filters, accumulate all `OR` clauses into **one** `QueryCondition` declared outside the loop, then attach it with **one** `whereCondition()` after the loop. Never call `queryBuilder.whereOr()` at the top level between `whereCondition()` calls -- that leaks the `OR` past your other `AND` filters because SQL evaluates `AND` before `OR`.
- The same rule applies to `count()` reuse: this composite condition is just data on `options.conditions`, so the same builder still produces a correct `COUNT(*)` with the keyword filter intact.
## Edge Case: GROUP BY
`count()` includes `GROUP BY` in the generated SQL, which means with grouping it returns one row per group rather than the total number of groups. In that case the same builder cannot be reused as-is for "how many groups are there".
Two valid approaches:
### Approach A: count via subquery
Wrap the grouped query as a subquery and count its rows. Build the inner query without `limit/offset/orderBy`, then count it:
```javascript
const { Query } = require('@axiosleo/orm-mysql');
const inner = new Query('select');
inner.table('orders', 'o')
.attr('o.user_id')
.where('o.status', 'paid')
.groupBy('o.user_id');
const total = await db.table(inner, 'sub').count();
const rows = await db.table('orders', 'o')
.attr('o.user_id', 'SUM(o.total) AS total')
.where('o.status', 'paid')
.groupBy('o.user_id')
.orderBy('total', 'desc')
.limit(pageSize)
.offset(offset)
.select();
```
### Approach B: dedicated count builder (only when grouping forces it)
If you genuinely need two builders because of grouping, factor the shared filters into a helper to keep them in sync:
```javascript
function applyFilters(q, query) {
q.where('o.status', 'paid');
if (query.user_id) q.where('o.user_id', query.user_id);
return q;
}
const groupedQ = applyFilters(
db.table('orders', 'o').attr('o.user_id', 'SUM(o.total) AS total').groupBy('o.user_id'),
query
);
const countInner = applyFilters(
new Query('select').table('orders', 'o').attr('o.user_id').groupBy('o.user_id'),
query
);
const total = await db.table(countInner, 'sub').count();
const list = await groupedQ.orderBy('total', 'desc').limit(pageSize).offset(offset).select();
```
For all non-grouped paginated lists, stick to the single-builder pattern at the top of this file.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |