Discover PlanetScale: The MySQL serverless database

Photo by Matthew Henry / Unsplash

PlanetScale is a Serverless database for MySQL that brings a new way to manage your database from development to production. It provides the concept of branching, which is popular among Git platform providers.

Basically, you can manage the evolution the same way you do with your application's source code.

PlanetScale Features

Apart from being a Serverless database, here are the features of PlanetScale:

• Database Branching: Allow you to manage your database change the way you do with your code using a Git Platform provider like GitHub, GitLab, BitBucket, etc.
• Non-blocking schema changes allow users to update database tables without locking or causing downtime for production databases.
• Backup and Restore: It provides the ability to create, schedule, and restore backups for production and development database branches.
• Access Control: Within an organization, you can create roles and permissions and then assign them to users to restrict access to only necessary data or actions.
• Audit log: It allows you to view all the actions and events that occurred in your organization.
• Authentication Security: You can use multi-factor authentication and Single Sign-On (SSO) to enforce your organizations' security.
• Query statistics: Find and optimize long-running queries in your application.
• Multi regions deployment: deploying a database in multiple regions is possible. Selecting the closest region to your application servers reduces latency between your database and application.
• Great Developer Experience: You can manage everything from a Web console or a CLI. The documentation is excellent, with many tutorials in various languages and technologies.

Create an account

PlanetScale offers a generous plan to start building your web application with a serverless MySQL database. If you are interested to see others pricing plans, check them out.

Go to the signup page, you will have the page below:

Signup with the method you prefer. I use GitHub authentication; you will be asked to confirm your email when using the email method. Once done, you will reach a dashboard page below:

There is a button at the top right of the page to create a database. Indeed, through the Web UI, you can do everything from the database creation until monitoring production.
PlanetScale also has a CLI, which is great when you don't have to go to Web UI to complete something. This can create friction which impacts developer velocity. Throughout this tutorial, we will use the CLI to discover Planetscale features.

Install the PlanetScale CLI

The CLI is currently supported on Windows, Linux, and MacOS. Check out this page to see the installation instructions. Here are the commands to install it on MacOS

brew install planetscale/tap/pscale

# required to run certain commands with the PlanetScale CLI
brew install mysql-client

To upgrade to the new version, just run: brew upgrade pscale

Check that the CLI is installed successfully by running: pscale --version

The use case

We will build a light authentication system where we only need the user's table.

We will use the branching workflow provided by PlanetScale to create this database.

Authenticate from the CLI

We created an account through the web application with credentials. To use the CLI to manage our organization, we need to log in first. Open a terminal and run the command below pscale auth login

To authenticate from the CLI, you must confirm that the confirmation code generated in the Terminal is the same web page.

To logout, run the command pscale auth logout

Create the database

We are now connected to the database. Let's create our database.

pscale database create auth-db

To view the list of your database, run.

pscale database list

Note: To delete a database, run pscale database delete <DatabaseName>. Add the flag --force to delete without a confirmation.

Promote the main branch to production

By default, the database is created with a default branch called "main"; you can run this command to view the list of a database branches:

pscale branch list auth-db

As we can see, the column "Production" value is "No", meaning it is not the production database. We need to change it to "Yes" with the command below:

pscale branch promote auth-db main

Why promoting the main branch to production:

By doing this:

• Production database: we indicate that is the branch to connect our application deployed in production on.
• No direct update: The database can no longer be updated directly by creating a deploy request. This prevents accidentally changing the database schema with an inconsistent one and crashes the application.
• Source of truth: When merging a branch created from this branch, the main, PlanetScale, will ensure that new changes are consistent. Otherwise, the branch will be seen as conflicted.

Create the table users

To create this table, let's create a new branch from the main; we will call it create-users-table.

pscale branch create auth-db create-users-table

Update database schema with PlanetScale Shell

The branch is created, we need to write and execute the SQL code to create the table, and to do that, PlanetSCale CLI makes it possible by opening a secure MySQL shell instance to our database's branch. Run the command below:

