Getting started with the PlanetScale CLI
Brian Morrison II
Brian Morrison II
7/12/2022
Product12 min read

Getting started with the PlanetScale CLI

Most modern platforms offer an excellent UI designed to get developers up and running quickly, and PlanetScale is no exception. However, a great command-line interface (also known as a CLI) can help developers in ways such as speeding up their tasks with the platform or automating tasks using scripts and DevOps tools.

In this article, we take a look at how you can quickly get up and running with the CLI, as well as perform some of the common tasks within PlanetScale like:

  • viewing databases
  • creating databases
  • running SQL
  • and deploying schema changes

Let's jump in!

Connecting to PlanetScale#

Before you can use the CLI, make sure you have installed it per the guide on our documentation portal. You can verify that you have the CLI installed by running the following command in the terminal:

bash
pscale --version

This should list the version of the PlanetScale CLI you have installed (v0.107.0 at the time of this writing).

pscale --version output

Let's start by connecting to the PlanetScale service. In your terminal, run the following command and you should receive a confirmation code. A browser window should open as well displaying the same code:

bash
pscale login

💡 If you don't already have a PlanetScale account, you can also create one straight from the CLI.

pscale login example with confirmation code

confirmation code being displayed in the browser

Confirm that the codes match, then click the Confirm code button in your browser. Your terminal will display the message “Successfully logged in.” if done correctly.

Working with Databases#

Let’s cover some of the common commands you’ll use when working with databases.

List your databases

To print a list of your databases, run:

bash
pscale database list

As you can see, I have a single database created in my account:

list of databases in my account

For the remainder of this walk-through, we’re going to go through some real-world examples using Beam, an open-source internal blogging platform we built with Next.js and Prisma. One of the common entities across most (if not all) blogging platforms is the Post, so let’s use this as a base to create a database and table. Here is the Post data model as defined with an ORM called Prisma:

js
model Post {
  id          Int          @id @default(autoincrement())
  title       String       @db.VarChar(255)
  content     String       @db.Text
  contentHtml String       @db.Text
  hidden      Boolean      @default(false)
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt
  author      User         @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId    String
  likedBy     LikedPosts[]
  comments    Comment[]
  @@index([authorId])
  @@fulltext([title, content])
}

Create a database

To create a database, you can run the following command, where <DATABASE_NAME> is the name of the database you want to create:

bash
pscale database create <DATABASE_NAME>

In this article, we’ll create and work with a database called cli-db.

pscale database create example

MySQL shell

Now, we need to drop into a MySQL shell within the database to create a table. To do this, run the following command:

bash
pscale shell cli-db

Your terminal prompt should change to indicate you are now connected to and running commands in the context of the database we just created.

pscale shell output

Since this is a new database, we don’t have any tables created yet. Let’s run the following command to create a table that mirrors the Post model from Beam.

sql
CREATE TABLE `Post` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `contentHtml` text NOT NULL,
  `hidden` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL,
  `authorId` varchar(191) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Post_authorId_idx` (`authorId`),
  FULLTEXT KEY `Post_title_content_idx` (`title`,`content`)
);

Show tables

When you hit enter, you shouldn’t get any output. You can check that the table exists with:

bash
SHOW TABLES;

output from creating the Post table

Working with Branches#

On top of managing your databases and tables, you can also manage your branches with the PlanetScale CLI.

List all branches

To demonstrate this, start by listing your existing branches on the database we created with:

bash
pscale branch list cli-db

list of branches in the cli-db database

Promote a branch

In this example, there is only one branch (main), but it’s currently not flagged as a production branch, so let’s promote it to production using:

bash
pscale branch promote cli-db main

output from promoting the main branch to production

Now that we have main set as our production branch, let's create a branch off of main called dev. Run the following command to create that branch:

bash
pscale branch create cli-db dev

You should get a message stating the branch was created successfully.

output from creating dev branch in cli-db

You can also check the dashboard to verify the branch exists.

the dev branch being displayed in the dashboard

Now that you have another branch to work on, let’s modify the schema of the dev branch and merge it into the main branch. Drop into a shell again with:

bash
pscale shell cli-db

Since you have multiple branches, the CLI will ask which branch you want to enter. Select dev and hit enter.

entering the mysql shell with the main and dev branches

Add a new column called tag with the following SQL command in the shell.

sql
ALTER TABLE Post ADD tag varchar(255);

You can use the DESCRIBE command to view how the table looks now.

DESCRIBE Post;

the current schema of the Post table in the dev branch

As you can see, tag is now added to the schema in dev.

Create a deploy request

Now let’s merge the changes in the dev branch into main using by creating a new deploy request using the CLI.

bash
pscale deploy-request create cli-db dev

This creates the deploy request for the cli-db, and we’re stating we want to merge the dev branch into the production branch.

output from creating the deploy request on the dev branch

List all deploy requests

You can show all active deploy requests with:

bash
pscale deploy-request list cli-db

a list of the deploy requests in the cli-db

You can also see the deploy requests in the dashboard using the Deploy requests tab.

a list of the deploy branches from the dashboard

Merge a deploy request

To finish this off, let’s merge this deploy request into the main branch. In your terminal, run the following where 1 is the deploy request number shown from the previous step:

bash
pscale deploy-request deploy cli-db 1

output from pscale deploy-request create

the deploy request displayed in the dashboard

Now you can check the schema of your main branch using the MySQL shell from the CLI. Enter into the shell with:

bash
pscale shell cli-db

Select the main branch from the terminal. Describe the Post table again to verify that our changes are now active.

the schema of the Post table in the main branch after the deploy request was merged

To learn more about the PlanetScale CLI, you can use the CLI Reference page in our docs which lists all of the available commands and how to use them. You can also use pscale --help to list available commands for further help within your terminal.

Your business deserves a predictable database.

Never worry about another 3am wake-up call saying the site is down. Give your engineers the power they deserve with a PlanetScale database today.