SQL and Postgres for JavaScript Developers

You have a users table and an orders table. Someone asks for the five customers who have spent the most this year. You know JavaScript, so you reach for the tool you trust:

const users = await db.getAllUsers();           // one query
const totals = [];
for (const user of users) {
  const orders = await db.getOrders(user.id);   // one query PER user
  const spent = orders
    .filter(o => o.year === 2026)
    .reduce((sum, o) => sum + o.total, 0);
  totals.push({ user, spent });
}
totals.sort((a, b) => b.spent - a.spent);
const top5 = totals.slice(0, 5);

On your laptop with ten users, fine. In production with 500,000 users it fires 500,001 queries, drags every order row across the network into your process, sums them in JavaScript, and sorts a giant array in memory just to throw away all but five. This is the classic N+1, and no amount of clever JavaScript rescues it.

Here is the same task as one query:

SELECT u.id, u.name, SUM(o.total) AS spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.id, u.name
ORDER BY spent DESC
LIMIT 5;

One round trip. The database does the joining, the summing, the sorting, and the trimming right next to the data, and hands you exactly five rows. With an index on orders.created_at it comes back in single-digit milliseconds. The query wins, and it isn’t close.

If a JOIN makes you freeze, this article is for you. The goal is not to memorise syntax. It’s to rewire how you think about data, so that queries stop feeling like a foreign language and start feeling like the obvious way to ask a question.

You describe the result, not the steps

The loop feels natural and the query feels alien because they are different kinds of instruction.

Your JavaScript is imperative. It lists steps in order, and you are personally on the hook for how the work gets done: loop here, filter there, accumulate into this variable. Get the order wrong and you get the wrong answer.

The SQL is declarative. It describes the shape of the answer and says almost nothing about how to compute it. Between your query and the bytes on disk sits a query planner: a small optimiser that reads your description, looks at which indexes exist and roughly how big each table is, and chooses an execution strategy. Nested loop or hash join? Scan the whole table or jump through an index? You didn’t say, and you don’t want to. That’s the planner’s job, and it re-decides every time the data changes shape.

The second consequence is bigger. SQL is set-based. A table is a set of rows, and every operation takes sets and produces sets. You don’t think “for each row”; you think “all the rows where…”. The loop in your head becomes a filter, a join, a grouping. Once that click happens, most reporting questions collapse into a few lines, and the database runs them faster than any loop you could write, because it was built to churn through sets.

Tables, rows, and a schema

A relational database stores data in tables. A table has named columns, each with a fixed type, and holds a set of rows. That’s the whole model. Its power comes from keeping data split across small, well-shaped tables and joining them back together on demand.

Two tables carry the rest of this article. Here they are as real Postgres:

CREATE TABLE users (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text NOT NULL,
  country text
);

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id),
  total      numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

A few things are load-bearing here:

• PRIMARY KEY marks the column that uniquely identifies a row. GENERATED ALWAYS AS IDENTITY auto-assigns the next number. This is the modern, SQL-standard replacement for the old serial type. Prefer it: it refuses accidental manual inserts into the id column, and the sequence is a proper part of the table definition rather than a hidden side object.
• REFERENCES users(id) is a foreign key. It tells the database that orders.user_id must point at a real user. Try to insert an order for a user that doesn’t exist, or delete a user who still has orders, and Postgres rejects it. That’s a guarantee your application code can stop enforcing by hand.
• NOT NULL forbids missing values in that column. country allows them; more on what “missing” really means shortly.

SELECT, WHERE, ORDER BY, LIMIT

The everyday query is four clauses. You’ll type them thousands of times.

SELECT name, country      -- which columns you want back
FROM users                -- which table to read
WHERE country = 'DE'      -- keep only rows that match
ORDER BY name ASC         -- sort the survivors
LIMIT 20;                 -- return at most 20

• SELECT lists the columns to return. SELECT * returns every column, which is handy at a psql prompt and a mild smell in application code (it ships columns you don’t need and breaks the moment someone reorders the table).
• WHERE is your filter. It keeps rows where the condition is true. Combine conditions with AND and OR, compare with =, <>, <, >=, and match text with LIKE 'A%' (starts with A) or the case-insensitive ILIKE.
• ORDER BY sorts. ASC is the default and can be dropped; DESC reverses it. Sort by more than one column with a comma.
• LIMIT caps the row count. Its partner OFFSET skips rows, which is the naive way to paginate. It gets slow on deep pages, and the keyset alternative is worth learning: see pagination, filtering and sorting.

JOIN: putting two tables back together

Your data is split across tables on purpose. A join stitches related rows back together by matching a column in one table against a column in another.

The most common is the inner join. It returns only rows that have a match on both sides:

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

