SQL from an application

Never build SQL out of strings

The value ended the string and became part of the query. Everything after -- is a comment, so the quote never had to balance. The same trick with ; drop table or union select password from users is how databases leak.

// the query
const sql = "select * from orders where status = '" + status + "'";

// what somebody types
status = "x' or 1 = 1 --";

// what the database receives
select * from orders where status = 'x' or 1 = 1 --'

Placeholders are not string interpolation

The driver sends the SQL and the values separately. The database parses the query once, with a hole in it, and then puts the value in the hole — as a value, whatever characters it contains. x' or 1 = 1 -- is then simply a status that does not exist, and the query returns nothing.

This is also why a placeholder cannot be a table or column name: those are part of the query, not values. If a column name really must be dynamic, check it against a list you wrote — never against user input.

// node-postgres
const { rows } = await db.query('select * from orders where status = $1', [status]);

// better-sqlite3 / mysql2 / sqlite
const rows = db.prepare('select * from orders where status = ?').all(status);

An IN list needs the right number of holes

const holes = ids.map(() => '?').join(', ');
const rows = db.prepare(`select * from orders where id in (${holes})`).all(...ids);

// postgres does it in one placeholder
await db.query('select * from orders where id = any($1)', [ids]);

N+1: the query in a loop

Each query is fast; a hundred of them is a hundred round trips, and on a remote database the latency alone is the page. This is the single most common performance bug in applications that use an ORM, because the loop looks innocent and the SQL is invisible.

const customers = await db.query('select * from customers');           // 1

for (const c of customers.rows) {
  c.orders = await db.query('select * from orders where customer_id = $1', [c.id]);   // N
}

const customers = await db.query('select * from customers');
const ids       = customers.rows.map((c) => c.id);

const orders = await db.query('select * from orders where customer_id = any($1)', [ids]);

const byCustomer = new Map();
for (const o of orders.rows) {
  if (!byCustomer.has(o.customer_id)) byCustomer.set(o.customer_id, []);
  byCustomer.get(o.customer_id).push(o);
}

Connections are a pool, not a variable

Opening a connection costs milliseconds and a server-side process; a pool keeps a few open and hands them out. A transaction must run on one client — pool.query twice may use two different connections, and your begin will be on the wrong one.

The credentials come from the environment, never from the source file. That is not only about secrets in git: it is how the same build runs against staging and production.

const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

const client = await pool.connect();
try {
  await client.query('begin');
  await client.query('update accounts set balance = balance - $1 where id = $2', [500, 1]);
  await client.query('update accounts set balance = balance + $1 where id = $2', [500, 2]);
  await client.query('commit');
} catch (err) {
  await client.query('rollback');
  throw err;
} finally {
  client.release();                 // the finally is the whole point
}

Migrations

The schema is code. Every change is a numbered file that is applied once, in order, recorded in a table, and never edited afterwards — if it shipped, the fix is a new migration. That is the whole discipline, and every tool (Prisma Migrate, Knex, Flyway, Alembic, Rails) is a variation on it.

Expand and contract is how a change goes out without downtime: add the new column, deploy code that writes both, backfill, deploy code that reads the new one, then drop the old one in a later release. Four boring steps instead of one exciting evening.