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 que compila y cachea el plan de ejecución del procedimiento tras la primera invocación.

En aplicaciones Node.js que usan el driver mysql2, llamar a un procedimiento almacenado es tan sencillo como ejecutar cualquier otra sentencia SQL con CALL. Sin embargo, hay matices importantes que debes conocer: la estructura del resultado es diferente a la de un SELECT convencional, los parámetros de salida requieren un flujo específico con variables de sesión, y los procedimientos que devuelven múltiples conjuntos de resultados necesitan un manejo cuidadoso del array de respuesta. En este artículo aprenderás a manejar todos estos escenarios con ejemplos prácticos y patrones reutilizables.

Requisitos previos

Necesitas un pool de conexiones configurado y permisos para crear procedimientos almacenados en tu base de datos. Si aún no tienes la conexión establecida, revisa primero el artículo sobre conectar MySQL con Node.js. Los procedimientos que usaremos en los ejemplos los puedes crear directamente en tu servidor MySQL con las siguientes sentencias.

El primer procedimiento recibe una categoría como parámetro de entrada y devuelve todos los productos activos de esa categoría ordenados por precio descendente. El segundo procedimiento calcula estadísticas agregadas y las devuelve a través de parámetros de salida. El tercero demuestra el uso de parámetros bidireccionales (INOUT), que reciben un valor y lo sobrescriben con el resultado. El cuarto devuelve múltiples conjuntos de resultados en una sola llamada.

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 ;

Llamar a un procedimiento con parámetros IN

El caso más habitual es llamar a un procedimiento que recibe parámetros de entrada y devuelve un conjunto de resultados. La sintaxis en Node.js es idéntica a la de cualquier consulta parametrizada: se usa CALL nombre_procedimiento(?) con placeholders para los parámetros.

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)

Entender la estructura del resultado

Cuando llamas a un procedimiento almacenado con CALL, el resultado tiene una estructura diferente a la de una consulta SELECT simple. Esta diferencia es la principal fuente de confusión para desarrolladores que empiezan a trabajar con procedimientos almacenados en Node.js, así que es importante entenderla bien.

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.

Puedes visualizar esta estructura con un ejemplo sencillo. Si añades un console.log(results) justo después de la llamada, verás algo como esto: un array cuyo primer elemento es el array de filas (objetos JavaScript con las columnas como propiedades) y cuyo segundo elemento es un ResultSetHeader con propiedades como fieldCount, affectedRows y serverStatus. Muchos errores se producen por acceder directamente a results en lugar de a results[0], lo que provoca que el código intente iterar sobre un array que contiene tanto filas como metadatos.

Parámetros OUT

Los parámetros de salida en MySQL requieren usar variables de sesión. El flujo consiste en tres pasos: llamar al procedimiento pasando variables de sesión como argumentos de salida, que el procedimiento asigne valores a esas variables, y finalmente ejecutar un SELECT para leer los valores de las variables. Este flujo es inherente al protocolo MySQL y no es algo específico de Node.js; cualquier cliente que trabaje con parámetros OUT sigue el mismo proceso.

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 con pool.execute(), la llamada al procedimiento podría ejecutarse en una conexión y el SELECT de las variables en otra conexión distinta donde esas variables no existen. El resultado sería que @total y @promedio tendrían valor NULL, produciendo un error silencioso difícil de depurar.

El bloque try/finally garantiza que la conexión siempre se devuelve al pool, incluso si ocurre un error. Olvidar llamar a connection.release() es uno de los errores más graves en aplicaciones Node.js con MySQL, porque las conexiones se agotan progresivamente hasta que el pool se queda vacío y la aplicación se bloquea.

Parámetros INOUT

Los parámetros INOUT funcionan como entrada y salida a la vez: reciben un valor al inicio del procedimiento y el procedimiento puede modificarlo para devolver un resultado. Se manejan de forma similar a los OUT, pero con un paso adicional al principio para establecer el valor inicial de la variable 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"

Observa que el flujo tiene tres sentencias SQL secuenciales: primero SET @porcentaje = ? para inicializar la variable con el valor de entrada, luego CALL aplicar_descuento(?, @porcentaje) que usa la variable como entrada y la sobrescribe con el resultado, y finalmente SELECT @porcentaje para leer el valor modificado. Las tres sentencias deben ejecutarse en la misma conexión por la razón que ya comentamos: las variables de sesión son locales a cada conexión.

Múltiples conjuntos de resultados

