Database Migrations

Plane uses sqlx (opens in a new tab) to manage database migrations.

Migrations are located in the plane2/schema/migrations directory as a series of .sql files that are prefixed with a timestamp.

The migrations are built into the Plane binary. When a controller starts, it will compare the latest migration in the database with the latest migration in the binary. If the database is behind, the controller will run the necessary migrations to bring the database up to date.

The derived_schema.sql file in plane2/schema is a generated file that contains a complete database dump of an empty database after applying all migrations. This file is meant to be a reference for the current database state. It is also useful when evaluating a PR that introduces a migration, as it shows how the migration will impact the entire schema.

Modifying the schema

Never attempt to modify the schema by changing already-committed migration files or derived_schema.sql directly. Changing existing migration files will break users who have already applied those migrations, and changing derived_schema.sql will not have the desired effect because it is only provided as a reference.

Instead, schema changes should be made by adding a new migration file. You can use the sqlx CLI tool to create a new migration file:

cargo install sqlx-cli
cd plane2/schema
sqlx migrate add [describe-the-migration]

This will create a blank migration file, in which you can add Postgres commands that modify the schema.

Comments

When a migration creates new tables or columns, it should generally add comments for those tables and columns.

Rather than using SQL comment syntax, it is recommended to use the COMMENT ON Postgres command. This will cause the comments to be stored in the database itself, and therefore in the derived_schema.sql file, rather than only in the migration file.

Developing with SQL

SQL queries in Plane are type-checked at compile time through sqlx. For this to work, sqlx needs to find all SQL statements in the code, compare them to a Postgres server with the database schema applied, and generate type information (stored in plane2/.sqlx/ as .json files.)

When you modify an existing SQL statement or add a new one, sqlx will initially complain because it does not have type information for the new SQL statement. This can be rectified by running plane2/schema/prepare.sh, which does the following:

• Runs a Postgres server in a Docker container
• Applies all migrations to the Postgres server
• Runs sqlx prepare to generate type information for all SQL statements
• Dumps the Postgres database to plane2/schema/derived_schema.sql
• Shuts down the Postgres server