Planet Scale database for Windows development: I’m doing it wrong, but it works!

I wanted to use a database by Planet Scale, and I had some initial trouble setting it up on my Windows machine. Here’s how I got it up-and-running.

Planet Scale offers a database that- similar to Git- you can make changes in a “branch” and them push/deploy it to a main branch. These are not data-driven changes, but rather structural database changes — things like adding a new table or changing the datatype of a column.

Spoiler alert: the bulk of this article did not work for me, given my computer settings. The final solution is at the end!

Looking at the Planet Scale documentation, one of the first things they suggest is to download the Command Line Interface (“CLI”). I did this, but got lost quickly after that.

Being a Windows user, I’m used to running an application and seeing a friendly prompt of what to do next.

Not a big deal. I have some basic CLI skills, so I navigated to the folder and ran it. Unfortunately that didn’t work either:

Something related to my specific instance of Windows, I would need to add “.\” in front of every command, for example:

.\pscale.exe

Strike Two! Not knowing how many other work-arounds I was going to have to deal with, I abandoned the CLI.

I already had MySQL Workbench on my computer, and I believe that Planet Scale was using a MySQL database, so I tried to connect with that.

Everything from here, forward, is using the Planet Scale dashboard; not the CLI

I created a new database in Planet Scale, but was confused with the login credentials. There were two things labeled as “password” with two different values.

I assume there was a reasonable explanation, but seeing as how I wasn’t going to pursue the CLI instructions anymore, I skipped over most of the rest of the documentation 😃

Familiar with database connectivity, I knew I needed a username, host, and password. I therefore used the bottom (second) password and ignored the first (the one that was in a dropdown).

I put these credentials into MySQL Workbench and was able to connect!

Beginning Database Development

I wrote the SQL to create a table. When I went to execute it, I received an error:

Error Code: 1005.  direct DDL is disabled

The problem was that I had connected to the “main” branch. A benefit of using Planet Scale is to create a separate development branch, make your changes there, and then merge it in after.

As such, I:

• Created a new branch within my database (on the Planet Scale site)
• Connected to this new branch in MySQL Workbench
• Ran the SQL that created a table
• Back on the Planet Scale site, merged in the new branch into “main”

Back on the “main” branch, still in MySQL Workbench, I was able to write an INSERT statement, and then a SELECT query to verify it works.

Cool. Now I just needed to write code to access it on the web.

Setting up Node.js locally on a Windows

I’m a big fan of PHP (I even wrote a book on it), but I wanted to try Node.js .

After installing the express package, I started with this code:

const express = require('express')
const app = express()
const port = 3000
app.get('/', (req, res) => {
   res.send('Hello World!')
})
app.listen(port, () => {
  console.log(`Example app listening at http://localhost:${port}`)
})

Then I installed the mysql package.

Planet Scale advises to connect with this string:

mysql://<USERNAME>:<PLAIN TEXT PASSWORD>@<ACCESS HOST URL>/<your-db-name>?sslmode=require

I put that in a variable and tried to connect with this:

var myDbUrl = "mysql://...myURLHere...?sslmode=require"
var mysql   = require('mysql');
var connection = mysql.createConnection(myDbUrl);
connection.connect();
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
   if (error) throw error;
   console.log('The solution is: ', results[0].solution);
});
connection.end();

Upon doing so, I received this error:

The problem was that the localhost- on my personal computer- was not set up to run over an SSL connection.

I came across this article, which I thought got me most of the way there: How to set up SSL locally with Node.js?

Being on a Windows machine, I first decided to install chocolatey (basically NPM for Windows).

Again, based on my Windows configuration, I had to pay extra attention to the instructions: I needed to run it as an Administrator from PowerShell it get it to work.

I then ran the problem of, how do I specify the SSL credentials using the connection string that they advised?

The answer: don’t use the suggested Planet Scale connection string.

I changed the connection string to this:

var connection = mysql.createConnection({
   host: 'xxx',
   user: 'yyy',
   password: 'zzz',
   database: 'aaa',
   ssl: {
      key: fs.readFileSync(KEY_PATH),
      cert: fs.readFileSync(CERT_PATH)
   }
});

Notice that I’m passing in an additional option of “ssl” and that relied on a specific file on my computer (as instructed by the article linked above). This relies on the npm package “fs” (file structure), so you need to install that.

At the time of writing, the instructions in the link above had reversed KEY_PATH and CERT_PATH. Make sure you have them in the right order!

Ultimately I couldn’t get that to work, either. Maybe you’ll have better luck 😃.

Final solution: Node.js on Windows — connecting to a Planet Scale database

I abandoned the concept of using the localhost.pem and localhost-key.pem files. Planet Scale’s documentation indicated that I could download a file cacert.pem here.

Note that I needed to “escape” the backslashes in my code. Even though it’s stored on my computer in C:\temp\cacert.pem, the actual code that references it is:

ca: fs.readFileSync('C:\\temp\\cacert.pem')

Here’s the code:

Be sure to NOT put your database credentials right in the code; perhaps use environment variables

Looking at the terminal, I can see the solution to the math equation:

No errors! Here’s sample code to actually retrieve data from a database:

The code on lines 14–20 indicate that, when accessing the “users” endpoint, data will be returned.

You may be wondering, why bother using Planet Scale if I’m not going to take advantage of the branch merging to test development code?

The previous MySQL platform I was using had some downsides, that I was hoping to avoid with a new provider. At the time of writing, Planet Scale’s “free” version seems like a suitable option for my project. And so far, the things I didn’t like about the other/initial competitor, I haven’t run into.