Testing with a MySQL 5.7 Database On Codeship

Every site I've used Codeship on—until today—has either used SQLite in testing or has worked fine with MySQL 5.6. But recently, one of our teams tried to test a codebase that uses JSON columns, meaning we needed to use MySQL 5.7+, which doesn't come enabled on Codeship out of the box.

Here's a quick walkthrough of how to set up a MySQL 5.7 testing database, locally in Codeship and without needing to rely on RDS.

Environment variables

Quick note: Codeship makes these environment variables available for you to use:

MYSQL_USER
MYSQL_PASSWORD

They also claimed to have a MYSQL_PORT variable, but I tried and found it didn't work. We'll be referencing these variables later.

Modify your setup scrupt

First, we'll need to run a script to "install" MySQL 5.7 on our testing instance; we'll then add a test database that our scripts will connect to.

Add the following lines to your setup script, somewhere before your migrations:

\curl -sSL https://raw.githubusercontent.com/codeship/scripts/master/packages/mysql-5.7.sh | bash -s
export PATH=/home/rof/mysql-5.7.17/bin:$PATH
mysql --defaults-file="/home/rof/mysql-5.7.17/my.cnf" -u "${MYSQL_USER}" -p"${MYSQL_PASSWORD}" -e "create database test";

You'll now have a local instance of MySQL version 5.7 with a test database, ready for your app to connect to.

Modify your environment variables

This will depend a bit on the framework or language you're working with. You can learn a little about how Codeship handles environment variables with Ruby on Rails and Django here: https://documentation.codeship.com/basic/databases/mysql/

But if you're working with your own environment variables, like I do in Laravel, here's how to get your variables to reference theirs.

First, edit the Project Settings (in the same place you were to edit your setup script) and choose the "Environment" tab instead, which will leave you at https://app.codeship.com/projects/YOURPROJECTNUMBER/environment/edit

For all of your environment variables on the left (in my case, things like DB_HOST and DB_PORT) you'll map them to either a static variable or one of the environment variables Codeship makes available (like $MYSQL_USER). You can see the values you'll want to use below:

As you can see, we need to connect to 127.0.0.1:3307 with user $MYSQL_USER (the dollar sign tells Codeship to pull the pre-existing environment variable with that name) and password $MYSQL_PASSWORD, and we'll be using the test database we created in our setup script.

Do a happy dance

That should do it! Restart your build and hopefully you're good to go. If you have any trouble, you can either hit me up on Twitter or email Codeship support.