Skip to content

AxiosLeo/node-orm-mysql / skills

orm-mysql-usage

Verified

Build 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.

FileKindSize
SKILL.mdskill md4.3 KB
crud-operations.mdmarkdown5.6 KB
pagination.mdmarkdown11.9 KB
query-building.mdmarkdown3.1 KB
transactions.mdmarkdown4.4 KB
where-conditions.mdmarkdown5.1 KB

query-building.md

View on GitHub
# Query Building

The `Query` class provides the fluent API for constructing SQL queries. You get a `Query`-based instance (actually `QueryOperator`) from `QueryHandler.table()`.

```javascript
const query = db.table("users"); // returns QueryOperator (extends Query)
```

## Table Selection

### Single table

```javascript
db.table("users");
db.table("users", "u"); // with alias
```

### Multiple tables

```javascript
db.tables(
  { table: "users", alias: "u" },
  { table: "orders", alias: "o" }
);
```

## Selecting Columns with attr()

```javascript
// Select specific columns
query.attr("id", "name", "email");

// Using sub-query as attribute
const { Query } = require("@axiosleo/orm-mysql");

query.attr(
  "id",
  "name",
  () => {
    const sub = new Query("select");
    sub.table("orders").attr("COUNT(*)").where("orders.user_id", "users.id");
    return sub;
  }
);
```

Calling `attr()` with no arguments clears all previously set attributes.

## Pagination

### limit and offset

```javascript
query.limit(10);       // LIMIT 10
query.offset(20);      // OFFSET 20
```

### page (shorthand)

```javascript
query.page(10);        // LIMIT 10 OFFSET 0
query.page(10, 2);     // LIMIT 10 OFFSET 2
```

## Sorting

```javascript
query.orderBy("created_at", "desc");
query.orderBy("name", "asc");
// Multiple orderBy calls are cumulative
```

## Grouping and Aggregation

```javascript
query.groupBy("status");
query.groupBy("status", "category"); // multiple fields

// HAVING clause (requires groupBy)
query.groupBy("status").having("COUNT(*)", ">", 5);
```

## Setting Data

### set() -- for insert/update data

```javascript
query.set({ name: "Joe", age: 25 });
```

### keys() -- specify columns for INSERT with ON DUPLICATE KEY UPDATE

```javascript
query.keys("uuid").insert({
  uuid: "abc-123",
  name: "Joe",
  age: 25,
});
// If uuid already exists, the insert becomes an update
```

## Joins

### leftJoin / rightJoin / innerJoin (preferred)

```javascript
query.table("users", "u")
  .leftJoin("orders", "u.id = orders.user_id", { alias: "o" })
  .attr("u.id", "u.name", "o.total")
  .select();

query.table("users", "u")
  .rightJoin("orders", "u.id = orders.user_id")
  .select();

query.table("users", "u")
  .innerJoin("roles", "u.role_id = roles.id", { alias: "r" })
  .select();
```

### join() -- generic form

```javascript
query.join({
  table: "orders",
  table_alias: "o",
  self_column: "id",
  foreign_column: "user_id",
  join_type: "left",
});
```

### Sub-query as join table

```javascript
const { Query } = require("@axiosleo/orm-mysql");

const subQuery = new Query("select");
subQuery.table("orders").attr("user_id", "SUM(total) AS order_total").groupBy("user_id");

query.table("users", "u")
  .leftJoin(subQuery, "u.id = sub.user_id", { alias: "sub" })
  .select();
```

## Complete Example

```javascript
const users = await db.table("users", "u")
  .attr("u.id", "u.name", "u.email", "o.total")
  .leftJoin("orders", "u.id = orders.user_id", { alias: "o" })
  .where("u.status", "active")
  .whereBetween("u.created_at", ["2024-01-01", "2024-12-31"])
  .groupBy("u.id")
  .having("COUNT(o.id)", ">", 0)
  .orderBy("u.name", "asc")
  .page(20, 0)
  .select();
```
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: 118kUpdated Claude CodeCodexVerifiedInstall
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: 118kUpdated Claude CodeCodexVerifiedInstall
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: 118kUpdated Claude CodeCodexVerifiedInstall
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: 118kUpdated Claude CodeCodexVerifiedInstall