PRQL translation:

    from cars
    filter brand == "BMW"
    group year (
      aggregate [
        average_price = average price
      ]
    )
    sort year
    select average_price

A bit longer, but I love that it reads from top to bottom. When you have a complex SQL query, I’ve sometimes found it easier to write it as PRQL and then convert it to SQL

https://prql-lang.org/

A good ORM will mostly get out of the way and let you write something like

  return Cars
    .query()
    .select(avg('price'))
    .where( { brand: "BMW" })
    .groupBy('year')
    .orderBy('year')

Like all alternatives to SQL I have seen, this is not as lean and readable as SQL.

I totally agree - but this has the advantage of returning something like a well-defined object that hews to an interface, as long as you have the model set up.

The trade-offs are usually worth it, vs, doing something like:

  const row = db.query('select whatever from wherever');
  const obj = {
    id: row.id,
    brand: row.brand,
    ... etc
  };

It looks like "row" is already the object you want. Why make a copy of it? And why copy it by copying each property individually?

Row is just a dumb object, without a type.

No, "row" is a generic type, all concrete rows have concrete types narrowing the generic type with an ordered sequence of element tags and element types. (Relations are typed similarly to rows, since they are containers for rows of homogenous type.)

So is the obj you created.

Ah, I meant to specifically decorate it as a Typescript object, but forgot to add it.

The SQL is preferable in this overly simple, cherry-picked case, but now do composition.

I've done composition in Objection ORM, and honestly, it's a fair amount of hassle.

On our projects, which are moderately small, I genuinely don't know if it is/was worth the hassle of setting up, vs. writing some repeated code. (We also have a pretty decent testing culture in place, so I would be much more accepting of DRY-violations.)

I assume the above is based on said package? Wherein lies the hassle?

    bmws = Cars.query().where( { brand: "BMW" }).groupBy('year').orderBy('year')
    bmwAvg = bmws.select(avg('price'))
    bmwMin = bmws.select(min('price'))
    bmwMax = bmws.select(max('price'))

This is just a map/filter/reduce/sort, you can do that in pretty much any language you want

The filtering on 'BMW' is the only aspect of that query that the DB server should be spending its resources on. Avg, grouping, and sorting the results are the domain of the front end that has to go through them and pretty print them anyway

You want to drop the ability to group data from database software?

Some issues come to mind:

Grouping on the client would mean that you have to send the ungrouped data over the wire/air. Which might be orders of magnitude more data than the client needs. What if "cars" is a table of car photos made over the last 15 years and there are 20 million photos of BMWs in there? You send all the 20 million rows to the client to crunch it down to 15 rows?

The ungrouped data might be something the client is not allowed to see.

Depending on the type of grouping, the client would have to reimplement fast and efficient algorithms that have been tested and optimized for decades in RDBMs. Good luck, matching that by writing some JS for the browser.

Author here. Mongodb offers primitives around filtering, group bys and orders, but calls them 'pipelines'. I'm not an expert on that, but it's quite possible. It's just a series of set and list operations after all.

It is certainly possible, but is it as lean and readable?

No.

Is it a skill, though? Ask ChatGPT, "In SQL, given a cars table, with a price field a brand field, and a year field, how do you get the average price of each brand for each year...?

ChatGPT and similar LLM will get anything more complex and interesting wrong though, even the models specialized in SQL.

And writing the query in SQL is probably also faster than trying to write down the prompt.

Sometimes it can be used to optimize your queries, but it isn't its strength either.

Use QUEL?

    range of c is cars
      retrieve (avg(c.price) where c.brand = 'BMW' by c.year)

It seems either the group or the order is missing?

Yes, QUEL is a language for querying relational databases. Relations are fundamentally unordered.

I suppose if your example is using a NoSQL (meaning non-relational) database, that is ironically queried using SQL, then you might need something else. But my understanding is that we're back on the relational kick these days.

Everyone here missing the point. Yeah it’s doable elsewhere, but the SQL is so perfectly readable.