Conectar a PostgreSQL

Visión General

PostgreSQL es la base de datos relacional open-source más popular. Conectarse de forma fiable requiere manejar connection strings, SSL y pools de conexiones. A continuacion se muestra como cómo conectar y consultar PostgreSQL en Python, JavaScript y Java.

Cuándo Usar

• For alternatives, see Connect to MySQL.

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

Solución

Python

import psycopg2
from psycopg2.extras import RealDictCursor

# Conexión básica
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="user",
    password="pass",
    sslmode="require"
)

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: 'localhost',
    database: 'mydb',
    user: 'user',
    password: 'pass',
    ssl: { rejectUnauthorized: false },
    max: 20
});

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

Java

import java.sql.*;

public class PostgresConnect {
    public Connection connect() throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb?sslmode=require";
        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 usan consultas preparadas (parameterized queries) para prevenir inyección SQL. El ejemplo de Python usa psycopg2, el adaptador estándar de PostgreSQL. El de JavaScript usa pg con un pool de conexiones, que reutiliza conexiones entre peticiones. El de Java usa JDBC, la API estándar de Java para bases de datos, con try-with-resources para cerrar conexiones automáticamente.

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 siempre pools de conexiones en producción en lugar de crear conexiones por petición
2. Almacena credenciales en variables de entorno o gestores de secretos, nunca en código
3. Usa SSL (sslmode=require) para todas las conexiones de producción
4. Prefiere consultas preparadas sobre concatenación de strings para valores en vivo
5. Cierra cursores y conexiones explícitamente o usa context managers

Errores Comunes

1. Hardcodear credenciales de base de datos en el código fuente
2. Crear una nueva conexión por cada consulta en lugar de usar un pool
3. Olvidar cerrar conexiones, causando errores de “demasiadas conexiones”
4. Desactivar la verificación SSL en producción (sslmode=disable)
5. Usar f-strings de Python o template literals de JS para consultas SQL

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

Buenas prácticas adicionales

6. Configura idle_timeout en las conexiones. Las conexiones inactivas pueden volverse stale después de un reinicio de base de datos o problema de red. Configura un timeout para reciclarlas automáticamente.
7. Usa application_name para debugging. Configura 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"
)

8. Habilita statement_timeout a nivel sesión. Previene que queries descontrolados consuman recursos:

SET statement_timeout = '10s';

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

import io

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

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

10. Monitorea la salud del pool. Rastrea conexiones activas, threads en espera y lifetime de conexiones. En HikariCP, usa getHikariPoolMXBean() para exponer métricas.