DEV Community

Cover image for Connecting to PlanetScale with a PDO Object in PHP
Luis Juarez
Luis Juarez

Posted on • Edited on

Connecting to PlanetScale with a PDO Object in PHP

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();
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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

    PDO::MYSQL_ATTR_SSL_CA => 'path_to_cert.pem',
Enter fullscreen mode Exit fullscreen mode

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']
Enter fullscreen mode Exit fullscreen mode

Alternatively you can run

var_dump(openssl_get_cert_locations());
Enter fullscreen mode Exit fullscreen mode

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,
Enter fullscreen mode Exit fullscreen mode

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,
);
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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

Top comments (1)

Collapse
 
juniorhuanca profile image
JuniorHuanca

Do you know what is causing this error?: "Error!: SQLSTATE[HY000] [2002]"