How to cascade truncate with Jooq on PostgreSql?

Question

I'm writing integration tests and want to clean the (postgres) database after each test. So I assume doing a cascading truncate operation on all (actually only most of) the tables is the way to go.

I'm working on an application that uses Kotlin, Spring and Jooq which is why I imaging a Truncator class with a truncateCascade which I could get autowired into my SpringBootTest classes.

import org.jooq.DSLContext
import org.jooq.Table

@Service
class Truncator(private val dsl: DSLContext) {
    fun truncateCascade(tables: List<Table<*>>) {
        dsl.truncate ...
    }

//      single truncate work only for tables without foreign key constraints
//      so I can't simply iterate over all tables and call this method.
//    fun truncate(table: Table<*>) {
//        dsl.truncate(table).execute()
//    }
}

Basically I'm looking for an implementation of truncateCascade (assuming that's not a wrong approach).

I found documentation on Jooq's TruncateCascadeStep and mentions of continueIdentity or restartIdentity while investigating this issue, but have not enough experience with Jooq or databases in general to piece this together.

Answer 1

What you're missing is to call cascade() on your truncate() command:

fun truncate(table: Table<*>) {
    dsl.truncate(table).cascade().execute()
}

Another option would be to simply drop your schema entirely, and re-create it from scratch. That might be more robust for testing and shouldn't take much more time for small to medium sized schemas.