Non-blocking schema changes

Overview

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 development 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.
5. You click Deploy changes. Your deploy is added to a queue and run immediately or when existing deploys are complete.
6. Your deployment makes it to the main branch (i.e., production), and you can now see your schema changes in the production branch.

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>
   

2. Create a development branch:

   pscale branch create <database> <branch>
   

3. 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 )
   Warning

   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;
   

4. Test changes on branch locally.

5. Create a deployment request by running:

   pscale deploy-request create <database> <branch>
   

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

7. 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 doesn't support FOREIGN KEY constraints. Learn why and how to operate without foreign keys constraints in this tutorial.

PlanetScale doesn't support direct RENAME for columns and tables. Learn why and how to rename tables or columns in this tutorial.