Conexión desde Node y PHP

Llegó el momento de conectar PostgreSQL con tu aplicación. En esta lección usarás node-postgres (pg), el driver más usado en Node.js; Prisma, el ORM favorito del ecosistema TypeScript y Next.js; y PDO en PHP. En todos verás consultas parametrizadas, transacciones, variables de entorno y conexiones con SSL.

Concepto: la cadena de conexión

PostgreSQL usa normalmente una URL de conexión (connection string), que es lo que te dan Supabase, Neon, Railway o Render:

txt

postgresql://usuario:contraseña@host:puerto/base_de_datos?sslmode=require
└── esquema ─┘└─ credenciales ─┘└── servidor ──┘└── base ────┘└── opciones ─┘

postgresql://tienda_app:secreto@localhost:5432/tienda

Caracteres especiales en la contraseña

Si la contraseña tiene @, :, /, # o %, debe ir codificada en la URL (@ → %40, # → %23). Una contraseña ab@12# se escribe ab%4012%23. Si no, el driver interpreta mal la URL y verás errores de host o autenticación.

Guárdala en una variable de entorno, nunca en el código:

env

# .env (en .gitignore)
DATABASE_URL="postgresql://tienda_app:secreto@localhost:5432/tienda"

Node.js con pg (node-postgres)

Instalación y pool

bash

npm init -y
npm install pg dotenv

// db.js
import 'dotenv/config';
import pg from 'pg';

const { Pool, types } = pg;

// NUMERIC (precios) llega como string para no perder precisión.
// Si prefieres number para precios normales, descomenta:
// types.setTypeParser(1700, (valor) => parseFloat(valor));

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                       // conexiones máximas en el pool
  idleTimeoutMillis: 30000,      // cerrar conexiones inactivas tras 30 s
  // ssl: { rejectUnauthorized: true }   // en la nube (ver abajo)
});

pool.on('error', (err) => {
  console.error('Error inesperado en una conexión inactiva', err);
});

(Usa "type": "module" en package.json.)

Consultas parametrizadas: $1, $2...

PostgreSQL usa marcadores numerados ($1, $2) en lugar de ?:

// consultas.js
import { pool } from './db.js';

// SELECT con parámetros
const { rows } = await pool.query(
  'SELECT id, nombre, precio FROM productos WHERE precio < $1 ORDER BY precio',
  [500]
);
console.table(rows);

// Una fila
const res = await pool.query('SELECT id, nombre FROM clientes WHERE email = $1', ['ana@correo.com']);
const cliente = res.rows[0] ?? null;

// INSERT con RETURNING: obtienes el id (y lo que quieras) sin otra consulta
const { rows: [nuevo] } = await pool.query(
  `INSERT INTO productos (nombre, categoria_id, precio, stock)
   VALUES ($1, $2, $3, $4)
   RETURNING id, nombre, precio`,
  ['Bocina portátil', 1, 749.0, 12]
);
console.log(nuevo);   // { id: 9, nombre: 'Bocina portátil', precio: '749.00' }

// UPDATE / DELETE: rowCount indica filas afectadas
const upd = await pool.query('UPDATE productos SET precio = $1 WHERE id = $2', [699, nuevo.id]);
console.log(upd.rowCount);

// Listas: un array como parámetro con = ANY($1)
const { rows: varios } = await pool.query(
  'SELECT id, nombre FROM productos WHERE id = ANY($1::int[])',
  [[1, 4, 7]]
);

// JSONB: pasa el objeto con JSON.stringify
await pool.query('UPDATE productos SET atributos = $1 WHERE id = $2', [
  JSON.stringify({ color: 'negro', garantia_meses: 12 }),
  1
]);

Correspondencia de tipos:

PostgreSQL	JavaScript con `pg`
`INTEGER`, `REAL`	`number`
`BIGINT`	`string` (puede exceder `Number.MAX_SAFE_INTEGER`)
`NUMERIC`	`string` (precisión exacta)
`BOOLEAN`	`boolean`
`TEXT`, `UUID`	`string`
`DATE`, `TIMESTAMPTZ`	`Date`
`JSONB`	objeto / array (ya parseado)
`TEXT[]`	`Array`

Nunca interpoles valores

// ¡VULNERABLE A INYECCIÓN SQL!
pool.query(`SELECT * FROM clientes WHERE email = '${req.query.email}'`);

