Using MySQL 5.7 On Codeship

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.

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:

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.

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.

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.

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

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);
    }
}

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.

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.

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/