Procedimientos almacenados en Node.js

Los procedimientos almacenados son bloques de código SQL que se guardan en el servidor MySQL y se pueden invocar desde cualquier aplicación cliente. Utilizar procedimientos almacenados desde Node.js ofrece varias ventajas: centralizar la lógica de negocio en la base de datos, reducir el tráfico de red al enviar solo la llamada en lugar de múltiples consultas, y aprovechar las optimizaciones del motor de MySQL. En este artículo aprenderás a crear y llamar procedimientos almacenados con parámetros de entrada, salida y bidireccionales.

Requisitos previos

Necesitas un pool de conexiones configurado y permisos para crear procedimientos almacenados en tu base de datos. Primero, crea los procedimientos que usaremos en los ejemplos:

USE tienda;
 
-- Procedimiento con parámetro IN
DELIMITER //
CREATE PROCEDURE obtener_productos_por_categoria(IN p_categoria VARCHAR(50))
BEGIN
    SELECT id, nombre, precio, stock
    FROM productos
    WHERE categoria = p_categoria AND activo = 1
    ORDER BY precio DESC;
END //
DELIMITER ;
 
-- Procedimiento con parámetros IN y OUT
DELIMITER //
CREATE PROCEDURE contar_productos(
    IN p_categoria VARCHAR(50),
    OUT p_total INT,
    OUT p_precio_promedio DECIMAL(10, 2)
)
BEGIN
    SELECT COUNT(*), AVG(precio)
    INTO p_total, p_precio_promedio
    FROM productos
    WHERE categoria = p_categoria AND activo = 1;
END //
DELIMITER ;
 
-- Procedimiento con parámetro INOUT
DELIMITER //
CREATE PROCEDURE aplicar_descuento(
    IN p_categoria VARCHAR(50),
    INOUT p_porcentaje DECIMAL(5, 2)
)
BEGIN
    DECLARE v_productos_afectados INT;
 
    UPDATE productos
    SET precio = precio * (1 - p_porcentaje / 100)
    WHERE categoria = p_categoria AND activo = 1;
 
    SET v_productos_afectados = ROW_COUNT();
    SET p_porcentaje = v_productos_afectados;
END //
DELIMITER ;
 
-- Procedimiento con múltiples conjuntos de resultados
DELIMITER //
CREATE PROCEDURE resumen_tienda()
BEGIN
    SELECT categoria, COUNT(*) AS total_productos, SUM(stock) AS total_stock
    FROM productos WHERE activo = 1
    GROUP BY categoria ORDER BY total_productos DESC;
 
    SELECT nombre, categoria, precio
    FROM productos WHERE activo = 1
    ORDER BY precio DESC LIMIT 5;
 
    SELECT COUNT(*) AS total_productos, SUM(stock) AS total_unidades,
           AVG(precio) AS precio_promedio, MIN(precio) AS precio_minimo,
           MAX(precio) AS precio_maximo
    FROM productos WHERE activo = 1;
END //
DELIMITER ;

Código completo

Este ejemplo llama a un procedimiento que recibe una categoría y devuelve los productos correspondientes:

const mysql = require('mysql2/promise');
 
async function llamarProcedimiento() {
  const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'tu_contraseña',
    database: 'tienda'
  });
 
  const [results] = await pool.execute(
    'CALL obtener_productos_por_categoria(?)',
    ['Periféricos']
  );
 
  // El primer elemento contiene las filas del resultado
  const productos = results[0];
 
  console.log(`Productos en Periféricos: ${productos.length}`);
  productos.forEach(p => {
    console.log(`  ${p.nombre} - $${p.precio} (${p.stock} en stock)`);
  });
 
  await pool.end();
}
 
llamarProcedimiento().catch(console.error);

Salida esperada:

Productos en Periféricos: 3
  Teclado Mecánico Corsair K70 - $2299.50 (80 en stock)
  Mouse Logitech MX Master - $1599.00 (150 en stock)
  Webcam Logitech C920 - $1299.00 (200 en stock)

Explicación paso a paso

Cuando llamas a un procedimiento almacenado con CALL, el resultado tiene una estructura diferente a la de una consulta SELECT simple. El método execute() devuelve un array donde cada elemento corresponde a un conjunto de resultados del procedimiento. MySQL también añade un elemento adicional al final con metadatos del procedimiento (un objeto ResultSetHeader).

Para un procedimiento que ejecuta un solo SELECT, results[0] contiene las filas y results[1] contiene los metadatos del procedimiento. Es importante acceder al índice correcto para obtener los datos que necesitas.

Parámetros OUT

Los parámetros de salida en MySQL requieren usar variables de sesión. El flujo consiste en llamar al procedimiento, que asigna valores a las variables de salida, y luego ejecutar una consulta para leer esas variables:

async function contarPorCategoria(categoria) {
  const connection = await pool.getConnection();
 
  try {
    await connection.execute(
      'CALL contar_productos(?, @total, @promedio)',
      [categoria]
    );
 
    const [rows] = await connection.execute(
      'SELECT @total AS total, @promedio AS precio_promedio'
    );
 
    const resultado = rows[0];
    console.log(`Categoría: ${categoria}`);
    console.log(`Total de productos: ${resultado.total}`);
    console.log(`Precio promedio: $${resultado.precio_promedio}`);
 
    return resultado;
  } finally {
    connection.release();
  }
}
 
await contarPorCategoria('Periféricos');

Salida esperada:

