PostgreSQL with node-postgres

npm install pg
npm install --save-dev @types/pg
createdb school_api
psql school_api -f schema.sql
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/school_api"
npx tsx src/main.ts

CREATE TABLE students (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL CHECK (length(trim(name)) >= 2),
  form       smallint NOT NULL CHECK (form BETWEEN 1 AND 6),
  balance    integer NOT NULL DEFAULT 0 CHECK (balance >= 0),   -- fees owed, whole shillings
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE payments (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id integer NOT NULL REFERENCES students(id),
  amount     integer NOT NULL CHECK (amount > 0),
  paid_at    timestamptz NOT NULL DEFAULT now()
);

import pg from "pg";

// One pool for the whole app: it keeps a few connections open and reuses them.
export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});

// Run several statements on ONE connection, committing only if all succeed.
export async function withTransaction<T>(work: (client: pg.PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await work(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();                     // always give the connection back to the pool
  }
}

import { pool, withTransaction } from "./db";

export interface Student {
  id: number;
  name: string;
  form: number;
  balance: number;
}

// $1, $2 ... are parameters: the values travel separately from the SQL, so no SQL injection.
export async function createStudent(name: string, form: number, balance = 0): Promise<Student> {
  const { rows } = await pool.query<Student>(
    "INSERT INTO students (name, form, balance) VALUES ($1, $2, $3) RETURNING id, name, form, balance",
    [name, form, balance],
  );
  return rows[0];
}

export async function findByForm(form: number): Promise<Student[]> {
  const { rows } = await pool.query<Student>(
    "SELECT id, name, form, balance FROM students WHERE form = $1 ORDER BY name",
    [form],
  );
  return rows;
}

export async function searchByName(text: string): Promise<Student[]> {
  const { rows } = await pool.query<Student>(
    "SELECT id, name, form, balance FROM students WHERE name ILIKE '%' || $1 || '%' ORDER BY name",
    [text],
  );
  return rows;
}

export async function findById(id: number): Promise<Student | undefined> {
  const { rows } = await pool.query<Student>("SELECT id, name, form, balance FROM students WHERE id = $1", [id]);
  return rows[0];
}

// Insert the payment and reduce the balance together, or not at all.
export async function recordPayment(studentId: number, amount: number): Promise<number> {
  return withTransaction(async (client) => {
    await client.query("INSERT INTO payments (student_id, amount) VALUES ($1, $2)", [studentId, amount]);
    const { rows } = await client.query<{ balance: number }>(
      "UPDATE students SET balance = balance - $2 WHERE id = $1 RETURNING balance",
      [studentId, amount],
    );
    if (rows.length === 0) throw new Error(`student ${studentId} not found`);
    return rows[0].balance;
  });
}

import { pool } from "./db";
import { createStudent, findById, findByForm, recordPayment, searchByName } from "./students";

const amina = await createStudent("Amina Hassan", 4, 150_000);
await createStudent("Juma Said", 4, 200_000);
console.log(await findByForm(4));

console.log("Balance after paying 50,000:", await recordPayment(amina.id, 50_000));

try {
  await recordPayment(amina.id, 500_000);           // would make the balance negative
} catch (err) {
  // The CHECK constraint failed, so the transaction rolled back the payment row too.
  console.log("Rejected:", (err as Error).message);
}
console.log("Still:", (await findById(amina.id))?.balance);

console.log(await searchByName("jum"));               // [ { id: 2, name: 'Juma Said', ... } ]
// Malicious input is just text when you use parameters: it matches no names.
console.log(await searchByName("' OR '1'='1"));        // []

await pool.end();                                     // let the process exit