What if an SQL Statement Returned a Database?

Comments

• Turun@feddit.de ⁨10⁩ ⁨months⁩ ago

  Neat idea and solves e.g. the N+1 problem.

  But doesn’t that just shift the DB logic (denormalization, filtering, aggregation) into the application code?

  source

  • Nighed@sffa.community ⁨10⁩ ⁨months⁩ ago

    SQL returns subsets of all tables with only those tuples that would be part of the traditional (single-table) query result set

    So it returns only the data that would be returned from the query, so the filtering is done.

    I can see some uses of it. If you look at what something like Entity Framework does behind the scenes to return nested objects, you can see how something like this might help.

    source

    • Turun@feddit.de ⁨10⁩ ⁨months⁩ ago

      Yeah, the post on Reddit had some insightful comments as well.

      I did not think of nested objects that may be returned by an entity framework before.

      source

      • -> View More Comments
• xmunk@sh.itjust.works ⁨10⁩ ⁨months⁩ ago

  An interesting concept, and I do agree that the post-join cost is something that we can probably safely ignore. But as I was reading it I was curious if a better way to start conceptually approaching the solution is to consider an n+1 table approach where any tables referenced will be returned (filtered to relevant rows and optionally omitting extraneous columns) along with an additional table containing necessary key references and any of the computed aggregates etc… this might shift the select phrase to instead of defining all desired columns to only specifying additionally needed columns.

  But… I do have some objections to this concept it seems to place an extremely heavy value on the initial schema that SQL does not and causes difficulties in some scenarios when a single table is joined against multiple times for different purposes. I think it’d become difficult for the front end to decipher and rebuild the relationships without very heavy lifting.

  It’s a really interesting idea and flips the established return structure on its head in a way I don’t hate.

  source
• BehindTheBarrier@programming.dev ⁨10⁩ ⁨months⁩ ago

  I do think the idea is pretty neat, although it’s pretty close to returning structured data like json.

  But I do get a bit of feeling they are trying to solve a trivial problem, at least in their use case. Ultimately there are only so many lecturers, and so many man lectures at a given time. The total data amount wouldn’t be so much, and you can easily group by and sort on client side to achieve the original table which is show on a per lecturer basis. A little redundancy is in my opinion preferred over a query that returns 3 tables that then needs additional complicated work. I also find arguments about overlapping names to not be something the database should be handling, it falls on the data owners/manager instead. Academia is a wild west at times, but either this table is presentation only or a link to lecturer or lecture. And in the latter case, you’ll already throw in the ids so they can be used in an URL to some other site.

  While this can have significant less bandwidth, it also risks falling as soon as more data is introduced, as you’re putting the large join operations on the client when you can get free optimizations from the SQL engine you use. I know not having duplicate data could be a thing for something where I work, where essentially we have hourly breakdowns but fetch at least the entire day for a single set of parameters. So that means 24x data for a surprisingly high amount of columns. When we only need 2 of them on the hourly level! But in this case, the data doesn’t strictly need many joins as it has a lot of the information itself, along with there being too much data to join on the client side anyways for this to feel ideal. I feel you’ll increase the complexity a bit too much as well. A big advantage of sql is how easy it is to understand what you are getting.

  Its somewhat of a solved problem, if the performance becomes a problem, since we can return nested data anyways. So we can already today technically return a row where the hour and value columns have arrays. We just haven’t done it because it is not a big enough problem.

  source
• graycube@kbin.social ⁨10⁩ ⁨months⁩ ago

  You can already return complex data structures using jsonagg and recursive queries. I can see wanting to make that easier and more intuitive to do. Current sql does not restrict you to just 2D table data structures though.

  source
• poweruser@lemmy.sdf.org ⁨10⁩ ⁨months⁩ ago

  “an sequel”? Tell me I haven’t been saying it wrong this whole time 💀

  source

  • Carighan@lemmy.world ⁨10⁩ ⁨months⁩ ago

    Plenty people say Ess Que Ell. And hence “an” would be correct to use.

    source
  • Oszilloraptor@feddit.de ⁨10⁩ ⁨months⁩ ago

    There are four types of people:

    • "S.Q.L."
    • Sequel
    • Squirrel (seriously, I met 3 unrelated people saying squirrel. Why?!)
    • Who cares? You should use [another tool] anyway

    source

    • jadero@programming.dev ⁨10⁩ ⁨months⁩ ago

      #5? (Me): pronounced S.Q.L. except for Microsoft’s product which is pronounced “sequel server”.

      source
    • xmunk@sh.itjust.works ⁨10⁩ ⁨months⁩ ago

      Just run if they pronounce SQL as “Mongo”.

      source
    • moonpiedumplings@programming.dev ⁨10⁩ ⁨months⁩ ago

      You forgot “squeal”

      source