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

Answer 1

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.

Answer 2

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 = ""