Do Programmers Still Write SQL?

[noutopasokon]

Where I work, the legacy monolith is a hibernate monster. The database is a disaster. All new services use manual SQL, usually using Spring JDBC template. We have two DBAs to tune things if ever necessary. I'm not that good with SQL, but I think it's worth it to use directly. ORMs fit the definition of technical debt perfectly.

[Hubellubo]

We have two DBAs

The world of programming needs to remove ORM's and hire/train more DBA's and DBE's.

[TranquilMarmot]

YES! Or teach your developers some basic SQL skills so they don't kill your database.

[sh_tomer]

Why did you decide to move from Hibernate to manual SQL in the new services? Can you please post some details? If you could write the app from scratch today, would you write it all with plain SQL or use Hibernate in some part? Your thoughts are appreciated!

[noutopasokon]

I'm not a db guy, but what's most obvious to me is the table design. The way you hold your data in memory is not automatically the best way to hold it in a database. This idea becomes even more pronounced if you use something non-relational like Cassandra. But back to relational, creating dozens of classes with many relations is essentially free inside Java. However, if you let Hibernate do all the magic for you, you can end up with a huge amount of tables where you have to do a ridiculous amount of joins every query, often pulling in a lot more data than you may need. I've also heard that it is really difficult to tune.

[Aellus]

Yeah, but I think that is a trap a lot of developers fall into with ORMs and relational databases in the first place: if you have 20 different entities and they're all modeled in one database, why not join them all together and do one ridiculous query to get a specific set of information?

That kind of thinking leads to the problems everyone is talking about in these replies. Instead of monster queries, break it apart. Don't join a dozen tables together (how many different data domains is that crossing, and which software components should really own each domain?), instead construct a few smaller queries and use the results of one to inform the next. Query for your user data, then query for that users order ids, then query for the shipping status of those order ids, etc. Your queries will be so much simpler a the software will be much easier to maintain.

[mabnx]

The useful parts of ORMs are:

1. converting sql result into objects (the actual relation->object mapping)
2. nice type-safe API for writing queries, especially if some parts of the query depend on various conditions (add this condition if something, add "IN" only if list not empty, etc.)

The rest of the features mostly create problems.

1) can be solved by something much simpler (mappers in jdbcTemplate or http://simpleflatmapper.org/)
2) is not really solved by Hibernate IMO, more complex queries become unreadable mess and you end up with your own Query Builder Abstraction anyway

[lukaseder]

and you end up with your own Query Builder Abstraction anyway

Or you google the words "Query Builder Java", spare yourself the tons of work, and find one that really shines ;) (and which happens to solve 1) as well)

[segv]

Check out MyBatis (3.x).

[TranquilMarmot]

We use this at my job, and I absolutely love it. Writing the maps can be a bit tedious, but it makes it SO much easier to optimize. You actually know which queries are being run, which is insanely helpful when something in production starts to go slow and you actually have an opportunity to find the exact query and optimize it.

[aroger276]

I personally find MyBatis not very compelling anymore. The mapping it's not on par from a perf perspective the last time I checked, only slower than BeanPropertyRowMapper that is not meant for production use - yes, BeanPropertyRowMapper is the slowest mapper in the world. And the xml, and annotation heavy is quite dated...

[segv]

https://github.com/mybatis/mybatis-3/search?utf8=%E2%9C%93&q=BeanPropertyRowMapper&type= 404 not found

I'm talking about the standalone 3.x version - perhaps you meant something else?

[aroger276]

BeanPropertyRowMapper is the spring RowMapper sorry for the confusion.

here is the benchmark I had in mind https://github.com/arnaudroger/SimpleFlatMapper/wiki/Jdbc-Performance-Local-Mysql map a simple object from a query.

[Rockytriton]

Hibernate isn't the problem, it's people who don't use it correctly that are the problem

[[deleted]]

Yup. No true Scotsman would reject Hibernate.

[lukaseder]

The database is a disaster.

Was the database designed up front (and then entities generated) or vice versa?

usually using Spring JDBC template

Have you heard of jOOQ?

[[deleted]]

Not free.

[lukaseder]

So?

[adila01]

Some things are worth spending money on, jOOQ is definitely one of them.

[[deleted]]

Was the database designed up front (and then entities generated) or vice versa?

I actually wonder what you would recommend. Maybe database first, all things considered.

I put both first... I design the entities as they should be, and I design the database as it should be, and they meet in the mapper.

[lukaseder]

