Schema model
Published 10 December 2024
EDITION: TEAMS / EDITION: ENTERPRISE
Schema model supports SQL Server, Oracle, PostgreSQL, and MySQL databases. Get in touch if you're interested in other databases.
A schema model is a representation of a database in a file-based format. Each database object is stored in a separate file containing a human readable representation of the object. This makes it possible to store the schema of your database as it looks right now in a version control system alongside your migrations. This can be useful to track how individual objects have evolved over time.
Note that this is similar to snapshots in that both are filesystem representations of a database, but snapshots are in a binary file format. Snapshots are more optimised for persisting a known database state, but are not useful for version control.
For more technical information see the reference.
Using the schema model manually through Flyway Desktop requires Flyway Teams edition. Automated use of the schema model through pipelines requires Flyway Enterprise edition.
Using the schema model to capture database changes
Multiple developers can make changes to their development database, capture those changes in the schema model, and then push them to their version control system.
This provides an object-level history of all objects in the database; you can see who made what change when for every object, and see how individual objects have been edited over time.
For more information, see Capturing development database schema in version control and Pulling in changes from Version Control.
Using the schema model alongside migrations
The schema model is complementary to migrations. The schema model provides object level history while migrations provide the most powerful means of deploying changes to a database and allow for testing deployment at development time.
There are several approaches to making database changes when using both schema model and migrations:
- For every change or set of changes you make to the database as an individual, you can save them to the schema model and then immediately generate a migration before committing to version control.
- You can iterate on the schema model, either as an individual or a team, and then later bundle up all of the schema model changes into a single migration.
- You can write your migrations, or generate them directly from the development database, and then update the schema model.
Each of these options will appeal to different teams.
In the first two of these scenarios, the schema model is the source of truth in terms of your development database schema. If you are using dedicated development environments, when you pull someone else's changes down from Version Control it is generally simplest to apply the changes back to your database using the schema model. As applying changes to the development database this way is a state based deployment it won't handle any data motion in your migration scripts, but this may be acceptable if you only care about the schema in your development database. It is alternatively possible to migrate your development database using migration scripts, but when taking this approach it is strongly recommended to ensure that all changes you had in your schema model before pulling from version control are captured in migration scripts and that those scripts have been applied or marked as applied (using `flyway migrate -skipExecutingMigrations` in the development database. See Pulling in changes from Version Control for more information.
In the last scenario, the migrations are considered the source of truth. Whenever migrations are written manually or pulled down from Version Control they need to be applied to the development database by migrating the database. Whenever migrations are generated from the development database they need to be marked as applied (using `flyway migrate -skipExecutingMigrations`) back to the development database. The schema model can then be updated based upon the development database.
Deploying directly from the schema model
It is possible to deploy from the schema model to a database in a state based workflow. This involves generating a SQL script to make the database match the schema model, and then executing it.
This can make for a simpler workflow than using migrations although there are some limitations.