Proposal to remove Flyway from Apollo

[nisiyong]

I propose to remove Flyway from Apollo after release 2.0.0. Flyway is a very good database migration tool, and it was introduced in Apollo with PR #1828 by @kezhenxu94.

Why remove it?

There are two main reasons:

1. Flyway community edition doesn't support some important features, like Undo, and recently I found MySQL 5.7 is no longer supported by Flyway Community Edition.
2. Maintaining the apollo database schema is very complex, if you want to add a new column from a table, you need to do a lot of work, and you should maintain a lot of scripts and keep them as same. I think we could keep it simple, pure scripts are more flexible.

@apolloconfig/committers If you have any suggestions, please leave your comments.

[kezhenxu94]

Hi.

1. Flyway community edition doesn't support some important features, like Undo, and recently I found MySQL 5.7 is no longer supported by Flyway Community Edition.

Dropping support to old versions of something is rather common in software world. We Apollo also dropped support to Java 7 too? It's reasonable flyway drops support to MySQL 5.7 which is 5 years old?

2. Maintaining the apollo database schema is very complex, if you want to add a new column from a table, you need to do a lot of work, and you should maintain a lot of scripts and keep them as same. I think we could keep it simple, pure scripts are more flexible.

I'm not against to remove flyway, I'm really interested in how simple would it be after we remove flyway, can you give some examples if we want to add a field to an existing table after removing flyway?

[nisiyong]

@kezhenxu94
MySQL 5.7 is still supported by Flyway Team Edition, I just want to say the community edition is lack some features, like the #3899 we provide some rollback scripts, it is easy to use undo to do that in Flyway, but we can't do that because using the community edition. Personally, I like Flyway, it is very easy to use, just in some situations, I still need to use pure scripts.

I'm not going to explain how it will be simple after removing it, but currently, when updating SQL, we need to maintain three parts scripts:

1. scripts/docker-quick-start/sql
2. scripts/flyway
3. scripts/sql

That's why I said we need to do a lot of maintenance work if want to add a new column, if there has another way to keep this stuff simple and also keep the flyway, I think it will be great.

[nobodyiam]

The main issue here is the cost of maintaining the database schemas.
As @nisiyong pointed out, there are 4 places to change when we need to update the database schema.

1. the apolloconfigdb.sql & apolloportaldb.sql under scripts/docker-quick-start/sql folder
2. the apolloconfigdb.sql & apolloportaldb.sql under scripts/sql folder
3. the delta sql under scripts/flyway folder
4. the delta sql under scripts/sql/delta folder

There are some duplications:

• 1 & 2 are mostly the same, with 1 having some data initialization scripts for quick start use
• 3 & 4 are exactly the same

For 1 & 2, I'm wondering whether it's possible to only have the data initialization scripts in the scripts/docker-quick-start/sql folder and reuse the sql scripts under scripts/sql folder when starting the quick start db. If we can do that, then there is no duplication for 1 & 2.

For 3 & 4, I'm not sure whether there is a better way to do it. One way to remove the duplication is to only maintain delta scripts in the flyway folder, then we update the release note to let users know the mapping between apollo versions and the delta scripts, e.g. if the user wants to upgrade from 1.9.0 to 2.0.0, then we tell the user the correct files to apply is V2.0.0__add-column-deletedat.sql and V2.0.1__add-unique-key.sql.

[kezhenxu94]

Make sense. Let's remove flyway

[nobodyiam]

I think we could leave this discussion open for a while to see if there are better ideas :-)

[nobodyiam]

@nisiyong

3 months passed and it seems the community is okay with removing flyway. So I think we could proceed with the proposal.