Clojurians Log v2

At a computer now so I can type more 🙂 I designed next.jdbc so the default would be table-qualified column names but no snake/kebab munging for efficiency reasons. The JDBC driver provides getTableName() on each column's metadata so, in theory at least, this is almost zero cost (but...) compared to the overall cost of building a Clojure data structure (vector of hash maps) from the ResultSet. My justification for this is that if you JOIN and the tables have the same column name (e.g., id is common as a PK), you get :table_a/id and :table_b/id etc so they do not conflict. In addition, having :customer/name and :product/name generally provides some default level of semantic uniqueness based on table+column, rather than just bare columns. Qualified keywords are in Clojure for a reason, esp. if you're using Spec. But... some databases don't implement getTableName() so it return nil or an empty string (Oracle, XTDB). And PostgreSQL is lazy about some metadata so it runs an extra DB query in the background to fetch the table name, which makes the default on PG slower than using a :builder-fn option with an unqualified builder 😞 And then, of course, some folks want the snake/kebab munging, hence the use of camel-snake-kebab to support those mappings and the specific builder function options provided in next.jdbc. I will admit, we use csk builder options in a couple of our namespaces at work, but everywhere else we rely on the defaults. In some parts of our code we explicitly map from :table_name/column_name keys to domain-specific keys, but that's typically only when we are combining tables or creating nested data structures from multiple result sets. Elsewhere we just live with the "native" table/column name format -- even where we have some old DB tables/columns that are camelCase, and newer DB tables/columns that are snake_case. For XTDB, my recommendation still stands: use the default as much as possible and maybe column_name to whatever in your domain model explicitly as needed (you won't have table_name, so beware of JOINs). Part of the reason is that options are generally per-call, although there is a way to wrap a DataSource up with some options -- except that doesn't carry across with-transaction because, for efficiency, next.jdbc works with bare JDBC types as much as possible. And then there's the whole "prefer plan over execute!" aspect: for maximum efficiency, use plan and reduce over the result set -- this can avoid constructing the Clojure data structure completely and also lets you handle very large result sets, so you can stream them while reducing over them. When reducing over a plan, you're working directly with the underlying ResultSet and fetching column values by name, via JDBC, so there's no table-qualifier involved either.

Thanks for your responses - very thoughtful and helpful! My question about matching database conventions was not well-formed in my head when I asked it, but thanks for making sense of it anyway. lol. I am still stymied by the error XTDB adapter is throwing when migratus tries to run a clojure migration. Namely, it's saying you can't run queries in a DML transaction (migratus is trying to query the migrations table to see where its at in the history of migrations that have already been run). I don't even get far enough to try honesql vs hugsql in the clojure migration - just running the migration kills the connection. But this is not properly a hugsql or honeysql issue so I mentioned it in #xtdb.

I thought I saw a thread about that earlier but now I can't find it. Link?

Seems reasonable to me. I only ever use XTDB via next.jdbc (with HoneySQL, but HugSQL should be fine too). Why migration library does Kit use?

Kit uses Migratus. I can't get it to work with clojure migrations, though. See #xtdb for details of the error I'm running into.

by clojure migrations, I mean where it tells migratus what namespace to find migrate-up/migrate-down functions in edn format.

I can't get far enough to use either hugsql or honeysql - the error appears to be related to the fact migratus is running a query to test for the existence of prior migrations, and the postgres/xtdb/next.jdbc adapter is not having it.

also, while I have your attention, what sql parameters related to table name translations etc do you recommend for XTDB2 dbs?

Default behavior.

(Sorry for slow response - lunch and then Love Is Blind 😁)

You probably want to simplify things to debug this. Kit is a lot. I'd try a simple repo with just Migratus and XTDB (and next.jdbc of course).

So - don't supply any additional parameters? (like a particular builder-fn ?

(I am also in ultra-async mode today lol)

Why would you need any?

I don't know - something like this is what I've seen in previous frameworks I tried:

(def ^:private sql-params
  {:builder-fn jdbc-rs/as-unqualified-kebab-maps
   :return-keys true})

I'm not entirely sure what the tradeoffs of the various options are

Well, XTDB isn't going to return qualified columns anyway, same as Oracle.

Is there any motivation at all for customizing the builder-fn etc?

I presume because the devs created such options, someone must have asked for them, so there must be some scenario-specific nuances worth understanding

I'm on my phone so I can't explain at length.

Autocorrect is defeating my attempts

no worries - when you get a chance, i'm just going to take your advice for now and not add any additional parameters

"the devs" = me, BTW

So - why did you add the ability to modify the defaults? There must have been a rationale.

"It seemed like a good idea at the time" = acceptable answer, BTW. lol.

All around it seems honeysql is better suited for XTDB 2 (and obviously has specific features to support it) than hugsql though to be honest I like using both - the former for "harder to express" queries, the latter for the meat and potatoes stuff you don't want to overthink.

People whined about the default returning qualified keywords (for most databases) 😁

"squeaky wheels get the oil"... lather, rinse, repeat ... lol

I'm more interested in the question of kebab-casing. Great for clojure apps, but kind of a pain for everybody else, no?

I guess a lot depends on how you name your database tables and columns...

Especially if they expect PostgreSQL conventions.

What would those be? (As opposed to other databases)

Yes, well, that's the rub. One advantage of Postgresql wire protocol support in XTDB 2 is using all the existing clients and tooling around it. but then, they don't expect clojure naming conventions.

OWNER_ID vs owner-id ?

or worse (and yes, I've seen this) ownerID vs owner-id

Right. And I think the right approach is to keep db table and column names asis

But if you are the author and can do what you want - you don't really want to break the Postgresql "contract" right?

If you need a mapping to a Clojure model, do it explicitly rather than trying to automate some part of it

I don't think it's the library's job to mess with names. But some people do.

Explicit is always better than implicit, obviously. But I guess I'm struggling with how much to care about PG conventions if I can have the Clojure Way all the way down?

Agree with that.

ok, well, I'm definitely not going to finangle any non-default parameters. Let the chips fall where they may in other regards.

If I have owner_id in the database, I want :owner_id in the code. If I map that to a different representation, that's up to the application(not the library)

I know PG can support owner-id`` as names when quoted, but it seems rude to PG admins.

So if you are creating a new XTDB DB from scratch, do you use the PG conventions, just to be a "good citizen" or nah?

That question doesn't even make sense to me, to be honest

I've worked in a lot of places where there's a dba team that sets naming conventions

Also, I use HoneySQL heavily and it maps kebab to snake case so that's never been an issue for me...