4

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.

Community
  • 1
  • 1
user967058
  • 475
  • 6
  • 12

2 Answers2

8

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
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • +1. I am wondering what are the use cases for using sysnonyms (and specifically for sequences) – Aravind Yarram Sep 30 '11 at 08:34
  • I've had a case where two versions of an application were deployed at the same time, on two different schemas. But we wanted to be able to merge the two schemas afterwards, and thus used synonyms for sequences to make sure both applications used the same sequence, and we would not have duplicate IDs in the two schemas. – JB Nizet Sep 30 '11 at 08:36
  • I wouldn't choose comments inside the SQL as an argument. You need to have some more general documantation about your objects/relationships outside your DDL anyways. Table spaces, query indices and such are a valid point, I have not considered this in my question! Comments and descriptions in the final db schema (e.g. for the db admin) are good as well. I wonder why those are not supported by ORM frameworks? E.g. extend the existing JPA annotations with comments to allow automatic creation of column descriptions or even db documentation ala javadoc. – user967058 Sep 30 '11 at 08:38
  • I was thinking of comments such as "this index is useful for the use-case XYZ. It might be useful to add the foo column in version 3". Or "this constraint is necessary to avoid bug 3452". – JB Nizet Sep 30 '11 at 08:45
  • After thinking about it a second time, aren't all those points limitations of the ORM frameworks? After all they're supposed to do the dirty work. If they do not support (automatic) documentation (of their SQL results) or versioning (their own schemata), that's sounds very much like a missing feature to me. Even fine tuning the schema depending on actual usage could be done by the ORM framework. – user967058 Sep 30 '11 at 08:51
  • Some of them, yes. Others, no. The way the SQL statements are distributed in SQL files is not something that should be specified in annotations. And the synonym thing, for example, is something which depends on the way the application is deployed. Not the way it is designed. Also, some indexes, for example, could be needed for an external application using the same schema. Being able to place these indexes in a separate file seems useful to me. – JB Nizet Sep 30 '11 at 09:02
  • Offtopic; I didn't mean to specify the format of the generated SQL by annotations. It's a result of the ORM framework and should thus be ignored. I want comments as part of the annotation attributes which cause the ORM framework to also add these comments to the db columns as well as being a possibility for tools like javadoc (or even the ORM framework) to create a documentation about your db schema just from your java code plus its annotations. An external application should specify its needs for indices etc. on its own, why would you want to include that SQL in your application? – user967058 Sep 30 '11 at 10:04
5

I usualy do the opposite, i.e. create the DB schema manually, then I use my IDE to export the JPA entity structure from it. Afterwards, I refine the JPA entities again manualy.

unixorn
  • 211
  • 4
  • 8
  • Haven't thought of this yet. Seems like a good idea, since this places the burden of knowing which annotations to use on the IDE :) – user967058 Sep 30 '11 at 08:33