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 |
crud-operations.md
View on GitHub# CRUD Operations
The `QueryOperator` class provides all execution methods. You get an instance via `db.table("name")`.
All CRUD methods return a `Promise`. Write operations resolve to `MySQLQueryResult` (OkPacket | ResultSetHeader). If `notExec()` was called, they resolve to a `Builder` object instead.
## Read Operations
### select(...attrs)
Returns an array of rows. Optionally pass column names to override previously set `attr()`.
```javascript
const rows = await db.table("users").select();
const rows = await db.table("users").select("id", "name", "email");
// With TypeScript generics
interface User { id: number; name: string; email: string; }
const users = await db.table("users").select<User>("id", "name", "email");
```
### find()
Returns a single row (the first match). Automatically applies `LIMIT 1`.
```javascript
const user = await db.table("users").where("id", 1).find();
// With TypeScript generics
const user = await db.table("users").where("id", 1).find<User>();
```
### count()
Returns the total number of matching rows as a number.
```javascript
const total = await db.table("users").where("status", "active").count();
// total is a number
```
For paginated list endpoints, do NOT create a separate count builder with duplicated `where` conditions. Reuse the same builder for both `count()` and `select()` -- `count()` ignores `attrs`/`limit`/`offset`/`orderBy`, and `select()` resets the operator. See [pagination.md](pagination.md) for the full pattern.
### explain(operator)
Returns the MySQL EXPLAIN result for the query.
```javascript
const plan = await db.table("users")
.where("status", "active")
.explain("select");
// plan is ExplainResult[]
```
## Write Operations
### insert(row)
Insert a single row.
```javascript
const result = await db.table("users").insert({
name: "Joe",
age: 25,
email: "joe@example.com",
});
// result.insertId -- the auto-increment ID
// result.affectedRows -- number of rows inserted
```
### Insert with ON DUPLICATE KEY UPDATE
Use `keys()` to specify unique columns. If a duplicate is found, the operation becomes an update.
```javascript
const result = await db.table("users").keys("email").insert({
email: "joe@example.com",
name: "Joe Updated",
age: 26,
});
```
### insertAll(rows)
Insert multiple rows. Returns an array of results.
```javascript
const results = await db.table("users").insertAll([
{ name: "Alice", age: 30 },
{ name: "Bob", age: 28 },
{ name: "Charlie", age: 35 },
]);
// results is MySQLQueryResult[]
```
### update(row)
Update rows matching the current where conditions.
```javascript
const result = await db.table("users")
.where("id", 1)
.update({ name: "Joe Updated", age: 26 });
// result.affectedRows -- number of rows updated
// result.changedRows -- number of rows actually changed
```
### delete(id?, index_field_name?)
Delete rows. Can be called with conditions or directly with an ID.
```javascript
// Delete by conditions
const result = await db.table("users").where("status", "banned").delete();
// Delete by primary key (defaults to "id" field)
const result = await db.table("users").delete(1);
// Delete by a custom index field
const result = await db.table("users").delete(1, "user_id");
```
### incrBy(attr, increment?)
Increment a column value atomically. Default increment is 1.
```javascript
// Increment by 1 (default)
await db.table("users").where("id", 1).incrBy("login_count");
// Increment by a specific number
await db.table("products").where("id", 1).incrBy("views", 5);
// Increment by string value
await db.table("users").where("id", 1).incrBy("score", "10");
// Conditional increment with callback
await db.table("users").where("id", 1).incrBy("error_count", (current) => {
return shouldIncrement ? 1 : 0;
});
```
### upsertRow(row, condition)
Insert a row or update it if a matching row exists based on the condition.
```javascript
// Using QueryCondition
const condition = new QueryCondition();
condition.where("email", "joe@example.com");
await db.table("users").upsertRow(
{ email: "joe@example.com", name: "Joe", age: 25 },
condition
);
```
## Dry Run with notExec()
Call `notExec()` before any CRUD method to get the generated SQL without executing it. Returns a `Builder` with `.sql` and `.values`.
```javascript
const builder = await db.table("users")
.where("status", "active")
.orderBy("name", "asc")
.limit(10)
.notExec()
.select("id", "name");
console.log(builder.sql); // The generated SQL string
console.log(builder.values); // The parameter values array
```
This works with all CRUD methods:
```javascript
const insertBuilder = await db.table("users")
.notExec()
.insert({ name: "Joe", age: 25 });
const updateBuilder = await db.table("users")
.where("id", 1)
.notExec()
.update({ age: 26 });
const deleteBuilder = await db.table("users")
.where("id", 1)
.notExec()
.delete();
```
## Complete Example
```javascript
const db = new QueryHandler(pool);
// Create user
const insertResult = await db.table("users").insert({
name: "Joe",
email: "joe@example.com",
age: 25,
});
const userId = insertResult.insertId;
// Read back
const user = await db.table("users").where("id", userId).find();
// Update
await db.table("users").where("id", userId).update({ age: 26 });
// Increment login count
await db.table("users").where("id", userId).incrBy("login_count");
// Count active users
const activeCount = await db.table("users").where("status", "active").count();
// Bulk insert
await db.table("logs").insertAll([
{ user_id: userId, action: "login" },
{ user_id: userId, action: "view_profile" },
]);
// Delete
await db.table("sessions").where("expired_at", "<", new Date()).delete();
```
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 |