Run a PLpgsql script after an extended task in Flyway and Gradle

Question

I have a Java Spring application using Gradle for dependency management connected to a Postgres database. I am testing it and constantly applying migrations, making changes to the architecture, and thus end up cleaning the database often.

The flywayClean task in Gradle doesn't completely clean the database, so custom users, schemas and roles I have to clean afterwards, which I do with a couple of pgsql scripts.

Since I am working with a sizeable amount of schemas, I've also extended the flywayClean task into individual schema cleaning tasks, and a big "clean all schemas" task.

Now I want to run my scripts after I execute my big dbClean task (which includes all other little schemaClean tasks), but I've run into a few pitfalls.

I've tried two ways:

1. Groovy SQL Instance and execution (Importing groovy.grape.Grape and groovy.sql.Sql):

   task dbClean (type: org.flywaydb.gradle.task.FlywayCleanTask) {
   group = 'dbClean'
   description = 'Cleans all schemas'
   doLast {
       @GrabConfig(systemClassLoader=true)
       @Grab(group='org.postgresql', module='postgresql', version='42.7.3')
       def dbUrl = "${dbJdbc}//${dbHost}"
       def dbUser = dbUser
       def dbPassword = dbPassword
       def dropSchemasSql = file('path/to/DropSchemas.sql')
       def dropRolesAndUsersSql = file('path/to/DropRolesAndUsers.sql')
   
       def dropSchemas = dropSchemasSql.text
       def dropRolesAndUsers = dropRolesAndUsersSql.text
   
       def sql = Sql.newInstance(dbUrl, dbUser, dbPassword, 'org.postgresql.Driver')
   
       sql.execute(dropSchemas)
       println 'dropSchemas executed.'
       sql.execute(dropRolesAndUsers)
       println 'dropRolesAndUsers executed.'
   
       sql.close()
   }
   }
   

   This Groovy approach gives me an error

Execution failed for task ':dbClean'.

java.sql.SQLException: No suitable driver found for jdbc:postgresql://mycontainer.docker.networked:5432/myDatabase

2. Using the afterClean Callback from Flyway

   This works, but the problem is that it is being called to all of the tasks that inherit from flywayClean, and that is troublesome for what my script is doing. I only want it to run after the dbClean task.

I think approach 1 should be feasible, but I have no idea how to solve the driver error after I've tried using @Grab, or using the GroovyClassLoader(getClass().getClassLoader()) call.

How do I execute Postgres PL/pgSQL scripts after an extended Flyway task using Gradle and Groovy?

Answer 1

It seemed that the problem with approach 1 is that the driver is not being loaded at query execution time.

To load it, use Groovy's classLoader before calling your SQL instance. Then you can use doLast to run the scripts at the end of the specific task you want. (dbClean).

I used the following answers as references:

Gradle buildscript doesn't find Postgresql JDBC driver

Unable to query a db from gradle build script

task dbClean {
    // Add dependencies here, in this case PostgreSQL
    configurations {
        jdbc
    }
    dependencies {
        jdbc 'org.postgresql:postgresql:42.7.3'
    }
    group = 'dbClean'
    description = 'Cleans all schemas'
    
    doLast {
        def dbUrl = "jdbc:postgresql://mycontainer.docker.networked:5432/myDatabase"
        def dbUser = dbUser
        def dbPassword = dbPassword
        def dropSchemasSql = file('path/to/DropSchemas.sql')
        def dropRolesAndUsersSql = file('path/to/DropRolesAndUsers.sql')

        def dropSchemas = dropSchemasSql.text
        def dropRolesAndUsers = dropRolesAndUsersSql.text

        // Call Sql.classLoader, add to configurations
        def sqlClassLoader = groovy.sql.Sql.classLoader
        configurations.jdbc.each { sqlClassLoader.addURL it.toURI().toURL() }

        def sql = groovy.sql.Sql.newInstance(dbUrl, dbUser, dbPassword, 'org.postgresql.Driver')

        sql.execute(dropSchemas)
        println 'dropSchemas executed.'
        sql.execute(dropRolesAndUsers)
        println 'dropRolesAndUsers executed.'

        sql.close()
    }
}