Call external sql from a flyway migration

Question

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

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

And V1__add_view.sql would be something like

\i views/my_view.sql

Which will currently work in psql, but not in flyway migrations. The advantage of this is that when we wanted to make a change, we could modify the view in place, and include it in the next migration. It would also just eliminate a vast amount of copy-pasting in view migrations.

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

Answer 1

It sounds like you might be able to accomplish this using Repeatable migrations.

I don't think flyway supports calling external scripts like a \i statement would. If you want to try the importing route, you could use placeholders for your scripts.

Using your example, use the placeholder in your sql migration file

${my_view}

When you call flyway define the placeholder replacement value with the text from your views/my_view.sql. I'm not sure what you're using to call flyway, but in ant it would 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