Connecting to PlanetScale with a PDO Object in PHP
Tags: php, tutorial, webdev, planetscale
Published: 2022-03-26 01:16:17

#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