Generic Table type for replacing real dbs with H2 for tests

[dave08]

Currently to create a table, one needs to inherit from a Table type for a specific db, like H2Table. This makes it very hard to replace db table's db backend to H2 for quick unit tests, while still allowing use of postgres or mysql for production and integration tests...

[pull-vert]

Hello @dave08

Each database have specific supported types, that's why I needed to have dedicated table types that only expose the supported types for one db : H2Table, MysqlTable.... From the beginning I wanted Kotysa to guide the user through SQL complexity with some DB restricted type-safe DSLs, compared to let's say JDBC / JPA that allow to write a query as a String (no syntax check), and to bind any Object to the query without any guide during coding to what is supported by the specific database you are targeting.

But there is a way to have shared behavior with Kotysa, using a shared interface that both H2 and PostgreSQL entities will implement. This is an example of how you can do it, in which I use an auto-incremented ID which has not the same type and syntax in H2 (int auto_increment) and in PostgreSQL (serial) :

// Common code for H2 and PostgreSQL
data class Role(
        val label: String,
        val id: Int? = null
)

interface Roles : Table<Role> {
    val id: IntColumnNotNull<Role>
    val label: StringColumnNotNull<Role>
}

// For PostgreSQL
object PostgresqlRoles : PostgresqlTable<Role>(), Roles {
    override val id = serial(Role::id)
            .primaryKey()
    override val label = varchar(Role::label)
        .unique()
}
private val postgreslTables = tables().postgresql(PostgresqlRoles)

// For H2 (in test part of your project)
object H2Roles : H2Table<Role>(), Roles {
    override val id = autoIncrementInteger(Role::id)
            .primaryKey()
    override val label = varchar(Role::label)
        .unique()
}
private val h2Tables = tables().h2(H2Roles)

class RoleDao(
    private val client: SqlClient,
    private val roleTable: Roles, // you will pass either H2Roles for tests or PostgresqlRoles for main
) {
    fun findAll() = client selectAllFrom roleTable

    fun findOne(id: Int) =
        (client selectFrom roleTable
                where roleTable.id eq id
                ).fetchOne()!!

    fun createTable() {
        client createTableIfNotExists roleTable
    }
}

Please let me know if this example solves your question.

[dave08]

That's an interesting possibility -- but it would still lead to tons of boilerplate code... for example varchar could technically be the same for both and not be written twice -- perhaps an abstract class as a base would help? I think if there would be a common base for all the common features, maybe some boilerplate could be avoided.

[pull-vert]

I see, that's a good idea.

I add this to the 3.1.0 roadmap :

• add a new GenericTable with the common supported types
• allow tables() DSLs for h2, postgresql and mssql to accept a combination of DB specific Table as today OR GenericTable

[pull-vert]

Hi @dave08

This feature is now released in Kotysa 3.1.0, this is the doc for it