Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is using JPA/ORM to generate a db schema a bad idea?

Salve!

Part of another question/answer on SO (as well as other statements claiming the same):

if you are updating your database schema by JPA (generally not a good practice though)

Is it true that you should not use a JPA implementation to generate your db schema?

I have to model the entities and relationships ony my own anyways. I need to define constraints such as notnull, primary and foreign keys, data types and sizes as well.

Assuming that the JPA implementation in use does not have any flaws in its DDL schema creation code and also assuming that I do specify all JPA constraints, relations etc. correctly, the db schema created by the JPA implementation should be exactly the same -if not better- as a schema handcrafted by myself, right?

This does not include "special cases" such as (business-logic-) specific INSERT triggers etc., because these can not be generated by the JPA implementation at all (as far as I know, correct me if I'm wrong).

What's your view on this?
I do currently handcraft my db schema first, then setup the JPA constraints, relations etc. and let the JPA implementation create a db schema as well. I then compare the two schemata to see whether I've done the setup correctly. This does of course mean that I also have to specify the same column names etc. as I did for my handcrafted schema.

To make my question more precise; I do not blindly trust on the ORM framework to generate a schema for me. I rather have an idea of how the schema looks in my mind and then configure the framework to match it.
I guess you could create more/most efficient schemata only by hand, but after all I need (or rather want to) use them with an ORM framework. So while I should not do so, I need to keep the limitations of ORM frameworks in mind when creating a db schema anyways.

So since I use an ORM framework in order not to have to care about RDBMS specifics, what's the point of having my application create the schema using RDBMS specific DDL?
If I have to use some existing and exotic schema I do not need to (re-)create the schema anways as well as I might not be able to use such a generic tool as an ORM framework.

like image 560
user967058 Avatar asked Dec 12 '22 09:12

user967058


1 Answers

I usually let JPA create the schema initially only. After, I fine-tune it and maintain it by hand.

There are several reasons I prefer maintaining the schema by hand:

  • it allows putting comments in the SQL code
  • it allows adding comments/descriptions for the tables and columns
  • it allows specifying table spaces and other things that are not possible with JPA annotations
  • it allows splitting the schema creation between several SQL files (one per table + one for the constraints, for example)
  • it lets me reuse some parts of the schema creation script in my schema migration script. For example, if version 2 of my app introduces 3 new tables, I need an alter script that can reuse the three SQL files creating the three new tables
  • I sometimes need to use synonyms for sequences, rather than concrete ones
  • it lets me choose names for primary key constraints
  • probably some other reasons I've forgotten
like image 189
JB Nizet Avatar answered Jan 05 '23 04:01

JB Nizet