Migration Directory Checkpoints

Atlas supports checkpoints, which are a way to capture the schema of the database at a certain version. When Atlas detects a checkpoint, it will use it to skip migrations that were applied before the checkpoint was created. Using checkpoints can speed up the process of setting up a database from scratch, especially in large migration directories.

Generating a Checkpoint

Atlas Pro Feature

The atlas migrate checkpoint feature is only available to Atlas Pro users. You can create a free account using the atlas login command. To learn more about logged-in features, see Features page.

Atlas can automatically generate a checkpoint for you using the atlas migrate checkpoint command. This command requires a Dev Database URL to be provided. Atlas will replay all migrations on the dev database and then inspect it to generate the checkpoint.

A checkpoint is a SQL file with a structure similar to:

-- atlas:checkpoint

CREATE TABLE t1 (
    -- ...
)

Notice the atlas:checkpoint directive, which indicates that this file is a checkpoint. Following this directive, the checkpoint file contains the SQL statements to create the tables and other objects in the database at the time the checkpoint was created.

Example

Suppose your project has been going on for a while, and you have a migration directory with 100 migrations. Whenever you need to install your application from scratch (such as during development or testing), you need to replay all migrations from start to finish to set up your database. Depending on your setup, this may take a few seconds or more. If you have a checkpoint, you can replay only the migrations that were added since the latest checkpoint, which can be much faster.

Here's a short example. Let's say we have a migration directory with 2 migration files, managing a SQLite database. The first one creates a table named t1:

migrations/20230830122359_start.sql

create table t1 ( c1 int );

And the second adds a table named t2 and adds a column named c2 to t1:

migrations/20230830122414_t2.sql.sql

create table t2 ( c1 int, c2 int );

alter table t1 add column c2 int;

To create a checkpoint, we can run the following command:

atlas migrate checkpoint --dev-url "sqlite://file?mode=memory&_fk=1"

This will create a SQL file, which is our checkpoint:

20230830123813_checkpoint.sql

-- atlas:checkpoint

-- Create "t1" table
CREATE TABLE `t1` (`c1` int NULL, `c2` int NULL);
-- Create "t2" table
CREATE TABLE `t2` (`c1` int NULL, `c2` int NULL);

Notice two things:

1. The atlas:checkpoint directive which indicates that this file is a checkpoint.
2. The SQL statement to create the t1 table included both the c1 and c2 columns and does not contain the alter table statement. This is because the checkpoint includes the state of the database at the time it was created, which can be thought of as the sum of all migrations that were applied up to that point.

Next, let's apply these migrations on a local SQLite database:

atlas migrate apply --url sqlite://local.db

Atlas prints:

Migrating to version 20230830123813 (1 migrations in total):

  -- migrating version 20230830123813
    -> CREATE TABLE `t1` (`c1` int NULL, `c2` int NULL);
    -> CREATE TABLE `t2` (`c1` int NULL, `c2` int NULL);
  -- ok (960.465µs)

  -------------------------
  -- 6.895124ms
  -- 1 migrations
  -- 2 sql statements

As expected, Atlas skipped all of the migrations up to the checkpoint and only applied the last one!

Seeding Data

Checkpoints currently do not include INSERT statements or other data seeding operations. If you need to seed data as part of your setup you will need to manually copy these statements into the checkpoint file.

Data Migrations

In long running applications, it is sometimes necessary to perform data migrations, which are migrations that modify the data in the database. Checkpoint generation ignores these migrations, as they are not needed for setting up a fresh database (which is the usecase which checkpoints are designed for).