kotlin with jooq and write table models manually without code generation

Question

I'm experimenting with jOOQ and Kotlin and seen some tutorials and docs and it looks really nice.

But if there is something very annoying with jOOQ is the code generation. It seems too complex, and eventually impossible to maintain. I decided to create my own table models (similar to how hibernate works).

I created two table models:

User

data class User(
    val id: String = UUID.randomUUID().toString(),
    val name: String,
    val email: String,
    val password: String? = null
) {
    companion object {
        val TABLE: Table<Record> = DSL.table("user")
        val ID: Field<String> = DSL.field("id", String::class.java)
        val USER_NAME: Field<String> = DSL.field("user_name", String::class.java)
        val EMAIL: Field<String> = DSL.field("email", String::class.java)
        val PASSWORD: Field<String> = DSL.field("password", String::class.java)
    }
}

Followers

data class Followers(
    val id: String,
    val followerId: String,
    val userId: String
) {
    companion object {
        val TABLE: Table<Record> = DSL.table("followers")
        val ID: Field<String> = DSL.field("id", String::class.java)
        val FOLLOWER_ID: Field<String> = DSL.field("follower_id", String::class.java)
        val USER_ID: Field<String> = DSL.field("user_id", String::class.java)
    }
}

When I did some trivial SQL statements and it worked perfectly, but when I tried the next statement, I'm getting exception.

return dsl.select().from(u.TABLE)
            .rightJoin(f.TABLE).on(u.ID.eq(f.FOLLOWER_ID))
            .where(u.ID.eq(id)).fetch().into(User::class.java)

The expected statement from this code is:

select *
from user u
right outer join followers f
on u.id = f.follower_id
where u.id = 'e30919bf-5f76-11e8-8c96-701ce7e27f83';

But the statement I got from this code is:

select *
from user
  right outer join followers
  on id = follower_id
where id = 'e30919bf-5f76-11e8-8c96-701ce7e27f83'

And of course, this givse me (rightfully) the error Column 'id' in where clause is ambiguous

It raises a few questions:

1. Is there a better way to declare table model without code generation.
2. Why the DSL select does not transform to proper SQL statement? What I'm doing wrong?

Answer 1

First off, some word of advice on your reluctance to use code generation:

i seems too complex, and eventually impossible to maintain. so, i decided to create my own table models (similar to how hibernate works).

You're (probably) going down a long path of pain and suffering. First off, you will already now need to think of database migrations, which are best done using your database's DDL language. This means, your database model of your data should be more important to you in the long run, than your client model. In fact, your client model is a copy of your database model, not something you'd like to maintain independently. With this mindset, it is more reasonable to have a code generator generate your client model from the database model, not vice versa.

Sure, Hibernate makes the client first approach easy as well, when you start a project. Yet, once you go to production, you will have to migrate your database, and then this model will break. You're back to database first, and it's worth setting up everything already now.

So, no. Code generation might introduce some complexity now, but it will be much more easy to maintain down the road, than you creating your own table models.

I've written up a longer blog post about this topic, here.

Regarding your specific questions:

return dsl.select().from(u.TABLE)
          .rightJoin(f.TABLE).on(u.ID.eq(f.FOLLOWER_ID))
          .where(u.ID.eq(id)).fetch().into(User::class.java)

the expected statement from this code is: [...]

Well, that depends on what u and f are. You cannot just rename your Kotlin references to your table and expect jOOQ to know what they mean. I.e. you probably created the references as follows:

val u = User.TABLE;
val f = Follower.TABLE;

If that's how you created the reference, then the two things are the same thing by identity. jOOQ doesn't magically reverse engineer your Kotlin code to find out that you meant to alias your table. You have to tell jOOQ:

val u = User.TABLE.as("u");
val f = Follower.TABLE.as("f");

But now you're not done. You constructed the User.TABLE reference using the plain SQL API, which means that jOOQ's runtime has no idea about the columns in that table. You cannot reference those columns anymore from the aliased table, because the type of the aliased table for plain SQL tables is Table<?>, not User.

You could, of course, create TableImpl instances and register all columns inside of your TableImpl instance - just like the code generator does. In that case, you would have tables and columns associated with them, and could use them type safely even with aliased tables.

All of this stuff is handled automatically by generated code, which again, I recommend you use with jOOQ. The main reason why anyone would not use the code generator with jOOQ is because the data model is dynamic, i.e. not known at compile time. Otherwise, you're just going to repeat tons of work that the code generator already does for you, automatically. And, as mentioned before, you will have much more work later on, when you start migrating your schema.