Connecting applications to Compose for MySQL

At Compose, we focus on making databases accessible and easy. Since launching Compose for MySQL in beta, we've discussed how to set up a MySQL deployment and connect to it from the command line. Now, we are going to look connecting your applications to Compose for MySQL databases.

In this article, we'll look at how to connect to Compose for MySQL using your deployment's connection string, so that you can start connecting to MySQL immediately. This quick connection guide covers connection drivers and gives code samples you can run in 5 popular programming languages: JavaScript, Go, Python, Java, and Ruby.

We’re assuming that you’ve set up a Compose for MySQL deployment. If you don't have a deployment yet, take a peek at our Connecting to Compose for MySQL guide, which will help you to quickly set one up.

The Connection String

The connection string is the first point of reference to extract nearly all the information we need to connect to a database. It's found in the deployment’s Connection Info panel.

The connection string is formatted as a URI that can be used where drivers and applications understand the standardized format. Where they don't, we can extract the essential details from the connection string URI. If, for example, this:

is the connection string URI, then to use it we'll need to enter the username and password into the [username] and [password] positions respectively. If you are going to use the 'admin' user's password, you can find that by revealing the password in the Credentials panel:

The [username] and [password] will automatically be replaced with your credentials in the displayed connection string.

In our connection guide examples, we'll be connecting with the admin user account. The admin user is a fully privileged user who has administrative access to all our databases. However, for security reasons, the admin account should only be used to create users and grant them privileges, and not to connect to applications.

Other drivers use component parts of the connection URI – the host name and port – for their connection parameters. The host name is found after the @ symbol and the port number is after the :. We've marked them below so they can be found easily.

We create a "compose" database in our deployments to ensure we have at least one database that can be connected to with the default connection string. If you don't designate a database to connect to, you would need the USE my_database command to select a database; a MySQL connection doesn't have to specify a database to connect.

SSL and MySQL

All MySQL deployments have SSL enabled by default; however, the way MySQL does SSL means it will also accept non-SSL connections. This means that, when configuring your application's MySQL driver, you will want to make sure that it's creating a secure connection. With the MySQL command line, that's simply a matter of adding --ssl-mode=REQUIRED to the connection command. For application drivers, the settings are specific to the driver. In our following examples, we will show commands which should always and only connect with SSL enabled.

Using the Self-Signed Certificate

Security isn't just a matter of encrypting your connection; it also helps to know you are talking to the right system. That's why Compose deployments offer a self-signed certificate which can be used to validate the host being talked to. To use the certificate we'll want to copy it to a file. The certificate is found on the Deployment Overview page here:

Click on the Show certificate button where you will be prompted to enter the Compose account password. Then the certificate will be revealed like:

Copy and paste the text from -----BEGIN CERTIFICATE----- to -----END CERTIFICATE----- into a file with the extension .cert or .pem, depending on which the connection driver requires. Save the file to a location that you can access because we'll need the certificate's path later.

We now have the username, password, connection string and certificate to enable us to connect to Compose for MySQL, so let's begin.

JavaScript, Node.js and Compose for MySQL

The leading driver for MySQL for Node.js is the node-mysql driver. We'll walk through setting up a simple Node.js/MySQL connection here. If you want to follow along, make sure that Node.js and npm are installed on your system.

After initializing a new project using npm init, install the driver package in the project’s folder using:

npm install mysql --save  

This will install the driver and its dependencies in the node_modules folder, and the driver will appear under dependencies in the package.json file.

We can connect to MySQL as follows:

const mysql = require('mysql');  
const fs = require('fs');  

Here, we require the mysql driver and filesystem module (as we need to read a local file).

const connection = mysql.createConnection(  
    {
        host: 'aws-us-east-1-portal.23.dblayer.com',
        port: 15942,
        user: 'admin',
        password: 'mypass',
        ssl: {
            ca: fs.readFileSync(__dirname + '/cert.crt')
        }
});

Here we're using the more explicit form of createConnection, which lets us specify all the parameters in the form of a JavaScript object. The createConnection call can take a URI as a parameter too, but doing that means we can't read and pass the certificate. That's what is happening with this bit of code:

        ssl: {
            ca: fs.readFileSync(__dirname + '/cert.crt')
        }

This uses Node's synchronous file read function to get our certificate from the local disk and pass it to the createConnection function. When the connection is being established, the driver will use it to verify that the server it is talking to is in the one that generated this certificate. If it isn't, then the connection will close and throw an error stating: Error: unable to verify the first certificate.

Interestingly, the driver doesn't actually make a connection after createConnection() is called. It just sets everything up ready to make a connection. To make a connection, you can call the connect() method on the created connection, which gives you the opportunity to catch any errors, or you can let the driver make the connection when needed by, for example, making a query. That's what we'll do next in the code.

connection.query('SHOW DATABASES', (err, rows) => {  
    if (err) throw err;
    console.log('Connected!');
    for (let i = 0, len = rows.length; i < len; i++) {
        console.log(rows[i]['Database'])
    }
});

