Interceptor and/or ability to rename returned id column?

[yatesco]

Hi there - I've moved from JTDS to Microsoft's JDBC Driver and the app broke. It turns out that JTDS returns the new PK ID under a field called :id: {:id THE_ID} but Microsoft's JDBC Driver doesn't: {:generated_keys THE_ID}.

I'd naively assumed that :id was standard/part of this library's API and that simply changing the driver (same app, same database, same server) wouldn't have such a big impact. Sigh - thanks Microsoft :-).

Anyway, I could elegantly solve this if:

• there was a middleware mechanism so I could write an interceptor which renamed the field
• there was a global set of defaults which allowed me to register an id-extractor-fn

I could painfully solve this by tracking down each and every instance in my app which performs an insert and then looks up the :id, but please, no :-). Going back to JTDS isn't feasible as it seems dead.

What would you suggest (other than not making such assumptions again!)?

[seancorfield]

Pretty much every DB driver returns generated keys differently, unfortunately:

  (let [new-key (cond (derby?)    :1
                      (jtds?)     :ID
                      (maria?)    :insert_id
                      (mssql?)    :GENERATED_KEYS
                      (mysql?)    :GENERATED_KEY
                      (postgres?) :fruit/id
                      (sqlite?)   (keyword "last_insert_rowid()")
                      :else       :FRUIT/ID)]

And those are only the ones I know about from testing -- other databases probably do something else.

What you get back also depends on the builder-fn you are using.

I'm not sure how a generic solution could work, without imposing a performance penalty on everyone...

[yatesco]

Thanks Sean. What's the use case for not always using the same key? I can understand the value of "consistent API regardless of vendor", but I can't see why a user wouldn't want this stability?

[yatesco]

in other words, what's the cost with new-key always :id (or some overridable const)?

[seancorfield]

First off, the generated key column could be called anything, not just :id, and next.jdbc knows nothing about your schema.

Second, PostgreSQL returns the entire row -- so next.jdbc cannot predict what columns may or may not be in the result.

Third, the list of possible keys I linked you to is only what I know about for testing purposes. Each database/driver combination could do something different -- and once you get down to the DataSource or Connection level, you can't reliably determine what the semantics should be: it's "just data".

In other words, only the user has any idea what the returned data might contain or might mean -- next.jdbc simply passes back the ResultSet it gets from JDBC: the data in that has no meaning to next.jdbc at all.

So you could provide a builder that maps :generated_key to :id if you're sure you won't have a column called :generated_key -- but there's no supported way to globally provide a builder function (you could use with-options but whenever you call get-connection or with-transaction you unwrap that because you're dealing with raw Java Connection objects). [And I view with-options as a bad design decision, made as a compromise in response to people complaining about having to be explicit in every call about the options they passed!]

If you're already passing an explicit builder function everywhere consistently, you could replace that builder function with one that renamed :generated_key to :id. Otherwise, the only workaround you would have available is redefining next.jdbc.result-set/as-maps (the default builder) to do what you need, with the usual caveats about AOT/direct-linking/etc.

[yatesco]

I see - so this lib is too low level for this - I get that, and that's fair enough. I thought you were pushing against the benefit of having the newly created ID being under a stable key, which I don't get, as that's exactly the type of abstraction I would expect from a JDBC type lib, even if it's out of scope for this :-).

[seancorfield]

It would be great if JDBC returned a stable key across all databases 😄 but since it doesn't, and there's no reliable, portable, efficient way to figure out what the returned key is called at that level, then it's not something next.jdbc can provide either.