CRUD con PostgreSQL (pg)

El mismo CRUD que en MySQL pero con PostgreSQL y la librería pg. Las diferencias clave: los parámetros son $1, $2..., existe RETURNING para obtener la fila insertada o actualizada en la misma consulta, y los errores tienen códigos propios (23505 = duplicado).

Ideal si despliegas en Supabase, Neon, Railway, Render o cualquier VPS con Postgres.

Instalación

npm install pg

# Una sola URL de conexión (formato que dan Supabase, Neon, Railway...)
DATABASE_URL=postgres://usuario:password@localhost:5432/mi_tienda
DB_SSL=false

CREATE TABLE productos (
  id          SERIAL PRIMARY KEY,
  nombre      VARCHAR(150) NOT NULL,
  slug        VARCHAR(160) NOT NULL UNIQUE,
  descripcion TEXT,
  precio      NUMERIC(10,2) NOT NULL CHECK (precio >= 0),
  stock       INTEGER NOT NULL DEFAULT 0,
  etiquetas   TEXT[] DEFAULT '{}',
  creado_en   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Archivos

src/db/postgres.js

import pg from 'pg';
import 'dotenv/config';

// NUMERIC llega como texto por precisión; lo convertimos a número (OK para precios)
pg.types.setTypeParser(1700, (valor) => parseFloat(valor));

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false,
  max: 10,
  idleTimeoutMillis: 30_000,
});

pool.on('error', (err) => console.error('Error inesperado en el pool de Postgres', err));

export const query = (texto, params) => pool.query(texto, params);

export async function transaccion(fn) {
  const cliente = await pool.connect();
  try {
    await cliente.query('BEGIN');
    const r = await fn(cliente);
    await cliente.query('COMMIT');
    return r;
  } catch (err) {
    await cliente.query('ROLLBACK');
    throw err;
  } finally {
    cliente.release();
  }
}

src/models/producto.model.js

import { query } from '../db/postgres.js';

const slugify = (t) =>
  t.normalize('NFD').replace(/[̀-ͯ]/g, '').toLowerCase()
   .replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '');

export const Producto = {
  async listar({ limite = 20, offset = 0, q = '' }) {
    // ILIKE = LIKE sin distinguir mayúsculas. COUNT(*) OVER() da el total en la misma consulta
    const { rows } = await query(
      `SELECT id, nombre, slug, precio, stock, COUNT(*) OVER() AS total
       FROM productos
       WHERE ($1::text = '' OR nombre ILIKE '%' || $1::text || '%')
       ORDER BY id DESC
       LIMIT $2 OFFSET $3`,
      [q, limite, offset]
    );
    const total = rows[0] ? Number(rows[0].total) : 0;
    return { data: rows.map(({ total, ...p }) => p), total };
  },

  async buscarPorId(id) {
    const { rows } = await query('SELECT * FROM productos WHERE id = $1', [id]);
    return rows[0] ?? null;
  },

  async crear({ nombre, descripcion = null, precio, stock = 0, etiquetas = [] }) {
    const { rows } = await query(
      `INSERT INTO productos (nombre, slug, descripcion, precio, stock, etiquetas)
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING *`,
      [nombre, slugify(nombre), descripcion, precio, stock, etiquetas]
    );
    return rows[0];
  },

  async actualizar(id, datos) {
    const permitidos = ['nombre', 'descripcion', 'precio', 'stock', 'etiquetas'];
    const campos = Object.keys(datos).filter((k) => permitidos.includes(k));
    if (!campos.length) return this.buscarPorId(id);

    // Genera "nombre = $1, precio = $2" y deja el id como último parámetro
    const set = campos.map((c, i) => `${c} = $${i + 1}`).join(', ');
    const valores = campos.map((c) => datos[c]);
    const { rows } = await query(
      `UPDATE productos SET ${set} WHERE id = $${campos.length + 1} RETURNING *`,
      [...valores, id]
    );
    return rows[0] ?? null;
  },

  async eliminar(id) {
    const { rowCount } = await query('DELETE FROM productos WHERE id = $1', [id]);
    return rowCount > 0;
  },
};

src/controllers/productos.controller.js

import { Producto } from '../models/producto.model.js';
import { asyncHandler } from '../utils/asyncHandler.js';

export const listar = asyncHandler(async (req, res) => {
  const pagina = Math.max(1, parseInt(req.query.pagina) || 1);
  const limite = Math.min(100, Math.max(1, parseInt(req.query.limite) || 20));
  const { data, total } = await Producto.listar({
    limite, offset: (pagina - 1) * limite, q: String(req.query.q ?? '').trim(),
  });
  res.json({ data, meta: { pagina, limite, total, paginas: Math.ceil(total / limite) } });
});

export const obtener = asyncHandler(async (req, res) => {
  const p = await Producto.buscarPorId(req.params.id);
  p ? res.json({ data: p }) : res.status(404).json({ error: 'Producto no encontrado' });
});

export const crear = asyncHandler(async (req, res) => {
  try {
    const p = await Producto.crear(req.body);
    res.status(201).json({ data: p });
  } catch (err) {
    // Códigos de error de PostgreSQL más comunes
    if (err.code === '23505') return res.status(409).json({ error: 'Ya existe un producto con ese nombre' });
    if (err.code === '23514') return res.status(422).json({ error: 'Valor fuera de rango (precio negativo)' });
    throw err;
  }
});

export const actualizar = asyncHandler(async (req, res) => {
  const p = await Producto.actualizar(req.params.id, req.body);
  p ? res.json({ data: p }) : res.status(404).json({ error: 'Producto no encontrado' });
});

export const eliminar = asyncHandler(async (req, res) => {
  (await Producto.eliminar(req.params.id))
    ? res.status(204).end()
    : res.status(404).json({ error: 'Producto no encontrado' });
});

Las rutas son idénticas a las del CRUD con MySQL y asyncHandler está en ese mismo snippet.

Diferencias MySQL ↔ PostgreSQL

Concepto	MySQL (mysql2)	PostgreSQL (pg)
Parámetros	?	$1, $2, $3
Id insertado	result.insertId	RETURNING id
Filas afectadas	affectedRows	rowCount
Resultado	const [filas] = await pool.execute()	const { rows } = await pool.query()
Autoincremento	AUTO_INCREMENT	SERIAL o GENERATED ALWAYS AS IDENTITY
Búsqueda sin mayúsculas	LIKE (según collation)	ILIKE
Duplicado	ER_DUP_ENTRY	23505

Upsert (insertar o actualizar)

// Si el slug ya existe, actualiza precio y stock
await query(
  `INSERT INTO productos (nombre, slug, precio, stock) VALUES ($1, $2, $3, $4)
   ON CONFLICT (slug) DO UPDATE SET precio = EXCLUDED.precio, stock = EXCLUDED.stock
   RETURNING *`,
  ['Taza', 'taza', 120, 30]
);

Buena práctica

En producción con Supabase/Neon usa el pooler que te dan (puerto 6543 en Supabase) y DB_SSL=true. Las bases serverless cierran conexiones inactivas, por eso es importante el manejador pool.on('error').

Error común

Reutilizar $1 creyendo que es posicional como ?. En Postgres sí puedes repetir $1 varias veces en la misma consulta (como en el filtro ILIKE de arriba), pero el número de parámetros del arreglo debe coincidir con el número más alto usado.