Using MySQL functions, triggers, and stored procedures has many advantages, but also some challenges. We will detail these different points, and see how to get around the few elements that could cause problems (versioning, unit tests, etc.).
Benefits of Using MySQL Functions, Triggers, and Stored Procedures
If you are in the context of a web application in LAMP mode, with PHP code and a MySQL database, you have two choices when implementing your business rules. Either create PHP methods in your classes, or use functions or procedures in MySQL. The question of the choice to be made can legitimately arise, each of the methods having its advantages and disadvantages.
Developing on the database side has several advantages:
- If you are like me in a mixed environment (LAMP servers and Microsoft servers with SQL Server and SSIS in particular), you will not be able to use in SSIS for example the developments that you have done in PHP. While you will be able to run and retrieve the dataset that your stored procedure will return to you with no problem. This will therefore save you from having to code the same thing twice, in addition in two different environments (with all the problems that this can bring you).
- This gives you a plus in terms of security , because your data is no longer modified live by an external code. They go through your SQL development layer, where you can define your various controls. The trigger in the context of the security of your data is also essential, because it is the only one that allows you controls that can never be overridden.
- The business rules are as close as possible to the data . This therefore prevents your processing from going through all the layers each time (client-side browser, web server, PHP interpreter and database). You are already at the lowest level.
You are therefore close to the data, your developments are authentic to guarantee the integrity of your data, and they are also reusable on different applications and on different environments. That is the demand of the people.
Disadvantages and Workarounds
You have just seen that the advantages were numerous and not insignificant. However, this is not without some problems. But once these issues are integrated, it becomes quite simple to propose solutions to deal with them.
Code versioning
This is a concern often cited when developing on the MySQL side. The code of your MySQL functions, triggers and stored procedures is not versionable, and once loaded on your web server you feel like you are “losing control” of your code.
Another problem, if you want to do a full search inside your application with your favorite IDE (eg as part of a code refactoring involving a renaming in a field), it won’t see the MySQL code.
The solution to this problem is quite simple: you have to integrate your SQL code into your application, and set up a mechanism to load your code into your database.
Let’s see this in more detail. If you have a MySQL function, trigger, or stored procedure to create, you’ll create the code in your IDE. The ideal is probably to dedicate a folder of your application (let’s name the SQL), with sub-levels allowing to classify them by type (function, trigger, view, procedure) and with a tree structure corresponding to your application. The files created will have a .sql extension (you will also benefit from syntax highlighting, or even corrections from your IDE, which generally knows how to recognize the SQL language).
You will do this for all your SQL code. You will add your code as you go along, adding DROP and CREATE. You will also need to define a particular DELIMITER ($$ for example, or any other that you are certain not to encounter in your SQL code) that you will add after the DROP statement and at the very end of your trigger, function or procedure stored MySQL.
As it is a file, you will be able to manage them in your code version management tool, for example with Git and SourceTree :
It now remains to load this code on your database.
To do this, you just need to set up a script that retrieves your .sql files (for example with a RecursiveDirectoryIterator), aggregates them into a temporary file (via a file_put_contents for example) and then plays this file on your database (by call mysql from command line). You can also put a header template for the temporary file, including the DELIMITER statement to set the delimiter you have chosen.
If you want to go a bit further, you can add arguments to your script. For example, only process functions, or procedures, or triggers. Or even to output only an aggregation sql file without launching it on the database (for control).
If you make your transitions to production via a version repository, nothing also prevents you from setting up a production script. It would go and update production with your project’s repository, and then play set up your sql files.
Unit tests
Another possible criticism of using MySQL triggers, functions and stored procedures is the impossibility of integrating them into automated unit testing systems which can be done for example with Jenkins in a continuous integration process .
It’s true. On the other hand, if you develop a MySQL function for example, there is a good chance that it will then be used in a PHP method which will call it by passing parameters.
However, this PHP method can absolutely be called in a unit test.
For example, consider a stored procedure that updates the prices of an item. You developed it in MySQL because it can be called by different tools. And you wanted to be sure to have a single point of passage for the price change. This procedure can be called by the majPrix method of your PHP article class, which you can test individually.
Finally, if we are talking about unit tests (excluding continuous integration here), there is nothing simpler than testing a MySQL stored procedure. Just call it from the command line passing it the right parameters. It’s even less restrictive than testing a PHP method. Nothing prevents even to develop an SQL script allowing to test with different parameters the call of a function or a MySQL stored procedure.
Conclusion
Here I give you my point of view. Do not hesitate in comments to let me know yours, your feedback or other constraints that are not listed above.
