Using MySQL 5.7 On Codeship

Development

Reading Time: 3 minutes

UPDATE: If you’d like to manually install MySQL 5.7 directly into the Codeship Basic environment please read the instructions at the end of this article.

For this article, I’ll cover how to use MySQL 5.7 when running CI builds on Codeship. Why MySQL 5.7? Because it finally has support for JSON queries like this:

$results = \App\Example::where("data->foo", "here")->first();

You can read more about this here, but basically this means I can store JSON data like this, in one column:

{
  "foo" => "here",
  "bar" => "baz",
  "boo" => [1,2,3]
}

Yes, PostgreSQL has been able to do this for some time. However, for the time being, I am on MySQL, and I really wanted to take advantage of this in a current project. In this project, I had data coming in from different sources, and I didn’t want to make unique tables and columns to store all the different data. Instead, I could dump it all into a “JSON” field type and query it as you saw earlier.

So by the time you’re done reading this post, you’ll have a very easy path to set up RDS on AWS and use MySQL 5.7 throughout your workflow.

Setting Up MySQL on AWS Using RDS and CloudFormation

RDS is the Relational Database Service for Amazon Web Services (AWS). I’m going to take advantage of this service since they deal with all the database updates and the server updates underneath it. This setup will use a CloudFormation script to make it the most painless experience you can imagine for building a resource. CloudFormation is an amazing way to manage resources on AWS.

You can find the CloudFormation script here. All you need to do is log into your AWS account, go to the CloudFormation area, and upload this script. You’ll be prompted with some questions as seen in this screenshot:

cloudformation_questions

After this page, you can just click Okay the rest of the way through.

In this example, I’ll call the stack codeship-testing and give it a really solid password. Lastly, I’ll name the database codeship. That’s it! Let it run for 15 minutes, and you’ll have a 5.7 MySQL instance that will allow connections.

Now let’s set up our code to use this.

First, I’ll download a PEM key from AWS for secure communications and save that to my ci folder in the root of my application.

pem

And I’ll add that to my database settings: config/database.php

    'mysql' => [
      'driver'    => 'mysql',
      'host'      => env('DB_HOST', 'localhost'),
      'database'  => env('DB_DATABASE', 'forge'),
      'username'  => env('DB_USERNAME', 'forge'),
      'password'  => env('DB_PASSWORD', ''),
      'charset'   => 'utf8',
      'collation' => 'utf8_unicode_ci',
      'prefix'    => '',
      'strict'    => false,
      codeship_ssl()
    ],

Next I add codeship_ssl() to the mysql connection.

 [PDO::MYSQL_ATTR_SSL_CA => base_path('/ci/rds-combined-ca-bundle.pem')]];
        }
    }
}

////

And finally, I add this snippet above the return statement, on the top of the file. This way we use the PEM file option ONLY if we are on the codeship database. You can see the full code example here.

Sign up for a free Codeship Account

Setting Up Codeship

Now to set up Codeship so it will have the correct environment settings and use the PEM file and the new database name, password, and URL. Here are our Environment settings for Codeship by the time I’m done.

codeship_variables

The DB_DATABASE codeship is the state I look for in the config/database.php file. And that’s it for Codeship settings!

A Working Example

Now of course I want to show this working. Since I’m using Laravel, I’ll go through the steps needed to make a simple example.

First I make a migration to include this JSON field:

increments('id');
            $table->json('data');
        });
    }

///

Then I’ll make a factory to easily populate it from my PHPUnit test: database/factories/ModelFactory.php

$factory->define(App\Example::class, function (Faker\Generator $faker) {

    return [
        'data' => [
            "foo" => $faker->text,
            "bar" => $faker->text,
        ]
    ];
});

And a Model to cast it to an array: app/Example.php

 'array'
    ];

}

Finally, I’ll make a PHPUnit test to show it working: tests/QueryJsonTest.php

create(
            [
                'data' => [
                    "foo" => "baz",
                    "bar" => "boo",
                ]
            ]
        );


        factory(\App\Example::class)->create([
            'data' => [
                "foo" => "here",
                "bar" => "not-here",
            ]
        ]);

        $results = \App\Example::where("data->foo", "here")->first();

        PHPUnit_Framework_Assert::assertNotNull($results);
    }

}

passing

That’s it! I now can work locally using MySQL 5.7 and have Codeship run our tests with the same version as my local and Production.

An Alternative to Setting up MySQL 5.7 via Amazon RDS

If you’d like to manually install MySQL 5.7 directly into the Codeship Basic environment, then add the following script to your setup commands:

\curl -sSL https://raw.githubusercontent.com/codeship/scripts/master/packages/mysql-5.7.sh | bash -s

You will need to reconfigure any MySQL port reference in your application to 3307. An alternative port can be defined with the MYSQL_PORT environment variable before the script call.

Please email support@codeship.com if you have any questions regarding this alternative approach.

Additional References

http://stackoverflow.com/questions/38710723/connect-laravel-5-to-aws-rds-with-ssl

https://laravel.com/docs/5.2/queries#json-where-clauses

http://themsaid.com/mysql-json-data-type-20160311/

Subscribe via Email

Over 60,000 people from companies like Netflix, Apple, Spotify and O'Reilly are reading our articles.
Subscribe to receive a weekly newsletter with articles around Continuous Integration, Docker, and software development best practices.



We promise that we won't spam you. You can unsubscribe any time.

Join the Discussion

Leave us some comments on what you think about this topic or if you like to add something.

  • Hong Ming

    Hi, something pique my interest while reading the blog, why we can’t have mysql 5.7 in codeship itself?

  • Most of the code snippets seem to be missing some lines.

  • Drew Kitch

    If you’d like to install MySQL 5.7 directly to your Codeship Basic environment and cache for future builds, add the following command to your setup steps:

    “`
    curl -sSL https://raw.githubusercontent.com/codeship/scripts/master/packages/mysql-5.7.sh | bash -s
    “`

    Add the following environment variables to your project configuration (otherwise script defaults will be used).
    – MYSQL_VERSION
    – MYSQL_PORT

    • Kai

      It took a while to actually get it working. Since Codeship is already installing a MySQL version by default, I needed to do some tweaks to make my Rails app actually use this other version:

      export MYSQL_DIR=”$HOME/mysql-5.7″
      export MYSQL_PORT=”3308″
      export MYSQL_SOCKET=”$MYSQL_DIR/socket/mysqld.sock”
      export PATH=”$MYSQL_DIR/bin:$PATH”
      curl -sSL https://raw.githubusercontent.com/codeship/scripts/master/packages/mysql-5.7.sh | bash -s

      bundle config build.mysql –with-mysql-dir=$MYSQL_DIR
      bundle install –quiet

      • Martin Streicher

        Are these instructions still applicable? Did you also have to install your own database.yml file pointing to the proper port or socket?

  • Max

    Codeship thinks it’s a good idea to overwrite database.yml files if you’re using a Rails app. This was the source of our challenges with getting both of these solutions working. Make sure you read this if using a Rails app: https://documentation.codeship.com/basic/databases/mysql/

  • Martin Streicher

    To get the alternative approach to work, see my gist at

    https://gist.github.com/martinstreicher/c22363334aae8673d5d27b60787a2856

  • Chamoy Donnie

    This is an example of the configuration file for a laravel project :)

    https://gist.github.com/chamoysvoice/e53ffde46edeeb2c10aead386aeda27d