µSQL

An easy-to-use super tiny flexible and 0-dependency SQL query builder with Knex compatible API! Works both in the browser and as a Node.js package.

Installation

yarn

yarn add usql

npm

npm install usql

Quick Start

import USql from "usql";

const sql = new USql("table").where({ column: "5", column2: "4" });

Then sql.toString() will produce:

SELECT * FROM `table` WHERE `column` = "5" AND `column2` = "4"

---

Security

Parameterised queries (recommended)

The safest way to execute a query is via .toSQL(), which returns a { sql, bindings } object. Pass sql and bindings directly to your database driver so it handles value escaping:

const { sql, bindings } = new USql("users").where("id", userId).toSQL();
// sql      → 'SELECT * FROM `users` WHERE `id` = ?'
// bindings → [userId]

await db.execute(sql, bindings); // driver binds values safely

.toString() is still available for logging and debugging, but it relies on inline string escaping. Prefer .toSQL() for any code that talks to a real database.

DB.raw() — use with care

DB.raw() inserts its argument verbatim into the generated SQL with no escaping or validation. Only pass hard-coded string literals or values you have already fully validated yourself:

// ✅ Safe — hard-coded fragment
DB.raw("COUNT(*) as total");

// ❌ UNSAFE — never pass user input
DB.raw(req.query.column);

Input validation

The following methods throw on invalid input rather than silently producing injectable SQL:

Method	Throws	When
where(col, op, val)	RangeError	op is not in the allowed operator set
join(…, op, …)	RangeError	op is not in the allowed operator set
orderBy(col, dir)	RangeError	dir is not 'ASC' or 'DESC'
limit(n)	TypeError	n cannot be parsed as a finite integer
offset(n)	TypeError	n cannot be parsed as a finite integer
where(null, …)	TypeError	column argument is null or undefined

Allowed comparison operators: =, !=, <>, <, >, <=, >=, LIKE, NOT LIKE, IN, NOT IN, IS, IS NOT.

---

API

Column selection

select — .select([...columns])

new USql("books").select("title", "author", "year");

Result:

SELECT `title`, `author`, `year` FROM `books`

select is optional — when omitted, * is used:

new USql("books");

Result:

SELECT * FROM `books`

---

Where Methods

where — .where(~mixed~)

Object syntax:

new USql("users")
  .where({
    first_name: "Test",
    last_name: "User",
  })
  .select("id");

Result:

SELECT `id` FROM `users` WHERE `first_name` = "Test" AND `last_name` = "User"

Key/value (defaults to =):

new USql("users").where("id", 1).where("info", null);

Result:

SELECT * FROM `users` WHERE `id` = "1" AND `info` IS NULL

Three-argument form (explicit operator):

new USql("users").where("age", ">=", 18);

Result:

SELECT * FROM `users` WHERE `age` >= "18"

Can be chained:

new USql("table")
  .where("id", 1)
  .whereNot("role", "admin")
  .orWhere({ created_at: Date.now() })
  .where({ is_deleted: 0 });

Result:

SELECT * FROM `table` WHERE `id` = "1" AND `role` != "admin" OR `created_at` = "1576417577608" AND `is_deleted` = "0"

---

whereNot — .whereNot(~mixed~)

Object syntax:

new USql("users")
  .whereNot({
    first_name: "Test",
    last_name: "User",
  })
  .select("id");

Result:

SELECT `id` FROM `users` WHERE `first_name` != "Test" AND `last_name` != "User"

Key/value:

new USql("users").whereNot("id", 1).whereNot("name", null);

Result:

SELECT * FROM `users` WHERE `id` != "1" AND `name` IS NOT NULL

---

orWhere — .orWhere(~mixed~)

Object syntax:

new USql("users")
  .orWhere({
    first_name: "Test",
    last_name: "User",
  })
  .select("id");

Result:

SELECT `id` FROM `users` WHERE `first_name` = "Test" OR `last_name` = "User"

Key/value:

new USql("users").orWhere("id", 1).orWhere("name", null);

Result:

SELECT * FROM `users` WHERE `id` = "1" OR `name` IS NULL

---

whereGroup — .whereGroup(callback)

