Stored Programs: When and How to Use Them

Stored procedures, functions, triggers and events move logic into the database. That can be right, and it can be a trap. These guidelines help you decide and keep them manageable.

The decision

When stored programs are a good idea

1. Several applications (in different languages) must apply the same rule, and a shared library is not possible.
2. A security boundary: an application account may run CALL place_order(...) but has no right to touch the tables directly.
3. Fewer round trips for a chain of dependent statements (rare in modern setups with low-latency networks).
4. Batch maintenance that is purely data-driven.
5. Auditing that must not be bypassed.

When they are a poor fit

1. Application logic that changes often, needs unit tests, feature flags, logging and metrics.
2. Teams without database specialists: the code is rarely reviewed or tested.
3. Scaling: the database server is the most expensive and hardest-to-scale part of the system. CPU spent on string processing and loops in procedures is CPU that queries cannot use.
4. Portability: stored program syntax is MySQL-specific.
5. Debugging: no real debugger, limited logging, errors that appear far from their cause.

Security

1

SELECT routine_name, routine_type, security_type, definer IS NOT NULL AS has_definer FROM information_schema.routines WHERE routine_schema = 'learn' ORDER BY routine_name;

1

Empty set

• SQL SECURITY DEFINER (the default) runs the program with the creator’s privileges. It is how a restricted account can run a controlled write. Use a dedicated low-privilege definer, not root, and not a developer’s personal account.
• SQL SECURITY INVOKER runs with the caller’s privileges, which is safer when the program only does what the caller is allowed to do anyway.
• Grant EXECUTE on specific routines, and nothing broader (Privileges).
• Avoid dynamic SQL (PREPARE from strings). If you must, whitelist identifiers (Prepared Statements).
• A trigger or procedure created by a powerful definer can be a privilege escalation path if a lower user can change what it calls.

Deployment and version control

• Keep every routine, trigger and event in a SQL file in Git, with DROP ... IF EXISTS and CREATE, so a deploy recreates them.
• Include them in the migration process, and list them in the schema documentation.
• Back them up: mysqldump --routines --triggers --events (mysqldump). Triggers are included by default, routines and events are not.
• Beware of drift: someone edits a procedure directly in production. Compare SHOW CREATE PROCEDURE output with the repository in CI.

Naming and structure

1
2
3
4
5
6
7
8
9
10
11
12
13

DELIMITER //

CREATE PROCEDURE rename_customer(IN id INT, IN new_name VARCHAR(30))
BEGIN
    UPDATE customers SET name = new_name WHERE id = id;
END//

DELIMITER ;

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(30));
INSERT INTO customers VALUES (1, 'Maya'), (2, 'Rohan');
CALL rename_customer(1, 'Maya Shah');
SELECT id, name FROM customers ORDER BY id;

1
2
3
4
5
6
7

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Maya Shah |
|  2 | Maya Shah |
+----+-----------+
2 rows in set

Both customers were renamed. Inside the procedure, the parameter id hides the column id, so WHERE id = id compares the parameter with itself and is true for every row. This classic bug is avoided by prefixing parameters (p_id, p_new_name), so that they never share a name with a column.

Testing

• Treat routines as code: write SQL test scripts that set up data, call the routine, and compare results (a framework such as tSQLt equivalents for MySQL exist, or plain scripts run in CI).
• Test failure paths: bad input, missing rows, concurrency.
• Test with realistic volume.

Monitoring

• The error log shows event and trigger errors.
• performance_schema and sys show statement and routine statistics: sys.statement_analysis includes CALL statements, and performance_schema.events_statements_summary_by_program gives per-routine totals.
• Log business events into a table from the routine, so that you can see what ran.

Alternatives that keep logic in the application

• A transaction script in one well-tested application service.
• A shared library or microservice that owns all writes.
• An outbox table processed by a worker instead of a trigger calling out (Transaction Patterns).

A balanced rule

Use the database for data rules and data movement, and the application for business rules and workflow. A few well-chosen stored programs (an audit trigger, a purge event, a controlled-write procedure) are a good investment. Hundreds of them make a system that only one person understands.

Checklist before you add a stored program

• [ ] Could a constraint, a view, a generated column or application code do this?
• [ ] Who will test it, and how?
• [ ] Is it in version control and in the deployment process?
• [ ] Who is the definer, and is the privilege level minimal?
• [ ] What happens on error, and who is told?
• [ ] Is it fast for the largest realistic input?
• [ ] Is it documented where the application developers will look?