MySQL and Sequelize

Hard limits for Node.js backends that use MySQL through Sequelize 6. The skills mysql-stored-procedure-authoring and sequelize-call-procedure have the details and the code.

• Use sequelize ^6.37.8, mysql2 ^3.24.5 and sequelize-cli ^6.6.5. Never import from @sequelize/* or install sequelize@alpha (v7 is still an alpha). Never use the old mysql package: it cannot log in with caching_sha2_password, the MySQL 8.4 default.
• Pin the database image to mysql:8.4. Never mysql:latest (an innovation release) or 9.x: Sequelize 6 supports MySQL ^5.7 and ^8.0 only.
• Money is DECIMAL(10,2) in every migration, model and JSON_TABLE column. A plain DECIMAL (also model:generate … price:decimal) becomes DECIMAL(10,0) and drops the cents. Never FLOAT or DOUBLE.
• Keep money as strings in JavaScript (mysql2 returns DECIMAL as strings). Compute line subtotals and totals in SQL with ROUND(…, 2) and SUM, never with JS numbers. Validate incoming prices with /^\d{1,8}(\.\d{1,2})?$/ (at most 99999999.99): MySQL only warns when it cuts extra decimals.
• Never write DELIMITER in SQL sent from Node. It is a command of the mysql client, and through Sequelize/mysql2 it fails with ER_PARSE_ERROR (1064). CREATE PROCEDURE … BEGIN … END is one statement.
• Create each stored procedure only from a sequelize-cli migration (CommonJS; .cjs if package.json has "type": "module") that runs DROP PROCEDURE IF EXISTS and CREATE PROCEDURE in two separate queryInterface.sequelize.query() calls, reading a .sql copy kept in the repo. Never also from docker-entrypoint-initdb.d, never DEFINER=, never CREATE OR REPLACE PROCEDURE (MariaDB only).
• A procedure that writes owns its transaction: START TRANSACTION … COMMIT plus DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;, and business rules fail with SIGNAL SQLSTATE '45000'. Call it without sequelize.transaction(): MySQL cannot nest transactions, so the inner START TRANSACTION commits the outer work.
• Call with sequelize.query('CALL sp_name(:param)', { replacements }) and read rows[0]. No QueryTypes.SELECT, no comment before CALL, no OUT parameters: the procedure returns data with exactly one final SELECT.
• Pass lists as one JSON parameter (JSON.stringify(items)) and read it with JSON_TABLE. Never put raw arrays or objects in replacements.
• Map database errors by err.parent.errno: 1644 (SIGNAL) → 400 or 409, 1452 (missing foreign key) → 400 or 404, 3140 (invalid JSON) → 400, 1062 (duplicate) → 409. Send any other error to the 500 handler without the SQL text.
• Migrations generated by sequelize-cli create createdAt/updatedAt as NOT NULL with no default. Give timestamp columns defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'), or set them with NOW() in every INSERT inside a procedure.
• Use Op.* symbols only, and escape \, % and _ in user text before Op.like.