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()


  1. MySQLPool.table("users").select("id", "name", "email");
  2. // SELECT `id`, `name`, `email` FROM `users`

RuleExample inputGenerated 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

where()


Each call adds one condition; multiple conditions are joined with AND, and values always go through ? placeholders.
FormExampleGenerated SQL
Two arguments (equality)where("status", "active")`status` = 'active'
Three arguments (comparison)where("age", ">", 18)`age` > 18
LIKEwhere("name", "LIKE", "Jo")`name` LIKE '%Jo%' (wrapped in % automatically)
INwhere("id", "IN", [1, 2])`id` IN ('1', '2') (elements converted to strings)
Third argument null or undefinedwhere("deleted_at", "IS", null)`deleted_at` = 'IS' (handled as the two-argument form; write IS NULL with Raw SQL)

  1. const rows = await MySQLPool
  2. .table("users")
  3. .where("status", "active")
  4. .where("age", ">=", 18)
  5. .where("name", "LIKE", "John")
  6. .where("role", "IN", ["admin", "editor"])
  7. .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.

中文