Skip to content
Tutorials Center
My learning

Topics

Lesson 80 of 90 ยท Using PostgreSQL from Applications

PostgreSQL from Python, Node.js, PHP and Java

Use PostgreSQL from psycopg, node-postgres, PDO and JDBC: connect, pass parameters, run transactions, pool connections, bulk load with COPY and listen for notifications.

Sooner or later your SQL has to run inside an application. Every mainstream language has a PostgreSQL driver, and they all follow the same small set of ideas: open a connection (usually through a pool), send SQL with parameters, wrap related changes in a transaction, and read rows back. The names differ, the shape doesn't.

This lesson shows the four most common drivers side by side: psycopg 3 for Python, node-postgres (pg) for Node.js, PDO with the pgsql driver for PHP, and JDBC for Java. The general ideas are in connecting from code; here we focus on what PostgreSQL adds: server-side parameters, RETURNING, COPY and LISTEN. Drivers need a real server, so the driver code is read-only. Two runnable Python examples use SQLite's DB-API as an honest stand-in: the calls have the same shape as psycopg's, and the transaction rules are identical.

Every driver does the same five things

Connect From a URL or keywords, ideally via a pool.
Parameters Values travel apart from the SQL text.
Transactions Commit on success, roll back on error.
Rows Results come back as tuples, dicts or objects.

PostgreSQL extras: RETURNING, COPY and LISTEN.

1 / 2

Connecting

All four drivers accept a connection string or its parts. PostgreSQL's native form is a URI such as postgresql://app_user:[email protected]:5432/shop?sslmode=verify-full (see installing and connecting). Read the password from the environment, never from source code.

Python
# Python: psycopg 3  (pip install "psycopg[binary]")
import os
import psycopg
from psycopg.rows import dict_row

with psycopg.connect(os.environ["DATABASE_URL"], row_factory=dict_row) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT id, title FROM books WHERE price < %s ORDER BY id", (20,))
        for row in cur.fetchall():
            print(row["id"], row["title"])
JavaScript
// Node.js: node-postgres  (npm install pg)
import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const result = await pool.query(
  "SELECT id, title FROM books WHERE price < $1 ORDER BY id",
  [20],
);
console.log(result.rows);          // [{ id: 1, title: '...' }, ...]
PHP
<?php
// PHP: PDO with the pgsql driver
$pdo = new PDO(
    'pgsql:host=db.internal;port=5432;dbname=shop;sslmode=verify-full',
    getenv('DB_USER'),
    getenv('DB_PASSWORD'),
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ],
);
$stmt = $pdo->prepare('SELECT id, title FROM books WHERE price < :max ORDER BY id');
$stmt->execute(['max' => 20]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Java
// Java: JDBC (the org.postgresql driver)
String url = "jdbc:postgresql://db.internal:5432/shop?sslmode=verify-full";
try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement ps = conn.prepareStatement(
         "SELECT id, title FROM books WHERE price < ? ORDER BY id")) {
    ps.setBigDecimal(1, new BigDecimal("20"));
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getLong("id") + " " + rs.getString("title"));
        }
    }
}

Look at what is the same. The SQL text contains a placeholder and the value is passed separately, so the input can't change the query's structure (SQL injection). Only the placeholder style differs: %s in psycopg, $1 in node-postgres, :name or ? in PDO, ? in JDBC. PDO's pgsql driver can use real server-side prepared statements, so turn ATTR_EMULATE_PREPARES off. In PDO, a literal question mark in PostgreSQL's jsonb operators such as ? and ?| must be written ??, or you can call the jsonb_exists() function instead.

The pattern in the Python example (execute with a parameter tuple, then iterate over rows) is the DB-API standard, so you can run the same shape here with SQLite. Note that SQLite uses ? where psycopg uses %s:

Python The DB-API shape: parameters and rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

RETURNING: get generated values back

PostgreSQL can return columns from INSERT, UPDATE and DELETE, so you don't need a second query (or a driver-specific "last insert id" call) to learn a generated key:

Python
cur.execute(
    "INSERT INTO books (title, price) VALUES (%s, %s) RETURNING id, created_at",
    ("Slow Rivers", 18.0),
)
new_id, created_at = cur.fetchone()

In node-postgres the new row is in result.rows[0]; in PDO call fetch() after execute(); in JDBC use ps.executeQuery() (or Statement.RETURN_GENERATED_KEYS, which the driver translates to RETURNING for you). See upsert and RETURNING.

Transactions

A transaction makes several statements succeed or fail together. The drivers differ in their defaults, and that difference causes real bugs:

  • psycopg 3 starts a transaction on the first statement. Leaving the with psycopg.connect(...) block commits if no exception happened and rolls back if one did. Use with conn.transaction(): for explicit blocks and nested savepoints.
  • node-postgres has no transaction helper, and pool.query() may use a different connection for each call. For a transaction, borrow one client and always release it.
  • PDO is in autocommit mode until you call beginTransaction().
  • JDBC starts in autocommit mode: every statement commits at once until you call setAutoCommit(false).