Usa siempre $1, $2... con el arreglo de valores. Para identificadores dinámicos (nombre de columna para ordenar), valida contra una lista blanca o usa pg-format con %I.

Transacciones

Una transacción debe ejecutarse en una sola conexión: pide un cliente al pool.

// pedidos.js
import { pool } from './db.js';

export async function crearPedido(clienteId, items) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const { rows: [pedido] } = await client.query(
      "INSERT INTO pedidos (cliente_id, fecha, estado) VALUES ($1, CURRENT_DATE, 'pendiente') RETURNING id",
      [clienteId]
    );

    for (const { productoId, cantidad } of items) {
      const { rowCount } = await client.query(
        'UPDATE productos SET stock = stock - $1 WHERE id = $2 AND stock >= $1',
        [cantidad, productoId]
      );
      if (rowCount === 0) throw new Error(`Stock insuficiente del producto ${productoId}`);

      await client.query(
        `INSERT INTO detalle_pedido (pedido_id, producto_id, cantidad, precio_unitario)
         SELECT $1, id, $2, precio FROM productos WHERE id = $3`,
        [pedido.id, cantidad, productoId]
      );
    }

    await client.query('COMMIT');
    return pedido.id;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();   // ¡siempre devolver la conexión!
  }
}

Errores de PostgreSQL

Los errores traen un código SQLSTATE en err.code:

Código	Significado	Respuesta HTTP sugerida
`23505`	`unique_violation` (duplicado)	409 Conflict
`23503`	`foreign_key_violation`	409 / 400
`23514`	`check_violation`	422
`23502`	`not_null_violation`	422
`22P02`	Texto con formato inválido (ej. UUID mal formado)	400
`40P01`	Deadlock detectado	Reintentar

try {
  await pool.query('INSERT INTO clientes (nombre, email, fecha_registro) VALUES ($1, $2, CURRENT_DATE)', [
    'Ana', 'ana@correo.com'
  ]);
} catch (err) {
  if (err.code === '23505') {
    console.log('Ese correo ya está registrado');   // err.constraint dice qué restricción
  } else {
    throw err;
  }
}

Prisma

Prisma es un ORM con tipado automático para TypeScript: describes tu modelo en un archivo schema.prisma, genera las migraciones SQL y te da un cliente con autocompletado.

Configuración

bash

npm install prisma --save-dev
npm install @prisma/client
npx prisma init --datasource-provider postgresql

Esto crea prisma/schema.prisma y agrega DATABASE_URL a .env.

El esquema

prisma/schema.prisma (Prisma usa su propio lenguaje; aquí lo mostramos como texto):

txt

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Categoria {
  id        Int        @id @default(autoincrement())
  nombre    String     @unique @db.VarChar(50)
  productos Producto[]

  @@map("categorias")                       // nombre real de la tabla
}

model Producto {
  id          Int             @id @default(autoincrement())
  nombre      String          @db.VarChar(100)
  precio      Decimal         @db.Decimal(10, 2)
  stock       Int             @default(0)
  atributos   Json            @default("{}")
  etiquetas   String[]
  categoriaId Int?            @map("categoria_id")
  categoria   Categoria?      @relation(fields: [categoriaId], references: [id], onDelete: SetNull)
  detalles    DetallePedido[]
  creadoEn    DateTime        @default(now()) @map("creado_en") @db.Timestamptz
  actualizadoEn DateTime      @updatedAt @map("actualizado_en") @db.Timestamptz

  @@index([categoriaId])
  @@map("productos")
}

model Cliente {
  id      Int      @id @default(autoincrement())
  nombre  String   @db.VarChar(100)
  email   String   @unique @db.VarChar(150)
  pedidos Pedido[]

  @@map("clientes")
}

enum EstadoPedido {
  pendiente
  pagado
  enviado
  entregado
  cancelado
}

model Pedido {
  id        Int             @id @default(autoincrement())
  uuid      String          @unique @default(uuid()) @db.Uuid
  clienteId Int             @map("cliente_id")
  cliente   Cliente         @relation(fields: [clienteId], references: [id], onDelete: Restrict)
  estado    EstadoPedido    @default(pendiente)
  fecha     DateTime        @default(now()) @db.Timestamptz
  detalles  DetallePedido[]

  @@index([clienteId, fecha])
  @@map("pedidos")
}

