如何让指定Flyway可重复脚本优先执行以支持非重复脚本调用？

Great question—this is a super common pain point when trying to centralize stored procedures/functions in Flyway's repeatable scripts, and I’ve worked through this with several teams. Let’s break down why your initial approach didn’t work, then walk through practical fixes:

Why Your Location-Based Approach Failed #

First, it’s important to clarify Flyway’s core execution order rules, which tripped you up:

• All versioned scripts (Vxxx__) run first, sorted by their version number in ascending order.
• Only after all versioned scripts finish do repeatable scripts (Rxxx__) run, sorted by a hash of their filename (not the location you placed them in).

So splitting scripts into loc_first and loc_normal doesn’t change the fact that repeatable scripts always run after versioned ones—location only controls the order within script types (e.g., versioned scripts from loc_first run before versioned scripts from loc_normal), but not across types.

Practical Fixes to Maintain Centralized Procs #

1. Split Dependencies into Early Versioned Scripts + Centralized Repeatable Script #

This is the cleanest and most maintainable approach:

• Identify all stored procedures/functions that are called by versioned scripts—let’s call these "dependency procs".
• Create an early versioned script (e.g., V1__base_dependency_procs.sql) that creates these procs with their minimum required implementation (even an empty stub works if the logic is simple).
• Keep all your full, updatable proc definitions in a single repeatable script (e.g., R__all_procs.sql) using CREATE OR REPLACE syntax.

Here’s how it works:

• The versioned stub procs run early, so any subsequent versioned scripts can call them without errors.
• After all versioned scripts finish, the repeatable script overwrites the stubs with the latest, full implementation.
• For future updates, you only need to modify R__all_procs.sql—no new versioned scripts required unless you add a new dependency proc that’s needed by a versioned script.

2. Use "Pre-Deploy" Repeatable Scripts with Flyway Callbacks #

If you want to keep all procs in repeatable scripts and have some run before versioned scripts, you can use Flyway’s callback system:

• Create a callback that runs during the beforeMigrate event (you can use a Java class or a SQL script for this).
• In this callback, execute your critical dependency procs from a dedicated repeatable-style script (you can manage this file manually, or have the callback pick up files from a specific directory).
• This ensures those procs exist before any versioned scripts run, while still letting you centralize updates to that script.

Note: This requires a bit more setup than the first approach, but it’s useful if you want to avoid any versioned stub procs entirely.

3. Refactor Versioned Scripts to Avoid Direct Proc Calls (Last Resort) #

If the above options don’t fit, you can adjust your versioned scripts to use dynamic SQL when calling procs that live in repeatable scripts. For example:

-- Instead of direct call:
CALL my_new_proc();

-- Use dynamic SQL (only works if you don't need the proc's output immediately):
EXECUTE 'CALL my_new_proc()';

This works because dynamic SQL resolves the proc name at runtime, but it’s a last resort—it hurts readability and only works for non-critical calls where you don’t need the proc’s result to complete the migration.

Final Recommendation #

Stick with option 1—it balances maintainability (centralized proc updates in one repeatable script) and dependency correctness (stub procs ensure versioned scripts can call them). It’s the approach most teams end up using for this exact scenario.

内容的提问来源于stack exchange，提问作者Chris Lewold