Shopfront guides

PostgreSQL in Shopfront

All guides
Part 1The conceptWhat PostgreSQL is and the ideas you need before reading the code.

Collapsed

PostgreSQL is the relational database that stores Shopfront's products and orders. The API talks to it through node-postgres (the pg package): the low-level Node.js driver that keeps a pool of connections, sends your SQL with its values, and turns result rows into JavaScript objects. Sources: node-postgres, PostgreSQL versioning, server/db.js.

In one paragraph

  • PostgreSQL stores data in tables you query with SQL. It runs as its own server; the API connects to it over the network.
  • node-postgres keeps a pool of open connections, so each query doesn't pay for a new one (Pool).
  • You write the SQL yourself. Values go in separately as $1, $2… (Queries).

The problem it solves

  • Durable, consistent storage: rows survive restarts, and many API requests can read and write at once.
  • node-postgres handles the connection protocol, pooling, and type conversion (json columns become objects, timestamps become Dates).

Compared with

  • Query builders and ORMs such as Knex sit on top of pg and write the SQL for you (Knex). Shopfront uses the driver directly: four hand-written queries.

How Shopfront uses it

  • One shared pool from DATABASE_URL, two tables, four queries, all parameterized (usage map).
  • No schema, migrations, or seeds in the repo, and no tests that touch the database.

Collapsed

The ideas you need before reading Shopfront's database code, then each one in code. node-postgres publishes docs for its current release only; everything here also works on the 8.11 line Shopfront allows (see Versions).

ConceptWhat it meansIn ShopfrontOfficial docs
Connections
PoolA set of reusable connections: up to 10 by default, idle ones closed after 10 seconds. With no connection timeout set, a request for a connection waits as long as it takes.server/db.js:2Pool
pool.queryBorrows a connection, runs one statement, and returns the connection for you. Fine for single statements, never for transactions.All 4 queriesPool
ClientOne connection, checked out with pool.connect() and always returned with client.release(). Needed for transactions.Not usedTransactions
Connection stringpostgres://user:password@host:port/database. In current releases, settings it leaves out come from the standard PG* environment variables, then defaults (localhost:5432).DATABASE_URLConnecting
Queries & results
Parameterized querySQL with $1, $2… placeholders and a separate values array. PostgreSQL never reads the values as SQL, which is what prevents SQL injection.Every queryQueries
Value conversionnull/undefined → NULL, Date → UTC timestamp, arrays → Postgres arrays, other objects → JSON.stringify.JSON.stringify(items) at server/routes/orders.js:6Queries
Resultrows (array of objects keyed by column), plus rowCount, fields, command. An empty SELECT gives rows = [].rows onlyResult
RETURNINGMakes an INSERT or UPDATE send back columns of the rows it changed, such as a generated id, in the same round trip.returning idQueries
Types & errors
Type parsingColumns become JS values: json/jsonb → objects, timestamps → Date, but bigint and numeric → strings (a JS number can't hold every 64-bit value).Column types unknown (no schema)Data types
SQLSTATEThe five-character code on every database error (err.code): 23505 duplicate key, 42P01 missing table, 22P02 bad input. Check it, not the message.Not usedError codes

Short examples adapted from the node-postgres docs and the official Docker image, with placeholder values. Compare each with the Shopfront file named under it.

Create a pool and run a query. Shopfront's server/db.js is the first line; it doesn't add the error listener.

JavaScriptExample (node-postgres docs)
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
pool.on('error', (err) => console.error('idle client error', err));   // otherwise an idle-connection error can crash the process

const { rows } = await pool.query('SELECT now() AS now');
console.log(rows[0].now);   // a Date

Select one row by id, and handle "not found". Compare server/routes/orders.js:10, which returns rows[0] even when it's undefined.

JavaScriptExample (node-postgres docs)
const { rows } = await pool.query('SELECT id, name FROM items WHERE id = $1', [itemId]);
if (rows.length === 0) return null;   // check rows.length, not rowCount
return rows[0];

Insert and get the new id back. Compare createOrder in server/routes/orders.js:6.

JavaScriptExample (node-postgres docs)
const text = 'INSERT INTO items(name, attrs) VALUES($1, $2) RETURNING id';
const values = ['example', { color: 'blue' }];   // a plain object is sent as JSON
const { rows } = await pool.query(text, values);
const newId = rows[0].id;                        // a string if the column is bigint/bigserial

Run a transaction. Not in Shopfront yet: an order plus stock changes would need one.

JavaScriptExample (node-postgres docs)
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();                      // always give the connection back
}

Turn a database error into a response (Express 4). What Shopfront's handlers are missing.

JavaScriptExample (node-postgres + Express 4 docs)
app.post('/items', async (req, res, next) => {
  try {
    const { rows } = await pool.query('INSERT INTO items(name) VALUES($1) RETURNING id', [req.body.name]);
    res.status(201).json(rows[0]);
  } catch (err) {
    if (err.code === '23505') return res.status(409).json({ error: 'duplicate' });   // SQLSTATE: unique violation
    next(err);
  }
});

Start a local database and create a table. Bind to 127.0.0.1 so it isn't reachable from your network.