model DetallePedido {
  pedidoId       Int      @map("pedido_id")
  productoId     Int      @map("producto_id")
  cantidad       Int
  precioUnitario Decimal  @map("precio_unitario") @db.Decimal(10, 2)
  pedido         Pedido   @relation(fields: [pedidoId], references: [id], onDelete: Cascade)
  producto       Producto @relation(fields: [productoId], references: [id], onDelete: Restrict)

  @@id([pedidoId, productoId])
  @@index([productoId])
  @@map("detalle_pedido")
}

@map y @@map

Usa nombres en PascalCase/camelCase en el código (Producto, categoriaId) y @map/@@map para que las tablas y columnas reales queden en minúsculas con guion bajo (productos, categoria_id). Así evitas el problema de los identificadores con comillas que viste en la lección 1.

Migraciones

bash

# Crea la migración SQL a partir del esquema y la aplica en desarrollo
npx prisma migrate dev --name inicial

# En producción: aplica las migraciones pendientes (sin generar nuevas)
npx prisma migrate deploy

# Si ya tienes una base existente: genera el schema.prisma desde ella
npx prisma db pull

# Explorador visual de datos
npx prisma studio

Las migraciones quedan como archivos .sql en prisma/migrations/: revísalas y versiónalas en Git.

El cliente: CRUD

// src/db.ts — una sola instancia (importante en Next.js para no agotar conexiones)
import { PrismaClient } from '@prisma/client';

const globalParaPrisma = globalThis as unknown as { prisma?: PrismaClient };

export const prisma = globalParaPrisma.prisma ?? new PrismaClient();

if (process.env.NODE_ENV !== 'production') globalParaPrisma.prisma = prisma;

// src/ejemplos.ts
import { prisma } from './db';

// Crear
const producto = await prisma.producto.create({
  data: {
    nombre: 'Bocina portátil',
    precio: '749.00',                       // Decimal: usa string para exactitud
    stock: 12,
    etiquetas: ['audio', 'inalambrico'],
    atributos: { color: 'negro', resistente_agua: true },
    categoria: { connect: { id: 1 } }
  }
});

// Leer con filtros, orden, paginación y relaciones
const baratos = await prisma.producto.findMany({
  where: { precio: { lt: 500 }, stock: { gt: 0 } },
  include: { categoria: true },             // JOIN con la categoría
  orderBy: { precio: 'asc' },
  take: 10,
  skip: 0
});

// Búsqueda sin distinguir mayúsculas (ILIKE)
const encontrados = await prisma.producto.findMany({
  where: { nombre: { contains: 'audí', mode: 'insensitive' } },
  select: { id: true, nombre: true, precio: true }
});

// Filtros en arrays y JSON
const gamer = await prisma.producto.findMany({ where: { etiquetas: { has: 'gamer' } } });
const negros = await prisma.producto.findMany({
  where: { atributos: { path: ['color'], equals: 'negro' } }
});

// Actualizar
await prisma.producto.update({
  where: { id: producto.id },
  data: { stock: { decrement: 1 } }
});

// Eliminar
await prisma.producto.delete({ where: { id: producto.id } });

// Agregaciones
const resumen = await prisma.producto.aggregate({
  _count: true,
  _avg: { precio: true },
  _sum: { stock: true }
});

// Pedidos de un cliente con su detalle y productos (sin N+1)
const pedidos = await prisma.pedido.findMany({
  where: { clienteId: 1 },
  include: { detalles: { include: { producto: { select: { nombre: true } } } } },
  orderBy: { fecha: 'desc' }
});

Transacciones en Prisma

// Transacción interactiva: todo o nada
export async function crearPedidoPrisma(clienteId: number, items: { productoId: number; cantidad: number }[]) {
  return prisma.$transaction(async (tx) => {
    const pedido = await tx.pedido.create({ data: { clienteId } });

    for (const { productoId, cantidad } of items) {
      // Descuento condicional atómico
      const { count } = await tx.producto.updateMany({
        where: { id: productoId, stock: { gte: cantidad } },
        data: { stock: { decrement: cantidad } }
      });
      if (count === 0) throw new Error(`Stock insuficiente del producto ${productoId}`);

      const producto = await tx.producto.findUniqueOrThrow({ where: { id: productoId } });
      await tx.detallePedido.create({
        data: { pedidoId: pedido.id, productoId, cantidad, precioUnitario: producto.precio }
      });
    }
    return pedido;
  });
}

SQL directo cuando lo necesitas

Para consultas complejas (funciones de ventana, búsqueda de texto completo) usa SQL crudo, que sigue siendo seguro con tagged templates:

