DEV Community

Kyle Nickel
Kyle Nickel

Posted on

PlanetScale CLI Basics

#database #sql #mysql

PlanetScale is a new hosted database provider. Where it differs from the rest though is in it's branching/merging features for migrations.

It allows developers to create branches off of the main database schema and then apply their schema changes in isolation.

After they're applied, a deploy request can be opened, approved, and merged seamlessly. It promises to be fast, non-blocking on schema changes, and easily scalable.

Their free tier is generous and plenty to get started messing around with.

This will be a quick crash course in how to use the CLI to accomplish some of the basic tasks through a workflow.

Prereqs

Installed the PlanetScale, pscale, CLI utility:
CLI Install Instructions

Installed the MySQL Shell:
MySQL Shell

I'll be using Linux and ZSH for reference.

Logging In

Logging into pscale is easy, but interesting process. Start by running the auth login command:

> pscale auth login
Enter fullscreen mode Exit fullscreen mode

This will open a tab in your default browser which displays a code and a confirm button.

Looking back at your shell you should see the confirmation code listed on the page:

Confirmation Code: XXXXXXX

If something goes wrong, copy and paste this URL into your browser: https://auth.planetscale.com/oauth/device?user_code=XXXXXXX
Enter fullscreen mode Exit fullscreen mode

After verifying the code and accepting it on the page your terminal should show a Successfully logged in. message.

Creating a Database

Starting out, you won't have any databases. The first step is to create one (I'm using thunks as a database name, but obviously use whatever name you want):

> pscale database create thunks
Enter fullscreen mode Exit fullscreen mode

Connecting to the Database

Note: The docs say we can just use pscale shell thunks, however I get a error for a unknown option --defaults-extra-file, so we'll be doing it manually-ish.

Now that we have a database, let's connect to it and take a look at what we have. In your terminal create the tunnel to your database:

> pscale connect thunks
Secure connection to database thunks and branch main is established!.

Local address to connect your application: 127.0.0.1:3306 (press ctrl-c to quit)
Enter fullscreen mode Exit fullscreen mode

This will give you a local address, default: 127.0.0.1, and the port, default: 3306. Use these to create your connection using the mysqlsh shell. You can omit the port if it's the default and when prompted for a password hit enter without entering one. You'll need to do this in a different shell instance as the connection process is blocking:

> mysqlsh --sql -uroot -h127.0.0.1
Enter fullscreen mode Exit fullscreen mode

Taking a look at what we have for databases:

> show databases;

+--------------------+
| Database           |
+--------------------+
| thunks             |
| information_schema |
| mysql              |
| sys                |
| performance_schema |
+--------------------+
5 rows in set (0.0279 sec)
Enter fullscreen mode Exit fullscreen mode

We see our new database thunks listed. It should be empty though:

> show tables;

Empty set (0.0245 sec)
Enter fullscreen mode Exit fullscreen mode

Just as we expected.

Branches

A empty database isn't of much use, so let's create a new branch to start a migration on.

PlanetScale uses branches much like git. So, we can create a new branch, perform our changes, and then ask the maintainer to merge/deploy those changes in. This is one of the most interesting features of PlanetScale. It allows changes to occur in isolation and deploys to be rolled out seamlessly.

By default a database is created with a branch of main.

To create a new branch we want to run the branch command with the desired name. We'll start by adding a users table so we can get some people into our data set:

> pscale branch create thunks add-users-table
Enter fullscreen mode Exit fullscreen mode

Then check that it was successfully created:

> pscale branch list thunks

  NAME              STATUS   PARENT BRANCH   CREATED AT       UPDATED AT       NOTES  
 ----------------- -------- --------------- ---------------- ---------------- ------- 
  main                       n/a             14 minutes ago   13 minutes ago          
  add-users-table            main            1 minute ago     47 seconds ago
Enter fullscreen mode Exit fullscreen mode

Making Changes

The connection we created earlier was to our main branch of the database, but the changes we want to make will be on add-users-table. Close the mysqlsh session and kill the connection process.

Now, reconnect using the our new branch:

