Hello, Habr!
We continue to explore the topic and , including at the database level. Today, we offer to read about why, when designing large applications, the database structure, rather than Java code, should be paramount, how it is done, and what exceptions exist to this rule.
In this somewhat delayed article, I will explain why I believe that in almost all cases, the data model in an application should be designed "from the database up," rather than "from the capabilities of Java" (or any other client-side language you are using). Choosing the latter approach leads you down a long path of pain and suffering once your project begins to grow.
This article is based on , asked on Stack Overflow.
Interesting discussions on Reddit in the sections and .
Code Generation
I was quite surprised that there is such a small layer of users who, after getting acquainted with jOOQ, are outraged by the fact that jOOQ seriously relies on code generation. No one prevents you from using jOOQ as you see fit and does not force you to use code generation. But by default (as described in the documentation), working with jOOQ goes like this: you start with an (inherited) database schema, perform its reverse engineering using the jOOQ code generator to obtain a set of classes representing your tables, and then write type-safe queries to these tables:
for (Record2 record : DSL.using(configuration)
// ^^^^^^^^^^^^^^^^^^^^^^^ Type information is derived from
// the generated code referenced by the condition below
// SELECT
.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
// vvvvv ^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ generated names
.from(ACTOR)
.orderBy(1, 2)) {
// ...
}The code is generated either manually outside the build or manually on each build. For example, such regeneration may follow immediately after .
Source code generation
Different philosophies, advantages, and disadvantages are associated with these approaches to code generation—both manual and automated. I won’t delve into them in detail in this article. However, the core idea of generated code is that it enables us to reproduce in Java the 'truth' that we accept as a given, either within our system or outside of it. In a sense, this is similar to what compilers do when generating bytecode, machine code, or some other form of code based on source files—we obtain a representation of our 'truth' in another language, regardless of the specific reasons.
There are many such code generators. For example, The principle is always the same:
- There exists some truth (internal or external)—for example, a specification, data model, etc.
- We need a local representation of that truth in our programming language.
Moreover, generating such representation is almost always advisable—to avoid redundancy.
Type providers and annotation processing
Note: Another, more modern and specific approach to code generation for jOOQ involves using type providers, In this case, the code is generated by the compiler at the compilation stage. Such code does not exist as source code per se. In Java, there are similar, albeit less elegant tools—these are annotation processors, for example, .
In a certain sense, the same processes occur here as in the first case, except for the following:
- You do not see the generated code (perhaps this situation doesn’t seem so off-putting to some people?).
- You must ensure that the types can be provided, meaning that 'truth' must always be accessible. This is easy in the case of Lombok, which annotates the 'truth.' It is a bit more complicated with database models, whose operation depends on a constantly available live connection.
What’s the problem with code generation?
In addition to the tricky question of whether it's better to start code generation manually or automatically, there's also the viewpoint that some people believe code generation is unnecessary at all. The reasoning I encounter most often is that it complicates the assembly line setup. Yes, it's indeed challenging. There are additional infrastructure costs. If you're just starting to work with a particular product (be it jOOQ, JAXB, Hibernate, etc.), the time spent setting up the working environment is time you'd prefer to spend learning the API itself to later extract value from it.
If the costs associated with figuring out the generator's workings are too high, then indeed, the API has poorly addressed the usability of the code generator (and over time, it turns out that user configuration is also complicated). Ease of use should be the highest priority for any such API. But this is just one argument against code generation. Otherwise, you should completely write the local representation of internal or external truth manually.
Many will say they don't have time to deal with this. They have tight deadlines for their Super Product. We'll tidy up the assembly lines later; there's still time. My response to them is:
,
But in Hibernate / JPA, writing code 'for Java' is so straightforward.
Indeed. For Hibernate and its users, this is both a blessing and a curse. In Hibernate, you can easily write a couple of entities like this:
@Entity
class Book {
@Id
int id;
String title;
}And almost everything is ready. Now Hibernate's duty is to generate the complex 'details' of how exactly this entity will be defined in the DDL of your SQL 'dialect':
CREATE TABLE book (
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
title VARCHAR(50),
CONSTRAINT pk_book PRIMARY KEY (id)
);
CREATE INDEX i_book_title ON book (title);… and we start running the application. It's a truly great opportunity to quickly get to work and try different things.
However, hold on. I was being dishonest.
- Will Hibernate actually apply the definition of this named primary key?
- Will Hibernate create an index on TITLE? – I know for sure we'll need it.
- And will Hibernate indeed make this key identifying in the Identity Specification?
Probably not. If you're developing your project from scratch, it's always convenient to just discard the old database and generate a new one as soon as you add the necessary annotations. Thus, the Book entity will ultimately look like this:
@Entity
@Table(name = "book", indexes = {
@Index(name = "i_book_title", columnList = "title")
})
class Book {
@Id
@GeneratedValue(strategy = IDENTITY)
int id;
String title;
}
Cool. Generate it again. Again, in this case, it will be very easy at the start.
But later on, you will have to pay for it.
Sooner or later, you will have to go into production. That's when this model will cease to work. Because:
In production, you will no longer be able to just discard the old database and start everything from scratch. Your database will turn into legacy.
From now on, you will have to write . And what will happen to your entities in that case? You will either adapt them manually (and thus double your workload), or instruct Hibernate to regenerate them for you (what are the chances that the generated ones will meet your expectations?). In any case, you lose.
Thus, as soon as you go into production, you will need hot patches. And they need to be deployed into production very quickly. Since you weren't prepared and didn't organize a smooth pipeline for your migrations in production, you will end up patching everything wildly. Then, you won’t have time to do everything correctly. And you blame Hibernate, since anyone but you is always to blame...
Instead, everything could have been done completely differently from the very beginning. For instance, put round wheels on the bicycle.
First, the database.
The real "truth" in your database schema and the "sovereignty" over it lies within the database. The schema is defined only in the database itself and nowhere else, and each client has a copy of this schema, so it makes complete sense to enforce schema compliance and integrity right in the database – where the information is stored.
This is an old, even hackneyed wisdom. Primary and unique keys are good. Foreign keys are good. Constraint validation is good. – are good.
Moreover, this is not all. For instance, when using Oracle, you might want to specify:
- In which tablespace your table is located
- What its PCTFREE value is
- What the cache size is in your sequence (after the identifier)
This may not be important in small systems, but you don't have to wait for the transition to 'big data' — you can start benefiting from the data storage optimizations provided by the vendor much earlier, such as those mentioned above. None of the ORMs I have encountered (including jOOQ) offer access to the full set of DDL options you might want to use in your database. ORMs provide some tools that help to write DDL.
But, ultimately, a well-designed schema is manually written in DDL. Any generated DDL is merely an approximation of it.
What about the client model?
As mentioned above, on the client, you will need a copy of your database schema, the client view. It goes without saying that this client view must be synchronized with the actual model. How best to achieve this? With a code generator.
All databases provide their metadata through SQL. Here's how to obtain all tables from your database in various SQL dialects:
-- H2, HSQLDB, MySQL, PostgreSQL, SQL Server
SELECT table_schema, table_name
FROM information_schema.tables
-- DB2
SELECT tabschema, tabname
FROM syscat.tables
-- Oracle
SELECT owner, table_name
FROM all_tables
-- SQLite
SELECT name
FROM sqlite_master
-- Teradata
SELECT databasename, tablename
FROM dbc.tables
These queries (or similar ones, depending on whether views, materialized views, and table-valued functions also need to be considered) are also executed via the call from JDBC, or via the jOOQ meta-module.
From the results of such queries, it is relatively easy to generate any client view of your database model, regardless of what technology you are using on the client side.
- If you are using JDBC or Spring, you can create a set of string constants
- If you are using JPA, you can generate the entities yourself
- If you are using jOOQ, you can generate the jOOQ meta-model
Depending on the volume of capabilities offered by your client API (e.g., jOOQ or JPA), the generated meta-model can be quite rich and complete. Take, for example, the ability for implicit joins, , which relies on the generated metadata about the relationships defined by foreign keys between your tables.
Now, any change in the database will automatically lead to updates in the client code. Imagine, for instance:
ALTER TABLE book RENAME COLUMN title TO book_title;Would you really want to do this work twice? Absolutely not. Just capture the DDL, run it through your build pipeline, and get the updated entity:
@Entity
@Table(name = "book", indexes = {
// Have you thought about this?
@Index(name = "i_book_title", columnList = "book_title")
})
class Book {
@Id
@GeneratedValue(strategy = IDENTITY)
int id;
@Column("book_title")
String bookTitle;
}Or the updated jOOQ class. Most DDL changes also reflect on the semantics, not just on the syntax. Therefore, it can be useful to see in the compiled code which code will (or may) be affected by your database changes.
The single source of truth
Regardless of the technology you use, there is always one model that serves as the single source of truth for a certain subsystem – or, at least, we should strive for this and avoid enterprise confusion where "truth" is both everywhere and nowhere at the same time. Things can be much simpler. If you are merely exchanging XML files with another system, just use XSD. Look at the INFORMATION_SCHEMA meta-model from jOOQ in XML form:
- XSD is well understood
- XSD marks up XML content very well and allows for validation across all client languages
- XSD has good versioning and offers robust backward compatibility
- XSD can be translated into Java code using XJC
The last point is important. When communicating with an external system using XML messages, we want to ensure the validity of our messages. This can be easily achieved using JAXB, XJC, and XSD. It would be utterly insane to assume that with a 'Java first' design approach, where we make our messages as Java objects, they could somehow be meaningfully converted to XML and sent for consumption in another system. XML generated this way would be of very poor quality, undocumented, and hard to evolve. If there were a service level agreement (SLA) in place for such an interface, we would have failed immediately.
Honestly, this is exactly what constantly happens with JSON APIs, but that's another story; I'll rant about it next time...
Databases: they are all the same.
When working with databases, you understand that they are fundamentally similar. A database owns its data and must govern the schema. Any modifications to the schema must be implemented directly on the DDL to update the single source of truth.
Once the source is updated, all clients must also update their copies of the model. Some clients may be written in Java using jOOQ and Hibernate or JDBC (or all at once). Other clients may be written in Perl (wish them good luck), while others in C#. It doesn't matter. The primary model resides in the database. Models generated with ORM are usually of poor quality, poorly documented, and difficult to evolve.
So don't make mistakes. Don't make mistakes from the very beginning. Work based on the database. Build a deployment pipeline that can be automated. Include code generators to conveniently replicate your database model and push it to clients. And stop worrying about code generators. They are good. With them, you will be more productive. You just need to spend a little time configuring them from the start – and you will reap years of increased productivity, which will shape your project's history.
Don't thank me yet; later.
Explanation
For clarity: This article does not in any way advocate for bending your entire system (i.e., subject area, business logic, etc.) to fit your database model. In this article, I am saying that client code interacting with the database should operate based on the database model, such that the database model is not fully reproduced within the client code as a 'first-class' object. This logic typically resides at the data access level on your client.
In two-tier architectures, which still exist in some cases, such a system model may be the only possible one. However, in most systems, the data access level seems to me to be a 'subsystem' encapsulating the database model.
Exceptions
Every rule has exceptions, and I have already mentioned that the approach of database primacy and source code generation may sometimes be unsuitable. Here are a couple of such exceptions (there may be others):
- When the schema is unknown and needs to be opened. For example, you are a tool provider helping users navigate any schema. Phew. This cannot be accomplished without code generation. But still – the database comes first.
- When the schema needs to be generated on the fly to solve a specific task. This example seems like a slightly elaborate version of the , meaning that you really don't have a clearly defined schema. In this case, one can often not even be sure that an RDBMS will suit you.
Exceptions are exceptional by nature. In most cases involving the use of an RDBMS, the schema is known in advance, resides within the RDBMS, and is the sole source of 'truth,' while all clients must obtain copies derived from it. Ideally, a code generator should be utilized in this scenario.
Source: habr.com
