How do I connect to a MySQL database server running on PlanetScale with SSL from node.js on localhost?

Question

I'm trying to connect to the MySQL server on PlanetScale, but can't as it requires SSL.

Here's their doc for that, but it's unclear what it says. https://planetscale.com/docs/concepts/secure-connections

Here's the connection URL: DATABASE_URL='mysql://co30rXXXXXXX:pscale_pw_XXXXXXX@hoqx01444p30.us-east-4.psdb.cloud/restaurant?ssl={"rejectUnauthorized":true}'

Here's what I see from my terminal when I run yarn run migration-run

yarn run v1.22.18 $ npx prisma migrate dev Environment variables loaded from .env Prisma schema loaded from prisma/schema.prisma Datasource "db": MySQL database "restaurant" at "hoqx0XXXXX.us-east-4.psdb.cloud:3306"

Error: Migration engine error: unknown error: Code: UNAVAILABLE server does not allow insecure connections, client must use SSL/TLS

error Command failed with exit code 1. info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

Is there anyone who has tried to connect to PlanetScale DB from Node.js on localhost? I have tried some other suggestions from Stackoverflow, but don't seem to work.

Answer 1

?ssl={"rejectUnauthorized":false}&sslcert=/etc/ssl/certs/ca-certificates.crt

Adding these params at the end of the connection link, the issue has been fixed. :)

Answer 2

SSL ISSUE ON WINDOWS

If you're working on a Windows machine and using a .env file for your connection string, here is what worked for me to run locally (windows does not have a default /etc/ssl/certs/ reference as answered here).

You get your connection string from the PlanetScale console, via "overview" > "connect"

This will look something like:

DATABASE_URL='mysql://xxxxxx:*****@aws-eu-west-1.connect.psdb.cloud/dbName?ssl={"rejectUnauthorized":true}'

When plainly using this you will most likley get the follow error message (as the question states):

Code: UNAVAILABLE server does not allow insecure connections, client must use SSL/TLS

You therefore need to provide a local cert, one can be downloaded from the following trusted location:

• https://curl.se/docs/caextract.html

Next, you need to save this file to a logical location on disk that can be referenced in your connection string, for example c:/temp/cacert.pem

Once saved you can then append the following to your connection string:

&sslcert=C:\\temp\\cacert.pem

Restart your server and you should be all set!

The equivelant ssl cert update in NodeJs would look as follows:

const connection = mysql.createConnection({
    host: 'hostNameHere',
    user: 'userNameHere',
    password: 'passwordHere',
    database: 'dbHere',
    ssl: {
        ca: fs.readFileSync('C:\\temp\\cacert.pem')
    }
});

Answer 3

This worked for me:

?ssl={"rejectUnauthorized":false}&sslaccept=strict