New ideas are here needed

[jjl]

Bored of existing SQL generation/ORM libraries and want to do something a bit different.

Having patched over the jdbc api sufficiently with utrecht and mpg, I'm left with a choice of libraries I'm not terribly keen on for the SQL part. Here are a few common tropes:

• Write SQL. - No thanks.
• Write SQL in files and add magic comments. - Really really no thanks
• Create a DSL with functions named e.g. 'select'. - Bit annoying as they clash with builtins and we have to exclude those builtins or import the namespace as an alias and qualify them. Also doesn't lend itself well to automated generation.
• Being tied to a particular database pool technology (and none of them choose hikari...)
• Give a query and parameters at the same time and return sql and bindings. This means we have to regenerate the query every time the parameters change (which really really doesn't lend itself well to prepared statements).
• Pretend to be activerecord. If I wanted rails, I'd use it.
• Support only core pgsql syntax (particularly the json extensions are never supported...)

So we need something that lends itself to automated generation, doesn't require us to write SQL, doesn't tie us to shitty technology, supports generating queries without providing data to bind into them, doesn't look like activerecord and supports as much of postgres syntax as possible.

Yes, it's going to be postgres-specific, the clue is in the project name. I said we can't be tied to shitty technology. It's going to have two layers as well: an end-user api and an api that can be easily wrapped to replace my API when I inevitably decide I don't like it any more without breaking existing code.

I'll tackle these each in comments on this issue

[jjl]

The SQL Generation layer should be basic clojure data, specifically a map. Going straight to the horse's mouth, I see that I don't even know what half of the options to select do, and I've been writing select queries for a looooong time now. Here is how I imagine a simple query could look in data:

{:select :distinct
 :fields [:*]
 :from [{:t1 :table1}
            [:left-join :table2 {:on [:= :t1/id :table2/table1_id]}]]
 :where [:= :t1/id {:binding :t1-id}]}

Remind ourselves that this is the SQL generation layer and that we will be wrapping it up better and there is flexibility. We could, for example, use symbols for e.g. = or on (although i also like that it's very easy to type and reads clearly as a literal, which you won't get having to insert quotes to replace the colons.

Critical here is the {:binding :t1-id}. Positional parameters suck for understandability. Ideally when we go to execute the query, we should be able to pass in a map of data and it fill out the bindings vector for us. This seems to achieve the clarity of other solutions even working at the raw SQL level while enabling us to prepare our queries ahead of time.

[jjl]

I'm wondering if the layer above this might just be a small DSL that shortens our typing and eliminates some of the boilerplate. I'm not talking about creating loads of functions that clash with core names, but for example we could have a ? function that expanded to {:binding ...}.

Here's how it might look with just this change and tidying up the query a tiny bit:

{:select :distinct
 :fields [:*]
 :from [{:t1 :table1}
            [:left-join {:t2 :table2} {:on [:= :t1/id :t2/table1_id]}]]
 :where [:= :t1/id (? :t1-id)]}

In practice I expect ? would actually expand to a record with a binding field so that we can speed it up with protocols.

[jjl]

I think my main concern with the SQL generation part really is fucking up the syntax so i don't take note of enough edge cases and then having to break backcompat because the sheer complexity of the options available to you for queries is staggering.