We went from hibernate/eclipselink to MyBatis - and have never looked back. Needing to deal with the idiomatic behaviour of JPA, HQL and the target SQL is a nightmare. We’ve literally removed tens of thousands of lines of Java code by dropping JPA.
Same. When writing complex queries, Hibernate just got in the way. MyBatis just lets you map any result set to objects as you see fit. I also make use non-standard features such as GIS, gin basesd queries, arrays and json in Postgres and this is just easier with MyBatis since I am still writing the queries in SQL. Performance seemed better too.
We’ve taken the view that audit should be done in the database layer using, for example, triggers. Audit at the application layer is less resilient then at the SQL layer, IMO.
In fact, an important part of our transition away from ORM was to invert the ORM-centric relationship between the application and the database. We wanted our tech people to be able to manipulate the database natively via SQL, which is generally much easier and more efficient than writing and deploying Java code to do the same thing.
For us, this meant moving most data manipulation logic into the database using PL/PGSQL. Doing so has empowered a team of non Java admins, massively improved performance, and significantly reduced LOC. I’d say that we now have about 30% (1/3rd) of the LOC in PL/PGSQL than we had in Java+JPA. Obviously this means less bugs, but performance is literally 100x in some cases.
I think that if you take a Java-first view of your application then all the complexity of JPA is part and parcel of any solution, and that’s fine for you. But we’ve found that taking an SQL-first approach has been great for our use cases, and has had lots of ongoing benefits.
Of course, we needed to build a bunch of tools to help with this. For example, we built a gradle plugin that treats SQL code like Java code so we can write libraries in SQL with transitive dependencies. We also needed to build testing tools and tools to automate schema migrations for CD.
I do think the lack of tooling around managing large SQL-based projects is a blocker to wider adoption of our approach, but the benefits of going against the ORM grain have been very significant for us.
That's a fair criticism about auditing. Doing it through database triggers avoids the problem with audits being missed because of code that doesn't participate in the Hibernate lifecycle (including native SQL queries and Criteria updates, yikes!).
I will also concede that under some circumstances it's too slow to bring the data to the code, and instead you have to bring the code to the data (i.e. PL/PGSQL). The sort of speedup is mostly from eliminating round trips and data marshaling, however. That's not quite a like-for-like comparison of an ORM vs a mapper.
The reason I stick to ORMs is mostly because of RAD tools that save me so much time. For instance, JHipster generates liquibase migrations and JPA entities. This gives me a relational schema very quickly. To avoid any "surprise" queries that tank performance, I do turn Hibernate's statement logging on when developing new features.
Next time if I can find some more tools to help with an SQL-only + stored procedure approach, I'll give it a deeper consideration. Maybe convince your company to open source some of tools it has developed!
There is a lot of internal enthusiasm for open sourcing our tools, but we are heads down at the moment so it’s just a matter of time (or lack of). I think we will time it for the next pgAU conference so we can talk about it at the same time.
I definitely agree with you that tooling is a big deal. We did spend a lot of time manually proving it out before we spent the time on the tools. It was a bit of a leap of faith but it quickly became obvious that we were onto something.
And you’re right, the point of our approach was to move the code to the data. We deal with reasonably large, complex real time data sets Sotheby’s round trips and marshalling become the dominating contributor to total time. Hence our ability to improve some operations by 100x.
I can see that if you have a familiar and reliable tool chain and a compatible use case then ORMs could be great. I think our problem was that we had neither, so it was never going to work out too well for us!
No, I did look at JOOQ briefly, but MyBatis came somewhat recommended and perfectly scratched the itch I had at the time. So I don’t have an opinion either way with JOOQ. I am however super happy with MyBatis, we’ve been using it 2+ years in dozens of microservices, but we are KISS oriented and so only use a subset of its functionality (no XML config for example).