JavaScript
const client = await pool.connect();      // one dedicated connection
try {
  await client.query("BEGIN");
  await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [50, 1]);
  await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [50, 2]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();                       // return it to the pool, success or not
}
PHP
$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([50, 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([50, 2]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}
Java
conn.setAutoCommit(false);
try {
    // ... execute both updates ...
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

PostgreSQL has one rule you must respect: after any error inside a transaction, the transaction is aborted and refuses further commands until ROLLBACK (or a rollback to a savepoint). In serializable or repeatable-read transactions you should also be ready to retry the whole transaction on a serialization_failure (see isolation levels). This live Python example shows the all-or-nothing behaviour with SQLite, whose transactions work the same way:

Python Commit and rollback with a failing transfer

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second transfer first credits account 2, then fails on the CHECK constraint when debiting account 1. Because both updates are in one transaction, the rollback also undoes the credit and the balances stay as the first transfer left them.

Pooling

Opening a PostgreSQL connection is expensive: the server starts a whole process for it, so applications reuse connections from a pool. Every ecosystem has one: psycopg_pool.ConnectionPool for Python, pg.Pool for Node.js, HikariCP for Java, and for PHP the usual answer is a pooler such as PgBouncer in front of PHP-FPM (since PHP processes don't share memory). A pool has a maximum size, and that number should be small. A good starting point is a few connections per CPU core of the database server, split across all your application instances; a pool of 200 per instance can swamp the database. See connection pooling for PgBouncer modes and how transaction pooling affects prepared statements and session settings.

Always give connections back: use with in Python, finally { client.release() } in Node.js and try-with-resources in Java. A leaked connection is the most common reason for an application that "hangs" once traffic rises.

COPY: fast bulk loading

For thousands of rows or more, a loop of INSERT statements is slow because of the round trips. PostgreSQL's COPY streams rows in one operation. The drivers expose it like this:

Python
# psycopg 3
with cur.copy("COPY books (title, price) FROM STDIN") as copy:
    for title, price in rows:
        copy.write_row((title, price))
PHP
// PDO pgsql: rows are tab-separated strings
$pdo->pgsqlCopyFromArray('books', ["Tide Tables\t14.50", "Ink and Salt\t22.00"], "\t", "\\N", 'title,price');

In Java, conn.unwrap(PGConnection.class).getCopyAPI() returns a CopyManager with copyIn(sql, reader). In Node.js the separate pg-copy-streams package provides stream objects. The next lessons cover the details in bulk loading and COPY.

LISTEN and NOTIFY

A connection can subscribe to a channel and receive messages sent with NOTIFY from any session, which is a lightweight way to tell workers that new work exists (see event triggers, LISTEN and NOTIFY). Use a dedicated connection that stays open, not a pooled one:

JavaScript
const listener = await pool.connect();
listener.on("notification", (msg) => console.log(msg.channel, msg.payload));
await listener.query("LISTEN new_orders");

psycopg offers conn.notifies() (with autocommit=True), PDO has pgsqlGetNotify(), and JDBC uses PGConnection.getNotifications(). Notifications are not stored: a listener that is disconnected misses them, so treat them as a hint to look at the table, not as the data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which driver defaults to autocommit, so every statement commits immediately until you turn it off?

    JDBC starts in autocommit mode; you must call setAutoCommit(false) to group statements. PDO behaves the same until beginTransaction().
  2. 2. Why can't you run a node-postgres transaction with several pool.query() calls?

    A transaction belongs to one connection. Borrow a client with pool.connect(), run BEGIN ... COMMIT on it, and release it in a finally block.
  3. 3. What does RETURNING id add to an INSERT?

    PostgreSQL returns the requested columns from the changed rows in the same round trip.
  4. 4. Why use a dedicated, non-pooled connection for LISTEN?

    LISTEN registers interest for the current session. If the connection is returned to a pool or closed, notifications are missed.

Summary

  • Every driver connects (preferably through a pool), sends SQL with parameters, and returns rows; only the placeholder style changes (%s, $1, :name, ?).
  • Pass the connection secrets from the environment and prefer sslmode=verify-full.
  • RETURNING hands back generated values; COPY loads many rows fast; LISTEN needs its own long-lived connection.
  • Know each driver's transaction default: JDBC and PDO autocommit until told otherwise, psycopg commits at the end of its with block, and node-postgres needs one borrowed client.
  • After an error, a PostgreSQL transaction is aborted until you roll back.
  • Keep pools small and always return connections.

Next: ORMs, Migrations and PostgreSQL Features.