Database Migration and Tooling
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.
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
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
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
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
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
aqueduct db list to list all database migration files and their resolved version number.
Executing Migration Files
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.