CRUD con MySQL (mysql2)

CRUD completo de productos con mysql2/promise: pool de conexiones, consultas preparadas, transacciones y respuestas con los códigos HTTP correctos. Se integra en la estructura del servidor Express base.

Instalación

npm install mysql2

DB_HOST=127.0.0.1
DB_PORT=3306
DB_USER=root
DB_PASS=
DB_NAME=mi_tienda

CREATE TABLE productos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(150) NOT NULL,
  descripcion TEXT NULL,
  precio DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Archivos

src/db/mysql.js

import mysql from 'mysql2/promise';
import 'dotenv/config';

// Un pool reutiliza conexiones: mucho más eficiente que abrir una por petición
export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT ?? 3306),
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  charset: 'utf8mb4',
  decimalNumbers: true,   // DECIMAL como número, no como texto
  dateStrings: false,
  namedPlaceholders: true // permite :nombre además de ?
});

/** Atajo: devuelve sólo las filas */
export async function query(sql, params = []) {
  const [filas] = await pool.execute(sql, params);
  return filas;
}

/** Ejecuta una función dentro de una transacción */
export async function transaccion(fn) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    const resultado = await fn(conn);
    await conn.commit();
    return resultado;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release(); // ¡siempre devolver la conexión al pool!
  }
}

// Comprueba la conexión al arrancar
export async function probarConexion() {
  await pool.query('SELECT 1');
  console.log('✅ MySQL conectado');
}

src/utils/asyncHandler.js

// Express 4 no captura errores de funciones async: este envoltorio los manda a next()
export const asyncHandler = (fn) => (req, res, next) =>
  Promise.resolve(fn(req, res, next)).catch(next);

src/models/producto.model.js

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

export const Producto = {
  async listar({ limite = 20, offset = 0, q = '' } = {}) {
    const filtro = q ? 'WHERE nombre LIKE ?' : '';
    const params = q ? [`%${q}%`] : [];
    // LIMIT/OFFSET van como números ya validados
    return query(
      `SELECT id, nombre, precio, stock FROM productos ${filtro}
       ORDER BY id DESC LIMIT ${Number(limite)} OFFSET ${Number(offset)}`,
      params
    );
  },

  async contar(q = '') {
    const [fila] = await query(
      `SELECT COUNT(*) AS total FROM productos ${q ? 'WHERE nombre LIKE ?' : ''}`,
      q ? [`%${q}%`] : []
    );
    return fila.total;
  },

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

  async crear({ nombre, descripcion = null, precio, stock = 0 }) {
    const r = await query(
      'INSERT INTO productos (nombre, descripcion, precio, stock) VALUES (?, ?, ?, ?)',
      [nombre, descripcion, precio, stock]
    );
    return this.buscarPorId(r.insertId);
  },

  async actualizar(id, datos) {
    // Actualización parcial: sólo los campos enviados (lista blanca)
    const permitidos = ['nombre', 'descripcion', 'precio', 'stock'];
    const campos = Object.keys(datos).filter((k) => permitidos.includes(k));
    if (!campos.length) return this.buscarPorId(id);

    const set = campos.map((c) => `${c} = ?`).join(', ');
    const valores = campos.map((c) => datos[c]);
    const r = await query(`UPDATE productos SET ${set} WHERE id = ?`, [...valores, id]);
    return r.affectedRows ? this.buscarPorId(id) : null;
  },

  async eliminar(id) {
    const r = await query('DELETE FROM productos WHERE id = ?', [id]);
    return r.affectedRows > 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 q = (req.query.q ?? '').toString().trim();

  const [data, total] = await Promise.all([
    Producto.listar({ limite, offset: (pagina - 1) * limite, q }),
    Producto.contar(q),
  ]);
  res.json({ data, meta: { pagina, limite, total, paginas: Math.ceil(total / limite) } });
});

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

export const crear = asyncHandler(async (req, res) => {
  const { nombre, precio } = req.body;
  if (!nombre?.trim() || typeof precio !== 'number' || precio < 0) {
    return res.status(422).json({ error: 'nombre y precio (número positivo) son obligatorios' });
  }
  const producto = await Producto.crear(req.body);
  res.status(201).location(`/api/productos/${producto.id}`).json({ data: producto });
});

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

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

src/routes/productos.routes.js

import { Router } from 'express';
import * as ctrl from '../controllers/productos.controller.js';

const router = Router();

router.get('/', ctrl.listar);
router.get('/:id', ctrl.obtener);
router.post('/', ctrl.crear);
router.patch('/:id', ctrl.actualizar);
router.delete('/:id', ctrl.eliminar);

export default router;

Y en server.js, verifica la base de datos antes de escuchar:

import app from './app.js';
import { probarConexion } from './db/mysql.js';

await probarConexion(); // top-level await (ESM)
app.listen(process.env.PORT, () => console.log('API lista'));

Transacción: crear un pedido descontando stock

import { transaccion } from '../db/mysql.js';

export async function crearPedido(usuarioId, items) {
  return transaccion(async (conn) => {
    let total = 0;
    for (const { productoId, cantidad } of items) {
      // FOR UPDATE bloquea la fila hasta el commit (evita vender stock que no existe)
      const [[p]] = await conn.execute('SELECT precio, stock FROM productos WHERE id = ? FOR UPDATE', [productoId]);
      if (!p || p.stock < cantidad) throw Object.assign(new Error('Stock insuficiente'), { status: 409 });
      total += p.precio * cantidad;
      await conn.execute('UPDATE productos SET stock = stock - ? WHERE id = ?', [cantidad, productoId]);
    }
    const [r] = await conn.execute('INSERT INTO pedidos (usuario_id, total) VALUES (?, ?)', [usuarioId, total]);
    return { pedidoId: r.insertId, total };
  });
}

Error común

Construir SQL con template literals y datos del usuario: `WHERE nombre = '${req.query.q}'`. Es inyección SQL directa. Los valores van siempre en el arreglo de parámetros (?); sólo interpola nombres de columna que vengan de una lista blanca.

execute vs query

pool.execute() usa consultas preparadas reales en el servidor (más seguras y cacheadas). pool.query() escapa los valores en el cliente; úsalo sólo para SQL sin parámetros o con sintaxis que execute no soporta.