Drivers, Pools & Transactions in Code

Application code talks to PostgreSQL through a driver (the language library), reuses database sessions through a connection pool (a bounded set of open connections), and wraps related writes in a transaction (one all-or-nothing unit). This lesson shows the Python and JavaScript shapes you will see in real apps: connection strings, query/fetch objects, pooled connections, COMMIT/ROLLBACK, and safe retryable writes.

The SQL is still the important part. The driver is just the adapter that carries your SQL text, your parameters, and the rows coming back.

Drivers, DSNs, And Connections

A driver is the language library that knows PostgreSQL's wire protocol. In Python you might use psycopg; in JavaScript you might use pg. Different libraries have different APIs, but they all do the same few jobs:

• open a connection to the server
• send SQL plus parameters
• fetch result rows
• report errors with database details such as SQLSTATE
• close or return the connection when work is done

A DSN means data source name: the connection address for the database. You will often see it as one URL stored in an environment variable:

postgresql://app_user:secret@db.example.com:5432/cafe_app?sslmode=require

Do not hard-code that string in source code. The normal shape is "read DATABASE_URL from the environment," so local, staging, and production can point at different databases without changing the program.

Here is the small Python shape. This code is transcript-shaped; the course checker does not execute Python snippets.

import os
 
import psycopg
 
conn = psycopg.connect(os.environ["DATABASE_URL"])
 
try:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT current_database() AS database_name,
                   current_user AS user_name;
            """
        )
        row = cur.fetchone()
        print(row)
finally:
    conn.close()

A cursor is the object you execute SQL through and fetch rows from. Some modern APIs let you execute directly on the connection, but the mental model is the same: send one statement, then read rows, command tags, or errors.

The JavaScript version has the same bones:

import pg from "pg";
 
const { Client } = pg;
 
const client = new Client({
  connectionString: process.env.DATABASE_URL,
});
 
try {
  await client.connect();
 
  const result = await client.query(`
    SELECT current_database() AS database_name,
           current_user AS user_name;
  `);
 
  console.log(result.rows[0]);
} finally {
  await client.end();
}

Those examples open one connection, run one query, and close the connection. That is fine for a script. It is not fine for a web server.

Why Serious Apps Use Pools

A connection pool is a small, reusable set of open database connections. Your app borrows one for a request or job, uses it briefly, and returns it.

Without a pool, a busy app tends to do the worst possible thing under pressure: open a new database connection for every request. That means repeated network setup, authentication, memory on the database server, and eventually connection exhaustion. PostgreSQL has a finite max_connections; an app that leaks connections or creates too many can lock itself out of the database it depends on.

With a pool, you put a deliberate ceiling on pressure:

incoming app work
  -> borrow one of 10 pool connections
  -> run a short unit of SQL work
  -> return the connection

If more work arrives than the pool can serve, the app waits, sheds load, or times out at the app boundary. That is much better than creating unlimited database sessions and turning a traffic spike into a database outage.

Pool sizing is not a magic "bigger is better" number. A pool should be large enough to keep useful work flowing and small enough that the database is not buried under simultaneous sessions. Start conservative, measure, and remember the habit from Section 16: structure and row counts before invented timing stories.

Transactions From Code

Inside SQL, Section 12 taught BEGIN, COMMIT, and ROLLBACK. From application code, the rule is the same: a transaction is one unit of work, and the program must choose how it ends.

The shape is always:

1. borrow a connection
2. BEGIN
3. run the related statements
4. COMMIT if the whole unit succeeded
5. ROLLBACK if anything failed
6. return the connection in finally

Here is a Python transaction using a pool. Notice that the connection is returned even when the SQL fails:

import os
 
from psycopg_pool import ConnectionPool
 
pool = ConnectionPool(
    conninfo=os.environ["DATABASE_URL"],
    min_size=1,
    max_size=10,
    timeout=2.0,
)
 
 
def create_payment_request(idempotency_key, order_id, amount_cents):
    conn = pool.getconn()
 
    try:
        with conn.cursor() as cur:
            cur.execute("BEGIN;")
            cur.execute("SET LOCAL statement_timeout = '3s';")
            cur.execute("SET LOCAL lock_timeout = '1s';")
 
            cur.execute(
                """
                INSERT INTO payment_requests (
                  idempotency_key,
                  order_id,
                  amount_cents,
                  status
                )
                VALUES (%s, %s, %s, 'requested')
                ON CONFLICT (idempotency_key) DO NOTHING
                RETURNING payment_id, order_id, amount_cents, status;
                """,
                (idempotency_key, order_id, amount_cents),
            )
            row = cur.fetchone()
 
            if row is None:
                cur.execute(
                    """
                    SELECT payment_id, order_id, amount_cents, status
                    FROM payment_requests
                    WHERE idempotency_key = %s
                      AND order_id = %s
                      AND amount_cents = %s;
                    """,
                    (idempotency_key, order_id, amount_cents),
                )
                row = cur.fetchone()
 
                if row is None:
                    raise ValueError("idempotency key reused with a different payload")
 
            conn.commit()
            return row
    except Exception:
        conn.rollback()
        raise
    finally:
        pool.putconn(conn)

And the same transaction skeleton in JavaScript:

import pg from "pg";
 
const { Pool } = pg;
 
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 2000,
  idleTimeoutMillis: 30000,
});
 
export async function createPaymentRequest(idempotencyKey, orderId, amountCents) {
  const client = await pool.connect();
 
  try {
    await client.query("BEGIN;");
    await client.query("SET LOCAL statement_timeout = '3s';");
    await client.query("SET LOCAL lock_timeout = '1s';");
 
    const inserted = await client.query(
      `
      INSERT INTO payment_requests (
        idempotency_key,
        order_id,
        amount_cents,
        status
      )
      VALUES ($1, $2, $3, 'requested')
      ON CONFLICT (idempotency_key) DO NOTHING
      RETURNING payment_id, order_id, amount_cents, status;
      `,
      [idempotencyKey, orderId, amountCents],
    );
 
    let row = inserted.rows[0];
 
    if (!row) {
      const existing = await client.query(
        `
        SELECT payment_id, order_id, amount_cents, status
        FROM payment_requests
        WHERE idempotency_key = $1
          AND order_id = $2
          AND amount_cents = $3;
        `,
        [idempotencyKey, orderId, amountCents],
      );
      row = existing.rows[0];
 
      if (!row) {
        throw new Error("idempotency key reused with a different payload");
      }
    }
 
    await client.query("COMMIT;");
    return row;
  } catch (err) {
    await client.query("ROLLBACK;");
    throw err;
  } finally {
    client.release();
  }
}

The placeholders matter, but this lesson is not the injection lesson. For now, read %s and $1 as "the driver sends values separately from SQL text." Next lesson shows exactly why string-building SQL is dangerous.

Timeouts, Retries, And Idempotency

A timeout is a deliberate limit on waiting. In app code, use at least two layers:

• a connection acquisition timeout, so a request does not wait forever for a pool slot
• a statement or lock timeout, so one unit of SQL work does not sit behind a stuck query or lock

SET LOCAL statement_timeout and SET LOCAL lock_timeout are transaction-local in PostgreSQL: they apply inside the current transaction and disappear when it ends. This course's browser playground cannot prove real network waits or production lock queues, so keep timeout claims structural here. Real servers make those limits operationally important.

A retry means rerunning work after a failure that might succeed on a clean attempt. Section 12 already named two retry-shaped SQLSTATEs: 40001 for serialization failure and 40P01 for deadlock detected. The important rule is: retry the whole transaction, not the last statement, because the reads and decisions belong to the same story.

Retries are dangerous when a write might have succeeded but the app did not hear the response. A payment request is the classic example: the database may have committed, then the network may have failed before the app saw the row. Blindly trying again can create a duplicate write.

An idempotency key is a unique request key for one exact logical write. The first attempt inserts the row. Later attempts with the same key and the same payload find the same row instead of creating a second one. The same key with a different order_id or amount_cents is a client bug, not a retry.

Try the database side of that pattern:

The first insert returns a row. The repeated request with the same payload returns that same row. The mismatched retry returns zero rows, which is the app's signal to raise a payload-mismatch error instead of pretending the old payment is the answer. The final SELECT proves there are still only two legitimate payment requests.

The Boundary To Keep Clear

Drivers do not make database design disappear. Pools do not make slow SQL fast. Transactions do not make unsafe retry logic safe. The app and the database share the job:

• the database enforces constraints, uniqueness, transaction boundaries, and SQLSTATEs
• the driver carries SQL and values without pretending strings are code
• the pool keeps connection pressure bounded
• the app retries only complete, exact-payload idempotent units of work

That is the production version of the habits you have practiced all course: state the unit of work, let the database enforce the invariant, and verify the result you meant.

Next lesson turns the placeholder detail into a security lesson: SQL injection, the live attack, and why parameterized queries are not optional.