Project 3 — Node.js API Integration

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Project 3 — Node.js API Integration
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

Applications must pass SQL input as parameters, never string concatenation. Check out one pool client and run every statement in a transaction through that same client. Map SQLSTATE codes such as unique and foreign-key violations to stable application errors without leaking raw database details.

Connect it to a real scenario

The enrollment endpoint inserts parameterized user/tutorial IDs and initializes progress in one transaction. Roll back on error, release the client in `finally`, and handle retryable transaction errors separately.

Parameterize all input
One transaction uses one checked-out client
Map SQLSTATE to safe application errors

Try the working example

typescript

import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

export async function enroll(userId: number, tutorialId: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows: [enrollment] } = await client.query(
      `INSERT INTO app.enrollments (user_id, tutorial_id)
       VALUES ($1, $2) RETURNING enrollment_id`,
      [userId, tutorialId],
    );
    await client.query(
      `INSERT INTO app.lesson_progress (enrollment_id, tutorial_id, lesson_id)
       SELECT $1, $2, lesson_id FROM app.lessons WHERE tutorial_id = $2`,
      [enrollment.enrollment_id, tutorialId],
    );
    await client.query('COMMIT');
    return enrollment;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally { client.release(); }
}

You should see

You have an injection-safe, atomic enrollment service function.

5-minute try-it

Map duplicate-enrollment SQLSTATE `23505` to HTTP 409 without exposing raw constraint details.

One important caution

Using separate `pool.query()` calls around BEGIN/COMMIT can run statements on different connections and break the transaction.

PostgreSQL — libpq Concepts — PostgreSQL Global Development Group

Easy traps

Using separate `pool.query()` calls around BEGIN/COMMIT can run statements on different connections and break the transaction.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Map duplicate-enrollment SQLSTATE `23505` to HTTP 409 without exposing raw constraint details.

You'll know it worked when: You have an injection-safe, atomic enrollment service function.