How I Fixed: Flyway Not Running Migration

Remember back to being a newb with Spring Boot? No? Well, feel free to tag along with my current experiences and chuckle away as you go. For today I wanted to run my first Flyway migration in a Spring Boot project, and for seemingly inexplicable reasons, the migration file never ran.

OK, so let’s quickly cover the demo project:

tree src/

src/
├── main
│   ├── java
│   │   └── uk
│   │       └── co
│   │           └── a6software
│   │               └── my_scraper
│   │                   ├── command
│   │                   ├── scraper
│   │                   │   └── CategoryScraper.java
│   │                   ├── service
│   │                   └── MyScraperApplication.java
│   └── resources
│       ├── application.properties
│       ├── db
│       │   └── migration
│       │       └── V1__Create_business_and_category_tables.sql 
│       ├── static
│       └── templates
└── test
    └── java
        └── uk
            └── co
                └── a6software
                    └── my_scraper
                        └── MyScraperApplicationTests.javaCode language: JavaScript (javascript)

Very unsurprisingly for me, it’s a web scraper project.

Here’s the pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.3.0</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>uk.co.a6software</groupId>
	<artifactId>my-scraper</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>my-scraper</name>
	<description>Scrape some listings</description>
	<properties>
		<java.version>21</java.version>
		<jobrunr.version>5.3.3</jobrunr.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.flywaydb</groupId>
			<artifactId>flyway-core</artifactId>
		</dependency>
		<dependency>
			<groupId>org.flywaydb</groupId>
			<artifactId>flyway-database-postgresql</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-docker-compose</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>


		<dependency>
			<groupId>com.microsoft.playwright</groupId>
			<artifactId>playwright</artifactId>
			<version>1.44.0</version>
		</dependency>
		<dependency>
			<groupId>org.jobrunr</groupId>
			<artifactId>jobrunr-spring-boot-starter</artifactId>
			<version>${jobrunr.version}</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>Code language: XL (xl)

As I say, I am a total newbie to Spring Boot with Java, having just finished reading a couple of books on the subject. This is my first real dive into using it properly.

Unsurprisingly therefore, I am taking help from ChatGPT.

This becomes important, because the problem I faced was a result of blind copy / pasting. But more on that in a moment.

The gist of my project is that there is a Postgres database, using Flyway for migrations, and a Docker environment. All of this is provided using Spring Boot starters. I thought perhaps the magic that this provides might have been causing me some problems, but that turned out not to be the case. Unsurprisingly, I suppose.

OK, so I got ChatGPT to make me a migration. It doesn’t matter much about the contents, but here it is for completeness:

-- V1__Create_business_and_category_tables.sql

CREATE TABLE business (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE category (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Code language: SQL (Structured Query Language) (sql)

When I downloaded the project from Spring Initializr, and opened it in my IDE, I had the following directory structure displaying in IntelliJ:

Obviously I have blanked some stuff out because I don’t want to reveal the site I am scraping. It really doesn’t matter – and actually isn’t even implemented yet. The problem with my migrations came early on.

OK – there are a couple of really interesting (well, err, maybe not really interesting) bits in that screenshot above.

1. Why does IntelliJ ‘helpfully’ collapse a directory using dotted notation? This completely threw me. This looks as if the path is resources/db.migration, but is actually resources/db/migration. No idea how to turn this off.
2. Note that the V1__Create_business_and_category_tables.sql file has the wrong file type icon. Very important spoiler alert, this was the crux of the issue.

Naming of the file is, apparently, very important with regards to Flyway Migrations. The V at the start means this is a Versioned migration. You can use U to Undo, R for a repeatable migration, and others apparently. I’m not that advanced yet.

Immediately next you need the version number. This should be a number, and I’ve gone with the most basic – 1.

Then the double underscores have to come after the version. From there we give a descriptive name – but I don’t think that’s actually used at all by Flyway, that’s a human friendly thing.

Then the extension. In my case .sql – and I’ve actually included the fault there. Do you spot it? No, I didn’t, either.

Boot Spring Boot

There’s actually nothing necessary to be done, as the whole point of the Spring Boot setup is to do everything for you, if you are following the defaults.

So I hit the Play button in IntelliJ and watched the terminal output:

At this point, as a newb, it’s like … err, why not?

Well, after quite a lot of headscratching and double, triple, quadruple checking everything, it all seemed to look right. Really there wasn’t much in my project, it should all just work.

The logging output, whilst helpful enough to tell me nothing had happened, wasn’t for telling me why.

Thanks to GitHub issues, I found you can activate a debug flag specifically for Flyway. Add this to your resources/application.properties file:

logging.level.org.flywaydb=DEBUG

That’s pretty cool. You can seemingly enable specific logging levels for specific dependencies. I’ve not used this beyond Flyway yet, but conceptually that’s very neat and likely helpful to know going forwards.

Once I had this config in, and rebooted the app, I got more info:

Again, the actual issue is being reported here.

Only, to a beginner it’s really not “jump out” obvious as to the issue. I wonder, have you spotted it yet?

So, clearly, Flyway can see my migration file. That must mean the configuration is valid. I should say I had been through and added in a bunch more explicit config:

spring.flyway.baseline-on-migrate=true
spring.flyway.locations=classpath:db/migration
spring.flyway.user=app_user
spring.flyway.password=password
spring.flyway.schemas=public

spring.datasource.url=jdbc:postgres://127.0.0.1:5649/app_db
spring.datasource.username=app_user
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.DriverCode language: JavaScript (javascript)

None of that made a difference by the way. And you can safely not bother with any of it, as Spring Boot is clever enough to figure it all out based on, I guess, the Docker config, and your pom.xml config.

The Solution

What was the problem then?

Well, this:

Looks right. It looks like SQL, anyway.

But notice that IntelliJ hasn’t added syntax highlighting?

That’s because this is not a .sql file.

It’s a “.sql ” file.

When I’d copy / pasted the code in from ChatGPT, I had then renamed the file based on the comment at the top of the file. Somehow, and quite surprisingly, IntelliJ had not trimmed the string as I had not even considered, just naively expected it would always do, and then this was the cause of all problems.

Again, look at the specific Flyway console error:

It’s admittedly a pretty hard mistake to make, seemingly.

But yeah, changing the filename fixed it.

I can’t promise that’s your issue, if you found your way here experiencing something similar. But turn on that debug logging, and really pay attention to the terminal output.

In an ideal world, the developer experience would be nicer. But as a complete Java newb right now, I don’t see an easy way that Flyway changes this line to highlight potential causes:

Still, good luck with resolving your particular problems from a fellow Java headache sufferer.