The Clojure SQL ecosystem is weird. clojureql seemed wonderful for a while, but has been left to rot and the fact that nobody's really picked it up implies people have just moved on with their lives. Korma and YeSQL seem to handle most of the Rails-like use cases, and I guess everyone else has moved to more esoteric datastores.

I really like the approach.

And for the 80-90% queries you can create a small function or namespace with a nice API and converts to the types you like.

This can be an overriding practical objection to every ORM I've encountered so far.

To give an example I've run into several times, Postgres offers several levels of transaction isolation. The more isolated levels offer stronger guarantees, but you also need to be able to recover and retry if a transactions fails a serialization condition the first time. These levels determine how interactions work with related tools like explicit locking, SELECT FOR UPDATE, and so on.

In least common denominator ORM world, you're lucky if you get any serious control over this kind of thing at all. If you actually have a use case that requires precision here -- and sometimes you do even in surprisingly simple use cases, such as needing to allocate new IDs in an increasing, guaranteed contiguous sequence in Postgres -- then this stuff matters.

Writing longhand SQL queries is a pain for several obvious reasons, but it's still better than using an ORM and finding it doesn't support a feature you need or, worse, it does things implicitly and sometimes gets them wrong.

By leveraging jinja2/django-style template inheritance, you can even bring some advantages of ORMs (composition, reuse, and extending) into the raw-sql world.

The OP also intimated that he's taking a templated approach:

"“In these cases, I've elected to write queries using a templating system and describe the tables using the ORM. I get the convenience of an application level description of the table with direct use of SQL. It's a lot less trouble than anything else I've used so far.”

[1] https://github.com/civitaslearning/swigql

On a tangent: you mentioned Upserts in Postgres features. I thought Postgres didn't have any kind of Upsert. Was it added recently or something?

    BEGIN;
    LOCK TABLE search_tracking IN SHARE ROW EXCLUSIVE MODE;
    WITH upsert AS (UPDATE search_tracking SET count=count+1 WHERE keyword = 'John Doe' RETURNING *) INSERT INTO search_tracking (keyword, count) SELECT 'John Doe', 1 WHERE NOT EXISTS(SELECT * FROM upsert);
    COMMIT;

Table1 SET (...) WHERE Column1='SomeValue'

IF @@ROWCOUNT=0

    INSERT INTO Table1 VALUES (...)

  CREATE RULE location_updates_on_duplicate_ignore AS
    ON INSERT TO location_updates
    WHERE (EXISTS (SELECT 1 FROM location_updates WHERE (eid = NEW.eid))) 
    DO INSTEAD NOTHING;

eg:

  val cachedPurchasesQuery = Q[String, (Int, AccountId, Timestamp)] + """
                                                                          |SELECT pi.purchasable_item_id, u.account_id, pi.created_at
                                                                          |FROM purchased_items AS pi
                                                                          |INNER JOIN users u ON pi.user_id = u.id
                                                                          |WHERE purchasable_item_type = ? AND u.account_id IS NOT NULL
                                                                        """.stripMargin

  val magazinePurchases = cachedPurchasesQuery("Magazine").list

I prefer this over the Table mapping DSL and for-comprehension stuff personally. But I've only used it in production on smaller projects that are limited to under a dozen queries/statements or so.

[1] http://scalikejdbc.org/ [2] http://phoenix.apache.org/ [3] http://prestodb.io/

No expereince with Slick in particular; but I've been using jOOq[1] which I believe is similar.

To be honest I'm not entirely sold that these DSLs are what I'd consider "strongly typed." I can get jOOq to pretty easily yield queries that won't work if I switch out database dialects. (Ignoring, for a moment, that jOOq will let you embed SQL fragments as strings.)

As an example: jOOq will happily let you write an update query targeting a table bound to an alias. This type checks just fine. In fact this query will even work in Postgres; but it yields a syntax error from the database if you target SQL Server 2008 instead.

Then there's the issue of the dialects letting you use features the RDBMS doesn't support. For example you can build a merge statement regardless of which dialect is selected. If you were to select the Postgres dialect you'll get an "unsupported exception" at runtime.

If a method is RDBMS specific (in practice) then why is it part of the generic API?

---

My biggest beef with all these SQL abstraction layers is that many of them claim to be "write once, run anywhere." In practice I've just never seen that to be true.

I wish they would incorporate some sort of "capabilities" system that could run at compile time. Just imagine: when you swap in the Postgres driver your IDE throws a little red squiggle under the `.merge()` call, "method not found."

[1]: http://www.jooq.org/

Absolutely! They're "quite" typesafe, much more than string-based SQL. Much less than actual compiled stored procedures.

> As an example: jOOq will happily let you write an update query targeting a table bound to an alias.

Yes, that currently cannot be detected.

> Then there's the issue of the dialects letting you use features the RDBMS doesn't support

That will be addressed in the near future when we implement an API "preprocessor" that will effectively remove all parts of the API that are not supported by your given dialect. We'll also distinguish between native support and emulated support, if this strictness matters in your application.

The relevant issue is here: https://github.com/jOOQ/jOOQ/issues/720

> In practice I've just never seen that to be true.

True, but if you need "write once, run anywhere", you're probably much better off with a headstart than if you started from scratch.

I can get Hibernate to pretty easily yield queries that won't work if I use a mildly unusual combination of JPA features. weeps

In fact, you can do this with SQL. The following is perfectly valid in MySQL but not in any of the other dialects:

    SELECT a, b, COUNT(*)
    FROM t
    GROUP BY a

It will even do database migrations for you if the schema evolution is reasonably simple.

I suppose i could execute this with EF ( http://goo.gl/yrpver Stored Procedure mapping) and this way, i can still map my table in code (using Code-First)

    SELECT id, title, expiry_date FROM posts WHERE id = {{ id }} AND expiry_date > {{ date }} ORDER BY expiry_date

Access could be handled by a map[string]string, with the key as the filename and the value as the SQL (as a first thought).

  getUser, _ := db.Prepare("select * from users where username=$1 limit 1")
  row := getUser.QueryRow("john")