Connecting from Python and from Node

Everything so far has been psql. Real applications talk to the database through a driver — a library that speaks PostgreSQL's wire protocol — and the details of that conversation cause more production incidents than SQL does.

Python: psycopg 3

pip install "psycopg[binary]"

The [binary] extra ships a precompiled libpq so you do not need PostgreSQL's development headers. For production, pip install psycopg[c] compiles against your own libpq and is a little faster.

import os
import psycopg

DSN = os.environ["DATABASE_URL"]     # postgresql://postgres:learning@localhost:5432/library

with psycopg.connect(DSN) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT id, name FROM members WHERE membership = %s", ("student",))
        for row in cur:
            print(row)

Five things in there worth naming.

The connection string in an environment variable. Never in the source. Never in the repository. The password is a secret and a repository is not a secret.

with psycopg.connect(...) closes the connection on exit — and commits on success, rolls back on exception. A with block around a connection is a transaction boundary in psycopg 3, which is a genuinely useful default and different from psycopg2's behaviour.

%s placeholders, always. Not f-strings. The next lesson is entirely about this.

A tuple for the parameters, even with one — ("premium",). The trailing comma is required, and forgetting it is the most common psycopg mistake:

cur.execute("... WHERE membership = %s", ("student"))   # a 7-character string, not a tuple

TypeError: query parameters should be a sequence or a mapping, got str

Iterating the cursor streams rows rather than building a list. fetchone(), fetchall() and fetchmany(size) are the alternatives; fetchall() on a million rows puts a million rows in memory.

Getting dictionaries instead of tuples

Tuple indexing (row[3]) is unreadable and breaks silently when you reorder a SELECT:

from psycopg.rows import dict_row

with psycopg.connect(DSN, row_factory=dict_row) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT id, name, membership FROM members LIMIT 1")
        member = cur.fetchone()
        print(member["name"])

There is also class_row for dataclasses and namedtuple_row. Use one of them. The cost of positional access is a bug that no test catches.

Transactions

with psycopg.connect(DSN) as conn:
    with conn.transaction():                       # explicit savepoint-aware block
        conn.execute("UPDATE books SET copies = copies - 1 WHERE id = %s", (1,))
        conn.execute("INSERT INTO loans (book_id, member_id, borrowed_on, due_on) "
                     "VALUES (%s, %s, CURRENT_DATE, CURRENT_DATE + 14)", (1, 1))

Both statements commit together or neither does. Nested conn.transaction() blocks become savepoints, so an inner failure can be caught without losing the outer work.

psycopg 3 is not autocommit by default. If you never commit, your writes vanish — which is the single most common "my insert did not work" question. Set conn.autocommit = True only for DDL that cannot run in a transaction, such as CREATE INDEX CONCURRENTLY.

Node: node-postgres

npm install pg

import { Pool } from 'pg';

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

const { rows } = await pool.query(
  'SELECT id, name FROM members WHERE membership = $1',
  ['student']
);
console.log(rows);          // already objects: rows[0].name

Differences from Python worth holding on to:

$1, $2 placeholders, numbered, not %s. A number may be reused — WHERE a = $1 OR b = $1 passes one parameter.

Pool, not Client. A Client is one connection; pool.query() takes one from the pool and returns it automatically. Use Pool unless you need a session-scoped thing like a transaction.

Rows are objects already. No row factory needed.

Everything is a promise. A forgotten await gives you a Promise where you expected rows, and it fails somewhere unrelated.

Transactions in node-postgres

This is the one that is easy to get wrong:

const client = await pool.connect();          // a dedicated connection
try {
  await client.query('BEGIN');
  await client.query('UPDATE books SET copies = copies - 1 WHERE id = $1', [1]);
  await client.query(
    'INSERT INTO loans (book_id, member_id, borrowed_on, due_on) VALUES ($1, $2, CURRENT_DATE, CURRENT_DATE + 14)',
    [1, 1]
  );
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();                           // ALWAYS, or you leak the connection
}

You must use pool.connect(), not pool.query(). Separate pool.query() calls may land on different connections, so your BEGIN and your writes end up in different sessions.

This is worth proving, because it is a bug that looks like it works. Four concurrent inserts inside what appears to be a transaction:

