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:

   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?