Call external sql from migration

Question

I have a database with views and stored procedures that often need to be changed. I would like to be able to store these views in another directory and include sql code when recording the migration. Basically, a dir structure would be

views/ my_view.sql functions/ my_func.sql sql/ V1__add_view.sql 

And V1__add_view.sql will be something like

 \i views/my_view.sql 

It currently works in psql, but not in migration to span. The advantage of this is that when we wanted to make a change, we could change the view in place and include it in the next migration. It also just eliminates the sheer number of copies inserted due to migrations.

Is there a way to include external SQL scripts in span migration?

Answer 1

It looks like you could accomplish this using recurring migrations .

I do not think flyway supports external script calls, such as the \ i operator. If you want to try importing a route, you can use placeholders for your scripts.

Using your example, use placeholder in your sql migration file

 ${my_view} 

When calling flyway, determine the value of replacing the placeholder with text from your views / my _view.sql. I'm not sure what you use to call flyway, but in ant it will be something like

 <loadfile property="flyway.placeholder" srcfile="views\my_view.sql"/> <flyway:migrate> <locations> <location path="database/migrations"/> </locations> <placeholders> <placeholder name="my_view" value="${flyway.placeholder}"/> </placeholders> </flyway:migrate> 

The documentation also has an example: https://flywaydb.org/documentation/ant/migrate