Algunos procedimientos almacenados ejecutan varios SELECT internamente, y cada uno produce un conjunto de resultados separado. MySQL envía todos los conjuntos al cliente en orden, y el driver mysql2 los organiza como elementos consecutivos en el array de resultados. Este patrón es especialmente útil para pantallas de dashboard o informes que necesitan datos de varias fuentes en una sola petición al servidor.

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

El procedimiento resumen_tienda() ejecuta tres SELECT, así que results[0] contiene las categorías, results[1] los productos más caros y results[2] las estadísticas globales. El último elemento del array (results[3] en este caso) es el ResultSetHeader con los metadatos del procedimiento, que normalmente puedes ignorar. Si tu procedimiento ejecuta N consultas SELECT, el array tendrá N+1 elementos, siendo el último siempre los metadatos.

Caso práctico: clase reutilizable para procedimientos

En una aplicación real, es habitual envolver las llamadas a procedimientos en funciones o clases reutilizables que abstraigan la complejidad del manejo de variables de sesión y la estructura del resultado. La siguiente clase proporciona un método estático que gestiona automáticamente tanto los parámetros de entrada como los de salida, obteniendo la conexión del pool y liberándola de forma segura.

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);

Esta clase encapsula todo el flujo que hemos visto: obtiene una conexión del pool, construye dinámicamente los placeholders para los parámetros de entrada y las variables de sesión para los de salida, ejecuta la llamada al procedimiento, lee los valores de salida si los hay, y libera la conexión en el bloque finally. El código que consume esta clase solo necesita pasar el nombre del procedimiento y los parámetros, sin preocuparse por los detalles de implementación.

Una mejora adicional sería añadir validación del nombre del procedimiento para prevenir inyección SQL, ya que el nombre se interpola directamente en la cadena. Podrías mantener un listado de procedimientos permitidos y verificar que el nombre proporcionado esté en esa lista antes de ejecutar la consulta.

Manejo de errores

Los procedimientos almacenados pueden lanzar errores específicos que debes capturar y manejar adecuadamente en tu aplicación. El driver mysql2 lanza estos errores como excepciones estándar de JavaScript con propiedades adicionales como code, errno y sqlMessage que te permiten identificar el tipo de error y responder de forma apropiada.

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' };
    }
  }
}

El código ER_SP_DOES_NOT_EXIST se produce cuando intentas llamar a un procedimiento que no existe en la base de datos, algo que puede ocurrir si el procedimiento fue eliminado o si hay un error tipográfico en el nombre. El código ER_SP_WRONG_NO_OF_ARGS indica que el número de parámetros proporcionados no coincide con la definición del procedimiento. El código ER_SIGNAL_EXCEPTION es especialmente interesante: se produce cuando el procedimiento lanza un error personalizado con la sentencia SIGNAL, lo que permite que la lógica de negocio del procedimiento comunique errores de validación al código Node.js.

En aplicaciones Express o Fastify, este patrón de manejo de errores se integra naturalmente con los middleware de error. Puedes transformar los códigos de error MySQL en códigos de estado HTTP apropiados: un ER_SP_DOES_NOT_EXIST podría mapearse a un 404, un ER_SP_WRONG_NO_OF_ARGS a un 400 y un error genérico a un 500.

Errores comunes al trabajar con procedimientos

El error más frecuente es acceder directamente a results en lugar de a results[0] al leer las filas del resultado. Recuerda que el CALL envuelve cada conjunto de resultados en un nivel adicional de array, por lo que results es un array de arrays, no un array de filas.

Otro error habitual es usar pool.execute() directamente para procedimientos con parámetros OUT o INOUT. Como las variables de sesión son locales a la conexión, necesitas obtener una conexión específica con getConnection() y ejecutar todas las sentencias sobre esa misma conexión. Si no lo haces, la segunda consulta (SELECT @variable) se ejecutará en una conexión diferente donde la variable no existe, y obtendrás NULL sin ningún mensaje de error.

También es común olvidar el DELIMITER al crear los procedimientos en la consola MySQL. El DELIMITER // es necesario para que MySQL no interprete los punto y coma internos del procedimiento como el fin de la sentencia CREATE PROCEDURE. Sin embargo, este problema no afecta a Node.js porque el driver envía las sentencias una a una, no como un script completo.

Antes de pasar a otros lenguajes, asegúrate de dominar las operaciones básicas como insertar datos desde Node.js y ejecutar consultas. En la siguiente sección veremos cómo conectar MySQL con Python, otro lenguaje muy popular para trabajar con bases de datos. Si necesitas permisos para crear procedimientos en tu servidor, revisa los artículos sobre CREATE USER y GRANT.