What is PlanetScale?

PlanetScale is a MySQL compatible serverless database platform. I think of it as GitHub for databases. You can use it to host your database, create branches, and preview the impact that merging your changes will do to your database.

Okay so how do I connect to it using PHP?

The recommended way to connect to a Planetscale database is to use the mysqli() class.

$mysqli = mysqli_init();
$mysqli->ssl_set(NULL, NULL, "/etc/ssl/cert.pem", NULL, NULL);
$mysqli->real_connect($_ENV["HOST"], $_ENV["USERNAME"], $_ENV["PASSWORD"], $_ENV["DATABASE"]);
$mysqli->close();

The title says WITH PDO!

I tend to prefer using PDO objects as a personal preference.
Using PDO with SSL requires us to also setup additional 'options' when instantiating the handle.

$dsn = 'mysql:host=$host;dbname=$database;port=3306';
$user = "";
$dbP = "";
$options = array(
    PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/ca-certificates.crt'
);
try {
    $db = new PDO($dsn, $user, $dbP, $options);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $error) {
    $msg = $error->getMessage();
    echo $msg;
}

Let's break down the options we used to turn SSL on

    PDO::MYSQL_ATTR_SSL_CA => 'path_to_cert.pem',

This sets the file path to the SSL certificate authority

In some cases the MYSQL_ATTR_SSL_CA may need to be set to "/etc/ssl/cert.pem" depending on the OS your server is running. Your best bet if you aren't sure would be to use this function:

openssl_get_cert_locations()['default_cert_file']

Alternatively you can run

var_dump(openssl_get_cert_locations());

To see the string of your default_cert_file.

If you run into an error like "openssl s_client no cipher match" like I did, you can try setting the following option:

    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,

Warning

Setting this option to false is NOT recommended, Setting this to false means that any connection is insecure and susceptible to a Man-in-the-middle-attack because the certificate is not verified. If your connection works without setting this option I recommend omitting this attribute since it defaults to true.

Settings the options variable

To be more concise I prefer to use the int values for the options array. So my options look like this. It is equivalent to what you see above.

$options = array(
    1009 => "/etc/ssl/cert.pem",
    1014 => false,
);

Final code snippet

Here is what I use to connect my PHP app to PlanetScale.

$dsn = 'mysql:host=$hostURL;dbname=$databaseName;port=3306';
$user = "";
$dbP = "";
$options = array(
    1009 => "/etc/ssl/cert.pem",
    1014 => false,
);
try {
    $db = new PDO($dsn, $user, $dbP, $options);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $error) {
    $msg = $error->getMessage();
    echo "error:".$msg;
}

I hope you found this helpful! If you did, or you have a question, drop a comment below or ping me on twitter @helloLuisJ