PlanetScale CLI Basics
Tags: database, sql, mysql
Published: 2021-05-21 03:48:37

---
title: PlanetScale CLI Basics
published: true
description: Using the PlanetScale CLI to create databases, branches, and updates.
tags: #databases #sql #mysql
---
[PlanetScale](https://www.planetscale.com/) 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](https://docs.planetscale.com/reference/planetscale-environment-setup)

Installed the MySQL Shell:
[MySQL Shell](https://docs.oracle.com/cd/E17952_01/mysql-shell-8.0-en/mysql-shell-install-linux-quick.html)

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
```

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
```

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
```

## 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)
```

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
```

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)
```

We see our new database `thunks` listed. It should be empty though:
```
> show tables;

Empty set (0.0245 sec)
```

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
```

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
```

## 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)
```

Reconnect the MySQL Shell the same way as before:
```
> mysqlsh --sql --uroot -h127.0.0.1
```

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)
```

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)
```

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
```

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
```

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.
```

## 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.
```

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)
```

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.
```

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.
```

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.