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.
• Keep one reproducible .sql per procedure: DROP PROCEDURE IF EXISTS, then the CREATE PROCEDURE … END block wrapped in DELIMITER $$ … $$, so it also runs with the mysql client. The app creates it with a sequelize-cli migration (CommonJS; .cjs if package.json has "type": "module") that sends DROP and only the CREATE PROCEDURE … END block, in two separate queryInterface.sequelize.query() calls. With the client, use the app's database user, never root: the app user cannot drop a procedure that root defined. Never 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) → 422, or the 4xx the project's spec sets, 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.