CODEWITHSUNDEEP
postgresqlhibernate
DavidT
DavidT

Reputation: 490

Hibernate / Postgres - Changing the default column type mappings

I am starting new project using Postgres and hibernate (5.5.7) as the ORM, however I have recently read the following wiki page:

https://wiki.postgresql.org/wiki/Don%27t_Do_This

Based on that I would like to change some of the default column mappings, specifically:

  1. Use timestamptz instead of timestamp
  2. Use varchar instead of varchar(255) when the column length is unspecified.
  3. Increase the scale of numeric types so that the default is numeric(19,5) - The app uses BigDecimals to store currency values.

Reading through the hibernate code it appears that the length, precision and scale are hardcoded in the class: org.hibernate.mapping.Column, specifically:

    public static final int DEFAULT_LENGTH = 255;
    public static final int DEFAULT_PRECISION = 19;
    public static final int DEFAULT_SCALE = 2;

For the 2nd and 3rd cases (varchar and numeric) I don't see any easy way to change the default (length, precision and scale), so the best option I have been able to come up with is to create a new custom "Dialect" extending from PostgreSQL95Dialect who's constructor redefines the mappings as follows:

    registerColumnType(Types.TIMESTAMP, "timestamptz");
    registerColumnType(Types.VARCHAR, "varchar");
    registerColumnType(Types.NUMERIC, "numeric($p, 5)");

Using this overridden dialect I can generate a schema which includes the changes I am trying to achieve.

I am happy with the timestamp change since I don't see any use cases where I would need to store a timestamp without the timezone (I typically use Instant's (UTC time) in my model).

I can't foresee a problem with the varchar change since all validation occurs when data is sent into the system (Rest service).

However I have lost the capability to use the (@Column) scale attribute - I have to use an explicit "columnDefinition" if I want to specify a different scale.

This still leaves me with the following questions:

  1. Is there a better solution than I have described?
  2. Can you foresee any problems using the custom dialect, that I haven't listed here?
  3. Would you recommend using the custom dialect for schema generation ONLY or should it be used for both schema generation and when the application is running (why)?

Upvotes: 1

Views: 994

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16430

Well, if you really must do this, it's fine, but I wouldn't recommend you to do this. The default values usually come from the @Column annotation. So I would recommend you simply set proper values everywhere in your model. IMO the only okish thing you did is the switch to timestamptz but you didn't understand how the type works correctly. The type does not store the timezone, but instead will normalize to UTC.

The next problem IMO is that you switch to an unbounded varchar. It might be "discouraged" to use a length limit, but believe me, it will save you a lot of trouble in the long run. There was just recently a blog post about switching back to length limited varchar due to users saving way too big texts. So even if you believe that you validated the lengths, you probably won't get this right for all future uses. Increasing the limit is easy and doesn't require any expensive locks, so if you already define some limits on your REST API, it would IMO be stupid not to model this in the database as well. Or are you omitting foreign key constraints just because your REST API also validates that? No you are not, and every DBA will tell you that you should never omit such constraints. These constraints are valuable.

As for numerics, just bite the sour apply and apply the values on all @Column annotations. You can use some kind of constant holder class to avoid inconsistencies:

public class MyConstants {
    public static final int VARCHAR_SMALL = 1000;
    public static final int VARCHAR_MEDIUM = 5000;
    public static final int VARCHAR_LARGE = 10000;

    public static final int PRICE_PRECISION = 19;
    public static final int PRICE_SCALE = 5;
}

and use it like @Column(precision = MyConstants.PRICE_PRECISION, scale = MyConstants.PRICE_SCALE)

Upvotes: 1

Related Questions