Flyway: reusing sql file

user_3483133 0
Viewed 194

I just started looking into Flyway using the command line route, and was wondering if it is possible to reuse the .sql file?

Example:

I have a file called V1__Create_user.sql which has

CREATE USER ${user_name} WITH PASSWORD '${pass}';                                                                                                                                                                                                                                    
GRANT readaccess TO ${user_name};

It looks like I can only use this sql file once, that is when I run below command

flyway -placeholders.userName=test_user -placeholders.pass=test migrate

When I run the above command again with different user_name and password for the placeholders, no changes was made.

So, I was wondering if there's a way to reuse that sql file instead of generating new sql files containing the same sql query over and over ?

flyway
1 Answers

The way Flyway works is that it runs the scripts you provide it in the order in which you provide it. It puts a marker in the database showing which scripts it has already run. Then, when you rerun it, it will only run the scripts that it has not yet: run V2__Whatever, V2.2__Something, etc..

You can't go back & modify an existing script and expect the tool to pick up that it has changed and then rerun it. Even if that script is using placeholders.

That doesn't include the repeatable scripts (stuff like view definitions) that run every time you deploy using Flyway. If you had to, you could make that a repeatable script and run it that way. BUT, that will work as long as every deployment is incremental, 1 to 2, 2 to 3, 3 to 4. As soon as you need to deploy from version 1 to 4, you can't pass multiple commands in.

Since every instance of SQL in relational data stores I'm aware of is a declarative language, the best way to deal with it, is to use it as such. Yes, that means stuff like CREATE USER is used over & over. However, since each USER created is unique, that's just how it works.

user_7938960
Related