Using Korma with ragtime generated h2 database

[sveri]

Hi,

I experienced the same problem as this user describes in his SO post: http://stackoverflow.com/questions/26542672/in-clojure-how-do-i-configure-korma-and-ragtime-to-use-the-same-database?noredirect=1#comment44700472_26542672

Repro steps:

1. generate h2 database with ragtime:
   :ragtime {:migrations ragtime.sql.files/migrations :database "jdbc:h2:~/closp2"}
2. Connect with clojure.java.jdbc:
   (def db-spec {:connection-uri "jdbc:h2:~/closp2"})
3. Try to read from a table will result in an error that it cannot find the table.

I used an sql client to make sure the requested table does exist in the database. I also added these dependencies:
[org.clojure/java.jdbc "0.3.6"] [yesql "0.4.0"] [korma "0.4.0"] [com.h2database/h2 "1.4.182"] [ragtime/ragtime.sql.files "0.3.8"]

Retrieving data from the same table with the same setup with yesql works as expected.

[immoh]

I suspect the issue is that h2 converts identifier name to uppercase by default.

The SO post example can be made to work by either

1) Quote identifiers in the migration file:

create table "users" ("id" INT, "first" varchar(32), "last" varchar(32));

OR

2) Use uppercase table name in select, e.g.

(korma.core/select :USERS))

You didn't post the contents of your migration file or yesql query file so I don't know how yesql solves this for you.

[sveri]

I tested both your solutions, they both work for me, so your suspicion was correct.

The question remains, is this a bug in korma or not?

[immoh]

So just to reiterate my knowledge about this:

• h2 treats unquoted identifiers case-insensitively (apparently by always using upper-case)
• h2 treats quoted identifiers case-sensitively
• Korma uses quoted identifiers with h2 by default

This means that if you use quoted identifiers everywhere, you shouldn't have problems with Korma and h2.

You could also configure Korma to use unquoted identifiers:

(defdb korma-db (h2 {:db "my.db" :delimiters ""}))

and we could even make this default in Korma but it wouldn't fix anything since it wouldn't necessarily work if tables were created using quoted identifiers.

[sveri]

Maybe it would be enough to document this somewhere, one of the problems I faced was that I did not find any useful information regarding my problem, besides the SO post, which did not have an answer yet.

[immoh]

Hopefully the next person struggling with the problem will find this issue. Each RDBMS has its own peculiarities and it is impossible for Korma to document all of those.