ShellExample (Docker official image)
docker run --name pg-local -e POSTGRES_PASSWORD=<local-password> -p 127.0.0.1:5432:5432 -d postgres
docker exec -it pg-local psql -U postgres
SQLExample (PostgreSQL tutorial)
CREATE TABLE items (
  id    bigserial PRIMARY KEY,
  name  text NOT NULL UNIQUE,
  attrs jsonb
);
Part 2In ShopfrontWhere Shopfront uses PostgreSQL, one write end to end, and how to change it.

Collapsed

PostgreSQL is Shopfront's only data store, reached from the Express API through one shared pg pool. There are 4 queries in total (git grep -c "db.query" server), two tables, and no schema, migrations, or seeds in the repo. Sources: server/db.js, server/routes/, package.json:10.

Edge & messagingCompute & app codeData storeSynchronous callAsync / event / queued
createOrderorders.js:61Poolserver/db.js2Clientone connection3INSERT … RETURNINGorders table4Resultrows[0].id → 201 { id }5API PROCESS (server/)POSTGRESQLrows

Placing an order: the handler calls db.query with the SQL and a values array, the pool lends it a connection, PostgreSQL runs the insert, and the result's rows come back to the handler. The guided walkthrough follows steps 1–5.

EndpointSQL (values)Where (repo path)
products
GET /api/productsselect * from products, all rowsserver/routes/products.js:3
GET /api/products/:idselect * from products where id=$1 (req.params.id), first rowserver/routes/products.js:4
orders
POST /api/ordersinsert into orders(items) values ($1) returning id (the cart as a JSON string)server/routes/orders.js:6
GET /api/orders/:idselect * from orders where id=$1 (req.params.id), first rowserver/routes/orders.js:10

