Non-Blocking Schema Changes

Non-Blocking Schema Changes in PlanetScale provide a schema change workflow that allows users to update database tables without locking or causing downtime for production databases.

Non-blocking Schema Changes Overview

Tip: To make Non-Blocking Schema Changes in PlanetScale, you'll first need a basic understanding of branching, the core PlanetScale feature that provide schema changes. Our branching concepts page is a great place to start.

Non-Blocking Schema Changes in PlanetScale provide a schema change workflow that allows users to update database tables without locking or causing downtime for production databases.

PlanetScale makes it safe to deploy schema changes to production databases via Development and Production branches. Production (main) branches cannot be deleted and can only be updated using deploy requests. Development branches are a separate database with a copy of the main branch's schema. Developers can make schema changes in Development branches, test locally, and open a deploy request for deploying their changes to the production database.

Developers can also comment on deploy requests and request reviewers to approve a deploy request before its schema changes can deploy into the main database branch. Currently, requiring approval is a per-database setting is turned off by default. With the setting turned off, developers do not need approval to merge a Deploy Request.

Adding columns to large tables with PlanetScale is safe!

Create, Drop, and Alter statements, also known as Data Definition Language (DDL), are used for making schema changes in a database table.

PlanetScale enables developers to make schema changes without the fear of dropping columns, locking tables, causing downtime in their app, etc. PlanetScale also prevents schema changes with conflicts from being migrated and handles schema changes from multiple teammates. A user doesn't have to wait to find out if their changes will be rejected, they learn as they add the change to the queue.

How do I make Non-Blocking Schema Changes with PlanetScale?

All PlanetScale schema changes must occur on a database branch. (A database branch is a separate database with a copy of the main branch's schema.)

At a high level, this is what happens during the Non-Blocking Schema Change process in PlanetScale:

  1. You create a database branch.
  2. You test your changes on this branch before attempting to apply the changes to the main branch. (i.e., You made some changes to the database you wish to deploy to the production database.)
  3. You open a request to deploy your changes to main.
  4. PlanetScale verifies that your schema changes are safe to be deployed to production. If there are any issues or schema conflicts, you'll be shown the errors.

When you hit deploy, it doesn't mean that PlanetScale can immediately apply your schema changes to the production database. After all, there may be a current running deployment from a previous user or even from yourself. The latest deployment request automatically yields to production traffic and gets added to a deployment queue in these situations.

  1. Your deployment makes it to the main branch (i.e., production), and you can now see your schema changes in the production database.
A note: PlanetScale makes sure not to exhaust your resources; the deployment may be throttled to avoid any impact on production queries.

PlanetScale Non-Blocking Schema Changes Diagram
PlanetScale Non-Blocking Schema Changes Diagram

PlanetScale Workflow

The PlanetScale command-line tool (CLI) runs an interactive shell equipped with many commands designed to make the database management workflow easier for developers.

A basic Non-Blocking Schema Change workflow in PlanetScale might look like this:

  1. Create a database:
pscale database create <database>
  1. Create a development branch:
pscale branch create <database> <branch>
  1. Make a schema change on this branch:
pscale shell <database> <branch>
Tip: A schema change is any change you make to the tables in your database environment created within the PlanetScale branch. (i.e., Create, Drop, and Alter statements )
Important: You can only apply direct schema changes to the development branch, not to `main`.

Here is a sample CREATE table schema change you could try using:

CREATE TABLE `reminders` (
`id` bigint NOT NULL AUTO_INCREMENT,
`body` varchar(1024) NOT NULL,
`created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`is_done` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  1. Test changes on branch locally.

  2. Create a deployment request by running:

pscale deploy-request create <database> <branch>
  1. Fix any schema conflicts.

PlanetScale displays the difference between what is currently in the main branch and your branch. Go back to Step 3 of the workflow and test out new schema changes to fix the schema conflict. If you did not encounter any schema conflicts, you're ready for Step 7.

  1. Deploy the deploy request.
  • To deploy the deploy request created in Step 5, run the following command:
pscale deploy-request deploy <database> <deploy-request-number>
  • To find your deploy-request-number, simply run:
pscale deploy-request list <database>

Copy the value from NUMBER and use that digit as your deploy-request-number.

Limitations

PlanetScale's Non-Blocking Schema Changes' workflow doesn't support FOREIGN KEYs in users' databases.

PlanetScale determined that the production safety that Non-Blocking Schema Changes provide are worth this technical tradeoff. Learn more.

Need help?

Get help from PlanetScale's support team, or join our GitHub Discussion board to see how others are using PlanetScale.

Next Steps

Was this page useful?
Last updated on June 17, 2021
PrivacyTerms
© 2021 PlanetScale Inc.