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.
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.
Never attempt to modify the schema by changing already-committed migration files or
directly. Changing existing migration files will break users who have already applied those migrations,
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
sqlx migrate add [describe-the-migration]
This will create a blank migration file, in which you can add Postgres commands that modify the schema.
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
rather than only in the migration file.
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
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
which does the following:
- Runs a Postgres server in a Docker container
- Applies all migrations to the Postgres server
sqlx prepareto generate type information for all SQL statements
- Dumps the Postgres database to
- Shuts down the Postgres server