const texto = 'audifonos inalambricos';
const resultados = await prisma.$queryRaw<{ id: number; nombre: string; rank: number }[]>`
  SELECT id, nombre, ts_rank(busqueda, q) AS rank
  FROM productos, websearch_to_tsquery('spanish', ${texto}) AS q
  WHERE busqueda @@ q
  ORDER BY rank DESC
  LIMIT 10
`;

Los valores ${texto} se envían como parámetros, no se concatenan. Nunca uses $queryRawUnsafe con datos del usuario.

	`pg`	Prisma
Control del SQL	Total	Generado (con `$queryRaw` como escape)
Tipado TypeScript	Manual	Automático
Migraciones	Aparte (node-pg-migrate, etc.)	Integradas
Curva de aprendizaje	SQL	Su API + esquema
Rendimiento	Máximo	Muy bueno; cuidado con `include` profundos

PHP con PDO

php

<?php
// db.php
declare(strict_types=1);

function conectar(): PDO
{
    static $pdo = null;
    if ($pdo) return $pdo;

    $host = getenv('DB_HOST') ?: '127.0.0.1';
    $port = getenv('DB_PORT') ?: '5432';
    $name = getenv('DB_DATABASE') ?: 'tienda';
    $user = getenv('DB_USERNAME') ?: 'tienda_app';
    $pass = getenv('DB_PASSWORD') ?: '';

    // sslmode=require para servicios en la nube; options fija el search_path
    $dsn = "pgsql:host=$host;port=$port;dbname=$name;sslmode=prefer;options='--search_path=public'";

    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    return $pdo;
}

Requiere la extensión pdo_pgsql (en php.ini: extension=pdo_pgsql; en Ubuntu: sudo apt install php-pgsql).

php

<?php
require __DIR__ . '/db.php';
$pdo = conectar();

// PDO usa ? o :nombre (traduce internamente a $1, $2)
$stmt = $pdo->prepare('SELECT id, nombre, precio FROM productos WHERE precio < :max ORDER BY precio');
$stmt->execute(['max' => 500]);
$productos = $stmt->fetchAll();

// INSERT ... RETURNING (en PostgreSQL no uses lastInsertId sin nombre de secuencia)
$stmt = $pdo->prepare(
    'INSERT INTO productos (nombre, categoria_id, precio, stock) VALUES (?, ?, ?, ?) RETURNING id'
);
$stmt->execute(['Bocina portátil', 1, 749.00, 12]);
$nuevoId = (int) $stmt->fetchColumn();

// Booleanos: indica el tipo explícitamente
$stmt = $pdo->prepare('UPDATE productos SET activo = :activo WHERE id = :id');
$stmt->bindValue(':activo', false, PDO::PARAM_BOOL);
$stmt->bindValue(':id', $nuevoId, PDO::PARAM_INT);
$stmt->execute();

// JSONB
$stmt = $pdo->prepare('UPDATE productos SET atributos = :attr::jsonb WHERE id = :id');
$stmt->execute(['attr' => json_encode(['color' => 'negro']), 'id' => 1]);

// Transacción
$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE productos SET stock = stock - 1 WHERE id = ? AND stock >= 1')->execute([3]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

// Errores: SQLSTATE en $e->getCode()
try {
    $pdo->prepare("INSERT INTO clientes (nombre, email, fecha_registro) VALUES (?, ?, CURRENT_DATE)")
        ->execute(['Ana', 'ana@correo.com']);
} catch (PDOException $e) {
    if ($e->getCode() === '23505') {
        echo 'Ese correo ya está registrado';
    }
}

En Laravel, basta configurar .env:

env

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=tienda
DB_USERNAME=tienda_app
DB_PASSWORD=secreto
# o DB_URL=postgresql://... en Laravel 11+

SSL en la nube

Los servicios en la nube exigen conexiones cifradas:

pgPrismaPDO

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,   // ...?sslmode=require
  ssl: { rejectUnauthorized: true }             // verifica el certificado del servidor
  // Si el proveedor usa un CA propio: ssl: { ca: fs.readFileSync('ca.pem').toString() }
});

env

DATABASE_URL="postgresql://usuario:clave@host:5432/db?sslmode=require"

php

<?php
$dsn = "pgsql:host=$host;port=5432;dbname=$db;sslmode=require";

rejectUnauthorized: false

