This post explains how to connect to CenturyLink Cloud's Relational Database Service (RDB) offering through AppFog using SSL. Readers who have some knowledge of the Cloud Foundry platform (that AppFog is built on) may find it a little easier to follow along.

Introduction

For my example, I'll create a simple Ruby/Sinatra app that binds to an existing RDB MySQL instance over SSL and validates that the connection is operational. Before I build the Ruby/Sinatra app I will create a MySQL instance through the CloudFoundry command line.

Prerequisites

Before you start, there are a few things you will need to set up on your machine:

Preparing AppFog

The first step is to log into AppFog through the AppFog Control Panel UI using the credentials created for you by the Cloud Foundry CLI. Once you're logged in, use the marketplace command to ensure that you have access to the RDB MySQL service:

Brians-MacBook-Pro:~ bbutton$ cf marketplace
Getting services from marketplace in org T3OS / space Dev as admin...
OK

service       plans                               description
ctl_mysql     free, micro, small, medium, large   CenturyLink's MySQL RDB.  For development use only; not subject to SLAs.
orchestrate   free                                Orchestrate DBaaS

TIP:  Use 'cf marketplace -s SERVICE' to view descriptions of individual plans of a given service.

If you follow the tip above, you can see the plans available for ctl_mysql. These plans define the quotas and costs for using a service:

Brians-MacBook-Pro:~ bbutton$ cf marketplace -s ctl_mysql
Getting service plan information for service ctl_mysql as admin...
OK

service plan   description                                                                                                                                                                                                                                                                                                                 free or paid
free           UnManaged MySQL-compatible Database as a Service - 1vCPU/1GB, up to 100MB storage, 100 concurrent connections, daily backups held locally for 7 days.  SSL Encryption option.  This service is a 0.1 release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for  terms of service.   free
micro          UnManaged MySQL-compatible Database as a Service - 1vCPU/1GB, up to 100MB storage, 100 concurrent connections, daily backups held for 7 days.  SSL Encryption option.  This service is a 0.1  release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for  terms of service.           free
small          UnManaged MySQL-compatible Database as a Service - 1vCPU/2GB, up to 1GB storage, 1200 concurrent connections, daily backups held for 7 days.  SSL Encryption option.  This service is a 0.1  release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for  terms of service.            free
medium         UnManaged MySQL-compatible Database as a Service - 2vCPU/6GB, up to 64GB storage, 3600 concurrent connections, daily backups held for 7 days.  SSL Encryption option.  This service is a 0.1  release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for  terms of service.           free
large          UnManaged MySQL-compatible Database as a Service - 4vCPU/16GB, up to 256MB storage, 9600 concurrent connections, daily backups held for 7 days.  SSL Encryption option.  This service is a 0.1  release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for  terms of service.         free

Once you know the service name, create an instance of that service. The service instance is a long-lived, provisioned MySQL database, created with unique credentials and SSL information that live as long as the instance exists:

Brians-MacBook-Pro:~ bbutton$ cf create-service ctl_mysql free example-dbaas-instance
Creating service instance example-dbaas-instance in org T3OS / space Dev as brianbuttonxp...
OK

You can then see your service ready for you to use:

Brians-MacBook-Pro:~ bbutton$ cf services
Getting services in org T3OS / space Dev as brianbuttonxp...
OK

name                     service       plan        bound apps       last operation
my_orchestrate           orchestrate   free                         create succeeded
example-dbaas-instance   ctl_mysql     free                         create succeeded

At this point, you have all the infrastructure created to be able to connect to MySQL with the code shown here.

Accessing the Database from Ruby

You will use a simple Sinatra app to access AppFog and the MySQL instance. This gives you an easy way to create an endpoint you can hit from a browser, which creates the database connection. I'll only discuss the relevant parts of the application here, but the entire code base is available on Github.

main.rb

