This section introduces two types of workflows that are supported by Atlasto manage database schemas:declarative andversioned migrations.

Declarative Migrations​

The declarative approach has become increasingly popular with engineers nowadays because it embodiesa convenient separation of concerns between application and infrastructure engineers.Application engineers describewhat (the desired state) they need to happen, andinfrastructure engineers build tools that plan and execute ways to get to that state (how).This division of labor allows for great efficiencies as it abstracts away the complicatedinner workings of infrastructure behind a simple, easy to understand API for the applicationdevelopers and allows for specialization and development of expertise to pay off for theinfra people.

With declarative migrations, the desired state of the database schema is givenas input to the migration engine, which plans and executes a set of actions tochange the database to its desired state.

For example, suppose your application uses a small SQLite database to store its data.In this database, you have ausers table with this structure:

schema"main"{}

table"users"{
schema= schema.main
  column"id"{
type= int
}
  column"greeting"{
type= text
}
}

Now, suppose that you want to add a default value of"shalom" to thegreetingcolumn. Many developers are not aware that it isn't possible to modify a column'sdefault value in an existing table in SQLite. Instead, the common practice is tocreate a new table, copy the existing rows into the new table and drop the old oneafter. Using the declarative approach, developers can change the default value forthegreeting column:

schema"main"{}

table"users"{
schema= schema.main
  column"id"{
type= int
}
  column"greeting"{
type= text
default="shalom"
}
}

And have Atlas's engine devise a plan similar to this:

-- Planned Changes:
-- Create "new_users" table
CREATETABLE`new_users`(`id`intNOTNULL,`greeting`textNOTNULLDEFAULT'shalom')
-- Copy rows from old table "users" to new temporary table "new_users"
INSERTINTO`new_users`(`id`,`greeting`)SELECT`id`, IFNULL(`greeting`,'shalom')AS`greeting`FROM`users`
-- Drop "users" table after copying rows
DROPTABLE`users`
-- Rename temporary table "new_users" to "users"
ALTERTABLE`new_users`RENAMETO`users`

Versioned Migrations​

As the database is one of the most critical components in any system, applying changesto its schema is rightfully considered a dangerous operation. For this reason, many teamsprefer a more imperative approach where each change to the database schema is checked-into source control and reviewed during code-review. Each such changeis called a "migration", as it migrates the database schema from the previous version tothe next. To support this kind of requirement, many popular database schema managementtools such asFlyway,Liquibase orgolang-migrate support a workflow thatis commonly called "versioned migrations".

In addition to the higher level of control which is provided by versioned migrations,applications are often deployed to multiple remote environments at once. These environments,are not controlled (or even accessible) by the development team. In such cases, declarative migrations,which rely on a network connection to the target database and on humanapproval of migrations plans in real-time, are not a feasible strategy.

With versioned migrations (sometimes called "change-based migrations") instead of describingthe desired state ("what the database should look like"), developers describe the changes themselves("how to reach the state"). Most of the time, this is done by creating a set of SQL filescontaining the statements needed. Each of the files is assigned a unique version and adescription of the changes. Tools like the ones mentioned earlier are then able tointerpret the migration files and to apply (some of) them in the correct order totransition to the desired database structure.

The benefit of the versioned migrations approach is that it is explicit: engineersknowexactly what queries are going to be run against the database when the timecomes to execute them. Because changes are planned ahead of time, migration authorscan control precisely how to reach the desired schema. If we consider a migration asa plan to get from state A to state B, oftentimes multiple paths exist, each with avery different impact on the database. To demonstrate, consider an initial state whichcontains a table with two columns:

CREATETABLE users(
    idint,
    namevarchar(255)
);

Suppose our desired state is:

CREATETABLE users(
    idint,
    user_namevarchar(255)
);

There are at least two ways get from the initial to the desired state:

• Drop thename column and create a newuser_name column.
• Alter the name of thename column touser_name.

Depending on the context, either may be the desired outcome for the developerplanning the change. With versioned migrations, engineers have the ultimate confidenceof what change is going to happen which may not be known ahead of time in adeclarativeapproach.

Migration Authoring​

The downside of theversioned migration approach is, of course, that it puts theburden of planning the migration on developers. This requires a certain levelof expertise that is not always available to every engineer, as we demonstratedin our example of setting a default value in a SQLite database above.

As part of the Atlas project we advocate for a third combined approach that we call"Versioned Migration Authoring". Versioned Migration Authoring is an attempt to combinethe simplicity and expressiveness of the declarative approach with the control andexplicitness of versioned migrations.

With versioned migration authoring, users still declare their desired state and usethe Atlas engine to plan a safe migration from the existing to the new state.However, instead of coupling planning and execution, plans are instead writteninto normal migration files which can be checked-in to source control, fine-tuned manually andreviewed in regular code review processes.