Hacker News Reader

Comment by db48x | Hacker News Reader

People build composable and reusable abstractions on top of sql all the time.

Examples would help. It's an assertion people frequently make. I have struggled with homegrown not-particularly-good ones. One problem is finding a good set of abstractions for the things I really need to abstract on. So there are probably different abstractions to note.

In the same vein that different languages provide higher-level abstractions for machine code or JVM bytecode.

Stored procedures, user-defined functions, views…

We may have differences in how we define "abstract". Views and SPs encapsulate, but they don't change the difficulty of parameterizing on table name or column name within SQL, for example. No spread operator to say "select the columns from thing X that are going to be inserted into thing Y".

There are so many different ways to abstract aspects for different goals.

The Django ORM is one of my favourite abstractions on top of SQL.

Nice try, Django core developer... :p

Now now, we all have some biases.

I haven't gotten into Python so its advantages are unknown. Does it provide an object model for join, aggregation, filter and from clauses? Can I compose named functions that represent transforms (map, filter, etc.) in a type-safe fashion with each other and have those translate to appropriate CTEs? ORM has no use for me, very little of what I work on exchanges data with programming-language objects.

> Does it provide an object model for join, aggregation, filter and from clauses?

Yes: https://docs.djangoproject.com/en/4.1/topics/db/queries/#ret... and https://docs.djangoproject.com/en/4.1/topics/db/queries/#loo...

> Can I compose named functions that represent transforms (map, filter, etc.) in a type-safe fashion with each other and have those translate to appropriate CTEs

The Django ORM doesn't use CTEs (yet). It's common however to create and compose custom filter operations which the ORM will then combine into larger SQL queries. A rough example:

    def today(qs):
        return qs.filter(created__date = datetime.date.today())

    def tagged(qs, tag):
        # This becomes a many-to-many lookup
        return qs.filter(tags__tag = tag)

    entries = today(tagged(Entry.objects.all(), "python")

This would build the following SQL query:

    SELECT
      "blog_entry"."id",
      "blog_entry"."created",
      "blog_entry"."slug",
      "blog_entry"."metadata",
      "blog_entry"."search_document",
      "blog_entry"."import_ref",
      "blog_entry"."card_image",
      "blog_entry"."series_id",
      "blog_entry"."title",
      "blog_entry"."body",
      "blog_entry"."tweet_html",
      "blog_entry"."extra_head_html",
      "blog_entry"."custom_template"
    FROM
      "blog_entry"
      INNER JOIN "blog_entry_tags" ON ("blog_entry"."id" = "blog_entry_tags"."entry_id")
      INNER JOIN "blog_tag" ON ("blog_entry_tags"."tag_id" = "blog_tag"."id")
    WHERE
      (
        "blog_tag"."tag" = 'python'
        AND ("blog_entry"."created" AT TIME ZONE 'UTC') :: date = '2022-07-30'
      )
    ORDER BY
      "blog_entry"."created" DESC

There are plenty of more sophisticated ways to build abstractions over queries in Django - using custom QuerySet methods for example: https://docs.djangoproject.com/en/4.1/topics/db/managers/#ca...

Cool, thank you for taking the effort to illuminate.

There is a database library called Diesel that I use which has good support for composition. The easiest example to point to is on their own webpage: https://diesel.rs/guides/composing-applications.html

Thank you. Like JOOQ this seems to provide a programming object model for SQL which is a good start. That's my 15-second scan, I look forward to looking further. A good expression model for everything, including non-parameterizable elements of SQL like table names and column names, type signatures for SQL results, is a great start.

EdgeDB & EdgeQL would be one good example.

https://www.edgedb.com/ https://www.edgedb.com/showcase/edgeql

Reply on news.ycombinator.com