Is it okay to integrate database migration tools l

Question

I mean is it good practice to allow application to modify the db? Or should database migration be done externally to the application code base?

New to this, so any input will be appreciated.

We are using java and gradle to build the application.

Answer 1

I completely agree with Axel on keeping this together.

However, when your DB user doesn't have the rights to change the DB structure or your simply not supposed to change the structure it comes in handy to not let the tool run the structure changes directly but generate SQL scripts instead.

These can then be given to a DB admin who inspects and approves the SQLs first and then runs those himself.

This is how we (have to) do it.

Don't get mistaken. This is just one level of indirection more. Instead of letting the tool execute the SQL directly you let the tool write the SQL to a file. And then the DBA will execute the SQL from the file.

Answer 2

Not, unless you are not aware of:

• you have multiple applications running in the same database. So, you need to choose one which will be holding all scripts.

• you need to be able to rollback your deployment, otherwise you need to snapshot DB on every startup.

• your DBA providing to your application ability to perform schema alteration, i.e. doing schema change without their approvals.

Coupling code and schema easily achieved by holding flyway target version with the code in all your projects.

Answer 3

Flyway author here.

Yes! At the end of the day, there are two important forces at play:

1. Your schema is really tightly coupled to your application code: Your application expects certain tables and columns to be present so it can read & write to them. Which ones exactly varies from one release of your application to the next.
2. You don't want to do database-based integration between applications/services as this breaks all encapsulation. A database is a private implementation detail of a service. Integration with other services should happen via an API layer with proper validation and business rules enforcement.

And so in light of all this, just keep together what belongs together.

By letting the application migrate the database on startup, you ensure the database schema is always in sync with the application's expectations of what it should find there.