Being able to deploy databases as part of a deployment or as part of running your test suite is essential to any project. So, automating that process should be at top of mind since you'll be doing it several times a day, especially in the case of running your unit tests. And when it comes time to setup your continuous integration/deployment pipeline, that kind of automation is crucial.
To that end, having a task-running tool like Grunt is invaluable. It makes setting up your database incredibly simple.
Let's take the example of a Node.js application running ontop of MySQL--a pretty common combination. We would likely have an SQL script to create the schema/tables, perhaps another to insert some sample/base data, and another series of scripts to create our stored procedures.
When we first start out, we likely run these scripts via the command line, like so:
mysql -udev_user -p < script_name_here.sql
What a pain this would be if we had to execute these all individually by this means! What can we do?
Grunt to the Rescue
I'm going to assume that you are familiar with setting up Grunt (it's seriously easy; if you're really not sure, check out here for a quick rundown).
So now how do we automate these database setup scripts? Mysqlrunfile saves the day!
To install it, simply run npm as follows:
npm install grunt-mysql-runfile
(Season to taste with any desired npm flags.)
And then, from within your Gruntfile, add the following line to enable the Mysqlrunfile tasks:
Check here for details on how to use it.
This All Seems a Little Too Easy...
Ok, ok. I wouldn't be writing this article if everything went so smoothly.
So, maybe you're running your SQL scripts and everything seems to be going ok.
You've created your tables.
You've inserted some data.
So you set out to create your stored procedures. It looks something like this:
USE your_database; DROP PROCEDURE IF EXISTS proc_report; DELIMITER // CREATE PROCEDURE prod_report () BEGIN ... END // DELIMITER ;
All very standard.
And then you get this:
Fatal error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER // CREATE PROCEDURE ...
But how can this be? You've run this CREATE PROCEDURE script countless times, both through the MySQL client and the command line interface!
Well, as it turns out, Mysqlrunfile isn't actually running the scripts through the command line. Instead, it is parsing your script and running it through a small Node.js app!
Again, you ask, "So what?"
Closer inspection and investigation reveals that the DELIMITER keyword is not actually an SQL keyword. In fact, it's a convenience for use via the MySQL client and command line interface. So actually trying to run the CREATE PROCEDURE script as through the MySQL driver isn't going to work as-is.
Changing the default delimiter when creating a stored procedure is a common practice, so seeing this kind of issue doesn't really come as much of a surprise.
Create a copy of the original SQL script and make the following modifications:
USE your_database; DROP PROCEDURE IF EXISTS proc_report; CREATE PROCEDURE prod_report () BEGIN ... END
In other words, get rid of the DELIMITER lines and leave the default delimiter alone.
Protip:Create a small shell script to take any similar, existing CREATE PROCEDURE script files and use sed to do a search and replace of a copied version of the file.
Then, update your Gruntfile to make use of these new files and you should find that your previously-problematic CREATE PROCEDURE scripts are no longer an issue!