This is the entry point of our Sinatra app. The interesting bits of this file begin with the ENV["VCAP_SERVICES] and continue beyond. The _VCAPSERVICES environment variable is set by CloudFoundry as your application is staged and deployed. It holds the information needed to consume any services that bind to your application. For your MySQL instance, the VCAP_SERVICES variable will hold something like this:

Brians-MacBook-Pro:Database bbutton$ cf env bab-dbaas-test
Getting env variables for app bab-dbaas-test in org T3OS / space Dev as brianbuttonxp...
OK

System-Provided:
{
 "VCAP_SERVICES": {
  "ctl_mysql": [
   {
    "credentials": {
     "certificate": "-----BEGIN CERTIFICATE-----\nMIIDgTCCAmmgAwIBAgIJAMubOSUqSIZOMA0GCSqGSIb3DQEBCwUAMFcxGTAXBgNV\nBAoMEENlbnR1cnlMaW5rREJhYVMxHTAbBgNVBAsMFENlcnRpZmljYXRlQXV0aG9y\naXR5MRswGQYDVQQDDBJDZW50dXJ5TGlua0RCYWFTQ0EwHhcNMTUwNjEyMDExMzIx\nWhcNMjUwNjA5MDExMzIxWjBXMRkwFwYDVQQKDBBDZW50dXJ5TGlua0RCYWFTMR0w\nGwYDVQQLDBRDZXJ0aWZpY2F0ZUF1dGhvcml0eTEbMBkGA1UEAwwSQ2VudHVyeUxp\nbmtEQmFhU0NBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAzN5bNNjV\nGLZzq1vpGzgIDBdKzZtl625QSCVXu5vOGKZxsQDdMcflDylOPlOyJmg6t9KEkduQ\nKJvZhAoR03/ftqsYTNvzsbzyTraZb3fK7NZhbPLml9JLGrCeN0F3XmmYCKy+hoDA\nIegCOk4QazHu2XvVp/ATFc+w9jzEb6uHRrfvXtBPoGV3Td5tqfLEx+ZC9JAm6Ri3\n/eT8D+ys+sKYUyPPqJD12QN/ceWjvBrlCpyca2QoBb7OfZOZR8Q/xhxznYLsqBda\n4gWov23bGOVj9vSD/2kr9eSO+Ap739Awlso/hOjB/abDumsW9t1NPYSdscjxTD+t\n2EVcdGrvT5CT+QIDAQABo1AwTjAdBgNVHQ4EFgQULNhBKIj12kTyxWrg/hwMbtSk\nND4wHwYDVR0jBBgwFoAULNhBKIj12kTdxWrg/hwMbtSkND4wDAYDVR0TBAUwAwEB\n/zANBgkqhkiG9w0BAQsFAAOCAQEAbuEg3VquJxgg5exRtdgff9tWTozM0OozJc6d\noYgV11oH8NtvKLkwbChgGHKL1bXmMxTfW4vUk3FhuiO5S85oi0vvDGPq5gqM6oxr\ntbhaml7Nd0OoNCvRsGJiINKS3G8JRKmZ3+WA55wQEjZC5KuPlgB5XO418byYYDnc\n/k08pmEr8ztymAjVvc6rzlK0ZmUJqQnIEk+cDTHNYbALQwJ7+QZMbOGj1v/9w05M\nxFpTIBmySTP2+leCTP2qnJUiFc9yzfcMPQs6wS1KOOTwWS5LAqEUicZ17hCOMUi+\n1J1oVss1KdfPYfhSmbCbPg1ELwEHvnE7Bo4ildRlPGeSSb+gZw==\n-----END CERTIFICATE-----",
     "dbname": "default",
     "host": "66.151.15.159",
     "password": "MyPassword",
     "port": 49171,
     "username": "admin"
    },
    "label": "ctl_mysql",
    "name": "bab-mysql",
    "plan": "free",
    "tags": []
   }
  ]
 }
}

This block of JSON includes information about the database name, username, password, the IP address of your service and the information that you'll need to connect to your service over SSL.

Next, we'll look at how to parse that data, but for now let's look at this code taken from main.rb:

vcap_services = ENV["VCAP_SERVICES"]

pem_file_path = './client_cert.pem'

connection_string_model = DatabaseConnectionModel.FromCloudFoundryJson(vcap_services, pem_file_path)
ssl_certificate_file = SslCertificate.FromCloudFoundryJSON(vcap_services)
ssl_certificate_file.create_pem(pem_file_path)

set :connection_info, connection_string_model

Most of the code is fairly simple Ruby. The important parts are the last four lines. These lines take the contents of the VCAP_SERVICES JSON and parse it into the connection string model using the DatabaseConnectionModel class. Then, they take the data from the client certificate passed through the JSON and store it in a file.

This particular database library requires the certificate to be in a separate file, rather than having the data passed into the hash for the connection string so we have to create the certificate file using the SslCertificateFile class. This presents a potential issue when running on AppFog because developers aren't supposed to use the file system when using CloudFoundry. When running on a Platform-as-a-Service (PaaS) like CloudFoundry the file system is only temporary and is recreated from scratch each time an application is staged or moved by the PaaS to another server. Since your application recreates the file from the VCAP_SERVICES variable each time it starts, this should not be a problem.

routes.rb

The other interesting class in your application is routes.rb, which is the class used by Sinatra to decide how to route requests coming into the endpoint. Again, the code is pretty simple:

get '/' do
  connection_string_model = settings.connection_info

  connection = Mysql2::Client.new(
      host: connection_string_model.host_name,
      port: connection_string_model.port,
      username: connection_string_model.user_id,
      password: connection_string_model.password,
      database: connection_string_model.database,
      sslca: connection_string_model.cert_file)

  results = connection.query("show status like 'Ssl_cipher'")
  results.count.to_s
end

This code fills out the Mysql2 connection string with information parsed out of VCAP_SERVICES by the DatabaseConnectionModel and then makes a simple request to the server to validate that everything worked correctly.

Note: The last field of the connection string has a few talking points. First of all, be careful when implementing your own code - the appropriate field to fill in is sslca, not other choices such as sslcert or sslkey. What is sent to you when binding to the instance is a key that basically allows you to trust the self-signed certificate sent by the RDB infrastructure, which is why you need to set the sslca field.

The Rest of the Code

From here it is simple. The code either pulls data out of JSON and creates and fills the PEM file with the appropriate information or provides the boilerplate code needed by Sinatra. All of the code is available in the Github repository.

Running the Application in AppFog

Pushing an application to AppFog is really easy. Most of the important data needed is in the manifest.yml file:

---
applications:
- name: bab-dbaas-test
  memory: 128M
  instances: 1
  services:
    - example-dbaas-instance

The information in this file allows AppFog to stage and run your application with no further manual interaction. The name represents the unique label associated with your program. This name does have to be unique across the entire AppFog universe so you should change it to something specific and meaningful to you. The name of the service, (example-DBaaS-instance in this case) has to match the name of the instance you created in the Preparing AppFog section from above. Other than that, it should be fine the way it is.

After you've made those changes, you can push the application by typing cf push from the same directory as the application on your file system. You'll see a lot of lines fly by showing the different stages of launching the application. At the end of it, though, you should see a line that looks something like this:

App started


OK

App bab-dbaas-test was started using this command `bundle exec rackup config.ru -p $PORT`

Showing health and status for app bab-dbaas-test in org T3OS / space Dev as brianbuttonxp...
OK

requested state: started
instances: 1/1
usage: 128M x 1 instances
urls: bab-dbaas-test.useast.appfog.ctl.io
last uploaded: Thu Sep 17 16:11:57 UTC 2015
stack: cflinuxfs2
buildpack: Ruby

     state     since                    cpu    memory          disk          details
#0   running   2015-09-17 11:12:26 AM   0.0%   63.3M of 128M   99.9M of 1G

As long as the state above is running, you should be able to go to the URL listed in the urls line and see the output. If you get any sort of error, like a 500 Internal Server error, you can investigate it using standard CloudFoundry tools, such as cf logs bab-dbaas-test.

Conclusion

It really is this easy to provision and consume a MySQL instance using Database-as-a-Service. It only requires a small amount of CloudFoundry command line knowledge and basic knowledge of Ruby.

If anything is unclear or if you have a question, feel free to add an issue to the repo or contact me directly through this blog.

-- bab