Categoría: Periféricos
Total de productos: 3
Precio promedio: $1732.50

Es fundamental usar getConnection() para obtener una conexión específica del pool, porque las variables de sesión (@variable) son locales a cada conexión. Si usaras el pool directamente, la segunda consulta podría ejecutarse en una conexión diferente donde las variables no existen.

Parámetros INOUT

Los parámetros INOUT funcionan como entrada y salida a la vez. Se manejan de forma similar a los OUT, usando variables de sesión:

async function aplicarDescuentoCategoria(categoria, porcentaje) {
  const connection = await pool.getConnection();
 
  try {
    await connection.execute('SET @porcentaje = ?', [porcentaje]);
 
    await connection.execute(
      'CALL aplicar_descuento(?, @porcentaje)',
      [categoria]
    );
 
    const [rows] = await connection.execute(
      'SELECT @porcentaje AS productos_afectados'
    );
    const afectados = rows[0].productos_afectados;
 
    console.log(`Descuento del ${porcentaje}% aplicado a ${afectados} productos de "${categoria}"`);
    return afectados;
  } finally {
    connection.release();
  }
}
 
await aplicarDescuentoCategoria('Periféricos', 10);

Salida esperada:

Descuento del 10% aplicado a 3 productos de "Periféricos"

Múltiples conjuntos de resultados

Algunos procedimientos devuelven varios conjuntos de resultados. Cada conjunto se accede por su índice en el array:

async function obtenerResumen() {
  const [results] = await pool.execute('CALL resumen_tienda()');
 
  console.log('=== Productos por categoría ===');
  results[0].forEach(cat => {
    console.log(`  ${cat.categoria}: ${cat.total_productos} productos, ${cat.total_stock} unidades`);
  });
 
  console.log('\n=== Top 5 productos más caros ===');
  results[1].forEach(p => {
    console.log(`  ${p.nombre} (${p.categoria}): $${p.precio}`);
  });
 
  console.log('\n=== Estadísticas generales ===');
  const stats = results[2][0];
  console.log(`  Total productos: ${stats.total_productos}`);
  console.log(`  Precio promedio: $${stats.precio_promedio}`);
}
 
await obtenerResumen();

Salida esperada:

=== Productos por categoría ===
  Periféricos: 3 productos, 430 unidades
  Computadoras: 1 productos, 25 unidades

=== Top 5 productos más caros ===
  Laptop HP Pavilion (Computadoras): $12999.99
  Auriculares Sony WH-1000XM5 (Audio): $5999.99
  Monitor Samsung 27" (Monitores): $6499.00
  Teclado Mecánico Corsair K70 (Periféricos): $2299.50
  SSD Samsung 1TB (Almacenamiento): $1899.00

=== Estadísticas generales ===
  Total productos: 8
  Precio promedio: $4399.31

Caso práctico

En una aplicación real, es habitual envolver las llamadas a procedimientos en funciones reutilizables:

const mysql = require('mysql2/promise');
 
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'tu_contraseña',
  database: 'tienda'
});
 
class ProcedimientosDB {
  static async ejecutar(nombre, paramsIn = [], paramsOut = []) {
    const connection = await pool.getConnection();
 
    try {
      const placeholdersIn = paramsIn.map(() => '?').join(', ');
      const placeholdersOut = paramsOut.map(p => `@${p}`).join(', ');
      const allPlaceholders = [placeholdersIn, placeholdersOut]
        .filter(Boolean)
        .join(', ');
 
      const [results] = await connection.execute(
        `CALL ${nombre}(${allPlaceholders})`,
        paramsIn
      );
 
      let outValues = {};
      if (paramsOut.length > 0) {
        const selectOut = paramsOut.map(p => `@${p} AS ${p}`).join(', ');
        const [outRows] = await connection.execute(`SELECT ${selectOut}`);
        outValues = outRows[0];
      }
 
      return { resultados: results, salida: outValues };
    } finally {
      connection.release();
    }
  }
}
 
// Uso simplificado
const { resultados } = await ProcedimientosDB.ejecutar(
  'obtener_productos_por_categoria',
  ['Periféricos']
);
console.log('Productos:', resultados[0]);
 
const { salida } = await ProcedimientosDB.ejecutar(
  'contar_productos',
  ['Audio'],
  ['total', 'precio_promedio']
);
console.log('Total:', salida.total, 'Promedio:', salida.precio_promedio);

Manejo de errores

Los procedimientos almacenados pueden lanzar errores específicos que debes capturar:

async function llamarProcedimientoSeguro(nombre, params) {
  try {
    const placeholders = params.map(() => '?').join(', ');
    const [results] = await pool.execute(
      `CALL ${nombre}(${placeholders})`,
      params
    );
    return { exito: true, resultados: results };
  } catch (error) {
    switch (error.code) {
      case 'ER_SP_DOES_NOT_EXIST':
        return { error: true, mensaje: `El procedimiento "${nombre}" no existe` };
      case 'ER_SP_WRONG_NO_OF_ARGS':
        return { error: true, mensaje: 'Número incorrecto de parámetros' };
      case 'ER_SIGNAL_EXCEPTION':
        return { error: true, mensaje: error.sqlMessage };
      default:
        console.error('Error al ejecutar procedimiento:', error);
        return { error: true, mensaje: 'Error interno' };
    }
  }
}

En la siguiente sección veremos cómo conectar MySQL con Python, otro lenguaje muy popular para trabajar con bases de datos.

Escrito por Eduardo Lázaro