> pscale connect thunks add-users-table      
Secure connection to database thunks and branch add-users-table is established!.

Local address to connect your application: 127.0.0.1:3306 (press ctrl-c to quit)
Enter fullscreen mode Exit fullscreen mode

Reconnect the MySQL Shell the same way as before:

> mysqlsh --sql --uroot -h127.0.0.1
Enter fullscreen mode Exit fullscreen mode

Now we can make our changes:

> CREATE TABLE users (
  login VARCHAR(50),
  name  VARCHAR(50)
);

> describe users;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| login | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.0183 sec)
Enter fullscreen mode Exit fullscreen mode

We have made the changes on our branch. Let's verify that these haven't been propagated to our main branch yet. Do the disconnect/reconnect dance and select the main branch. Then check the tables in the database:

> show tables;

Empty set (0.0247 sec)
Enter fullscreen mode Exit fullscreen mode

Perfect.

Deploy Requests

Now that the changes we want are on our own branch, we need to create a deploy request to get them merged into the main branch. This is very similar to a PR/MR in GitHub or GitLab.

We do this by running the deploy-request command with the database name and the branch we want merged in:

> pscale deploy-request create thunks add-users-table
Enter fullscreen mode Exit fullscreen mode

Then we're able to list the deploy requests that are pending:

> pscale deploy-request list thunks
  ID             NUMBER   BRANCH            INTO BRANCH   APPROVED   STATE   DEPLOY STATE   DEPLOYABLE   QUEUED AT      STARTED AT     FINISHED AT   CREATED AT   UPDATED AT   CLOSED AT  
 -------------- -------- ----------------- ------------- ---------- ------- -------------- ------------ -------------- -------------- ------------- ------------ ------------ ----------- 
  zdsty5vxjfin   1        add-users-table   main          No         open    ready          Yes          1 minute ago   1 minute ago
Enter fullscreen mode Exit fullscreen mode

Just like a PR/MR, we'll need approval before we can deploy our changes to main. This uses the number listed above as the identifier:

> pscale deploy-request review --approve thunks 1
Deploy request thunks/1 is approved.
Enter fullscreen mode Exit fullscreen mode

Deploying Changes

Now that our changes have been approved, we can deploy them out to main and get out database migrated:

> pscale deploy-request deploy thunks 1
Successfully deployed zdsty5vxjfin from add-users-table to main.
Enter fullscreen mode Exit fullscreen mode

We should verify that our changes have been made before cleaning up. Connect to the main branch and open up a MySQL Shell then check the schema:

> describe users;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| login | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.0213 sec)
Enter fullscreen mode Exit fullscreen mode

Great, our changes are there on main. Now for some finishing tasks.

Let's start out by closing the deploy request:

> pscale deploy-request close thunks 1
Deploy request thunks/1 was successfully closed.
Enter fullscreen mode Exit fullscreen mode

Now we can delete the branch:

> pscale branch delete thunks add-users-table
? Please type thunks/add-users-table to confirm: thunks/add-users-table
Branch add-users-table was successfully deleted from thunks.
Enter fullscreen mode Exit fullscreen mode

We've migrated our database and cleaned up the request and branch.

Conclusion

PlanetScale is definitely cool. It brings the very familiar feel of branching, request, and merging to live databases.

It promises to be fast, non-blocking on schema changes, and easily scalable.

I do wish the CLI documentation was better. It's pretty sparse after the top-level commands, so the help messages will be your friend.

Now that I've gotten local the local development and CLI down, I'm excited to give it a go with a app and deployment.

Top comments (0)

Read next

esproc_spl profile image

Java, perform COUNT on each group of a large csv file #eg33

Judy -

mrcaption49 profile image

Common Table Expression (CTE) in PLSQL | Part 1

Pranav Bakare -

trixsec profile image

List of the best Google Dorks in 2024 for finding SQL injection vulnerabilities and other security issues:

Trix Cyrus -

bengreenberg profile image

Hacktoberfest: The Couchbase Quest Awaits—Are You Ready?

Ben Greenberg -