What ORMs have taught me: just learn SQL (2014)

Yes I optimize when my automated performance testing/stress testing, tells me I need to and may write handcrafted sql, but I've also handcrafted some classes in C back in the day when my old Windows Mobile app using the C# compact framework wasn't performing.

It all depends on the talent of the authors of the drivers.

I still have to write the actual code that maps the result set to objects, but I've found this is a very small tax to pay, especially with Kotlin's data classes.

I used an ORM in a previous job (RoR ActiveRecord). I didn't find it an altogether horrible experience, but there where many cases where we would get these 'leaky abstractions' in the wrong direction from the model -> database schema. There were also a lot of cases where we would realise that some ActiveRecord query we were doing was unintentionally loading entire tables into memory (our fault, not ActiveRecord's fault). This was usually remedied quite easily by just RTFMing the docs, but my feeling is we could have avoided it in the first place if we'd used a lower level strategy.

Personally, when developing a new feature I always like to start by thinking about the database representation first and then working my way up. I think having this sensitivity to how it should be represented in the database can allow you to avoid many of the pitfalls that may come with using a more opaque ORM.

Another valuable attribute I get from using JDBI is it's dead easy to mock (like heavyweight ORMs), so unit testing stuff that interacts with it is super simple.

1. provide idiomatic domain-object oriented query interface which it in turn translates to SQL

2. provide CRUD sql generation

3. provide some sort of session-based object lifecycle change tracking and management

#1 - The generated SQL is important on many levels. As things like HQL/linq/ deviates further from the generated sql transparency is lost. SQL is normally brittle compared to your domain language which has better testability and type safety but still you have a handful of queries where it feels more sensible to write the SQL yourself.

#2 - Code which reflects on a type and generates basic insert/select/update/delete is usually pretty naive and easily done. With the exception of complicated legacy databases and iBatis-style tools ORMs which only support #2 arent really worth bothering.

#3 - I've found this to be the real benefit of an ORM. Being able to scope object lifecycles into clear units of work, buffer pending changes until a discrete point and get scoped caches for "free" have been hugely beneficial.

Each ORM unfortunately/inevitably come with great learning curve. It seems to unfortunately/inevitably bring lots of new concepts and conventions to the table required for the user simply must understand. Sometimes it requires you to re-arrange the way you may write your code to be more session-oriented.

I dont like the idea of AI managing how/when to apply changes to storage media. AI can be smart and efficient yes but you lose that important transparency/predictability. On the contrary I prefer very dumb/mechanical/predictable ORM, something not elegant but the behavior of what happens when is well-understood and easily scaled out to a large team. In my experience hibernate, EF, sqlalchemy have that sort of dumb/predictable behavior (however the SQL they sometimes generate can be performant but unreadable).