u and o are aliases, short nicknames so you don’t retype the table name. ON o.user_id = u.id is the matching rule. A user with three orders shows up three times, once per matching order. A user with no orders doesn’t show up at all, and that last part bites people.

When you want to keep every row from the left table even when the right side has no match, you reach for a left join. The missing columns come back as NULL:

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

GROUP BY, aggregates, and why HAVING exists

An aggregate collapses many rows into one value. COUNT, SUM, AVG, MIN, and MAX are the ones you’ll live in. On their own they fold the whole table:

SELECT COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders;

GROUP BY says: don’t fold everything into one number, fold it per group. One output row per distinct value of the grouping column:

SELECT user_id, SUM(total) AS spent
FROM orders
GROUP BY user_id;

That’s the revenue-per-user query from the intro, minus the join. The rule that trips everyone at first: every column in your SELECT must either be in the GROUP BY or wrapped in an aggregate. Postgres can’t put a bare total next to SUM(total), because there are many totals per group and one sum. It’s not being fussy; the request is genuinely ambiguous.

Now, why does HAVING exist when we already have WHERE? Because they run at different times. WHERE filters individual rows before they’re grouped. HAVING filters whole groups after aggregation. If you want “users who spent more than 100 in total”, you can’t say WHERE SUM(total) > 100, because at WHERE time there are no sums yet, only raw rows.

That diagram also explains two other mysteries. You can’t reference a SELECT alias in WHERE (the alias doesn’t exist yet), and ORDER BY can use one (it runs after SELECT). Internalise this order once and a dozen “why won’t this work” errors stop being surprising.

So the full shape, with the group filter in its rightful place:

SELECT user_id, SUM(total) AS spent
FROM orders
WHERE created_at >= '2026-01-01'   -- drop old rows first
GROUP BY user_id
HAVING SUM(total) > 100            -- then drop small spenders
ORDER BY spent DESC;

Here’s a runner with three tiny tables’ worth of data baked in. Pick a query and watch the rows it produces. The operations run in plain JavaScript, no database involved, but the results match exactly what Postgres would return:

<div class="qr">
<div class="tabs">
  <button class="qt on" data-q="inner">INNER JOIN</button>
  <button class="qt" data-q="left">LEFT JOIN</button>
  <button class="qt" data-q="revenue">Revenue by user</button>
  <button class="qt" data-q="filter">total &gt; 40</button>
</div>
<pre class="sql" id="qr-sql"></pre>
<div class="res" id="qr-res"></div>
</div>