pscale shell auth-db create-users-table

Now we are connected, we need to execute the code below to create the database:

CREATE TABLE `users` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `full_name` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `password` VARCHAR(100) NOT NULL,
    `role` VARCHAR(191) NOT NULL,
    `enabled` BOOLEAN NOT NULL DEFAULT false,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `users_email_key`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Copy the code above, paste it into your Terminal, then press ENTER to execute it. Use the MYSQL command show tables command to verify the table has been created successfully.

Create a deployment request

Now we need to apply the change done on the branch create-users-table to the main branch. To do that, we need to create a deployment request. Remember, the main branch can't be updated directly (connect to shell and execute the script)

Run the command below to create a deploy request:

pscale deploy-request create auth-db create-users-table

This step can be viewed as the pull request when working with GitHub. So at this step, a teammate should review the database schema then, approve if everything is good, or comment to make a suggestion or ask for clarity.

Review the deployment request

This step should be done by someone who didn't write the SQL script. So when the creator of the deploy request notifies you, you need to list the deploy request first. Run this command:

deploy-request list auth-db

We got the following output:

The column to pay attention to is the "NUMBER" because we need it to view or update the deploy request. So our deploy request number is 1.

To view the change made on the deploy request, run the command:

pscale deploy-request diff auth-db 1

We got the following output:

You can add a comment or approve to review the deploy request, and they aren't mutually exclusive.

There is no index on the columns role and enabled we could add a comment to notify the developer he forgot to add that. He is the comment we can do:

pscale deploy-request review auth-db 1 --comment You forgot to add indexes on role and enabled. I will approve but create a new branch to fix it

Here is the output:

Approve and merge to the main branch

To approve the deploy request, run the command:

pscale deploy-request review auth-db 1 --approve

Once the deploy request is approved, we can merge it to the main branch. Note that the merge only copies the structure and not the data so, don't expect to see the data you have inserted through Shell in the main branch.

Run the command below to deploy in the main branch:

pscale deploy-request deploy auth-db 1

We got the following output:

The deployment to the production branch can take some time, depending on how many deployments are in the queue. Think of multiple teams working with the same database, and others teams created a deploy request before you.

Delete the branch

Once the deployment to the main branch is done, we no longer need the branch create-users-table so will delete it:

pscale branch delete auth-db create-users-table

Display the branch list to see if it is deleted

Add forgotten indexes

Since we are now familiar with how PlanetScale will work, we will quickly review all we did.

• Create the new branch: pscale branch create auth-db add-forgotten-indexes
• Connect to shell and execute the script: pscale shell auth-db add-forgotten-indexes
• Execute the script below:

CREATE INDEX `users_role_idx` ON `users`(`role`);
CREATE INDEX `users_enabled_idx` ON `users`(`enabled`);

• Create a deploy request: pscale deploy-request create auth-db add-forgotten-indexes
• Verify the changes: pscale deploy-request diff auth-db 2 (2 is the number of the deploy-request)

• Approve the deploy request: pscale deploy-request review auth-db 2 --approve
• Deploy the request: pscale deploy-request deploy auth-db 2

We are all set!

Others CLI command

We have seen some useful commands of the PlanetScale CLI to show branching workflow, but we didn't cover everything. To view other available commands, you can type pscale --help

Connect from a database GUI tool

We saw how to connect through the shell using the CLI but, how about GUI database tools?
To connect to the database using a GUI tool, good the web UI to copy the connection string.

• On the dashboard page, click on the database you want to get the credentials
• On the next page, do the following steps:

• Once the password is generated, you will get the page with the credentials you need; copy them and fill in the inputs of the GUI tool.

Conclusion

It is the end of this tutorial where we explored the branching workflow provided by PlanetScale to manage change in our database. To learn more about it, check out their well-detailed documentation.

Overview - Documentation - PlanetScale

Start small and grow to massive scale

PlanetScale

Follow me on Twitter or subscribe to my newsletter to not miss the upcoming posts and the tips and tricks I share every week.

Happy to see you soon ?