Database Migration and Tooling

The aqueduct db command line tool creates and executes migration files. A migration file contains SQL commands that create and modify database tables to match your application's data model.

PostgreSQL 9.6 and Greater

The minimum version of PostgreSQL needed to work with Aqueduct is 9.6.

Migration Files

Database tables are described by ManagedObject<T> subclasses and their table definition. Migration files describe a series of database commands that will create or modify a database schema to match an application's ManagedObject<T> declarations. Migration files are executed on a database when an application is first deployed and when changes to the data model occur - like adding new ManagedObject<T> subclasses or changing the name of a ManagedObject<T> property.

Each migration file contains only the changes made since the last migration file was generated. For example, let's say that version 1 of your application has two ManagedObject<T> subclasses, User and Post. Before you launch, you create a migration file that creates two tables, one for User and one for Post. A month later, you have developed version 1.1 of your application and now you have a third ManagedObject<T> named Location. Prior to deploying version 1.1, you generate a new migration file and execute it. This migration file only contains instructions to create a table for Location. The 'final product' of your database is the sum of both migration files.

For this reason, migrations files should be stored in source control.

Generating Migration Files

Migration files are automatically generated by running aqueduct db generate in an Aqueduct project directory. This tool finds every ManagedObject<T> subclass and adds commands to the migration file to create a database table that matches its declaration. When subsequent migration files are generated, the difference between the schema created by existing migration files is compared to the current schema declared in an application's code. The commands to rectify those differences are added to the new migration file.

This tool will find ManagedObject<T> subclasses in an application. As a convention, every ManagedObject<T> subclass is declared in its own file in lib/model/. For example, a User class is defined in lib/model/user.dart.

Migration files are stored in an application's migrations directory. Migration files are prefixed with a version number, a "0" padded eight digit number, ad suffixed with .migration.dart. For example, 00000001_initial.migration.dart is a migration filename. The version number portion of the filename is required, as is the .migration.dart suffix. The underscore and remainder of the filename are optional and have no effect, they are just a way to name the file. Here is an example of two migration file names:


The version number of migration files indicate the order in which they are applied. Leading zeros are stripped from the filenames before their version numbers are compared. Version numbers do not necessarily have to be continuous, but doing otherwise is not recommended.

Migration files may be created manually or altered after they are generated by aqueduct db generate. A migration file's Migration.upgrade method makes calls to Migration.database (an instance of SchemaBuilder) property to add, remove, and modify tables and columns. Each method invocation creates one or more SQL commands. When a migration file is executed, its upgrade method is invoked and each command is collected and run within a transaction. The commands are executed in order, and it's important to note that the order often matters.

There are scenarios where there are more than one operation can rectify a change in the data model. It is important to review migration files after they have been generated to ensure the expected behavior.

Validating Migration Files

Migration files may be altered after they have been generated. This is often the case if aqueduct db generate can't say for certain how a database should change. For example, is renaming a property just renaming a column, or is it deleting a column and creating a new column? The aqueduct db validate tool ensures that the database schema after running all migration files matches the database schema declared by an application's ManagedObject<T>s. Any generated migration file will pass aqueduct db validate. The validate tool will display differences found between the schema in code and the schema created by migration files.

Listing Migration Files

Use aqueduct db list to list all database migration files and their resolved version number.

Executing Migration Files

The tool aqueduct db upgrade will apply migration files to a running database. This tool is run in an application's directory and finds migration files in the migrations directory. The connection info for a the running database is provided with the --connect option. For example, the following would execute migration files on a PostgreSQL database:

aqueduct db upgrade --connect postgres://username:[email protected]:5432/my_application

The first time aqueduct db upgrade is executed, it creates a version table that keeps the version number and dates of upgrades. When aqueduct db upgrade is ran after the initial migration, the version number is fetched from the database. The tool only runs migration files after the version number stored in the database.

Connection information can also be stored in a database configuration file named database.yaml in the application directory. If this file exists with the following format, --connect can be omitted and connection information will be read from this file:

username: "user"
password: "password"
host: "host"
port: port
databaseName: "database"

Getting a Database's Version

You can fetch a database's current version number with aqueduct db get-version. This command takes --connect or a database.yaml file as described in the previous section to get connection info for the database.

When to Execute Migration Files

During development, there is no need to create a migration file for each change. Execute migration files prior to deployment of a new version of an application.

You may delete migration files. When aqueduct db generate is run again, will replay only the existing migration files before determining which commands to add to the new migration file. For example, if you have 10 migration files over time and delete them all - the next generated migration file will contain commands to recreate the entire database schema.