Using a Java class for DB migration

English
,

Hi!

I got some good feedback from @phxql@fosstodon.org related to my previous post:

with flyway it’s possible to have a JavaMigration, which let’s you write custom code.

So let’s test this out today!

JavaMigration

It took me a while to make this work. I couldn’t figure it out what it was meant by adding the class to the db.migration package. Some places mentioned src/db/migration, some places mentioned src/main/java/db/migration and I even tried src/main/resources/db/migration (shouldn’t work, but here are my SQL files), but it was fruitless. In the end, @ComponentScan helped me again, as I created a new sub-package migration in my Spring project and annotated my class with @Component, that made it work, with the added benefit of allowing me to control the location of the files.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.ytemail.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Component;

@Component
public class V3__RecalculateHashes extends BaseJavaMigration
{
    final private PasswordEncoder passwordEncoder;

    public V3__RecalculateHashes(PasswordEncoder passwordEncoder)
    {
        this.passwordEncoder = passwordEncoder;
    }

    @Override
    public void migrate(Context context) throws Exception
    {
        // code goes here
    }
}

Given Spring also initializes this object, I can use dependency injection to get any service or Bean I need to handle the migration. I couldn’t, however, use a Repository, as Spring would consider it a circular dependency:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
***************************
APPLICATION FAILED TO START
***************************

Description:

The dependencies of some of the beans in the application context form a cycle:

┌─────┐
|  flyway defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]
↑     ↓
|  v3__RecalculateHashes defined in file [/home/kassner/workspace/ytemail/build/classes/java/main/com/ytemail/migration/V3__RecalculateHashes.class]
↑     ↓
|  userRepository defined in com.ytemail.repository.UserRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration
↑     ↓
|  jpaSharedEM_entityManagerFactory
└─────┘


Action:

Relying upon circular references is discouraged and they are prohibited by default. Update your application to remove the dependency cycle between beans. As a last resort, it may be possible to break the cycle automatically by setting spring.main.allow-circular-references to true.

So we must do things the old way today:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@Override
public void migrate(Context context) throws Exception
{
    context.getConnection().beginRequest();

    PreparedStatement updateStmt = context.getConnection().prepareStatement("UPDATE public.user SET password = ? WHERE id = ?");
    PreparedStatement selectStmt = context.getConnection().prepareStatement("SELECT id, email FROM public.user");
    ResultSet resultSet = selectStmt.executeQuery();

    while (resultSet.next()) {
        Long id = resultSet.getLong("id");
        String email = resultSet.getString("email");
        String password = resultSet.getString("password");

        updateStmt.setString(1, passwordEncoder.encode(convertOldHash(password)));
        updateStmt.setLong(2, id);
        updateStmt.executeUpdate();
    }

    context.getConnection().commit();
}

Callback

Flyway has the concept of Callbacks, which allows you to plug either a SQL file or a Java class to run after each Event. You can also leverage that to run some Java code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.ytemail.migration;

import org.flywaydb.core.api.callback.Callback;
import org.flywaydb.core.api.callback.Context;
import org.flywaydb.core.api.callback.Event;
import org.springframework.stereotype.Component;

@Component
public class Test1 implements Callback
{
    @Override
    public boolean supports(Event event, Context context)
    {
        if (event != Event.AFTER_EACH_MIGRATE) {
            return false;
        }

        if (!context.getMigrationInfo().getVersion().getVersion().equals("3.0.1")) {
            return false;
        }

        return true;
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context)
    {
        return true;
    }

    @Override
    public void handle(Event event, Context context)
    {
        // migration code goes here
    }

    @Override
    public String getCallbackName()
    {
        return "test1";
    }
}

In this example, supports is telling Flyway that this callback should only be executed for the event AFTER_EACH_MIGRATE and if the version that was just applied is 3.0.1. You could also tie back to your SQL file using context.getMigrationInfo().getScript() if you prefer. This is a quite similar approach to Magento’s deprecated UpgradeData classes, although I rather use JavaMigration for this purpose. I can see it being useful though, like running external commands like flushing a cache layer or reporting about the migration if you have observability tooling built into code.

Thank you.