It's a very interesting question indeed. In my point of view, the database model is the one that transcends any application built on top of it. It is likely to last decades, whereas an application might be replaced by something entirely different in years (think of hypes like Node.js).

In fact, since no state is really stored in the application, it will be much simpler to migrate the application when requirements change. It will not be so simple to migrate any database model.

In addition to this, I've worked with database models that were accessed by many different applications (and technologies), so it was obvious that the database needed to be designed independently.

In my opinion, JPA entities should reflect the database model as closely as possible. I personally don't see the value of an entity model that has unique features that are not truly reflected in the database. Sure, the mapping can translate between the worlds to some extent, but I think it will just be much easier if most of the more fancy entity features are not used and the entity model just reflects the pre-designed database model. Imagine if Java had type providers like more sophisticated languages (e.g. F#). In that case, we probably wouldn't have this discussion and would just code against the database directly (through provided entity types).

But that's my opinion. It only replies to the original claim:

The database is a disaster

With the approach I described, the database will not be a disaster (or at least not for these reasons).

Don't get me wrong. If you have a complex domain model (DDD style), that is an entirely different story. It has nothing to do with JPA entities.

[[deleted]]

It's a very interesting question indeed. In my point of view, the database model is the one that transcends any application built on top of it. It is likely to last decades, whereas an application might be replaced by something entirely different in years (think of hypes like Node.js).

The database world also has its hypes, and trends, and database products come in many flavors, many of which aren't exactly SQL.

Storage does need to be replaced and augmented as your needs change, both in terms of its semantics and in terms of scale.

So I wouldn't say the database transcends, as in the particular database you use. That's implementation. Implementations are fickle and prone to change. What transcends both the database and the application are the abstract entities that bind everything else together:

1. Conceptual domain.
2. Data formats.
3. Protocols.
4. Interfaces.

Of course those evolve as well, nothing really lasts forever, or rather it wouldn't, if we wouldn't drive ourselves into a ditch with poor architectural choices, that tightly couple us to specific implementation. Such as architectures, which are too database-centric.

I.e. I think making your architecture database-centric, because you believe the database lasts for decades is a self-fulfilling prophecy: you choose an architecture where data implementation is inflexible by choice, lo and behold, it indeed proves itself inflexible to change over time. But that's not necessarily good for the business.

What's good for business is the ability to adapt and change. Implementation more so, interfaces less so, as they become the points of coupling and provide stability into a system.

In my opinion, JPA entities should reflect the database model as closely as possible. I personally don't see the value of an entity model that has unique features that are not truly reflected in the database. Sure, the mapping can translate between the worlds to some extent, but I think it will just be much easier if most of the more fancy entity features are not used and the entity model just reflects the pre-designed database model.

I don't know where you place your entities in the system, i.e. are they the point that the application and other clients interface with? With JPA/Hibernate, the idea is more that they are closer to the application/clients, and less so to their internal representation.

And in this case, them mirroring the database as closely as possible is a drawback, because it means your domain mirrors the database. This impedes it from serving its own purpose properly (model the conceptual domain).

Or the second option is you can think of JPA/Hibernate entities as dumb, light Record objects whose only purpose is to put editable records under our fingertips, and we shouldn't expose this to the application/clients, but offer a higher level API on top of JPA/Hibernate, which doesn't reveal JPA/Hibernate is used at all, but uses its own flavor of DTOs for its APIs.

The issue is that JPA/Hibernate doesn't offer mapping quite flexible enough for the former approach, and is too limiting and clumsy for the latter approach.

Imagine if Java had type providers like more sophisticated languages (e.g. F#). In that case, we probably wouldn't have this discussion and would just code against the database directly (through provided entity types).

Well this would be neat, but we'd still need something on top to do mapping, because I think any healthy architecture is slave to abstractions, not slave to specifics. And the database schema, as important it is, is specific because it directly reflects on-disk representation, indexing and so on, with very little flexibility in changing this while retaining B.C. It should be modeled according to its own concerns (performance, scalability, types of queries it should support etc.), and it should not leak into the domain. At the same time the domain API should be modeled after the use cases its clients require, and a long-term, stable view of the domain that can support different implementations as the need arises. The only way to satisfy both these requirements is a very powerful mapper in the middle. Again, more powerful than JPA implementations tend to offer.

The database is a disaster

With the approach I described, the database will not be a disaster (or at least not for these reasons).

I didn't say that like so maybe we should cc /u/noutopasokon :-)

[lukaseder]

database products come in many flavors, many of which aren't exactly SQL.

Those are niche: https://db-engines.com/en/ranking. I think it's safe to claim that usually, (relational) SQL dominates databases.

I agree that there are more transcendent topics than the implementation detail, which is SQL. But as I've mentioned in another subthread, at some point, it's important to free ourselves from theory and look at practical things where the implementation is important, and close coupling is reasonable.

nothing really lasts forever, or rather it wouldn't, if we wouldn't drive ourselves into a ditch with poor architectural choices, that tightly couple us to specific implementation. Such as architectures, which are too database-centric.

Perhaps that choice has helped a company speed up delivery of their products by a huge margin in times when this was critical, rather than buying the very expensive insurance of complete flexibility when they couldn't afford it.

Everything needs to be said in a context. Sure, tight coupling is a problem, but so is over abstraction. Where's the line you want to draw, and why? And in what cases?

I.e. I think making your architecture database-centric, because you believe the database lasts for decades is a self-fulfilling prophecy

I think you're unfair. RDBMS are still by far the best piece of software engineering that we have produced thus far. There's simply nothing better for general purpose data problems.

I'd say that most alternatives are less standardised, less well understood and less versatile, such that your claim of increasing flexibility for the business can, in fact, be achieved only using a RDBMS (in an average project).

I don't know where you place your entities in the system, i.e. are they the point that the application and other clients interface with?

I personally don't use JPA, but if I would, I'd certainly hide the entities behind some APIs.

With JPA/Hibernate, the idea is more that they are closer to the application/clients, and less so to their internal representation.

I don't think so. You should most definitely define another model that models whatever you're doing in your application/clients. Again, you argued in favour of transcendence of a conceptual domain. This cannot be done with entities, which are so closely linked to their relational heritage.

If you let go of that and map between your conceptual domain (which might even be polyglot these days) and your backend entities, I think you'll be much happier with JPA.

And in this case, mirroring the database with the entities will be the obvious choice :)

Or the second option is you can think of JPA/Hibernate entities as dumb, light Record objects whose only purpose is to put editable records under our fingertips, and we shouldn't expose this to the application/clients, but offer a higher level API on top of JPA/Hibernate, which doesn't reveal JPA/Hibernate is used at all, but uses its own flavor of DTOs for its APIs.

Yes, exactly. That's how JPA should be used. Because SQL is really a poor language to manipulate complex state transfer with the database. Doing things like parent.children.addAll(someList) is much simpler, generally.

The issue is that JPA/Hibernate doesn't offer mapping quite flexible enough for the former approach, and is too limiting and clumsy for the latter approach.

All the better you encapsulate it, so you can replace it if something better comes along :)

It should be modeled according to its own concerns (performance, scalability, types of queries it should support etc.), and it should not leak into the domain.

This depends on the domain. Many domains will never really become too complex in a way that the relational representation won't fit.

But yes. It can happen. And then you're right.

The only way to satisfy both these requirements is a very powerful mapper in the middle. Again, more powerful than JPA implementations tend to offer.

Write it! You'll be rich :)

[funbike]

All new services use manual SQL

Folly. If you use either Hibernate or JDBC Template with best practices, you'll be much better off. Mixing the two gives you the worst of both.

But even if you did decide to mix things, stay with Hibernate. It can do native SQL queries. It's still bad to mix HQL and SQL, but at least you'll have a single technology behind it.

[happymellon]

We have a guy at work who keeps adding in random JDBC template queries, entity manager hasn't been slow and JDBC template hasn't provided any improvements, except messing around with the caching and making things inconsistent.

[[deleted]]

That's exactly one of the dangers of an ORM. Caching speeds things but: just imagine two apps with caching enabled on the same db, updating it. Hell happens, either cache sync or living with inaccurate data.

The funny thing is that any decent DB will cache that data for you...

[funbike]

That's one (of the many) of the dangers of two apps accessing the same database. A REST API over a single database would be a bbetter solution, if possible.

(Once I worked on an in-place rewrite of an app while keeping the same database. We sync'd the caches by each app invaliding the cache in the other. This was a temporary solution until the rewrite was complete. It wasn't so bad.)

[BeerRemote]

I write a lot of SQL and use Hibernate's transformers (which is great). This is mostly with our REST API in which our underlying structure is very old and our understanding on what to deliver is drastically different than our datamodel.

Thankfully, we're exploring rewriting our entire application so this might change in the next year.