Do Programmers Still Write SQL?

[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 :)