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 |
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) |
Third argument null or undefined | where("deleted_at", "IS", null) | `deleted_at` = 'IS' (handled as the two-argument form; write IS NULL with Raw SQL) |
- 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.