> Compose SQL in an actual real programming language that isn't horrible instead of doing any of this.
Got any recommendations? That's a serious question, I've tried many (and built my own) and some are okay but none feel remotely good enough when you consider how much progress we've made basically everywhere else in the wider world of software engineering. Relational databases are so good; they deserve better than SQL!
Shameless plug if you've tried a range of alternatives: I've tried building a version (Trilogy) targeted at analytics, and I'd be curious about how it stacks up to your experience with other attempts at this - can read more about the composition/reuse story here: https://trilogydata.dev/blog/composability_reuse.html
I looked at it when it hit HN a while ago. Looks nice, but not exactly what I want because
> Joins and source tables are automatically resolved in Trilogy. You won't ever explicitly specify one in your query; you're declaring what you want, not how to get it. The responsibility of how to get it is delegated to the semantic model.
I don't fully understand how the semantic model is created (is this documented anywhere?), but I don't think I would enjoy a hard separation between the query layer and the semantic layer regardless.
I would prefer a continuum of "how much indirection do I really want" to be available to the user. My own exploration of this topic is https://docs.racket-lang.org/plisqin/index.html and you can compare section 2 (Using define-schema) to section 7.2 (Plisqin Desugared) if you want to know what I mean about a continuum.
Unrelated, the article we are commenting on has inspired me such that I think I have an answer to the big type system questions that eluded me when I decided to put Plisqin on the shelf. Maybe time to pick it up again... but probably not.
Ah thanks for the response! That makes sense - I gloss over it a bit in the demo intentionally, but that's a miss. The semantic definition flow is pretty much identical to what I think I'm reading with plisqin - there's an inline definition format (similar to DDLs in SQL), with the opportunity to reuse it by saving it and importing it. The separation is between SELECT/DDL - the relationships aren't defined in a select via Joins, they're defined before it in a separate statement then implicitly reused in the select.
Plisqin looks fun, the design rules resonate. If you don't pick it up again today, perhaps another time!
Find a good ORM that allows you to keep things at the "set logic" level for as long as possible and has a good internal query representation. Avoid or turn off any fancier features of the ORM like "navigation properties" or "implicit subtyping" or automatically detecting changes or anything that happens "magically" in the background: prefer simplicity and being explicit about everything. For C#, this is EntityFramework with most of its features turned off -- compose queries by joining IQueryables together, and only drop down into in-memory iteration when you have to.
If that's not available, I'd probably look for a library that presents a structured representation of the SQL abstract syntax tree (probably a subset) as an object, along with a translation to the various dialects. Pass those ASTs around and compose them together. I'd probably slowly build abstractions on top of this that eventually look similar to what I described in the first paragraph.
https://kysely.dev/ (SQL query builder for Typescript) is good, although I'm not sure it lives up to your expectations. Instead of an ORM, build the underlying SQL queries efficiently.
It basically allows to express queries independent of the target language. E.g. it's entirely possible that the same LINQ query can target PG, MSSQL Server, objects in local memory or RavenDB (NoSql). Quality of the generated query depends on the translating library ofc.
Syntax is very nice and much more aligned with programming than SQL.
E.g:
someData.Where(x => x.id =="someId").OrderBy(x => x.LastModified).Take(10).ToListAsync()
Note that the query is not executed until ToListAsync (or other materializing calls) is called. This allows for composabillity.
There is an alternative form of writing it more resembling SQL, but I've never seen it much used.
Got any recommendations? That's a serious question, I've tried many (and built my own) and some are okay but none feel remotely good enough when you consider how much progress we've made basically everywhere else in the wider world of software engineering. Relational databases are so good; they deserve better than SQL!