.qr { font: 14px system-ui, sans-serif; padding: 14px; color: #171717; }
.tabs { display: flex; flex-wrap: wrap; gap: 8px; margin-bottom: 12px; }
.qt { font: 13px system-ui, sans-serif; padding: 7px 12px; border: 1px solid #d4d4d4; background: #fff; color: #171717; border-radius: 8px; cursor: pointer; }
.qt.on { background: #7c3aed; border-color: #7c3aed; color: #fff; }
pre.sql { margin: 0 0 12px; padding: 12px 14px; background: #f6f6f7; border: 1px solid #ebebeb; border-radius: 8px; font: 13px/1.5 ui-monospace, monospace; color: #171717; white-space: pre-wrap; overflow-x: auto; }
table { border-collapse: collapse; width: 100%; font: 13px ui-monospace, monospace; }
th, td { text-align: left; padding: 7px 12px; border-bottom: 1px solid #ececec; }
th { color: #7c3aed; font-weight: 600; }
.nul { color: #b45309; }

const users = [
{ id: 1, name: 'Ada', country: 'DE' },
{ id: 2, name: 'Lin', country: 'US' },
{ id: 3, name: 'Mo',  country: 'DE' },
];
const orders = [
{ id: 1, user_id: 1, total: 50 },
{ id: 2, user_id: 1, total: 20 },
{ id: 3, user_id: 2, total: 90 },
];
const nameOf = (id) => users.find((u) => u.id === id).name;

const queries = {
inner: {
  sql: 'SELECT u.name, o.total\nFROM users u\nJOIN orders o ON o.user_id = u.id;',
  cols: ['name', 'total'],
  run: () => orders.map((o) => ({ name: nameOf(o.user_id), total: o.total })),
},
left: {
  sql: 'SELECT u.name, o.total\nFROM users u\nLEFT JOIN orders o ON o.user_id = u.id;',
  cols: ['name', 'total'],
  run: () => {
    const rows = [];
    for (const u of users) {
      const os = orders.filter((o) => o.user_id === u.id);
      if (os.length === 0) rows.push({ name: u.name, total: null });
      else for (const o of os) rows.push({ name: u.name, total: o.total });
    }
    return rows;
  },
},
revenue: {
  sql: 'SELECT u.name, SUM(o.total) AS spent\nFROM users u\nJOIN orders o ON o.user_id = u.id\nGROUP BY u.name;',
  cols: ['name', 'spent'],
  run: () => {
    const sums = new Map();
    for (const o of orders) {
      const n = nameOf(o.user_id);
      sums.set(n, (sums.get(n) || 0) + o.total);
    }
    return [...sums].map(([name, spent]) => ({ name, spent }));
  },
},
filter: {
  sql: 'SELECT u.name, o.total\nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE o.total > 40;',
  cols: ['name', 'total'],
  run: () => orders.filter((o) => o.total > 40).map((o) => ({ name: nameOf(o.user_id), total: o.total })),
},
};

const sqlEl = document.getElementById('qr-sql');
const resEl = document.getElementById('qr-res');
const tabs = Array.from(document.querySelectorAll('.qt'));

function buildTable(cols, rows) {
const t = document.createElement('table');
const head = t.createTHead().insertRow();
for (const c of cols) {
  const th = document.createElement('th');
  th.textContent = c;
  head.appendChild(th);
}
const body = t.createTBody();
for (const r of rows) {
  const tr = body.insertRow();
  for (const c of cols) {
    const td = tr.insertCell();
    const v = r[c];
    td.textContent = v === null ? 'NULL' : String(v);
    if (v === null) td.className = 'nul';
  }
}
return t;
}

function show(key) {
const q = queries[key];
sqlEl.textContent = q.sql;
resEl.replaceChildren(buildTable(q.cols, q.run()));
tabs.forEach((b) => b.classList.toggle('on', b.dataset.q === key));
}

tabs.forEach((b) => b.addEventListener('click', () => show(b.dataset.q)));
show('inner');

NULL is not null

In JavaScript, null is a value you can compare. null === null is true, and you check for it all day. SQL’s NULL is a different animal. It doesn’t mean “empty” or “zero”. It means unknown, and unknown values poison comparison.

The rule is three-valued logic. A comparison isn’t just true or false; it can also be NULL, meaning “can’t say”. And any comparison with NULL produces NULL. So NULL = NULL is not true. It’s NULL, because “is one unknown thing equal to another unknown thing?” genuinely has no answer.

The practical fallout:

• To test for a missing value, use IS NULL and IS NOT NULL, never = NULL. WHERE country = NULL returns no rows, always, silently.
• count(*) counts rows; count(country) counts rows where country is not NULL. That difference is often exactly the report you want, and occasionally the bug you’re hunting.
• Aggregates skip NULL. AVG(total) ignores the nulls rather than treating them as zero, which is usually right but worth knowing.

Types worth knowing

Postgres has a rich type system, and picking the right column type prevents whole categories of bug. The ones you’ll reach for constantly:

text for strings. Just use text. Unlike some databases, Postgres stores text and varchar(n) identically with no performance difference; the length limit on varchar is a constraint, not an optimisation. Reach for varchar(n) only when you genuinely want to enforce a maximum length.

numeric for money, never float. This is the same trap you already know from JavaScript, where 0.1 + 0.2 is 0.30000000000000004. Binary floating point (Postgres real and double precision) can’t represent most decimal fractions exactly, so sums drift by a cent and comparisons for equality misfire. Store money as numeric(12,2), which is exact, or as an integer count of the smallest unit (cents). The Postgres manual is blunt about it: use numeric when you need exact results, such as monetary amounts.

price   numeric(12,2)   -- exact: 19.99 is 19.99, forever
balance bigint          -- or store integer cents and divide on display

timestamptz, and store UTC. Here’s the rule that saves you a daylight-savings incident: always use timestamptz (timestamp with time zone), never plain timestamp. Despite the name, timestamptz does not store a time zone. It converts your input to UTC on the way in, stores that, and converts back to the session’s time zone on the way out. Plain timestamp stores a naked wall-clock reading with no offset, so 14:00 could mean anything, which is a bug waiting for October. Store the instant, format to a local zone only when you show it to a human.

-- both inputs land at the same UTC instant, then display in your session zone
INSERT INTO orders (user_id, total, created_at)
VALUES ($1, $2, '2026-07-17 14:00:00+02');

uuid for identifiers you generate outside the database. A uuid is a 128-bit id. If you want the database to mint them, gen_random_uuid() gives you a random v4. Postgres 18 (released September 2025) added uuidv7(), which prefixes a timestamp so the ids sort roughly by creation time. That matters more than it sounds: random v4 ids scatter inserts all over the primary-key index and fragment it, while time-ordered v7 ids append neatly, which is friendlier to the database indexes underneath. On Postgres 18 and up, prefer uuidv7() when you want UUID keys.

jsonb for genuinely variable shapes. jsonb stores a parsed, binary JSON document you can index and query into: data->>'theme' pulls a text field, data @> '{"pro": true}' tests containment, and a GIN index makes those fast. It’s the right tool for a settings blob or a webhook payload whose shape you don’t control. It is the wrong tool for data with a known shape. Cramming your whole model into one jsonb column throws away the types, the constraints, and the foreign keys that make a relational database worth using.

text[] arrays and enum types, sparingly. Postgres columns can hold arrays (tags text[]) and custom enumerated types (CREATE TYPE status AS ENUM ('open', 'closed')). Both are handy and both have sharp edges: a tag array is easy until you want to count usages across rows, where a proper join table wins, and an enum is compact until you need to rename a value, which is a schema migration. Use them when the shape is small and stable.

Never build a query by gluing strings together

You will eventually be tempted to write this. Don’t.

// DANGER: never do this
const q = "SELECT * FROM users WHERE name = '" + name + "'";

If name comes from a user and they type ' OR '1'='1, the string you built is SELECT * FROM users WHERE name = '' OR '1'='1', and the OR '1'='1' is now part of the query. The filter is always true and you’ve handed over the whole table. A more creative input appends a second statement and drops it. This is SQL injection, and it remains one of the most reliable ways to get breached, decades after it was first documented.

The fix is not to escape quotes by hand. It’s to never mix code and data in the first place. Send the query with placeholders and pass the values separately. The driver ships the query text and the parameters to Postgres as distinct things; the query gets parsed and planned before the value is ever looked at, so a value can never change the query’s structure.

In pg, the most widely used Postgres driver for Node, placeholders are $1, $2, and so on, with the values in an array:

// safe: values travel separately from the query text
const { rows } = await pool.query(
  'SELECT * FROM users WHERE name = $1 AND country = $2',
  [name, country],
);

There is no combination of characters name can contain that turns into SQL here. The worst a malicious name achieves is failing to match any row.

Why Postgres is the default in 2026

When you’re starting a new project and someone asks which database, the boring, correct answer is Postgres. Not because it wins every benchmark, but because it is the safest bet across the widest range of futures.

It’s open source under a permissive licence, with no vendor able to pull it out from under you. It’s genuinely reliable: its multi-version concurrency control lets readers and writers work without blocking each other, and it has a long track record of not losing data. It’s flexible in a way that keeps you from switching databases later, because the extension ecosystem is huge. PostGIS makes it a first-class geospatial database. pgvector stores and searches embeddings, which is why so many AI features run on plain Postgres instead of a separate vector store. And jsonb means you can hold semi-structured data next to your relational data rather than bolting on a document database.

The version story, as of mid-2026: Postgres 18 is the current stable release (18.4 is the latest patch), and Postgres 19 is in beta with a final release expected in the autumn. Every major cloud offers managed Postgres, from AWS RDS and Google Cloud SQL to newer serverless-flavoured providers, so you rarely run it yourself. Whether you deploy on traditional servers or edge runtimes, there’s a Postgres-compatible path.

The relational model is not fashionable and it is not new. It’s from 1970. It has outlasted a parade of “SQL is dead” declarations because describing what you want and letting a mature planner figure out how remains an extraordinarily good deal. Learn it once and it pays out for the rest of your career.

The one thing this article deliberately skipped is why a given query is fast or slow, which comes down to indexes and reading the planner’s chosen strategy with EXPLAIN. That’s a big enough topic to stand alone: continue with database indexes.

Summary

• SQL is declarative and set-based. You describe the result; a query planner picks how to compute it. Stop thinking “for each row” and start thinking “all the rows where”.
• One query usually beats a JavaScript loop by a wide margin, because the database joins, filters, aggregates, and sorts next to the data instead of dragging every row across the network.
• SELECT / WHERE / ORDER BY / LIMIT is the everyday query. WHERE filters rows; ORDER BY sorts; LIMIT caps.
• JOIN is inner by default (only matching rows); LEFT JOIN keeps every left row and fills the missing side with NULL.
• GROUP BY with an aggregate folds rows into one row per group. WHERE filters rows before grouping; HAVING filters groups after, which is the whole reason it exists.
• NULL means unknown. Any comparison with it yields NULL, so = NULL never matches; use IS NULL. Watch the NOT IN trap.
• Pick types deliberately: text for strings, numeric (not float) for money, timestamptz storing UTC, uuid (prefer uuidv7() on Postgres 18+), jsonb for truly variable shapes, arrays and enums sparingly.
• Always parameterise. Send query text and values separately ($1 in pg, tagged templates in postgres.js). Never concatenate input into SQL.
• Postgres is the sensible default in 2026: open source, reliable, and extensible enough (PostGIS, pgvector, jsonb) that you rarely outgrow it.