Home GitHub

Select and Where

Last updated

This page shows how to choose returned columns with select(), filter rows with where(), and the SQL each one produces.

select()

MySQLPool.table("users").select("id", "name", "email");
// SELECT `id`, `name`, `email` FROM `users`
Rule Example input Generated SQL
Not called, or called with no arguments — *
Plain columns are backtick-quoted "name" `name`
Columns containing ., (, or ) pass through as-is "users.email", "COUNT(*) AS c" users.email, COUNT(*) AS c
An alias without those characters is quoted as a whole "name AS n" `name AS n` (invalid column)

For an alias, add the table prefix ("users.name AS n") so it passes through unchanged.

where()

Each call adds one condition; multiple conditions are joined with AND, and values always go through ? placeholders.

Form Example Generated SQL
Two arguments (equality) where("status", "active") `status` = 'active'
Three arguments (comparison) where("age", ">", 18) `age` > 18
LIKE where("name", "LIKE", "Jo") `name` LIKE '%Jo%' (wrapped in % automatically)
IN where("id", "IN", [1, 2]) `id` IN ('1', '2') (elements converted to strings) 
const rows = await MySQLPool
  .table("users")
  .where("status", "active")
  .where("age", ">=", 18)
  .where("name", "LIKE", "John")
  .where("role", "IN", ["admin", "editor"])
  .get();

Column Name Rules

A column name without ( and without . is backtick-quoted; otherwise it passes through, so users.id and DATE(created_at) both work.

Forms to Watch

Form Actual behavior
where("name", "like", "Jo") Only uppercase "LIKE" adds %; lowercase compares the raw value
where("id", "IN", []) Produces IN (), which MySQL rejects as a syntax error
where("deleted_at", null) Produces `deleted_at` = NULL, which never matches
where("deleted_at", "=", null) With a null/undefined third argument the operator becomes the value: `deleted_at` = '='
where("a", "IS", null) Same as above: `a` = 'IS'

IS NULL/IS NOT NULL and OR conditions cannot be expressed with where(); use Raw SQL. The operator is written into SQL verbatim, so pass only fixed strings from your code.

中文