← Back to Blog
1 August 2025 Node.jsExpressPostgreSQLRailwayREST API

Project 04 — SA Fuel Price API

Building a public REST API for South African fuel prices with Node.js, Express and PostgreSQL on Railway — and upgrading the fuel tracker from a static JSON file to a live database-backed API.

The Goal

Project 02 (the SA Fuel Price Tracker) loaded data from a static JSON file. Every month a developer had to manually edit the file, commit and push. That works, but it is not a real system.

Project 04 fixes this properly — a public REST API backed by PostgreSQL, deployed on Railway, that any developer can consume. The fuel tracker is updated to pull from it live. A simple admin page lets the prices be updated from a browser with no terminal needed.

This is also the first project that leaves the Google Apps Script stack entirely and builds a classical Node.js backend.

Stack

  • Node.js + Express — REST API with versioned routes (/v1/)
  • PostgreSQL — hosted on Railway, connected via pg Pool
  • Railway — Node service + managed database, auto-deploys from GitHub
  • No ORM — raw parameterised queries throughout

The decision to skip an ORM was deliberate. Understanding pg directly — query planning, parameterisation, connection pooling — is more useful at this stage than learning Prisma or Drizzle syntax.

Database Schema

Financial data goes into DECIMAL(5,2) columns, not floats. Float arithmetic produces rounding errors that matter when displaying prices to the cent.

CREATE TABLE fuel_prices (
  id          SERIAL        PRIMARY KEY,
  month       VARCHAR(7)    NOT NULL UNIQUE,  -- '2026-05'
  month_label VARCHAR(8)    NOT NULL,          -- 'May 2026'
  p95i        DECIMAL(5,2)  NOT NULL,
  p95c        DECIMAL(5,2)  NOT NULL,
  p93i        DECIMAL(5,2)  NOT NULL,
  d005i       DECIMAL(5,2)  NOT NULL,
  d005c       DECIMAL(5,2)  NOT NULL,
  updated_at  TIMESTAMPTZ   DEFAULT NOW()
);

An updated_at trigger fires on every row update at the database level — no application code needed to maintain it.

API Design

All read endpoints are public. Write endpoints require an API key passed as Authorization: Bearer YOUR_KEY. The key is stored in Railway’s environment variables, never in source code.

GET  /v1/prices              → All months, newest first
GET  /v1/prices/latest       → Current month
GET  /v1/prices/:month       → Specific month (YYYY-MM)
GET  /v1/prices/range        → Date range (?from=&to=)
GET  /v1/stats               → Min, max, average per fuel type
POST /v1/prices              → Add new month  (key required)
PUT  /v1/prices/:month       → Update month   (key required)

The PUT endpoint uses COALESCE so only supplied fields are updated — a partial update with no risk of accidentally zeroing untouched columns:

UPDATE fuel_prices SET
  p95i = COALESCE($1, p95i),
  p95c = COALESCE($2, p95c),
  ...
WHERE month = $7

Seed Script

The 29 months of historical data from the fuel tracker’s prices.json was imported via a seed script using ON CONFLICT DO UPDATE (upsert). This means the script is safe to re-run — it updates existing rows rather than failing on duplicate keys.

await pool.query(
  `INSERT INTO fuel_prices (month, month_label, p95i, ...)
   VALUES ($1,$2,$3,...)
   ON CONFLICT (month) DO UPDATE SET
     p95i = EXCLUDED.p95i, ...`,
  [month, row.month, row.p95i, ...]
);

Upgrading the Fuel Tracker

Connecting the tracker to the API required three changes:

1 — Data URL

const DATA_URL = 'https://sa-fuel-api-production.up.railway.app/v1/prices?limit=36';

2 — Normalise the response — the API returns nested prices (prices.petrol.p95Inland) but the tracker expects flat keys (p95i):

function normalizeApiRow(row) {
  return {
    month: row.monthLabel,
    p95i:  row.prices.petrol.p95Inland,
    p95c:  row.prices.petrol.p95Coastal,
    p93i:  row.prices.petrol.p93Inland,
    d005i: row.prices.diesel.d005Inland,
    d005c: row.prices.diesel.d005Coastal,
  };
}

3 — Reverse the array — the API returns newest first, but the tracker expects chronological order for slice(-12) and change calculations:

priceData = rows.map(normalizeApiRow).reverse();

Interactive Chart Navigation

With 29 months of data a fixed 12-month chart window left most history hidden. A sliding window with ← Older and Newer → navigation was added. The window size is constant at 12 months; chartOffset tracks how many windows back the view is. Disabled states prevent navigating beyond the available data in either direction.

Admin Price Editor

A password-protected admin.html page lets prices be updated from any browser — no terminal, no code changes, no redeploy. It authenticates by testing the API key against a dummy request, then exposes two modes: Add New Month and Correct Existing. The current last six months are shown in a live table that refreshes after every save.

What This Project Demonstrates

  • Express REST API with versioned routes and clean separation of concerns
  • PostgreSQL with raw pg — parameterised queries, DECIMAL types, triggers
  • ON CONFLICT DO UPDATE upsert for idempotent seed scripts
  • COALESCE partial updates
  • API key auth via Authorization: Bearer header
  • Rate limiting with express-rate-limit
  • Global error handler with PostgreSQL error code mapping
  • Environment-based SSL config for Railway production
  • Consuming your own API — normalisation, ordering, window navigation

Live API: sa-fuel-api-production.up.railway.app
Docs: sa-fuel-api-production.up.railway.app/docs
GitHub: sa-fuel-api

Next: Project 05 — TypeScript Migration. Taking the fuel API and rebuilding it with full type safety — utility types, Zod validation, typed query results.

← All Posts