Using MySQL Stored Procedures with Node.js: Best Practices

Why Stored Procedures?

1. Performance — compiled and cached by MySQL, reducing query parsing overhead.
2. Security — no raw SQL in application code means no SQL injection from that code path.
3. Centralised logic — business rules live in the database, consistent across all apps.

Calling with Knex.js

Use db.raw('CALL sp_name(?)', [param]). The first element of the result array contains the result sets. For multiple result sets, access rows[0] for the first and rows[1] for the second.

Naming Conventions

• Prefix all procedures with sp_
• Use snake_case verb-noun: sp_get_services_list, sp_create_inquiry
• Prefix parameters with p_: p_featured, p_page

Best Practices

• Store in source control — maintain a procedures/all_procedures.sql file in your repo
• Keep procedures focused — one procedure, one concern
• Test in isolation — call procedures directly in MySQL Workbench first
• Log calls — include the procedure name and parameters in your application logs

This is exactly how we structure the Sat Sai Infocom backend — every complex query is in a stored procedure, keeping our Node.js controllers lean and business logic auditable in one place.