SHOW DATABASES is a MySQL command that will list the databases that a user has privileges for. For this driver, the results of the query are returned as an array of objects, which we can iterate through and print to the console. Since we've logged in as the admin user, we'll see all the databases on our deployment.

Connecting with Go

Go has a generic interface for SQL databases and using it with the go-sql-driver will provide us with several options to connect to MySQL. The problem with the driver is that we cannot verify the self-signed certificate. The only TLS/SSL option we have with self-signed certificates is skip-verify, which is less than optimal.

However, if you want to connect to MySQL using Go, here's the way to do it. First, we'll have to install the go-sql-driver package to our $GOPATH by:

go get github.com/go-sql-driver/mysql  

We can now create the code needed to connect to the database. We start by importing the sql interface and the mysql driver that uses it.

package main

import (  
    "database/sql"
    "log"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

After importing the driver, we sql.Open a database connection using the variant of the deployment's connection string. The Go driver likes to be told what what transport it is using (TCP in this case), and that's done by surrounding the host and port number with tcp(). Also, as it's a generic SQL framework, the "mysql://" part is removed and the first parameter passed is the name of the driver - mysql. Finally, there's the option to pass parameters in a URL style with a ?name=value format. Put that together and it looks like this:

func main() {  
    db, err := sql.Open("mysql", "admin:mypass@tcp(aws-us-east-1-portal.23.dblayer.com:15918)/compose?tls=skip-verify")

Notice that we have passed in a TLS/SSL configuration option tls=, which in our case is ?tls=skip-verify. This is necessary because the Go library isn't currently able to verify with the self-signed certificate.

As with all idiomatic Go code, we then check the error returned, and to ensure we clean up, defer the act of closing the connection.

    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

Again, the driver does not make a connection to the database until a query is made. One important note is that the sql.Open function is a pool for database connections, which opens connections when they're requested. The function does not handle errors, so we'll want to handle them ourselves like:

    if err != nil {
    log.Fatal(err)
    }
    defer db.Close()

Executing a query is achieved by using the connection's query method with an SQL command:

    rows, err := db.Query("SHOW DATABASES")
    if err != nil {
    log.Fatal(err)
    }

Here, we're assigning the results of the Query to the variable rows. We can iterate through the results using Next(), extract the values from the row with Scan and then log the results – the database names – to the console.

    var dbNames string
    for rows.Next() {
    err := rows.Scan(&dbNames)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(dbNames)
    }
}

Connecting with Python

To connect MySQL with Python application, we'll use MySQL's official Python connector by installing its connector package. After installing the package, we can connect to our database using the following code:

import mysql.connector

config = {  
    'user': 'admin',
    'password': 'mypass',
    'host': 'aws-us-east-1-portal.23.dblayer.com',
    'port': 15942,
    'database': 'compose',

    # Create SSL connection with the self-signed certificate
    'ssl_verify_cert': True,
    'ssl_ca': 'cert.pem'
}

connect = mysql.connector.connect(**config)  
cur = connect.cursor()  
cur.execute("SHOW DATABASES")

for row in cur:  
    print(row[0])

connect.close()  

In this example, we're importing the MySQL connector and adding any connection parameters within a config dictionary. We're also initializing a secure connection using the deployment's self-signed certificate in the connection dictionary:

{ ...
    'ssl_verify_cert': True,
    'ssl_ca': 'cert.pem'
}

ssl_verify_cert requires the ssl_ca option. With the configuration option 'ssl_verify_cert': True, we're making sure that the server's certificate and the certificate stored in our application cert.pem are the same. If there's a certificate error, Python will return a ValueError indicating that the certificates don't match.

When establishing a connection, we can pass in the configuration dictionary into the connector's connect method like:

connect = mysql.connector.connect(**config)  

or insert the required arguments individually within the function. It depends on what's easier for you. A useful list of the available connection arguments can be found here.

The MySQL Python connector requires that a cursor object be instantiated in order to execute SQL queries. Therefore, in our code sample, we use the cursor method of the connection object here and assign it the variable cur:

cur = connect.cursor()  

Then, we assign the variable query to the SQL command, which tells MySQL to show all of the deployment's databases:

cur.execute("SHOW DATABASES")  

The execute method executes database operations that convert Python objects to MySQL commands over a secure connection. In this case, our execution method tells MySQL to show all the databases, which we then iterate over, log to the console, and close the connection:

for row in cur:  
    print(row[0])

connect.close()  

Connecting with Java

Perhaps the most frequent query that we get from customers is for details of how to connect to their Compose deployment using Java. Java's handling of certificates and SSL does make this process a bit more involved than other languages.

The first step in connecting to MySQL is to download and install MySQL's Connector/J JDBC driver.

We can then use the following code to connect to the deployment:

import java.sql.*;

public class Main {

    public static void main(String[] args) {

       System.setProperty("javax.net.ssl.trustStore","/home/project/truststore");
   System.setProperty("javax.net.ssl.trustStorePassword","somepass");

       String user = "admin";
       String password = "mypass";
       String URL = "jdbc:mysql://aws-us-east-1-portal.23.dblayer.com:15942/compose" +

                "?verifyServerCertificate=true"+
                "&useSSL=true" +
                "&requireSSL=true";
        try {

            Connection conn = DriverManager.getConnection(URL, user, password);

            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SHOW DATABASES");

            while (rs.next()) {
                String dbname = rs.getString("Database");
                System.out.format("%s\n", dbname);
            }

            st.close();

        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("Failed");
        }
    }
}

The basic information we need to set up a connection is within the connection string. We use that with the JDBC API and store that string in the variable URL:

String URL = "jdbc:mysql://aws-us-east-1-portal.23.dblayer.com:15942/compose?verifyServerCertificate=true&useSSL=true&requireSSL=true";  

The username and password for the deployment are stored in separate variables. These will be used to establish a connection with the JDBC DriverManager.getConnection() method. There, we will pass in the user, password, and the URL as arguments like:

String URL = "jdbc:mysql://aws-us-east-1-portal.23.dblayer.com:15942/compose?verifyServerCertificate=true&useSSL=true&requireSSL=true";  
String user = "admin";  
String password = "mypass";

Connection conn = DriverManager.getConnection(URL, user, password);  

Like Go's driver, the connection string includes the host name, port, and database we're connecting to, while the SSL options are appended to the connection string.

"?verifyServerCertificate=true&useSSL=true&requireSSL=true"

What we're telling the server to do here is to establish an SSL connection and to verify using the self-signed certificate. We could remove these options from the URL and still connect to the server, but it would be insecure and we'd receive a warning from MySQL stating:

"Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default..."

To allow the driver to process the certificate, we'll have to save the credentials in a file called cert.pem. Unlike the other drivers discussed so far, we can't use the certificate directly. Instead, for Java we create a trust store to hold the certificate's credentials using keytool, which would look like this:

keytool -import --alias composeCert -file ./cert.pem -keystore ./truststore -storepass henrythedog  

What the command does is import the cert.pem file, assign it an alias called composeCert, or whatever name we want, and define the output file as a keystore called truststore. It uses the password henrythedog to unlock the keystore.

Now, we'll have to set up some JVM properties to use the truststore filename and password so that they're set in the system before we start the connection. We'll use the setProperty method on javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword to use the information we set up in the keystore.

System.setProperty("javax.net.ssl.trustStore","/your/path/truststore");  
System.setProperty("javax.net.ssl.trustStorePassword","henrythedog");  

The system properties should be set towards the top of the program to run early at runtime; preferably set them before the Connection variable, so that they're locked in before starting an SSL connection.

After creating the connection, we then get a Statement object from the Connection and execute our query against that. This returns a ResultSet which we can iterate through, retrieving and printing the database names from each row.

Statement st = conn.createStatement();  
ResultSet rs = st.executeQuery("SHOW DATABASES");

while (rs.next()) {  
    String dbname = rs.getString("Database");
    System.out.format("%s\n", dbname);
}

Connecting with Ruby

There are a number of MySQL drivers for Ruby. MySQL2 is the fastest and the more Ruby-like of them. The code to create a connection looks like:

# MySQL2 Driver
require "mysql2"  
config = {  
    "hostname": "aws-us-east-1-portal.23.dblayer.com",
    "port": 15942,
    "database": "compose",
    "username": "admin",
    "password": "mypass",
    "sslCA": "cert.pem",
    "sslverify": true
}
conn = Mysql2::Client.new(config)  
results = conn.query("SHOW DATABASES")  
results.each {|row| puts row["Database"]}  

In keeping with Ruby's tendency towards succinct and readable code, creating a connection is straightforward. All of the connection configuration is done within the config hash:

config = {  
    "hostname": "aws-us-east-1-portal.23.dblayer.com",
    "port": 15942,
    "database": "compose",
    "username": "admin",
    "password": "mypass",
    "sslCA": "cert.pem",
    "sslverify": true
}

The configuration hash is where we set up the host name, ports, database, username, and password. Other driver options are available from setting up the configuration of the Ruby gem to other connection and SSL options, which can be specified in the configuration hash.

Setting up an SSL connection is done within the connection's configuration hash. The gem lists seven parameters that a user can define, but the parameters are given default values if they are not set.

To set up a secure connection using the self-signed certificate, only two options need to be set: sslCA and sslverify. sslCA is the path of the .pem or .cert file containing the certificate, while sslverify should be set to true in order check for a valid certificate.

Now, setting up a connection and query is similar to other drivers that we've been looking at.

conn = Mysql2::Client.new(config)  
results = conn.query("SHOW DATABASES")  
results.each {|row| puts row["Database"]}  

We set up a new connection variable by initializing the Mysql2::Client.new constructor and passing in the config file. We create a query using the constructor we defined and pass in a SQL command into the query method to execute on the database. The results of the query is an array of hashes, which are iterated through and printed to the console.

All set

We set up this quick connection guide to help you get connected to your deployment so that you can start exploring Compose for MySQL. In the coming weeks, we will dive into new MySQL features and provide you with a more in-depth guide to security features. For now, spin up a database and start connecting.

Image by Mike Wilson