MySQL stored procedure authoring

Write MySQL 8.4 stored procedures that Node can create through sequelize-cli and mysql2, that save everything or nothing, and that fail with errors the app can map to HTTP status codes.

1. Files

• db/procedures/sp_<name>.sql: the reproducible copy. One CREATE PROCEDURE statement, no DELIMITER.
• migrations/<timestamp>-create-sp-<name>.js: the only place that creates it. CommonJS like the other migrations (.cjs if package.json has "type": "module").

Do not also create it from docker-entrypoint-initdb.d. Those scripts run as root, which has SYSTEM_USER, so root becomes the definer and the app user can no longer drop or replace the procedure. Never write DEFINER=: the definer is whoever runs the migration.

2. Migration

'use strict';
const fs = require('fs');
const path = require('path');

const sql = fs.readFileSync(path.join(__dirname, '../db/procedures/sp_register_sale.sql'), 'utf8');

module.exports = {
  async up(queryInterface) {
    await queryInterface.sequelize.query('DROP PROCEDURE IF EXISTS sp_register_sale');
    await queryInterface.sequelize.query(sql);
  },
  async down(queryInterface) {
    await queryInterface.sequelize.query('DROP PROCEDURE IF EXISTS sp_register_sale');
  },
};

• DROP and CREATE go in two separate query() calls. No DELIMITER and no multipleStatements: CREATE PROCEDURE … BEGIN … END is one statement. DELIMITER sent through mysql2 fails with ER_PARSE_ERROR (1064).
• Pass no replacements and no type to the CREATE call: a :name inside the body would be replaced.
• MySQL has no CREATE OR REPLACE PROCEDURE (that is MariaDB). CREATE PROCEDURE IF NOT EXISTS (8.0.29+) skips an existing procedure, so it never updates the body.
• To change a procedure that already shipped, add a new migration that drops and creates it again. Do not edit a migration that already ran.
• Do not try the file with mysql < file.sql: the client splits at every ;. Run the migration (npx sequelize-cli db:migrate).

3. Template: a sale with its items

CREATE PROCEDURE sp_register_sale(IN p_items JSON)
BEGIN
  DECLARE v_sale_id INT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  IF p_items IS NULL OR JSON_TYPE(p_items) <> 'ARRAY' OR JSON_LENGTH(p_items) = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'SALE_WITHOUT_ITEMS';
  END IF;

  START TRANSACTION;

  INSERT INTO sales (total, created_at, updated_at) VALUES (0, NOW(), NOW());
  SET v_sale_id = LAST_INSERT_ID();

  INSERT INTO sale_items (sale_id, product_id, quantity, unit_price, subtotal, created_at, updated_at)
  SELECT v_sale_id, j.product_id, j.quantity, j.unit_price, ROUND(j.quantity * j.unit_price, 2), NOW(), NOW()
  FROM JSON_TABLE(p_items, '$[*]' COLUMNS (
    product_id INT PATH '$.productId' ERROR ON EMPTY,
    quantity INT PATH '$.quantity' ERROR ON EMPTY,
    unit_price DECIMAL(10,2) PATH '$.unitPrice' ERROR ON EMPTY
  )) AS j;

  UPDATE sales
  SET total = (SELECT SUM(subtotal) FROM sale_items WHERE sale_id = v_sale_id)
  WHERE id = v_sale_id;

  COMMIT;

  SELECT id AS saleId, total FROM sales WHERE id = v_sale_id;
END

The caller sends [{"productId": 3, "quantity": 2, "unitPrice": "19.90"}] and gets one row: { saleId, total }.

4. Rules inside the body

• Declare in this order: variables and conditions, then cursors, then handlers.
• DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;. RESIGNAL sends the original error, with its errno, to Node. Never swallow the error or return a success row from the handler.
• Validate with SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UPPER_SNAKE_CODE' before START TRANSACTION. Node receives errno 1644 (ER_SIGNAL_EXCEPTION) with the code as the message.
• MySQL has no array parameters. Lists arrive as one JSON parameter read with JSON_TABLE; the alias after JSON_TABLE(...) is required. Use ERROR ON EMPTY for required fields.
• JSON_TABLE inside a procedure needs MySQL 8.0.19+ (before that, the second call could return no rows). mysql:8.4 is fine.
• Money is DECIMAL(10,2), also in JSON_TABLE columns. Compute subtotals with ROUND(quantity * unit_price, 2) and the total with SUM in SQL. Never trust a total sent by the caller.
• Set every NOT NULL column that has no default. Migrations generated by sequelize-cli create createdAt/updatedAt with no default: either the migration gives them defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') or the INSERT sets NOW().
• Return data with exactly one SELECT, at the end. Each extra SELECT (debugging, checks) adds a result set that the caller reads by mistake; use SELECT … INTO v_var for checks. No OUT parameters: reading them needs a second query on the same connection.
• The procedure owns its transaction. The app must call it without sequelize.transaction(), because MySQL cannot nest transactions and this START TRANSACTION would commit the outer work.

5. Final check

• grep -rn DELIMITER db migrations finds nothing.
• No DEFINER=, no CREATE OR REPLACE, no copy in docker-entrypoint-initdb.d.
• The handler runs ROLLBACK and then RESIGNAL.
• Empty or invalid input raises SIGNAL SQLSTATE '45000' before anything is inserted.
• Exactly one result SELECT, at the end, returning what the app needs (for example saleId and total).