Muchos tutoriales usan ssl: { rejectUnauthorized: false } para "hacer que funcione". Eso cifra la conexión pero no verifica el servidor, dejando la puerta abierta a ataques de intermediario. Úsalo solo para pruebas; en producción configura el certificado CA del proveedor.

Buenas prácticas

Checklist

DATABASE_URL en variables de entorno; .env fuera de Git.
Rol de aplicación sin superusuario (Esquemas y roles).
Un pool compartido (o una sola instancia de PrismaClient); en serverless, usa el pooler del proveedor.
Parámetros $1... / ? siempre; $queryRaw con tagged templates, nunca Unsafe con entradas.
RETURNING en lugar de consultas extra para obtener ids.
Transacciones con try / catch / finally y release().
Traduce los códigos SQLSTATE (23505, 23503...) a respuestas claras.
sslmode=require y verificación de certificado en producción.

Ejercicios

Con pg, escribe una función buscarClientes(texto) que busque por nombre sin distinguir mayúsculas (ILIKE) y devuelva máximo 10 resultados.
Con Prisma, obtén las categorías con el número de productos de cada una (pista: _count).
Con PDO, inserta un pedido y su detalle en una transacción usando RETURNING.
¿Por qué pg devuelve los precios NUMERIC como texto y qué opciones tienes?

Ver solución

// 1
export async function buscarClientes(texto) {
  const { rows } = await pool.query(
    "SELECT id, nombre, email FROM clientes WHERE nombre ILIKE '%' || $1 || '%' ORDER BY nombre LIMIT 10",
    [texto]
  );
  return rows;
}

// 2
const categorias = await prisma.categoria.findMany({
  select: { nombre: true, _count: { select: { productos: true } } },
  orderBy: { nombre: 'asc' }
});
// [{ nombre: 'Deportes', _count: { productos: 0 } }, { nombre: 'Electrónica', _count: { productos: 3 } }, ...]

php

<?php
// 3
$pdo->beginTransaction();
try {
    $stmt = $pdo->prepare(
        "INSERT INTO pedidos (cliente_id, fecha, estado) VALUES (?, CURRENT_DATE, 'pendiente') RETURNING id"
    );
    $stmt->execute([5]);
    $pedidoId = (int) $stmt->fetchColumn();

    $pdo->prepare(
        'INSERT INTO detalle_pedido (pedido_id, producto_id, cantidad, precio_unitario)
         SELECT ?, id, ?, precio FROM productos WHERE id = ?'
    )->execute([$pedidoId, 2, 7]);

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Porque los number de JavaScript son de punto flotante y no pueden representar con exactitud todos los decimales (ni enteros muy grandes). pg prefiere no perder precisión. Opciones: dejarlo como texto y usar una librería decimal (decimal.js) para cálculos de dinero, convertirlo con types.setTypeParser(1700, parseFloat) si los valores son precios normales, o trabajar en centavos con enteros.

Resumen

PostgreSQL usa URLs de conexión (postgresql://usuario:clave@host:5432/db); codifica caracteres especiales.
pg: Pool, marcadores $1, $2, RETURNING, rowCount; transacciones con un cliente dedicado y release().
BIGINT y NUMERIC llegan como texto por precisión; decide conscientemente cómo convertirlos.
Prisma: esquema declarativo con @map, migraciones (migrate dev / migrate deploy), cliente tipado, $transaction y $queryRaw seguro.
PDO con pgsql: en el DSN; usa RETURNING en vez de lastInsertId.
En la nube: SSL obligatorio y verificación del certificado.

Conexión desde Node y PHP ​

Concepto: la cadena de conexión ​

Node.js con pg (node-postgres) ​

Instalación y pool ​

Consultas parametrizadas: $1, $2... ​

Transacciones ​

Errores de PostgreSQL ​

Prisma ​

Configuración ​

El esquema ​

Migraciones ​

El cliente: CRUD ​

Transacciones en Prisma ​

SQL directo cuando lo necesitas ​

PHP con PDO ​

SSL en la nube ​

Buenas prácticas ​

Ejercicios ​

Resumen ​

Conexión desde Node y PHP

Concepto: la cadena de conexión

Node.js con pg (node-postgres)

Instalación y pool

Consultas parametrizadas: $1, $2...

Transacciones

Errores de PostgreSQL

Prisma

Configuración

El esquema

Migraciones

El cliente: CRUD

Transacciones en Prisma

SQL directo cuando lo necesitas

PHP con PDO

SSL en la nube

Buenas prácticas

Ejercicios

Resumen