PieceRoleWhere
In the API
PoolOne new Pool(…), created when server/db.js is first required and shared by every route.server/db.js:2
DATABASE_URLThe connection string the pool is built from. Nothing in the repo sets it: not CI, not the Dockerfile.server/db.js:2
pgThe node-postgres client package, range ^8.11.0, no lockfile (see Versions).package.json:10
In the database
productsHas an id column; other columns aren't visible in the code (select *).no schema in repo
ordersHas id (generated by the database, since the insert doesn't set it) and items.no schema in repo

Collapsed

Follow one write, placing an order with POST /api/orders, from the handler to PostgreSQL and back. The Express walkthrough covers the same request up to the handler; this one picks up there. Sources: server/routes/orders.js, server/db.js.

createOrder passes two things: SQL with a $1 placeholder, and an array of values. The cart is turned into a JSON string first. Concept: parameterized queries.

JavaScriptserver/routes/orders.js:6-7
const { rows } = await db.query('insert into orders(items) values ($1) returning id', [JSON.stringify(req.body.items)]);
res.status(201).json({ id: rows[0].id });

db is the shared pool from server/db.js. pool.query borrows a connection, runs one statement on it, and gives the connection back. Concept: pool.

JavaScriptserver/db.js
const { Pool } = require('pg');
module.exports = new Pool({ connectionString: process.env.DATABASE_URL });

The SQL text and the values travel separately, so the cart's contents can't change the meaning of the SQL. PostgreSQL treats $1 as data, whatever it contains.

PostgreSQL inserts a row into orders and, because of returning id, sends back the id it generated, in the same round trip. Where the flow stops today: the repo doesn't define the orders table, so the column types (is items json, jsonb, or text?) can only be read from a running database.

SQLThe statement PostgreSQL runs
insert into orders(items) values ($1) returning id;
-- $1 = '[{"productId":42,"qty":1}]'

db.query resolves to a result object; Shopfront uses only rows, an array of plain objects keyed by column name. rows[0].id is the new order's id. If the query fails, the promise rejects, and because the handler has no try/catch, that crashes the process in Express 4 (see the Express gotchas).

None of the repo's tests touch the database, and it has no schema, so you need a local PostgreSQL with an orders table you create yourself (recipe). Then:

ShellLocal only
export DATABASE_URL=postgres://postgres:<local-password>@localhost:5432/postgres
npm run dev
curl -X POST http://localhost:4000/api/orders \
  -H 'Content-Type: application/json' -d '{"items": [{"productId": 42, "qty": 1}]}'   # → 201 {"id": 1}
psql "$DATABASE_URL" -c 'select * from orders;'

Collapsed

Shopfront's database code gets the most important thing right: every value is parameterized. The gotchas are about what happens when something goes wrong. Sources: server/db.js, server/routes/.

Conventions here

  • One shared pool, required as db from server/db.js, the single module the node-postgres project-structure guide recommends.
  • Always parameterized: $1 with a values array in all 4 queries, never string concatenation.
  • JSON is stringified on purpose: JSON.stringify(req.body.items) (server/routes/orders.js:6). Passing the array itself would send a Postgres array, not JSON.
  • Ids come back with RETURNING instead of a second query.

Gotchas

  • No pool error listener. If the database drops an idle connection, node-postgres emits an error on the pool; with no pool.on('error') in server/db.js, that can crash the process (Pool).
  • Query errors crash the server too: handlers await db.query with no try/catch, and Express 4 doesn't catch rejected promises (Express gotchas).
  • A missing DATABASE_URL fails quietly. In current node-postgres releases, an undefined connection string falls back to PG* variables and localhost, so a misconfigured server starts fine and only fails on its first query.
  • No schema in the repo. Column types, constraints, and indexes exist only in whatever database you point at. If id is a bigint, it comes back as a string ("42", not 42).
  • No connection timeout. The pool's default is to wait forever for a free connection, so a leak or a stuck database makes requests hang rather than fail.

Collapsed

Database changes you're likely to make in Shopfront. The repo has no schema, so start by creating one locally. Sources: server/db.js, server/routes/.

TaskStepsWhere
Run a local databaseStart PostgreSQL in Docker, create the two tables (below), and set DATABASE_URL before npm run dev.server/db.js
Add a queryCall db.query(sql, values) with $1 placeholders; check rows.length for "not found"; wrap it in try/catch and call next(err).server/routes/
Write two things at onceUse a transaction on one client from db.connect(), and release it in finally (example).server/routes/orders.js
Make failures visibleAdd pool.on('error', …) in server/db.js, and fail fast at startup if DATABASE_URL is missing.server/db.js

These tables are inferred from the queries (id is generated, items receives JSON). They aren't from the repo: check them against your real database before relying on them.

SQLExample (inferred from server/routes/)
CREATE TABLE products (
  id   serial PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE orders (
  id    serial PRIMARY KEY,
  items jsonb NOT NULL
);

JavaScriptserver/db.js (example change)
const { Pool } = require('pg');
if (!process.env.DATABASE_URL) throw new Error('DATABASE_URL is not set');
const pool = new Pool({ connectionString: process.env.DATABASE_URL, connectionTimeoutMillis: 5000 });
pool.on('error', (err) => console.error('postgres idle client error', err));
module.exports = pool;

serial ids keep id a JavaScript number; bigserial would return strings. The 5-second timeout is illustrative: pick one that fits your traffic.

Part 3ReferenceVersions, official docs, and terms.

Collapsed

Shopfront allows any pg 8.x from 8.11.0; with no lockfile, a fresh install gets the newest 8.x. The repo doesn't say which PostgreSQL server version it targets. Checked 2026-10-13. Sources: package.json:10 (no lockfile committed).

PackageIn ShopfrontLatestWhat changed that matters here
pg (node-postgres)^8.11.0; a fresh install resolves to 8.23.18.23.1, 2026-09-30 (npm)No breaking changes or security fixes are labeled in 8.12–8.23 (changelog). Additions Shopfront could use: per-query timeouts (8.13), a minimum pool size (8.16). The docs site only covers the latest release.
PostgreSQL (server)Not pinned in the repo18, first released 2025-09-25Each major version is supported for 5 years; 14 through 18 are supported now, and 14 reaches end of life on 2026-11-12 (versioning policy). Check what your environments run.

Pin both: commit a lockfile for pg, and record the server version the app is tested against.

Collapsed

Where to go next, in reading order. Links checked 2026-10-13.

ResourceWhat it coversChecked
node-postgres
ConnectingConnection strings and PG* variables.2026-10-13
QueriesParameters, value conversion, SQL injection.2026-10-13
Pool APIDefaults, connect/release, the error event, end().2026-10-13
TransactionsBEGIN / COMMIT / ROLLBACK on one client.2026-10-13
Data typesDates, JSON, bigint as strings.2026-10-13
Project structureA single db module, finding connection leaks.2026-10-13
PostgreSQL
Tutorial: creating a tableCREATE TABLE and psql basics.2026-10-13
Error codesThe SQLSTATE list.2026-10-13
Versioning policySupported versions and end-of-life dates.2026-10-13
Docker official imageRunning PostgreSQL locally.2026-10-13

Collapsed

Across the guide, the first mention of a term in each section is underlined: hover it for the definition, or click to jump here.

TermMeaning (as used in Shopfront)Where it shows up (repo path)
PostgreSQL
PostgreSQL / PostgresThe relational database that stores Shopfront's products and orders. See What is PostgreSQL?.server/db.js
Parameterized query / Parameterized SQLSQL with $1, $2… placeholders and a separate values array, so values are never read as SQL. Every Shopfront query is one.server/routes/orders.js:6
RETURNINGSQL clause that makes an INSERT or UPDATE send back columns of the rows it changed, such as a generated id.server/routes/orders.js:6
TransactionSeveral statements that succeed or fail together (BEGIN … COMMIT, or ROLLBACK), run on one checked-out client. Not used in Shopfront yet.docs
SQLSTATEThe five-character code on every PostgreSQL error (err.code), e.g. 23505 for a duplicate key.docs
psqlPostgreSQL's command-line client, for running SQL by hand against a database.docs
Code & config
pg / node-postgres / PoolThe Postgres client; one shared pool from DATABASE_URL, used through db.query with $1 placeholders.server/db.js
DATABASE_URLPostgreSQL connection string read by the pool.server/db.js