I understand using flyway for database versioning and using to create a database which matching my development efforts. However, there are often times when scripts need to be run on production databases for maintenance, fix problems, or change data, etc. These are more DML scripts vs. DDL scripts These aren't things which need to be include in DDL with my database versioning. Is there a good pattern/support in flyway for these kind of DML scripts?
2 Answers
Yes. It is possible to have a separate schema for utilities that can be independently maintained.
Flyway understands the scope of a database from the list of schemas that you provide in the configuration, so you can have more than one project on the same database as long as the RDBMS has full support for schemae. It is possible to have a separate flyway project that is concerned only with what is in the utilities, in their own schema(s). (you see this often called something like 'utils').
It makes deployment more complicated because the utilities are deployed with a different set of migrations to a different schema, and will normally only be the latest version. You will need to develop both sets of schema on the same database and server, but it is best to keep the utilities version-independent. You access the objects of the database whenever necessary by specifying their schema in the reference.
We use it that way. The way we manage that is we have 2 separate git repositories, one for DDL and one for DML and we use also 2 different flyway history tables:
- default table flyway_schema_history for DDL
- dml table flyway_schema_history_dml for DDL
When we deploy dml we use the option: -table=flyway_schema_history_dml
That way we keep track of what and when dml scripts are executed in production.
Related