Use H2 in dev and Postgresql in prod

Question

In Play Framework, I use evolutions to manage the database migration. I also use the combination of H2 for dev and test, and Postgresql for production. However, H2 seems to UPPERCASE all the table names and column names, and Postgresql does exactly the opposite.

Is there a way to make these 2 databases play nice together?

Here is the excerpt from my application.conf:

db.default.driver = org.h2.Driver
db.default.url = "jdbc:h2:mem:play;MODE=PostgreSQL"
db.default.user = ""
db.default.password = ""

# db.default: {
#  driver = org.postgresql.Driver
#  url = "jdbc:postgresql://127.0.0.1/khanguyen"
#  user = khanguyen
#  password = ""
# }

I use Slick 2.1.0

Does your application.conf file have `MODE=PostgreSQL` in the database url? — Mike Sherrill 'Cat Recall', Dec 10 '14 at 02:31
If you're using one of the ORMs, the ORM ought to take care of unquoted identifiers. If not, you'll probably have to quote *all* the identifiers yourself. — Mike Sherrill 'Cat Recall', Dec 10 '14 at 03:25

score 1 · Answer 1 · edited May 23 '17 at 11:59

1

There is an option to ignore case in H2, as answered in this post.

My application.conf is now like this:

db.default.driver = org.h2.Driver
db.default.url = "jdbc:h2:mem:play;MODE=PostgreSQL;IGNORECASE=TRUE"
db.default.user = ""
db.default.password = ""

edited May 23 '17 at 11:59

Community

1
1

answered Dec 10 '14 at 03:42

Khanetor

11,595
8
40
76

score 1 · Accepted Answer · answered Dec 12 '14 at 01:38

My previous answer was wrong. I did not know why it worked for a brief moment, on a machine running Ubuntu.

For case sensitive database system such as H2, or Postgresql, if you want to keep the case of character, then you should use double quotes surrounding all your table and column names.

The evolutions will look like this:

CREATE TABLE "products" (
    "ean" bigserial NOT NULL,
    "name" varchar(255) NOT NULL,
    "expiration_date" date NOT NULL,
    "expiration_time" time NOT NULL,
    PRIMARY KEY ("ean")
);

Then both H2 and Postgres will create this table with lowercase chars. Or:

CREATE TABLE "PRODUCTS" (
    "EAN" bigserial NOT NULL,
    "NAME" varchar(255) NOT NULL,
    "EXPIRATION_DATE" date NOT NULL,
    "EXPIRATION_TIME" time NOT NULL,
    PRIMARY KEY ("EAN")
);

Then both H2 and Postgres will create this table with UPPERCASE chars.

Use H2 in dev and Postgresql in prod

2 Answers2