Conectar a PostgreSQL

Visión General

PostgreSQL mueve buena parte de las cargas de producción, pero conectar bien falla siempre en los mismos tres puntos: elegir driver, configurar SSL y gestionar el pool para que no se agote max_connections. Aquí va el setup que funciona en Python, JavaScript y Java — listo para copiar.

Cuándo Usar

Usa este recurso cuando:

• Construyes aplicaciones web que persisten datos en PostgreSQL
• Migras desde SQLite o MySQL a PostgreSQL
• Configuras pipelines de datos que leen o escriben en PostgreSQL

Si necesitas MySQL, consulta Conectar a MySQL.

Solución

Python

import psycopg2
from psycopg2.extras import RealDictCursor

# psycopg2 también lee PGHOST, PGDATABASE, PGUSER, PGPASSWORD del entorno
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="user",
    password="pass",
    sslmode="prefer"  # usa "verify-full" con sslrootcert en producción
)

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,
    // En desarrollo local no hace falta SSL; en producción usa
    // ssl: { rejectUnauthorized: true } y apunta PGSSLROOTCERT a tu CA.
    // Nunca desactives la verificación.
    ssl: false,
    max: 20
});

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

// Al cerrar la app, drena el pool: await pool.end();

Java

import java.sql.*;

public class PostgresConnect {
    public Connection connect() throws SQLException {
        // Producción: añade ?sslmode=verify-full&sslrootcert=/ruta/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"));
            }
        }
    }
}

Explicación

Los tres ejemplos pasan la entrada del usuario por consultas preparadas (%s, $1, ?) en lugar de interpolar strings, así que el driver escapa los valores y la inyección SQL queda descartada. En el lado de Python vamos con psycopg2, que sigue siendo el adaptador con el que más te vas a cruzar. La versión de Node pasa por el Pool de pg, de modo que las peticiones comparten un conjunto fijo de conexiones en vez de abrir una sesión TCP por consulta. Y la de Java usa JDBC plano envuelto en try-with-resources, que cierra la conexión aunque una query lance excepción.

Una vez conectado, puedes ejecutar consultas SQL directas o usar un ORM para operaciones CRUD.

Variantes

Tecnología	Enfoque	Notas
Python	asyncpg	Driver asíncrono de PostgreSQL para asyncio
JavaScript	pg-promise	Librería helper con transacciones y tareas
Java	HikariCP	Pool de conexiones JDBC de alto rendimiento

Lo que funciona

1. Usa un pool de conexiones en producción — una conexión nueva por petición agota max_connections rápido
2. Guarda las credenciales en variables de entorno o un gestor de secretos, nunca en el código
3. Activa SSL en producción (sslmode=verify-full cuando tengas el certificado CA)
4. Parametriza siempre; concatenar valores en strings SQL es como se producen las inyecciones
5. Cierra cursores y conexiones, o deja que los context managers / try-with-resources lo hagan

Errores Comunes

1. Hardcodear credenciales de base de datos en el código fuente
2. Abrir una conexión nueva por consulta en lugar de usar un pool
3. Fugar conexiones hasta que Postgres da errores de “demasiadas conexiones”
4. Desactivar la verificación de certificados en producción (rejectUnauthorized: false, sslmode=disable)
5. Construir queries con f-strings o template literals

Ejemplos Avanzados

Python con context manager y pool de conexiones

import psycopg2
from psycopg2 import pool
from contextlib import contextmanager

# Crear un pool de conexiones
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)

# Uso
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 asíncrono con asyncpg

import asyncio
import asyncpg

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

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

    # Insert masivo
    await conn.executemany(
        "INSERT INTO logs (level, message) VALUES ($1, $2)",
        [("INFO", "startup"), ("WARN", "alta latencia")]
    )

    await conn.close()

asyncio.run(main())

JavaScript con manejo de transacciones

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, // misma regla: verifica certificados en producción
    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 con pool HikariCP

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();
    }

    // Llama a ds.close() cuando la aplicación se apague
    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();
        }
    }
}

Variantes adicionales

Tecnología	Driver	Async	Pooling	Notas
Python	psycopg2	No	Manual o SimpleConnectionPool	Maduro, estable
Python	psycopg3	Sí	Built-in	Recomendado para proyectos nuevos
Python	asyncpg	Sí	Built-in	Driver async más rápido
JavaScript	pg	Sí (Promise)	Pool built-in	Driver estándar de Node.js
JavaScript	pg-promise	Sí	Built-in	Helpers extra para tareas
Java	JDBC	No	HikariCP	Estándar de la industria
Go	pgx	Sí	pgxpool	Alto rendimiento

Consejos de Producción

1. Configura idle_timeout en las conexiones. Las conexiones inactivas pueden quedar obsoletas tras un reinicio de base de datos o un corte de red. Configura un timeout para que el pool las recicle automáticamente.
2. Usa application_name para depurar. Define application_name en el connection string para identificar tu aplicación en pg_stat_activity:

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

3. Habilita statement_timeout a nivel sesión. Evita que queries descontrolados consuman recursos:

SET statement_timeout = '10s';

4. Usa COPY para inserts masivos. COPY es 10-100x más rápido que INSERT individuales para datasets grandes:

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. Monitorea la salud del pool. Rastrea conexiones activas, threads en espera y lifetime de conexiones. En HikariCP, usa getHikariPoolMXBean() para exponer métricas.