orWhereGroup — .orWhereGroup(callback)

Group conditions in parentheses to control AND/OR precedence. The callback receives a fresh builder; call .where() / .orWhere() on it to populate the group.

new USql("users")
  .where("active", 1)
  .whereGroup((q) => q.where("role", "admin").orWhere("role", "moderator"));

Result:

SELECT * FROM `users` WHERE `active` = "1" AND (`role` = "admin" OR `role` = "moderator")

new USql("users")
  .where("is_deleted", 0)
  .orWhereGroup((q) => q.where("role", "superadmin").where("active", 1));

Result:

SELECT * FROM `users` WHERE `is_deleted` = "0" OR (`role` = "superadmin" AND `active` = "1")

Why does this matter? Without grouping, SQL evaluates AND before OR, so .where('a', 1).orWhere('b', 2).where('c', 3) produces a = 1 OR b = 2 AND c = 3, which is a = 1 OR (b = 2 AND c = 3) — almost never what you want in an authorization check. Use whereGroup / orWhereGroup to make precedence explicit.

---

Join method

join — .join(table, first, [operator], second)

new USql("table")
  .join("contacts", "users.id", "=", "contacts.user_id")
  .select("id");

Result:

SELECT `id` FROM `table` JOIN `contacts` ON `users`.`id` = `contacts`.`user_id`

You can omit the operator (defaults to =):

new USql("table").join("contacts", "users.id", "contacts.user_id").select("id");

Result:

SELECT `id` FROM `table` JOIN `contacts` ON `users`.`id` = `contacts`.`user_id`

---

Ordering

orderBy — .orderBy(column, [direction])

Direction defaults to ASC and is normalised to uppercase.

new USql("table").orderBy("table1.column1_value", "DESC");

Result:

SELECT * FROM `table1` ORDER BY `table1`.`column1_value` DESC

Multiple columns:

new USql("table")
  .orderBy("table1.column1_value", "DESC")
  .orderBy("table1.column2_value", "ASC");

Result:

SELECT * FROM `table1` ORDER BY `table1`.`column1_value` DESC, `table1`.`column2_value` ASC

---

Pagination

limit — .limit(value)

new USql("table").limit(2);

Result:

SELECT * FROM `table` LIMIT 2

offset — .offset(value)

Requires limit() to be set; ignored otherwise.

new USql("table").limit(2).offset(5);

Result:

SELECT * FROM `table` LIMIT 5, 2

---

Aliasing

as — .as(name)

Alias a sub-query. Ignored when the query is used at the top level.

new USql("table").select("column").as("subquery");

Result:

(SELECT `column` FROM `table`) as `subquery`

Full sub-query example:

const subquery = new USql("groups")
  .select("groups.name")
  .where("users.group_id", USql.raw("`groups`.`id`"))
  .as("group_name");

const sql = new USql("users").select("users.*", subquery);

Result:

SELECT `users`.*, (SELECT `groups`.`name` FROM `groups` WHERE `users`.`group_id` = `groups`.`id`) as `group_name` FROM `users`

---

Cloning

clone — .clone()

Returns a deep copy of the builder. Use this when you want to reuse a base query across multiple code paths without risk of shared-state mutation:

const base = new USql("users").where("active", 1);

const admins = base.clone().where("role", "admin").limit(10);
const mods = base.clone().where("role", "moderator");

// base is unchanged

---

Parameterised output

toSQL — .toSQL()

Returns { sql, bindings } with ? placeholders instead of inline values. Pass both to your database driver for safe parameterised execution.

const { sql, bindings } = new USql("users")
  .where("email", userEmail)
  .where("active", 1)
  .toSQL();

// sql      → 'SELECT * FROM `users` WHERE `email` = ? AND `active` = ?'
// bindings → [userEmail, 1]

await connection.execute(sql, bindings);

---

Raw queries

raw — USql.raw(statement)

Inserts a raw SQL fragment verbatim. See the Security section for important warnings before use.

new USql("users").select(USql.raw("count(*) as item_number"));

Result:

SELECT count(*) as item_number FROM `users`

raw is supported in select, where, join, and orderBy.