4 ms·

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

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.

PaulHoule 4y ago
Stored procedures, user-defined functions, views…
- quantified 4y ago
  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.
simonw 4y ago
The Django ORM is one of my favourite abstractions on top of SQL.
- rglullis 4y ago
  Nice try, Django core developer... :p
  - db48x 4y ago
    Now now, we all have some biases.
- quantified 4y ago
  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.
  - simonw 4y ago
    > Does it provide an object model for join, aggregation, filter and from clauses? Yes: https://docs.djangoproject.com/en/4.1/topics/db/queries/#retrieving-specific-objects-with-filters https://docs.djangoproject.com/en/4.1/topics/db/queries/#ret... and https://docs.djangoproject.com/en/4.1/topics/db/queries/#lookups-that-span-relationships 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/#calling-custom-queryset-methods-from-the-manager https://docs.djangoproject.com/en/4.1/topics/db/managers/#ca...
    - quantified 4y ago
      Cool, thank you for taking the effort to illuminate.
db48x 4y ago
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 https://diesel.rs/guides/composing-applications.html
- quantified 4y ago
  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.
sequence7 4y ago
EdgeDB & EdgeQL would be one good example. https://www.edgedb.com/ https://www.edgedb.com/ https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql

PaulHoule 4y ago

Stored procedures, user-defined functions, views…

quantified 4y ago
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.

simonw 4y ago

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

rglullis 4y ago
Nice try, Django core developer... :p
- db48x 4y ago
  Now now, we all have some biases.
quantified 4y ago
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.
- simonw 4y ago
  > Does it provide an object model for join, aggregation, filter and from clauses? Yes: https://docs.djangoproject.com/en/4.1/topics/db/queries/#retrieving-specific-objects-with-filters https://docs.djangoproject.com/en/4.1/topics/db/queries/#ret... and https://docs.djangoproject.com/en/4.1/topics/db/queries/#lookups-that-span-relationships 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/#calling-custom-queryset-methods-from-the-manager https://docs.djangoproject.com/en/4.1/topics/db/managers/#ca...
  - quantified 4y ago
    Cool, thank you for taking the effort to illuminate.

db48x 4y ago

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 https://diesel.rs/guides/composing-applications.html

quantified 4y ago
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.

sequence7 4y ago

EdgeDB & EdgeQL would be one good example. https://www.edgedb.com/ https://www.edgedb.com/ https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql