Home GitHub

Raw SQL

Last updated

This page explains how to run hand-written SQL with read(), write(), and query() when the query builder cannot express what you need.

Three Entry Points

Method Pool When the pool is missing
read<T>(sql, params?) Read pool Throws Read pool connection is not available.
write<T>(sql, params?) Write pool Throws Write pool connection is not available.
query<T>(sql, params?, target?) target; when omitted, the target of the most recent table() call Throws read connection is not available. or write connection is not available.

All three run through query(), so they get the same slow query log and automatic connection release.

Parameter Binding

Pass values through ? placeholders and let mysql2 escape them:

import MySQLPool from "@pardnchiu/mysql-pool";
import type { ResultSetHeader, RowDataPacket } from "mysql2/promise";

try {
  const rows = await MySQLPool.read<RowDataPacket[]>(
    "SELECT id, name FROM users WHERE deleted_at IS NULL AND (role = ? OR role = ?)",
    ["admin", "editor"]
  );

  const header = await MySQLPool.write<ResultSetHeader>(
    "UPDATE users SET last_login = NOW() WHERE id = ?",
    [rows[0].id]
  );

  console.log(header.affectedRows);
} catch (err) {
  console.error("raw query failed:", err);
}

The generic T is only a type assertion. The return value is the first element of mysql2's query() result: an array of rows for SELECT and a ResultSetHeader for INSERT/UPDATE/DELETE.

When to Use Raw SQL

Need Reason
IS NULL, IS NOT NULL, OR, BETWEEN where() only produces column operator ? joined with AND
GROUP BY, HAVING, subqueries, UNION The builder has no matching methods
DELETE There is no delete() method
Joins with several conditions in ON The join methods accept a single comparison
Transactions Every query() checks out a fresh pooled connection and releases it immediately; see Known Limitations
中文