Queries and Parameters
Write placeholders in the SQL and pass the values as an array, so input can never become part of the command.
const { rows } = await pool.query('SELECT id FROM users WHERE email = $1', [email])A query has two parts: the SQL text, and the values it works on. Keep them apart. In Postgres you put a numbered placeholder, $1 or $2, where each value belongs, and pass the values as an array. The driver sends text and values separately. The database parses the statement first and only then looks at the data, so a value cannot turn into a command.
Joining strings does the opposite. '... WHERE id = ' + input makes the input part of the SQL, and whoever supplied it is now writing your query. That is SQL injection.
How it works
pool.query('SELECT id, email FROM users WHERE id = $1', [id]) resolves to a result object. result.rows is an array of plain objects, one per row, keyed by column name. result.rowCount is how many rows came back, or how many rows an UPDATE or DELETE changed.
The two-argument call is shorthand for a single config object: pool.query({ text: 'SELECT ...', values: [id] }). They are the same call. The object form is what you use when you also want name, which makes it a prepared statement the server remembers, or rowMode: 'array'.
What trips people up
A placeholder stands in for a value only, never for a table or column name. To let a caller choose the sort column, check their string against a list of allowed column names and put the matched one into the text yourself. The mysql2 driver writes its placeholders as ? instead of $1, with the same rule behind it.
Example
When to use it
- Look up a user by an id or email that arrived in a URL, without risking injection.
- Tell 'no such row' from 'update applied' using rowCount so the API returns 404 instead of 200.
- Let an endpoint sort by a column the client picks, from a list you control.
- Read back a generated id with RETURNING instead of querying again.
More examples
Insert a row and read back what the database generated
RETURNING saves a second round trip for the id and defaults the database filled in. rowCount is the only way to tell 'updated one row' from 'that id does not exist', since neither is an error.
import { pool } from './db.js';
async function createUser(email, name) {
const result = await pool.query(
`INSERT INTO users (email, name)
VALUES ($1, $2)
RETURNING id, created_at`,
[email, name],
);
return result.rows[0]; // { id: 42, created_at: 2024-05-01T... }
}
async function deactivate(id) {
const result = await pool.query(
'UPDATE users SET active = false WHERE id = $1',
[id],
);
// rowCount is 0 when no row had that id: a 404, not a 500.
return result.rowCount === 1;
}A caller-chosen sort column, checked against an allow-list
This is the one case where text goes into SQL by concatenation, and it is safe because the text can only ever be one of three constants you wrote. Use it for sort columns, table suffixes, and ASC/DESC.
import { pool } from './db.js';
// Placeholders cannot hold identifiers, so the column name is chosen
// from a fixed list and never taken from the request as text.
const SORTABLE = { email: 'email', created: 'created_at', name: 'name' };
async function listUsers({ sort = 'created', dir = 'desc', limit = 20 }) {
const column = SORTABLE[sort];
if (!column) throw new Error(`cannot sort by ${sort}`);
const direction = dir === 'asc' ? 'ASC' : 'DESC'; // two possibilities only
const { rows } = await pool.query(
`SELECT id, email, name FROM users
ORDER BY ${column} ${direction}
LIMIT $1`,
[Math.min(limit, 100)],
);
return rows;
}The config-object longhand, and a prepared statement
Recognising both forms matters when reading other people's code: the two-argument call is just the object form with the common fields filled in. The named form is worth it for a hot query run thousands of times a minute; rowMode is worth it when exporting many rows and the per-row objects cost real memory.
import { pool } from './db.js';
// Shorthand: text and values as two arguments.
const a = await pool.query('SELECT id FROM users WHERE email = $1', [email]);
// Longhand: exactly the same query as one config object.
const b = await pool.query({
text: 'SELECT id FROM users WHERE email = $1',
values: [email],
});
// The object form is also how you name a statement. The server parses and
// plans it once per connection and reuses that plan for later calls.
const c = await pool.query({
name: 'user-by-email',
text: 'SELECT id FROM users WHERE email = $1',
values: [email],
});
// rowMode skips the per-row object and gives arrays of column values.
const d = await pool.query({
text: 'SELECT id, email FROM users WHERE id = $1',
values: [1],
rowMode: 'array',
}); // d.rows[0] is [1, '[email protected]']
Discussion