Connect to PostgreSQL

Overview

PostgreSQL runs a huge share of production workloads, but actually connecting to it trips people up in three places: picking a driver, getting SSL right, and pooling connections so max_connections doesn’t run out. Here’s the working setup in Python, JavaScript, and Java — copy-paste ready.

When to Use

Use this resource when:

• Building web applications that persist data to PostgreSQL
• Migrating from SQLite or MySQL to PostgreSQL
• Setting up data pipelines that read from or write to PostgreSQL

If you need MySQL instead, see Connect to MySQL.

Solution

Python

import psycopg2
from psycopg2.extras import RealDictCursor

# psycopg2 also reads PGHOST, PGDATABASE, PGUSER, PGPASSWORD from the environment
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="user",
    password="pass",
    sslmode="prefer"  # use "verify-full" with sslrootcert in production
)

cursor = conn.cursor(cursor_factory=RealDictCursor)
cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
row = cursor.fetchone()
cursor.close()
conn.close()

JavaScript

const { Pool } = require('pg');

const pool = new Pool({
    host: process.env.PGHOST || 'localhost',
    database: process.env.PGDATABASE || 'mydb',
    user: process.env.PGUSER || 'user',
    password: process.env.PGPASSWORD,
    // Local dev needs no SSL; in production use ssl: { rejectUnauthorized: true }
    // and point PGSSLROOTCERT at your CA cert. Never disable verification.
    ssl: false,
    max: 20
});

async function getUser(id) {
    const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);
    return result.rows[0];
}

// On shutdown, drain the pool: await pool.end();

Java

import java.sql.*;

public class PostgresConnect {
    public Connection connect() throws SQLException {
        // Production: append ?sslmode=verify-full&sslrootcert=/path/to/ca.crt
        String url = "jdbc:postgresql://localhost:5432/mydb";
        return DriverManager.getConnection(url, "user", "pass");
    }

    public void queryUser(int id) throws SQLException {
        try (Connection conn = connect();
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("email"));
            }
        }
    }
}

Explanation

All three examples pass user input through prepared statements (%s, $1, ?) instead of string interpolation, so the driver escapes values for you and SQL injection is off the table. On the Python side we go with psycopg2, which is still the adapter you’ll run into most often. The Node version goes through pg’s Pool, so requests share a fixed set of connections rather than opening a new TCP session per query. And the Java version uses plain JDBC wrapped in try-with-resources, which closes the connection even when a query throws.

Once connected, you can execute raw SQL queries or use an ORM for CRUD operations.

Variants

Technology	Approach	Notes
Python	asyncpg	Async PostgreSQL driver for asyncio
JavaScript	pg-promise	Helper library with transactions and tasks
Java	HikariCP	High-performance JDBC connection pool

What Works

1. Use a connection pool in production — a fresh connection per request will exhaust max_connections fast
2. Keep credentials in environment variables or a secrets manager, never in source
3. Turn on SSL for production (sslmode=verify-full once you’ve got the CA cert)
4. Always parameterize; values concatenated into SQL strings are how injections happen
5. Close cursors and connections, or let context managers / try-with-resources do it

Common Mistakes

1. Hardcoding database credentials in source code
2. Opening a new connection per query instead of pooling
3. Leaking connections until Postgres hits “too many connections” errors
4. Turning off certificate verification in production (rejectUnauthorized: false, sslmode=disable)
5. Building queries with f-strings or template literals

Advanced Examples

Python with context manager and connection pool

import psycopg2
from psycopg2 import pool
from contextlib import contextmanager

# Create a connection pool
pg_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=10,
    host="localhost",
    database="mydb",
    user="user",
    password="pass",
    sslmode="prefer"
)

@contextmanager
def get_db_cursor():
    conn = pg_pool.getconn()
    try:
        cursor = conn.cursor()
        yield cursor
        conn.commit()
        cursor.close()
    except Exception:
        conn.rollback()
        raise
    finally:
        pg_pool.putconn(conn)

# Usage
with get_db_cursor() as cur:
    cur.execute("SELECT * FROM users WHERE active = %s", (True,))
    rows = cur.fetchall()
    for row in rows:
        print(row)

Python async with asyncpg

import asyncio
import asyncpg

async def main():
    conn = await asyncpg.connect(
        host="localhost",
        database="mydb",
        user="user",
        password="pass",
        ssl="prefer"
    )

    # Parameterized query
    row = await conn.fetchrow(
        "SELECT * FROM users WHERE id = $1", 1
    )
    print(row)

    # Batch insert
    await conn.executemany(
        "INSERT INTO logs (level, message) VALUES ($1, $2)",
        [("INFO", "startup"), ("WARN", "high latency")]
    )

    await conn.close()

asyncio.run(main())

JavaScript with transaction handling

const { Pool } = require('pg');

const pool = new Pool({
    host: process.env.PGHOST || 'localhost',
    database: process.env.PGDATABASE || 'mydb',
    user: process.env.PGUSER || 'user',
    password: process.env.PGPASSWORD,
    ssl: false, // same rule as above: verify certs in production
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 5000
});

async function transferBalance(fromId, toId, amount) {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');

        await client.query(
            'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
            [amount, fromId]
        );

        await client.query(
            'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
            [amount, toId]
        );

        await client.query('COMMIT');
    } catch (err) {
        await client.query('ROLLBACK');
        throw err;
    } finally {
        client.release();
    }
}

Java with HikariCP connection pool

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;

public class PostgresPool {
    private static final HikariDataSource ds;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("user");
        config.setPassword("pass");
        config.addDataSourceProperty("sslmode", "prefer");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setIdleTimeout(30000);
        config.setConnectionTimeout(5000);
        ds = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    // Call ds.close() when the application shuts down
    public static void batchInsert(List<String> emails) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(
                 "INSERT INTO users (email) VALUES (?)")) {
            for (String email : emails) {
                stmt.setString(1, email);
                stmt.addBatch();
            }
            stmt.executeBatch();
        }
    }
}

Additional Variants

Technology	Driver	Async	Pooling	Notes
Python	psycopg2	No	Manual or SimpleConnectionPool	Mature, stable
Python	psycopg3	Yes	Built-in	Recommended for new projects
Python	asyncpg	Yes	Built-in	Fastest async driver
JavaScript	pg	Yes (Promise)	Pool built-in	Standard Node.js driver
JavaScript	pg-promise	Yes	Built-in	Extra helpers for tasks
Java	JDBC	No	HikariCP	Industry standard
Go	pgx	Yes	pgxpool	High performance

Production Tips

1. Set idle_timeout on connections. Idle connections can go stale after a database restart or a network blip. Configure a timeout so the pool recycles them automatically.
2. Use application_name for debugging. Set application_name in the connection string so you can identify your app in pg_stat_activity:

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="user",
    password="pass",
    application_name="my-api-server"
)

3. Enable statement_timeout at the session level. It keeps runaway queries from consuming resources:

SET statement_timeout = '10s';

4. Use COPY for bulk inserts. COPY is 10-100x faster than individual INSERT statements for large datasets:

import io
import psycopg2

buf = io.StringIO()
buf.write("1\talice@example.com\n")
buf.write("2\tbob@example.com\n")
buf.seek(0)

with psycopg2.connect("dbname=mydb user=user host=localhost") as conn:
    with conn.cursor() as cur:
        cur.copy_from(buf, "users", columns=("id", "email"))

5. Monitor pool health. Track active connections, waiting threads, and connection lifetime. With HikariCP, use getHikariPoolMXBean() to expose metrics.