Calling a stored procedure from Sequelize

For sequelize ^6.37.8 with mysql2 ^3.24.5 and Express 5 (the code also runs on Express 4). Never import from @sequelize/* (v7 is still an alpha).

1. Service

'use strict';
const { sequelize } = require('../db');

async function registerSale(items) {
  const rows = await sequelize.query('CALL sp_register_sale(:items)', {
    replacements: { items: JSON.stringify(items) },
  });
  return rows[0]; // { saleId: 12, total: '39.80' }
}

module.exports = { registerSale };

• For SQL that starts with CALL, Sequelize (default query type) returns the rows of the first result set, not [results, metadata]. rows[0] is the first row of the procedure's final SELECT.
• Nothing before CALL, not even a comment: Sequelize detects a call by the SQL starting with CALL.
• Always JSON.stringify lists. In replacements, an array expands into a comma-separated list and a plain object throws.
• No type: QueryTypes.SELECT: it returns objects with numeric keys and throws when the procedure returns no rows.
• No OUT parameters: reading them needs SELECT @var on the same connection. The procedure returns its data with one final SELECT.
• Money comes back as a string ('39.80') because mysql2 returns DECIMAL as strings. Return it as is. Do not turn on decimalNumbers and do not add prices with JS numbers.

2. No outer transaction

Call the procedure without sequelize.transaction() and without a transaction option. The procedure runs its own START TRANSACTION … COMMIT, and MySQL cannot nest transactions: that START TRANSACTION commits whatever the outer transaction had done, and a later rollback cannot undo it. The procedure already makes the sale all or nothing.

If the app really must combine the call with other writes, pick one owner: remove START TRANSACTION/COMMIT from the procedure and wrap the call in the app.

3. Validate before calling

const PRICE = /^\d{1,8}(\.\d{1,2})?$/; // DECIMAL(10,2) holds at most 99999999.99

function findItemsProblem(items) {
  if (!Array.isArray(items) || items.length === 0) return 'SALE_WITHOUT_ITEMS';
  for (const item of items) {
    if (item === null || typeof item !== 'object') return 'INVALID_ITEM';
    if (!Number.isInteger(item.productId) || item.productId < 1) return 'INVALID_PRODUCT_ID';
    if (!Number.isInteger(item.quantity) || item.quantity < 1) return 'INVALID_QUANTITY';
    if (typeof item.unitPrice !== 'string' || !PRICE.test(item.unitPrice)) return 'INVALID_UNIT_PRICE';
  }
  return null;
}

MySQL only warns when a price has more than two decimals ('19.999' is cut to two), so validate in the app and send prices as strings.

4. Map database errors to HTTP

Sequelize wraps the mysql2 error (DatabaseError; UniqueConstraintError for 1062; ForeignKeyConstraintError for 1451/1452). The original error is in err.parent, with errno, code, sqlState and sqlMessage.

function httpErrorFromDatabase(err) {
  const db = err.parent;
  if (!db) return null;
  switch (db.errno) {
    case 1644: return { status: 422, error: db.sqlMessage };
    case 1452: return { status: 404, error: 'PRODUCT_NOT_FOUND' };
    case 3140: return { status: 400, error: 'INVALID_ITEMS' };
    case 1062: return { status: 409, error: 'DUPLICATE_ITEM' };
    default: return null;
  }
}

router.post('/', async (req, res, next) => {
  try {
    const items = (req.body || {}).items;
    const problem = findItemsProblem(items);
    if (problem) return res.status(400).json({ error: problem });
    res.status(201).json(await registerSale(items));
  } catch (err) {
    const httpError = httpErrorFromDatabase(err);
    if (httpError) return res.status(httpError.status).json({ error: httpError.error });
    next(err); // 500 from the error middleware, without SQL details
  }
});

Express 5 sends rejected promises to the error middleware by itself (Express 4 does not). Keep everything, validation included, inside the try/catch anyway, because it turns the errno into an HTTP status, and end with next(err). In Express 5 req.body is undefined when nothing was parsed, hence (req.body || {}).items. The error middleware keeps the 4xx that express.json() sets (malformed JSON → 400, body too large → 413) and answers 500 for the rest:

app.use((err, req, res, next) => {
  const status = err.status || err.statusCode;
  if (status >= 400 && status < 500) return res.status(status).json({ error: err.type || 'BAD_REQUEST' });
  console.error(err);
  res.status(500).json({ error: 'INTERNAL_ERROR' });
});

5. Search products by name or barcode

const { Op } = require('sequelize');

const escapeLike = (s) => s.replace(/[\\%_]/g, '\\$&');

function searchProducts(q) {
  return Product.findAll({
    where: { [Op.or]: [{ barcode: q }, { name: { [Op.like]: `%${escapeLike(q)}%` } }] },
    order: [['name', 'ASC']],
    limit: 20,
  });
}

• Op.like does not escape % or _. Without escapeLike, searching 50% matches every name that contains 50.
• barcode is UNIQUE, so the exact match uses its index. The index on name cannot help a pattern that starts with %, so keep the limit.
• Use Op.* symbols only.