If you use an automated testing platform (such as Jenkins) you may have come across the problem where you need to manually create a new database (and sometimes user) every time you configure a different build or a different host. For small projects it’s not much of a chore, but for larger projects (or complicated build environments) it quickly becomes a pain.
To try and get around this problem, you need to do two things:
- Create a database user who has permissions to create new databases.
- Create a script that checks for a config file, and creates a database and new config if required.
Since I’m a PHP+MySQL guy, this will be specifically based on those two. But the concepts can be ported to any language and database.
#1 – Creating a database user
For security reasons, the user in #1 should only have access to create databases with a prefix. This will ensure that if those database credentials are exposed, the damage will be minimal and the testing databases can be easily identified. To do this in MySQL you can restrict permissions to a database name with wildcard.
In this case, we want to create a new database user project with the password password, along with setting the permissions to create database tables with the prefix project_. The MySQL looks like:
CREATE USER 'project'@'localhost' IDENTIFIED BY 'password';CREATE USER 'project'@'%' IDENTIFIED BY 'password';GRANT ALL PRIVILEGES ON `project\_%`.* TO 'project'@'localhost';GRANT ALL PRIVILEGES ON `project\_%`.* TO 'project'@'%';FLUSH PRIVILEGES;
#2 – Check for test database script
In ZF2 your database credentials are stored in ./config/autoload/local.php, which shouldn’t be included in your version control. If you use something other than ZF2 that doesn’t have a local config file, you will have to get creative 🙂
We can take advantage of a local config file by checking if the file exists. If the file does not exist, we can assume that we need to create a new database, and go ahead and do it. Finally we need to create the config file so it can be used by the application.
My finished script looks like:
#!/usr/bin/php 'Mysqli', 'hostname' => $hostname, 'username' => $username, 'password' => $password,));// Create a new database with random name$database = $prefix.mt_rand(10000, 99999);$adapter->query( "CREATE DATABASE IF NOT EXISTS `{$database}` DEFAULT CHARACTER SET utf8", Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);// Write config file$config = << array( 'dsn' => 'mysql:dbname={$database};host={$hostname}', 'username' => '{$username}', 'password' => '{$password}', ),);EOF;file_put_contents($file, $config);echo "\nConfig file created for database: {$database}.\n\n";
Pretty simple really 🙂