Flyway and Arcade

[unimaginativeusername]

I did mention this elsewhere and thought I'd leave instructions here:

I have a working setup for managed database migrations with flyway. Flyway itself uses a postgresDB to store its metadata. It might be possible to integrate that into ArcadeDB itself - but I didn't want to manually create the structures for that. (I came close to making it work by recreating the flyway_schema_history table as a document in arcade, but hat no real use for this, so I didn't pursue it further.)

I am running postgres from docker as follows:

version: '3.9'

services:
  postgres:
    image: postgres:14-alpine
    ports:
      - 7654:5432
    volumes:
      - ~/apps/postgres:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=password
      - POSTGRES_USER=flyway_user
      - POSTGRES_DB=flyway_db
    restart: unless-stopped
    container_name: flyway-public

Dependencies:

        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>

        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-database-postgresql</artifactId>
            <version>10.4.1</version>
            <!-- <scope>runtime</scope> -->
        </dependency>

application.properties:

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url= jdbc:postgresql://localhost:7654/flyway_db
spring.datasource.username=flyway_user
spring.datasource.password=password
spring.datasource.defaultSchema=flyway_db
spring.datasource.hikari.schema=flyway_db

graph.user=root
graph.password=playwithdata
graph.connection=jdbc:postgresql://localhost:5432/graph
graph.http-port:2480
graph.postgres-port:5432
graph.host=localhost
graph.graph-name=graph

I am not certain if this is strictly necessary, at some stage I had to include the following in the main application:

// https://stackoverflow.com/questions/51221777/failed-to-configure-a-datasource-url-attribute-is-not-specified-and-no-embedd
@SpringBootApplication(exclude = {FlywayAutoConfiguration.class})

The config file contains the path to the migrations. I have chosen the JavaRoute, I'm not certain if pure SQL files would also work.

FlywayConfig.java

import jakarta.annotation.Nullable;
import jakarta.annotation.PostConstruct;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.migration.JavaMigration;
import org.flywaydb.core.api.output.MigrateResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
public class FlywayConfig {
    // Custom migration history table name
    @Nullable
    private static final String SCHEMA_HISTORY_TABLE = "flyway_schema_history";

    @Autowired
    @Nullable
    private DataSource dataSource;

    @Autowired
    @Nullable
    private ApplicationContext applicationContext;

    @PostConstruct
    public void migrate() throws SQLException {

        // Retrieving all migration implementation classes through the Spring container
        // All implementation classes no longer need to be defined in the `db.migration` package,
        // they can be placed in any location that supports Bean scanning
        JavaMigration[] migrationBeans = applicationContext
                .getBeansOfType(JavaMigration.class)
                .values()
                .toArray(new JavaMigration[0]);
        Flyway flyway = Flyway.configure()
                .dataSource(dataSource) // No need to configure flyway JDBC URL by using the original DataSource
                .defaultSchema(dataSource.getConnection().getSchema())
                .locations("integration/flyway/migration") // Default migration script path
                .table(SCHEMA_HISTORY_TABLE) // Default migration history table is `flyway_schema_history`
                .baselineOnMigrate(true) // Default false, must be set to true for initial migration on existing databases
                .baselineVersion("0") // Default "1"
                .executeInTransaction(true) // Performing migration as a transaction
                .installedBy(applicationContext.getId()) // Using microservice name as migration executor
                .javaMigrations(migrationBeans) // Registering migration classes
                .load();
        MigrateResult migrate = flyway.migrate(); // Executing migration by calling subclass implementations sequentially
    }
}

The following pulls the connection data from a config file, it should be straight forward to include your own data. I am omitting my GraphDbConfig class here. The interface makes the individual migrations easy to implement and read.

MigrationWrapper.java

import jakarta.annotation.Nonnull;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public interface MigrationWrapper {

    default void executeQuery(@Nonnull GraphDbConfig graphDbConfig, @Nonnull String query) throws ClassNotFoundException, SQLException {

        Class.forName("org.postgresql.Driver");
        Properties props = new Properties();

        props.setProperty("user", graphDbConfig.getUser());
        props.setProperty("password", graphDbConfig.getPassword());
        props.setProperty("ssl", "false");
        props.setProperty("sslmode", "disable");

        System.out.println ( graphDbConfig.getHost());

        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://" + graphDbConfig.getHost() + ":" + graphDbConfig.getPostgresPort() + "/" + graphDbConfig.getGraphName(), props)) {
            Statement st = connection.createStatement();
            st.execute(query);
        }

    }

}

Finally, under the path specifid in the config (integration/flyway/migrations), I have the versioned setup for arcade DB:

V1__CreateTest.java

import com.stktrk.app.integration.configuration.types.GraphDbConfig;
import com.stktrk.app.integration.flyway.MigrationWrapper;
import jakarta.annotation.Nonnull;
import lombok.EqualsAndHashCode;
import lombok.Value;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@EqualsAndHashCode(callSuper = true)
@Component
@Value
public class V1__CreateTest extends BaseJavaMigration implements MigrationWrapper {
    @Autowired
    @Nonnull
    GraphDbConfig graphDbConfig;

    public void migrate(@Nonnull  Context context) throws Exception {

        this.executeQuery(graphDbConfig,"CREATE VERTEX TYPE test");
    }
}

All of this has been cobbled together with a lot of trial and error; not everything will be optimal, I am sure I have a few pointless steps in here still. It seems to work for me; and if you want to use your graph DB with a schema, it's a big help.

[robfrank]

Very cool.
Can you provide a small project with the code? My plan is to open a repository to collect examples and POCs like this one.

[unimaginativeusername]

The snippets here should be complete enough to allow you to get it to work, I think it's only missing the docker setup for Arcade; feel free to grab it.

I'd be happy to create a repo, but I am not certain what that should look like, given the dependencies for external database servers. I'm sure there is a way to get maven to set this up, but I don't know how.

[robfrank]

A small project with all the pieces, even a zip and not a repo, would be enough. Even something that has errors but that can be used as a starting point.
The point is, the more you provide, the easiest is for us to add it to a repo with examples.
Any contribution is valuable for us, committers, and the community.

[unimaginativeusername]

Coming up....

[unimaginativeusername]

flywaydemo.zip

This should get you started.

Untested; I had to remove personal details and passwords and don't have the DBs set up as spares.
The pom.xml contains lots of dependencies that aren't used here.

The yaml-files should set up the relevant docker containers.

I flattened the package structure, but it should work as is.

Let me know if anything is unclear or doesn't work; I'll be happy to try and fix or provider further information.

[unimaginativeusername]

In FlywayConfig, you'll need to adapt .locations("integration/flyway/migration") so it points to the package with the migration scripts, V1__CreateAccount.java in this case.

[robfrank]

great, let me rework it a bit and add to the just created https://github.com/ArcadeData/arcadedb-examples