Two Kinds of SQL Query Builders

Pipe syntax was a real "aha!" moment for me. I'm reasonably proficient at SQL, but pipelines make things so much more composable. It's really easy to just comment/uncomment parts of a query (like putting a LIMIT or WHERE in for quick testing) without having to edit anything else.

(I work at Google, but my enthusiasm for language syntax transcends employment)

tl;dr: A description of 2 almost-pipe syntaxes.

For declarative-dsls, I have thought about enabling the order of arguments to be meaningful (i.e. pipe syntax without pipes) (it would actually simplify the parser, currently you can put things in basically any order), so this works today:

(df/select :id :from person
                :where |(= ($ :gender-concept-id) 8507)
                :sort < :year-of-birth
                :limit 100)

There's also an approach with -> used by e.g. tech.ml or Qi which I, again, don't like much. Using a macro, something like this would execute everything efficiently (but most of these are privately defined apply- at the moment, so it won't work):

(->> person # random convoluted example with multiple :where
    (df/where {:gender-concept-id 8507})
    (df/join visit-occurrence)
    (df/where |(>= ($ :visit-start-year) 2020))
    (df/select :id [[:visit-start-year :year-of-birth] :age-at-visit
                     |(- ($ :visit-start-year) ($ :year-of-birth))])
    (df/where |(>= ($ :age-at-visit) 65))
    (df/exclude death)
    (df/sort > :age-at-visit)
    (df/select :id)
    (df/distinct true)
    (df/limit 100))

->>` already works, for example, given this data:

(def [person visit-occurrence death] [@{:id @[1 2 3 4 5 6 7] :gender-concept-id @[8507 8507 8532 8507 8507 8507 8507] :year-of-birth @[1940 1970 1945 1950 1935 1948 1955]} @{:visit-id @[101 102 103 104 105 106 107 108 109] :id @[1 1 2 3 4 4 5 7 7] :visit-start-year @[2021 2023 2022 2021 2019 2021 2020 2020 2024]} @{:id @[5] :death-year @[2022]}])

You can do:

(import declarative-dsls :as df)
(->> person
     (df/select :where {:gender-concept-id 8507} :from)
     (df/select :join visit-occurrence :from)
     (df/select :where |(>= ($ :visit-start-year) 2020) :from)
     (df/select :id [[:visit-start-year :year-of-birth] :age-at-visit
                  |(- ($ :visit-start-year) ($ :year-of-birth))] :from)
     (df/select :where |(>= ($ :age-at-visit) 65) :from)
     (df/select :exclude death :from)
     (df/select :sort > :by [:age-at-visit] :from)
     (df/select :id :from)
     (df/select :distinct true :from)
     (df/select :limit 100 :from))

# Or:
(->> person
     (df/select :id [[:visit-start-year :year-of-birth] :age-at-visit
                  |(- ($ :visit-start-year) ($ :year-of-birth))]
             :join visit-occurrence
             :where {:gender-concept-id 8507}
             :where |(>= ($ :visit-start-year) 2020)
             :from)    # multiple where already work in order!
     (df/select :id  # but the join makes us need a new select
             :where |(>= ($ :age-at-visit) 65)
             :exclude death
             :sort > :by [:age-at-visit]
             :distinct [:id]
             :limit 100
             :from))

...should I expose the functions directly to allow the first thing? Do people like such an API? I think letting the order be significant is best (but also giving a default select which handles it for you; easier for users, maybe.)

My understanding from SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL (2024) (previously (3 comments) and previouslyier (31 comments)) is that the pipe syntax is an SQL native data-oriented query builder.

The first example in the paper is translating the following trad-SQL query:

SELECT c_count, COUNT(*) AS custdist
FROM
  ( SELECT c_custkey, COUNT(o_orderkey) c_count
    FROM customer
    LEFT OUTER JOIN orders ON c_custkey = o_custkey
         AND o_comment NOT LIKE '%unusual%packages%'
    GROUP BY c_custkey
  ) AS c_orders
GROUP BY c_count
ORDER BY custdist DESC, c_count DESC;

In the equivalent in pipe syntax:

FROM customer
|> LEFT OUTER JOIN orders ON c_custkey = o_custkey
      AND o_comment NOT LIKE '%unusual%packages%'
|> AGGREGATE COUNT(o_orderkey) c_count
   GROUP BY c_custkey
|> AGGREGATE COUNT(*) AS custdist
   GROUP BY c_count
|> ORDER BY custdist DESC, c_count DESC;

Similarly in the blog post, the following:

SELECT "person_2"."person_id"
FROM (
  SELECT
    "person_1"."person_id",
    "person_1"."gender_concept_id"
  FROM "person" AS "person_1"
  ORDER BY "person_1"."year_of_birth"
  LIMIT 100
) AS "person_2"
WHERE ("person_2"."gender_concept_id" = 8507)

In the equivalent data-oriented FunSQL:

From(:person) |>
Where(Get.gender_concept_id .== 8507) |>
Order(Get.year_of_birth) |>
Limit(100) |>
Select(Get.person_id)

n.b. the expanded sub-queries.

That’s what I’ve done for the past few years. I don’t see the point in writing SQL keywords as methods. The only thing that’s tricky is when you need dynamic queries (eg. appending a WHERE filter if a variable is present) but I just write them as static queries instead (WHERE $1 IS NULL OR foo = $1). Writing raw SQL has made me wayyy better at SQL, I can easily copy paste queries if I want to try them in a DB query tool, and I don’t have surprise regarding the final query since it’s already written.

Which begs the question, why use them in the first place?

Using a DSL instead of raw SQL brings several benefits:

• You can run the same query against different databases, useful for applications that support that (e.g. something like Forgejo that supports different databases). The exact degree to which this works depends a bit on the query and whether the database supports whatever is spit out (e.g. CTE support is limited)
• Composing queries dynamically based on conditions, such as dynamically adding something like a WHERE admin = true clause, without having to concatenate strings or duplicate queries entirely (one for regular users, one for admins, etc)
• SQL injection becomes a lot more difficult, provided the DSL doesn't just inject the values as-is into the final SQL query
• Some degree of type safety in the case of a statically typed language, though the effectiveness of this is somewhat limited unless you start loading DB schemas at compile time
• Easier to factor out query logic into helper functions so composition is easier, something that gets really annoying when using string literals

Note that there's a difference between a query builder and an ORM, and the above specifically refers to query builders. ORMs can be useful but depending on their implementation they tend to add a lot of bloat that may not be necessary, and they typically force you into a very particular way of doing things.

Presumably one could do things like join generation helpers (based on the model one already has somewhere in the code), abstract frequently-used fragments of where, etc.? At least that's what I built for the project on top of clsql.

The biggest benefit I'd say for query builders beyond the syntax changes (which might be a wash, depending on one's preferences) is that they allow you to abstract common logic out in a way that almost always ends up awkward, especially from a performance angle, in a number of SQL dialects. I once tried to factor things out into views and procs for a big query I'd come across in a T-SQL environment, doing so cut the performance by a -lot-

Perhaps query optimizers have made strides in that department, I've not been slinging SQL in a while, but that's where I see the benefits.

I think that the DSLs that simply map to the existing SQL structure (like ActiveRecord) are less useful than ones that create a pipeline abstraction (like FunSQL). IMO the latter is much easier to compose programmatically or build extra helper functions, as each step is just a transform from one schema to another.

There are advantages of having your "main" or "real" language be aware of the queries so you can do cross-references, etc. Doing less string concatenation to build SQL also discourages people from accidentally/unknowningly creating an SQL injection vulnerability.

Sure, SQL has some warts, but finally what is the value of all those DSLs, apart from letting you put the SELECT clause at the end?

Indeed. And isn’t this prioritising writability (and code completion) over readability? I don’t want to have to read the entire query to figure out what it actually returns. (Although, what I really want is for people to use views more, in which case it wouldn’t matter as much).

why use them in the first place?

Two reasons for me. One is that you can have a pipe syntax for generic keywords that just happen to translate to subqueries, ctes, joins, and other constructs as needed. Or filters that turn into where/having as needed. Or groups that can create all the weird subqueries and syntax weirdness automatically. I mean, check for example the "orthogonality" and "top N by group" examples on https://prql-lang.org/

The other is that pipe-style queries are composable - you can insert a subexpression or append to the end, knowing you don't have to suddenly rearchitect the whole thing due to SQL limitations.

If I could change one thing about the Django ORM design it would be the double underscore thing, mainly because it's incompatible with type hints (which were more than a decade from becoming a thing when the Django ORM was designed.)