Proposal: Stop using the Exposed DAO API

[mnonnenmacher]

Problem

The Exposed DAO API can be very convenient because it hides the underlying database calls and makes it very easy to access related data across multiple tables.
However, it also makes it very easy to create inefficient database code, either by making inefficient queries or by creating a massive amount of queries which are technically not required. Also, when loading a DAO all columns of the table are read which is often not required.

Another problem is that complex queries can often not be implemented with Exposed DAOs and the Exposed DSL has to be used instead which makes the code inconsistent.

Finally, the DAO API makes the code look more simple in many cases, but adds another layer of complexity when trying to understand database related issues.

Proposal

Stop using the DAO API and only use the DSL instead.

Benefits

• Database related code becomes more consistent because only one API is used.
• Less risk to accidentally run inefficient queries or more queries than required.
• Forcing the use of the DSL requires developers to think about which data is actually required.

Challenges

• The database related code might become more verbose.
• Developers need to think more about SQL when writing database related code (which is not only a challenge but also a benefit because the underlying technology needs to be understood anyway).

[wkl3nk]

My first thought was "Oh no, Kotlin DAO is still good for basic CRUD and easy relationship between database entities".
But having a second thought: The functionality of this Exposed DAO API ist just ridiculous, e.g. when compared with JPA. If we stop using the DAO API, we really will not lose a lot.

With the DSL API comes the risk of too many degrees of freedom. One developer might choose to implement this this way, while another one implements it another way. Not all developers are very proficient in SQL. So it would be important to keep eyes on what code is written in this database layer, to make sure the same things are solved in the same way.

[mnonnenmacher]

The functionality of this Exposed DAO API ist just ridiculous, e.g. when compared with JPA.

Just for reference, Exposed DAO uses the active record pattern while JPA uses the data mapper pattern, both have their own pros and cons.

[nnobelis]

We did the same move in a previous company, and I think it makes sense to have a fine-grained control over which SQL statements are run against the DB.

Although, we need to align somehow on the query style.

It would also be nice to have some tooling support do directly get the SQL at a code location when debugging, to be able to copy-paste it to PGAdmin for debugging.

[mnonnenmacher]

It would also be nice to have some tooling support do directly get the SQL at a code location when debugging, to be able to copy-paste it to PGAdmin for debugging.

At least when running tests the SQL statements are already logged, but the only way to get the raw queries is to actually execute the code.

[oheger-bosch]

I am in favour of this move; practice has shown that the DAO layer generates more problems than it offers benefits.

The transformation may be tricky though. I think we first need to start with a prototype, i.e. convert a certain area of the codebase from DAO to DSL and see how it looks like. With the time, patterns should emerge.

[mnonnenmacher]

Using the DAO API can also consume a lot more memory: #2610

[sschuberth]

Is it always the case that the DSL approach requires so much more code than the DAO approach? I was a bit shocked to see this, TBH.

[mnonnenmacher]

The difference depends on the type of query, but in general the DSL is more verbose because the DAO API hides the SQL queries behind it.

[sschuberth]

Maybe it's also worth looking at https://github.com/ExoQuery/ExoQuery in this context.

[mnonnenmacher]

The API looks very promising, would be interesting to give it a try in some isolated piece of code.