Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.
Join the Persistence Hub!
Last weeks Hibernate Tip triggered some discussions on twitter on how you should create your database schema. Is it a good approach to generate your table model based on your entity mappings or should you create it yourself?
I think that both approaches are a good first step when you implement a new application.
Yes, that’s right. Both of them are just the first step.
You’re not done after you generated the table model or defined it yourself. You need a domain model and a table model. Both have to fit together, and you need to optimize them for usability and performance. That’s not easy, and it can’t be done in one step. There is also more than one way to achieve that. But before I talk about the different options, let’s quickly define the goals of the modeling process.
What are the goals of all these modeling steps?
When I create a new application, I want to make sure that I get the most out of my database and my Hibernate-based persistence tier. At the same time, I want to keep the amount of work as low as possible.
I like to break this down into 4 goals:
- The table model has to allow the database to store and retrieve the data as efficient as possible.
- The domain model has to be a good fit for all use cases so that they can get implemented easily and efficiently.
- The table and domain model have to fit together.
- The implementation process has to be as easy and fast as possible.
The goals 1, 2 and 3 are the most important. If you don’t achieve all of them, you will run into huge issues during development and production.
But you also shouldn’t ignore goal 4. One option to speed-up your development is to use code generation. It prevents you from boring development tasks, keeps the costs of your project small and helps you to finish it in time.
But more about that later. Let’s first have a look at an unnecessary but often discussed question.
Which model should I define first?
To be honest, I don’t care!
When you run your application in production, your database and your persistence layer have to work together. Don’t ignore that when you design, implement and test your application.
In the end, you need 2 optimized models that fit each other and allow you to use the database and Hibernate as good as possible. It doesn’t matter which model you define first.
You (or your team) are not done with the task before you defined 2 models that allow you to use Hibernate and the database in the best way possible. You will not achieve that if you just focus on one of the models and ignore the other one. You have to find a solution that works for both systems and that sometimes requires a few compromises.
Just pick the model you feel the most comfortable with and create it first. But don’t stop when you’ve done that. Create the other model and adapt both of them until you achieved the goals 1 and 2 and 3.
Be as efficient as possible
OK, I said that you can choose the order in which you define and adapt your table and your domain model. So, how do you create your models in the most efficient way?
It’s pretty obvious that you have to create the first model yourself. There are lots of different tools out there that help you do that. Just pick the one you’re most familiar with.
But what about the second model? Should you take the same approach and define it from scratch?
Sure, you can do that. But it’s boring and inefficient. It’s so boring that there are several tools available to automate this step. Hibernate and JPA can generate the database based on entity mappings and tools like JBoss Forge generate entities that map given database tables.
So why not use these tools?
The most common argument against them is simple: These tools create a working solution, but most often not a good one.
I like to use them anyways. The generated solution isn’t perfect, but it’s also not that bad that you can’t use it at all. It’s a good first step that saves you some time. It just shouldn’t be the final version you deploy to production.
I prefer to create my domain model first and let Hibernate generate a database script for it. I then take this script and improve it. This approach is often a lot faster than creating the table model from scratch. I just need to review the generated table definitions and make the required changes. In most cases, I can keep most of the generated table definitions and just replace or adapt a few of them. That saves me quite some time, especially for huge domain models.
Just keep in mind that, by default, Hibernate doesn’t generate any indexes. That’s something you always need to add to the generated SQL script. And please, don’t annotate them on your entities. Index definitions belong to the database and not to your domain model.
You can, of course, also define the table model first and generate your domain model. The process is basically the same. You generate the entities, review the generated code and adapt it if necessary. In my experience, the code generated by JBoss Forge is quite OK. It doesn’t require too many changes as long as your table model doesn’t get too complex.
There are 2 things left that I want to talk about quickly:
- How to store your entities and database scripts.
- How to deploy and migrate your production database.
Database scripts are source code
You’ve put a lot of work into the definition of your table and domain model. You defined one of them yourself and generated and improved the other one. You now need to make sure not to lose all this work.
The entities of your domain model are obviously source code which you store in your VCS. The same applies to the SQL script that creates your database. It will evolve alongside your Java code, and you should handle it in the same way. You should store it in your VCS so that you can keep multiple versions of it and adapt it in the future.
Database migration and deployment
Database deployment and migration are 2 independent topics that you shouldn’t mix with the generation of your first schema. Sure, JPA and Hibernate provide you an option to execute a script that creates the database, and you could create the next version of your database script in a similar way as I explained. But there are much better ways to do that.
Your production database will most likely contain customer data when you deploy a new version of your application. Dropping the old database and creating a new one is obviously not an option. You need to migrate instead of replacing it.
That requires the management of different database versions and the migration between them. The required update scripts are, of course, different to the create scripts, and you can’t generate them with Hibernate. You need to update your table and domain model yourself.
As soon as you’ve done that, you need to deploy your changes to your test and production databases. Flyway and Liquibase are great tools you can use for that. They help you to manage different versions of your database and to deploy the necessary updates in an automated way.
Let’s wrap it up
The generation of table and domain models is an often discussed topic. There are a lot of tools available that you can use for it. Unfortunately, I haven’t found one that creates a perfect result. But that doesn’t mean that I don’t recommend to use them. You just shouldn’t deploy the generated database or Java code to production without improving it.
I like to generate the table model based on my entity mapping definitions. That often provides a good solution for all simple entity mappings, and I just need to adapt the other table definitions, add indexes, etc. As you can see, there is still some work to do after I generated the database script. But it at least saves me from creating all the simple table definitions myself.