UVF IT: Zero‑Dependency PostgreSQL Job Queue (Node.js)

Welcome to another official UVF IT build guide. In this tutorial we’ll spin up a tiny PostgreSQL‑backed job queue in pure Node.js—no external libraries, no paid services. The result behaves like the core of CatQueue: enqueue jobs, claim them, and mark them done.

You’ll see how a few dozen lines of JavaScript plus a local Postgres instance give you a reliable, persistent work queue you can extend for any background processing need.

Before you start…

Node ≥ 18 and a running PostgreSQL server (Docker or local install) are required.
The clone omits advanced features such as delayed jobs, retries, and metrics—focus is on the basic enqueue/claim/complete cycle.
Make sure the database user has CREATE TABLE privileges; otherwise the init script will fail.

Component Overview

Phase 1: Scaffold & Init DB

Create a fresh folder for the project, install the official pg driver, and add a tiny SQL schema that stores jobs. The schema includes an auto‑increment id, a JSON payload column, a status flag, and timestamps for visibility and debugging.

We’ll also add a short shell script that runs the CREATE TABLE statement, so you can re‑run it whenever you spin up a fresh database.

All of this is pure Node and plain SQL—no ORM, no migrations framework, just what you need to get a queue table ready.

mkdir catqueue-clone && cd catqueue-clone
npm init -y > /dev/null
npm install pg@8

# Create init.sql with the job table definition
cat > init.sql <<'SQL'
CREATE TABLE IF NOT EXISTS jobs (
  id SERIAL PRIMARY KEY,
  payload JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
SQL

# Run the script (adjust connection string as needed)
export DATABASE_URL=postgres://postgres:password@localhost:5432/queue_db
psql $DATABASE_URL -f init.sql

Phase 2: Enqueue Function

Add a tiny module that inserts a job row and returns its generated id. The function receives any JSON‑serializable payload, stores it in the payload column, and leaves the status as pending.

We keep the module self‑contained: it creates a Pool from the pg driver, runs a parameterised INSERT, and resolves with the new job id. This mirrors CatQueue’s enqueue API but stays dependency‑free.

queue.js

const { Pool } = require('pg');

// Connection string can be set via env var DATABASE_URL
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

/**
 * Enqueue a new job.
 * @param {object} payload - Any JSON‑serialisable data.
 * @returns {Promise<number>} The id of the newly created job.
 */
async function enqueue(payload) {
  const client = await pool.connect();
  try {
    const res = await client.query(
      `INSERT INTO jobs (payload) VALUES ($1) RETURNING id;`,
      [payload]
    );
    return res.rows[0].id;
  } finally {
    client.release();
  }
}

module.exports = { enqueue };

Phase 3: Claim & Complete API

We now add the two HTTP endpoints that make the queue useful. The /claim route will atomically lock the oldest job whose status is "pending" and return its payload, while /complete will mark a claimed job as "done". Both endpoints use the same PostgreSQL client we set up earlier, so there are no extra dependencies. This keeps the server tiny and easy to understand for beginners.

server.js

const http = require('http');
const { Pool } = require('pg');
const url = require('url');

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

async function claimJob() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const res = await client.query(
      `SELECT id, payload FROM jobs WHERE status='pending' ORDER BY created_at ASC LIMIT 1 FOR UPDATE SKIP LOCKED`
    );
    if (res.rowCount === 0) return null;
    const job = res.rows[0];
    await client.query('UPDATE jobs SET status=$1 WHERE id=$2', ['claimed', job.id]);
    await client.query('COMMIT');
    return { id: job.id, payload: job.payload, status: 'claimed' };
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally { client.release(); }
}

async function completeJob(id) {
  await pool.query('UPDATE jobs SET status=$1 WHERE id=$2', ['done', id]);
}

const server = http.createServer(async (req, res) => {
  const parsed = url.parse(req.url, true);
  if (req.method === 'POST' && parsed.pathname === '/claim') {
    try {
      const job = await claimJob();
      if (!job) { res.writeHead(204); return res.end(); }
      res.writeHead(200, {'Content-Type':'application/json'});
      res.end(JSON.stringify(job));
    } catch (e) { res.writeHead(500); res.end(e.message); }
  } else if (req.method === 'POST' && parsed.pathname === '/complete') {
    let body = '';
    req.on('data', chunk => body += chunk);
    req.on('end', async () => {
      try {
        const {id} = JSON.parse(body);
        await completeJob(id);
        res.writeHead(200); res.end('ok');
      } catch (e) { res.writeHead(400); res.end(e.message); }
    });
  } else { res.writeHead(404); res.end('Not found'); }
});

const PORT = process.env.PORT || 3000;
server.listen(PORT, () => console.log(`Server listening on ${PORT}`));

Phase 4: Run Locally

With the server ready, we launch it and immediately test the new endpoints using curl. The first request to /claim should return a JSON object representing the oldest pending job, and the second request to /complete will tell the server which job to mark as done. Running these commands locally verifies that our queue works end‑to‑end without any external tooling.

# Install dependencies and start the server
npm install pg && node server.js &
SERVER_PID=$!
# Give the server a moment to start
sleep 1
# Claim the oldest pending job
curl -s -X POST http://localhost:3000/claim | tee claim.json
# Extract the job id and mark it complete
JOB_ID=$(jq .id < claim.json)
curl -s -X POST -H "Content-Type: application/json" -d "{\"id\":$JOB_ID}" http://localhost:3000/complete
# Clean up
kill $SERVER_PID

Phase 5: Verify & Extend

After the API calls succeed, we inspect the PostgreSQL table to confirm the job status changed from "claimed" to "done". This step shows the full round‑trip: enqueue → claim → complete → persisted state. Once you’re comfortable, you can extend the queue with retries, a dead‑letter table, or richer job metadata without altering the core API.

# Show all jobs and their statuses
echo "Current jobs:" && psql $DATABASE_URL -c "SELECT id, status, payload FROM jobs ORDER BY id;"

Verifying the Stack

Check	Command / Why it matters
Project initializes without errors	npm i pg && node server.js (no stack trace)
Enqueue returns a numeric id	node -e "require('./queue').enqueue({x:1}).then(id=>console.log('id',id))" prints a number
/claim returns a job JSON with status 'claimed'	curl -X POST http://localhost:3000/claim \| jq .status yields "claimed"
/complete updates status to 'done' in DB	psql $DATABASE_URL -c "SELECT status FROM jobs WHERE id=1;" shows 'done'

You’re ready to queue!

You now have a functional, zero‑dependency PostgreSQL job queue. Plug it into any worker process, add retry logic, or expose a richer API as you grow.

Before you start…

Phase 1: Scaffold & Init DB

Phase 2: Enqueue Function

Phase 3: Claim & Complete API

Phase 4: Run Locally

Phase 5: Verify & Extend