await pool.query('BEGIN');
await Promise.all([
  pool.query('INSERT INTO probe VALUES (1)'),
  pool.query('INSERT INTO probe VALUES (2)'),
  pool.query('INSERT INTO probe VALUES (3)'),
  pool.query('INSERT INTO probe VALUES (4)'),
]);
await pool.query('ROLLBACK');

surviving rows: 3  [ 2, 3, 4 ]

Three of the four inserts escaped the rollback. Only row 1 happened to land on the same connection as the BEGIN; the other three went to other connections, where they committed immediately because those sessions had no open transaction.

Notice why this is so dangerous: on your laptop, with one request at a time, all four land on the same idle connection and it appears to work perfectly. It only breaks under concurrency, which means it breaks in production.

client.release() in a finally. Miss it and that connection never returns to the pool. Enough of those and the pool is exhausted and your application hangs — with no error, which makes it hard to diagnose.

Type mapping, and the surprises

PostgreSQL	Python (psycopg 3)	Node (pg)
`int`, `bigint`	`int`	`number`
`numeric`	`Decimal`	`string`
`bigint`	`int`	`string`
`boolean`	`bool`	`boolean`
`text`	`str`	`string`
`date`	`datetime.date`	`Date`
`timestamptz`	aware `datetime`	`Date`
`jsonb`	`dict` / `list`	object
`int[]`	`list`	array
`NULL`	`None`	`null`

Three of those will bite you.

numeric becomes a JavaScript string, deliberately, because numeric can hold values IEEE doubles cannot. So + concatenates instead of adding:

3.14 + 10  =>  "3.1410"

Not a wrong number — a string that looks like a number and is 60% longer. Parse it with a decimal library, not parseFloat.

bigint also becomes a string, for the same reason — beyond 2^53 a number loses precision:

big === 9007199254740993  =>  false

This includes your id columns if you followed module 8's advice and made them bigint. A bigint primary key arrives as '2', so row.id === 2 is false and row.id === '2' is true. Every comparison, cache key and URL match needs to expect a string.

And a bare date shifts. This one catches everybody:

DATE '2026-09-27'  arrives as a JS Date
  .getDate()      =>  27                          correct locally
  .toISOString()  =>  2026-09-26T18:30:00.000Z    the 26th

node-postgres builds a Date at local midnight, so in Asia/Kolkata that is 18:30 UTC the previous day. Read it locally and it is right; JSON.stringify it into an API response and your users see yesterday. A date has no instant, so any conversion to one is a fiction.

The fix is to not convert at all — cast in SQL and handle a string:

SELECT borrowed_on::text AS borrowed_on FROM loans

timestamptz in Python is timezone-aware, timestamp is naive. Comparing the two raises TypeError: can't compare offset-naive and offset-aware datetimes, which is the language usefully refusing to guess — and another reason to use timestamptz everywhere.

Connecting securely

postgresql://user:password@host:5432/dbname?sslmode=require

sslmode matters and the default is weak. require encrypts but does not verify the server's certificate, so it stops eavesdropping and not impersonation. verify-full checks the certificate and the hostname, and is what you want for any database across a network:

?sslmode=verify-full&sslrootcert=/path/to/ca.pem

For a container on your own laptop, plain is fine.

Never build the URL by concatenating a password — a @, / or # in it breaks the parse. Pass the parts separately:

psycopg.connect(host=..., port=..., dbname=..., user=..., password=...)

Timeouts, which you should set from the start

psycopg.connect(
    DSN,
    connect_timeout=5,
    options="-c statement_timeout=30000 -c idle_in_transaction_session_timeout=60000",
)

Without connect_timeout, a connection attempt to an unreachable host can hang for minutes, holding a request thread. Without statement_timeout, one runaway query holds a connection forever.

idle_in_transaction_session_timeout is the underrated one: it kills sessions that opened a transaction and went away. Those hold locks and block VACUUM from cleaning up, which is how a forgotten BEGIN in a psql window causes table bloat across the whole database.

Handling failure

Networks fail. The database restarts. A deploy rolls.

import psycopg, time

def fetch_with_retry(dsn, sql, params, attempts=3):
    for attempt in range(attempts):
        try:
            with psycopg.connect(dsn, connect_timeout=5) as conn:
                return conn.execute(sql, params).fetchall()
        except psycopg.OperationalError:
            if attempt == attempts - 1:
                raise
            time.sleep(2 ** attempt)          # 1s, 2s, 4s

Retry OperationalError — connection-level problems, which are usually transient.

Do not blindly retry IntegrityError or ProgrammingError. A unique-violation or a syntax error will fail identically every time; retrying just delays the error.

Only retry writes you can safely repeat. A retried INSERT after a timeout may be a duplicate, because the first attempt might have committed before the connection dropped. Make it idempotent with ON CONFLICT DO NOTHING or a client-supplied unique key. This is the same idempotency argument as the nightly job in module 8.

Check your work

Why the connection string lives in the environment. It contains a secret; a repository is not a secret store.

What a with psycopg.connect(...) block does on exit. Commits on success, rolls back on exception, closes.

Why ("premium",) needs the comma. Without it, it is a string, and psycopg raises a TypeError.

Why fetchall() is risky. It materialises every row in memory.

Why a row factory is worth it. Positional access breaks silently when a SELECT is reordered.

Placeholder syntax in each. %s in psycopg; $1 numbered in node-postgres, and reusable.

Why pool.connect() for a transaction in Node. pool.query() calls may use different connections, so BEGIN lands in a different session. Measured: 3 of 4 concurrent inserts escaped the ROLLBACK — and it works perfectly with no concurrency, so it only fails in production.

What happens without client.release(). The connection leaks and the pool eventually exhausts, hanging with no error.

What numeric and bigint become in Node, and why. Strings — a JavaScript number cannot hold them exactly. So 3.14 + 10 gives "3.1410", and a bigint id arrives as '2'.

Why a date can arrive as the previous day. node-postgres builds a Date at local midnight, so toISOString() and JSON.stringify show the day before east of UTC. Cast to text in SQL instead.

Why comparing timestamp and timestamptz raises in Python. One is naive and one is aware.

The difference between sslmode=require and verify-full. require encrypts without verifying the certificate, so it prevents eavesdropping but not impersonation.

Three timeouts to set, and what each prevents. connect_timeout — hanging on an unreachable host; statement_timeout — a runaway query; idle_in_transaction_session_timeout — abandoned transactions holding locks and blocking VACUUM.

Which errors to retry. OperationalError. Not integrity or programming errors.

Why a retried write can duplicate. The first attempt may have committed before the connection dropped.

Practice

Connect from Python and print one row. Then break the password and read the error.
Move the DSN into an environment variable and confirm the code has no secret in it.
Pass a single parameter without the trailing comma. Read the TypeError.
Fetch 10,000 rows with fetchall() and by iterating. Measure the memory with tracemalloc.
Switch to dict_row and rewrite a query to use names.
Reorder the columns in a SELECT used with positional access and watch the bug appear.
Run two statements in a conn.transaction() and make the second fail. Confirm the first rolled back.
Remove the transaction and repeat. Confirm the first survived.
Write a INSERT with no commit and no with block. Confirm the row is absent afterwards.
Do the same in Node with Pool, Client and a proper transaction.
In Node, run BEGIN, then four concurrent inserts via Promise.all on pool.query, then ROLLBACK. Count the survivors. Then run the same inserts sequentially and note that it appears to work.
Select a date in Node, log .getDate() and .toISOString(), and explain the difference. Then cast to text in SQL and compare.
Remove client.release() from a loop of 20 iterations against a pool of 10. Watch it hang.
Select a numeric column in Node and check typeof. Add 10 to it and explain the output.
Select a bigint id in Node and compare with === 1.
Set statement_timeout=100 and run SELECT pg_sleep(1). Read the error.
Open a transaction in psql, leave it idle, and find it in pg_stat_activity with state idle in transaction.
Write the retry helper and test it by stopping and starting the container mid-loop.

Official documentation

psycopg 3 — Basic module usage — Connections, cursors and the with semantics.
psycopg 3 — Transactions — Autocommit, transaction() blocks and nested savepoints.
psycopg 3 — Row factories — dict_row, class_row and friends.
node-postgres — Pooling — Why Pool, and the release requirement.
node-postgres — Transactions — Explicitly warns against using the pool directly.
node-postgres — Data types — Why numeric and bigint arrive as strings.
PostgreSQL — Connection strings — Every parameter, including all the sslmode values.
PostgreSQL — SSL support — The table of what each sslmode actually protects against.

Next: the one habit that prevents SQL injection.