Ask HN: Does anyone else think SQL needs help? | Hacker News Reader

Ask HN: Does anyone else think SQL needs help?

96 pointsfny210 comments

I have been writing a decent amount of SQL for three years now, and while the paradigm is extremely powerful, the language lends itself to unmaintainable spaghetti code quickly.

Fundamentally, SQL lacks meaningful abstraction, and there's no sane way to package very common operations.

Say you want to find elements in a row that correspond to some maximum value when grouped by date. Today, you'd need to write something like this EVERY SINGLE TIME:

``` SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_percentage, sd1.sales_department FROM sales_department_details sd1 INNER JOIN (SELECT sale_person_id, MAX(no_products_sold) AS no_products_sold FROM sales_department_details GROUP BY sale_person_id) sd2 ON sd1.sale_person_id = sd2.sale_person_id AND sd1.no_products_sold = sd2.no_products_sold; ```

Wouldn't something like this be nicer?

``` SELECT sale_person_id, max(no_products_sold) as max_sold, link(sale_person_name, max_sold), FROM sales_department_details ```

Frankly, it seems like some sort of macro system is needed. Perhaps the SQL compiles into the above?

View on HN

Agreed that SQL lends itself to spaghetti. However after working with it for 25 years both as a developer but also collaborating with analysts and data scientists, I have to say I appreciate its basic declarative nature and remarkable portability as it has expanded into distributed databases.

For me the value of being able to walk up to an unfamiliar database of widely varying tech and start getting value out of it immediately is the killer app. Macros or other DB-specific extensions would be useful at times, but I’m not sure how much they would enable solving the messiness problem in a generic way that reshapes the paradigm more broadly. My instinct is the messiness is a consequence of the direct utility and trying to make it nicer from a programmerly aesthetics point of view might be hard to sell. It’s not like SQL doesn’t have affordances for aesthetics already (eg. WITH clauses).

SQL is masquerades as declarative. It's relational algebra in disguise. This results in...

- Queries that are difficult to interpret/verify.

- Non trivial amount of effort to go from question to query: I often feel like I have to jump through hoops to obtain something trivial. I have to think, and I don't want to think.

It's relational algebra, plus relational calculi.

> Fundamentally, SQL lacks meaningful abstraction

My RDBMS course at college was really hardcore and SQL code answers for exam questions were enormous.

One neat trick they didn't teach us, but still perfectly legal SQL:1999, is to use WITH recursive constructs.

This way you can decompose very complex queries into simple ones, that can tested independently.

The recursive stuff is pretty cool. In general, I like CTEs a way to break queries into pieces to improve readability and understanding (among other improvements).

Also, integrating aggregate functions along with row_number() and similar functions to perform more complex work with less code.

> SQL is masquerades as declarative. It's relational algebra in disguise.

IMHO declarative and relational algebra are orthogonal concepts. And why do you say SQL is relational algebra in disguise (paraphrased) ? Most introductions to SQL I've read, but certainly the SQL page on Wikipedia [1], starts with a remark about Codd's relational model. Granted SQL does not implement it perfectly, and it is not perfectly declarative, although that is a matter of taste.

> Non trivial amount of effort to go from question to query: I often feel like I have to jump through hoops to obtain something trivial.

I've had colleagues which expressed similar sentiments, but I have the opposite impression. I think I can express many queries on a much higher abstraction level than with most other languages.

> I have to think, and I don't want to think.

Not wanting to think while writing code is an understandable wish, but also a big red flag about developers for me. Even the best AI with perfect mind reading capabilities will not be able to execute the query you want, if you don't know what you want yourself. Even the AI would guess sometimes right, you would have no way of knowing whether it was right or wrong.

[1]: https://en.wikipedia.org/wiki/SQL

> Not wanting to think while writing code is an understandable wish, but also a big red flag about developers for me.

Dude... these are simple queries. I have *far* more important things to ponder than the specific incantations needed to get one of the values that correspond to a maximum.

max() is an iso standard function. But you need to think relationally to express exactly which maximum you're looking for, that's true. I can see wanting the language to do that for you, but that would be extremely limiting, since every problem seeking the "maximum" isn't remotely similar. Imagine a request for fewer tools in a toolbox. It might take time to learn where they are located, but they are all in the box for a reason. There's a certain point in learning SQL where you stop thinking about your work in rows and columns and you start thinking in tables. When that happens, you'll easily write good code with very little thinking. Give yourself time. Also look into dynamic SQL if you're concerned with repetition. There's no reason SQL should be overly so.

I agree that SQL is needlessly baroque. Not sure whether I agree that there are too many hoops for trivial things though. Leaving SQL ergonomics aside for the moment, isn't relational algebra something which does require one to think carefully to avoid hastily producing plausible but incorrect queries?

PowerBI is definitely down the "don't want to think" alley, lots of misleading graphs are born that way lol

Disagree. Microsoft sells these as "unskilled", but at the end of the day, you still need skill but also perseverance.

At least SQL is old enough to get plenty of relevant search results. You can be a copy-paste DJ if you really can't figure it out.

I agree! the bar's so low that I've seen a lot of people just click around till they get a pretty graph without knowing what/why they did!

I want to train a language model (or use existing ones) to translate question to query for trivial questions.

I find 80% of queries that people want across a company are trivial.

I’d have saved myself many many hours over the years if people could just ask a question in natural language rather than asking me to write a query.

I think if your SQL becomes spaghetti, it is time to fetch the data out of the database and then process it. SQL databases aren't a data-science studio that lends itself for extreme processing capabilities just because it is fast. Furthermore, it is the last thing that scales typically. Relational processing is what its good at. Doing things like bayesian/statistical analysis on the data in SQL is asking for trouble.

Relational databases are perfect for data analysis scenarios, especially when it fits in a single server. Why are you pumping data over the network, and serializing/deserializing it from a table structure usually back into a table structure, when you could just process it , often using only the data in the datastore, and pipe the final computed results over?

Hell they even have a name for databases specialized for this usecase — OLAP

The problem is that databases, and SQL, are such a pain to extend and define sufficiently general operations that you don’t just have scipy in the database, and thus your complicated analytics have to be fully specified with the few rudimentary analytic functions that are actually provided to you.

https://madlib.apache.org/

https://docs.timescale.com/timescaledb/latest/how-to-guides/...

https://duckdb.org/2022/05/04/friendlier-sql.html

It's a shame that so many people regard SQL using the reference SQL92 spec when in fact that it is continually evolving and improving like any other language still in use.

The story of the past 10 years at my FAANG has been one of making our key-value databases look as much like relational databases as possible, complete with SQL query engines (at least two of them, because what would a FAANG be without multiple slightly incompatible ways of doing things?). Honestly it's been a huge boon. Should we have abandoned SQL entirely, and come up with a new language? Maybe. But given that the primary initial motivation for this work was to allow analysts to creation millions of reports without bothering eng, it had to be a language that analysts understand. That's SQL.

OP here. SQL is fine: it's powerful. It just feels like there's something missing. I don't think you need a new language, just a set of extensions or macro system to make it more DRY.

Fair, and we do use A LOT of macros in general.

Views are only useful if you will repeatedly do the same query on the same view. In this case, it sounds like the OP was saying for every pair of tables like this you have to do this big query.

Still, views are free aren't they? Might as well create a view even if it is just for one query.

Are there any good libraries for exposing a SQL interface to specific languages that FAANG could open source and share making easier for more of us to do the same?

Trino (nee Presto) and Hive both have open source Java SQL parsers. I'm sure their connection management code would also be really strong place to start for a full SQL server interface.

That said, it almost certainly is a better idea to write a connector to something like Trino and just build on its capabilities rather than write your own SQL interface.

BigQuery?

I did appreciate the SQL interface, but as poster said, thinks could be a lot better, especially for exploratory analysis.

I can't believe that at time of this writing, only a few other comments are mentioning VIEWs! Like that is this dude's exact complaint!

smh...

Views, especially Materialized Views if you’re on Postgres, are about as close as you can come to a “one neat trick that solves all your problems” in modern software development imo.

I'd love to use materialised views more often but my experience is that they're full of annoying limitations, typically around functions that feel like they should be immutable but aren't.

Right, I think the timestamp with timezone type is a great example of one of these short comings.

I’ve generally used it as a step in a data pipeline, where some intermittent state or aggregation where being stale/stable for a period of time is ok. Did this recently for a daily trends report on some news data - live querying the data is slow, but baking it off into a MV makes it much faster and keeps the query result from changing too rapidly to new data.

Materialized views + pg_cron = chef's kiss for simple yet performant report generation.

Let's say you have 14 derived facts (eg aggregations, computations involving joined tables, etc) about a SalesPerson. Do create 14 single-column views? Or have one view with 14 columns? Either way the composability is terrible. If you go with 14 views, the syntax is awful and not very resistant to refactoring. If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).

> If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).

This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.

It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too.

(Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)

At least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.

For LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.

That's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)

I'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.

With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.

As others have said, any decent optimiser will prune out values that aren't SELECT'ed. I don't think it's that complex either - find the unused columns, remove them from subviews, recurse all the way down. Try it on MSSQL/Postgres and you can see it being done.

The bigger problem is views are tightly coupled to the underlying data.

Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data.

Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.

Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table.

Now, ... reuse. Surely you write the result to another table and then refer to that.

The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples.

Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros...

Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions.

Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle.

The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface.

Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions.

So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.

>functions that take sets as input and produce arbitrarily different sets as output

It is possible to return arbitrary sets from functions in many systems-- recent versions of the SQL standard even specify table functions (with syntax "RETURNS TABLE(columns)")[0] as well as polymorphic table functions (PTFs)[1]. And in addition to implementations of those standards, extensions like PL/[pg]SQL also have a few other ways of effecting table-like returns (parameter modes, composite type returns).

>function overloading

Not standard, but some systems support it[2].

[0] https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC...

[1] https://share.ansi.org/Shared%20Documents/News%20and%20Publi...

[2] https://www.postgresql.org/docs/current/xfunc-overload.html

Right, I'm aware of table-returning functions and the general idea of functions as table sources in a FROM clause. I think the biggest gap is not having complementary table-consuming function signatures and ways to use functions as table sinks other than aggregate function calls.

I'd also want to allow a function to be both a table source and a table sink, which I think means that you might need a sub-query like syntax to supply the source that will be consumed by the function. This is a vague idea and not a well-defined language proposal:

    CREATE OR REPLACE FUNCTION
    -- invent a table-sink func signature?
    func1( TABLE t1(a int, b text, c text) )
    RETURNS TABLE (x int, y boolean) AS $$
      -- access input set via declared name
      SELECT 
        a, b < c
      FROM t1
    $$ LANGUAGE SQL;

    CREATE OR REPLACE FUNCTION
    func2( TABLE t1(a int, b boolean) )
    RETURNS int AS $$
      SELECT a WHERE b
    $$ LANGUAGE SQL;

    -- implicit positional matching of func input cols?
    SELECT f1r.x, f1r.y
    FROM func1( SELECT e1, e2, e3 FROM ...) AS f1r;

    -- explicit input mapping to override positions?
    SELECT f1r.x, f1r.y
    FROM func1( SELECT e1, e2, e3 INTO b, a, c FROM ...) AS f1r;

    -- function composition
    SELECT func2(func1( SELECT e1, e2, e3 FROM ...));

You can SELECT just the columns you want with one view, and IIRC that will exclude other columns from being retrieved unless they are the source of a derived value.

Oh good, it’s not just me. As soon as I read his post I said to myself, “It sounds like he hasn’t heard of views.” But the first comment didn’t mention views and I was so confused that I started wondering if I misunderstood the post.

I disagree. This is a problem with filtering data. Creating a view every time you want 1 row from each group based on some calculation is beyond cumbersome IMHO.

But OP says every single time in their post which nicely fits a view as building block for more readable and simpler sql. Their issue seems not a one of filtering problem.

You can't build a view that allows you to do that query with an arbitrary function instead of MAX.

It's _every single time_ you need to do this kind of trivial querying you have to write this convoluted mess of SQL.

>with an arbitrary function instead of MAX

Many systems support user-defined aggregates[0][1]

[0] https://www.postgresql.org/docs/current/sql-createaggregate....

[1] https://docs.oracle.com/en/database/oracle/oracle-database/2...

There are situations where you can't use the views. For example I'm writing software which integrates with $vendor app. Not only do I have restricted select access, I have no influence on the schema at all. By definition, I have to send a complete query every time. And there are many similar scenarios.

Not SQL's fault. They probably wouldn't let you use macros either.

It would be nice to have a more composable solution though. Macros etc. don't have to be permanent. On the other hand, CTEs are pretty strictly tied to the structure for the current query.

Views.

https://news.ycombinator.com/item?id=32803396

They did mention they've only been using it 3 years.

It's been an ANSI standard for 36 years.

Frankly, the biggest problem with SQL is that not enough people know it well (and a lot of people think they know it well).

SQL is one of, if not the most, common programming language out there. I agree it isn't known well, but it not lending itself to being known well, and tricking people into thinking they know it well, is a flaw of the language. This is a much bigger problem. A good language would not lead people down that path. It not being known well certainly is not for lack of exposure.

They used to say the problem with C (especially with respect to memory issues) was that people didn't know it well, which was also true to some extent, but then we realized that we could improve language design to not leave people in that situation in the first place. Something like Rust would have never materialized if people simply took the attitude that you just need to hunker down and know C harder.

SQL is ripe for a similar evolution, but such evolution is difficult when the only reason we settled on SQL in the first place was to be compatible with Oracle and Oracle has no reason to change as long as the money keeps flowing in. The competition was going down some other interesting paths for a while. Even Postgres originally used QUEL, which is much closer to Codd's original vision, instead of SQL but eventually had to accept that Oracle won. But does Oracle need to win forever?

> SQL is one of, if not the most, common programming language out there.

Agreed with "one of", but the most common programming language is definitely Excel.

One of the biggest eye-openers for me was this chart.

https://www.sql-workbench.eu/dbms_comparison.html

One day I just sat down and learned about every item on that table. Not memorizing, just learning what it was for. Not only did my concept of what you can do with a database expand dramatically but also the limitations of insisting on hacking up equivalents in your own application code.

It's amazing how many steps can be skipped and race conditions can be avoided by leaning into the SQL.

Exactly. Even knowing a small bit of SQL (i.e. enough to know the full scope of what SQL can give you so you grok the limits of your ability) is a super power compared to using ORMs.

The right views, applied in the right places are like a super power compared to trying to do the same thing in application code.

I have just learned about the RETURNING close of INSERT just a few months ago for example and I'm programming since 1983... and I've learnt it from someone, who is 20 years older than me...

I started using databases a lot at a time when I was coding in Java. I would search for SQL tricks that would simplify my Java, so RETURNING was one of the first things I used heavily.

Soon I discovered window functions so that I could, for instance, get my data back with an extra column with the total number of rows, or the data in the first and last rows, and so on.

These little things had a large impact on the number of lines of Java that I had to write.

That is one of the reasons why I enjoy working with PostgreSQL. It's reference manual is of such high quality, that I consult it before Google or Stack Overflow when I have questions. That lead me to the discovery of many useful features of the years.

Roughly would you consider your statement equal to "SQL is designed for experts?" just curious

Not at all. I think it looks deceptively simple so programmers don't waste too much time learning it properly. But you can take up a good book (such as Joe Celko's) and set up a good foundation in a short time.

I happened to be in charge of organizing the translation of one of Celko's books to Hungarian. I even translated some chapters myself and proof-read the other chapters. It was indeed an eye-opening exercise! My background at that time was DBase, FoxPro and reading the Postgres docs. It was great to read don't recommendations about how to use the toolkit of SQL to answer real-world questions.

he seems to have done a whole series, which would you recommend?

"SQL for Smarties" is a good book to learn the language and "Puzzles and Answers" has a good collection of problems/solutions for typical situations you might find in real life.

I find CTEs (the WITH statement) greatly improved my ability to run more complex queries because they offer an abstraction I can use to name and compose queries together.

SQL views are useful for that kind of thing too.

The only issue I see with CTE's and have encountered, is poor query performance. The query planner needs to improve in this regard. At one point I noticed some of my CTE with queries being written to a temp file on disk and used to join against, when an inlined query did not do this producing the same results.

In Postgres you can use AS NIT MATERIALIZED to avoid the temp buffers. A recent PG version also makes this default, but only when the CTE is only queried once later.

This can definitely cause issues. I’ve seen queries with CTEs that basically process unindexed 100 MB data due to querying the materialized rows.

It would be cool to be able generate indexes with the CTEs though

How would this look with a sample query? (using AS NIT MATERIALIZED)

    WITH one AS NOT MATERIALIZED (
      SELECT 1 AS value, 'one' AS name
    )
    SELECT *
    FROM one

As srcreigh said, this is the default since I believe PostgreSQL 12, as long as the CTE is only used a single time. If you use it more than once, it by default wants to materialize it instead of recalculating each time. You can also force materialization by dropping the `NOT`.

Also note that materialization acts as an optimization fence. That is, PostgreSQL will not push down filter criteria and such from the query into the `WITH` clause. It can do such if it's not materialized.

This is incredibly helpful. I did not know this was a possibility.

What you're saying is analogous to "My horse won't run when I tie its legs together." You'll see what I mean here and hopefully it helps you.

Not sure which engine you're using, but for MSSQL at least, the engine writes stuff to disk where it can index it so that it can avoid table scans on retrieval. That is ideal where you have big joins or large tables. And is generally good for most use cases.

When you use CTE's you're telling the engine, "I want this in memory, don't do any indexing." That's awesome for write heavy workloads. But if you plan on joining the results of the CTE later, you will find that you can't read from it very quickly because for every row joined you must scan the entire table.

Now you'd think that since it's also forcing the data to stay in memory (if there's enough) that this would be faster. But that's not true. There's compute power at work to make comparisons on each row. The point of indexing is to reduce the required compute power by reducing the number of rows that you need to compare.

With that in mind, it's obvious that it kills performance if you just told your engine to not use indexes but do a big join on a large set of data.

Temporary tables are a better choice for read heavy workload compared with CTEs. MSSQL will index temp tables automatically. And in very rare cases where the automatic indexes aren't performant, then you can index them manually with a few extra keywords.

Happy querying.

Right, but can you 1) store CTEs and 2) pass arguments in to CTEs, such that the same basic 'code' can be shared between multiple different queries?

I'm a principal engineer who is relatively new with SQL (just learned it 5 years ago, using it for a side project, not my daily work) and almost completely self-taught; and the biggest problem I have with SQL is that I find it nearly impossible to do even basic levels of DRY in a way that's not ugly.

The problem at least with MSSQL is that these Table Valued Functions or User Defined Functions (scalar) are definitely not zero-cost abstractions.

Another issue is that the “SELECT” is also part of the function, which makes it even worse - so you can’t create just one function and reuse it all over the place. If only you could tell SQL that a certain JOIN always contains one row, or zero or one row (for a LEFT JOIN), then there would be more room for optimization.

I think that's subjective. You can index to optimize for whatever your function is doing.

I don't see any reason that the "select" limits reusability. If you define your function with relevant parameters you can create theoretically infinite reusability.

Yes, you can. Thats called a table valued function.

I believe what you're looking for is (in PostgreSQL) called a set returning function.

How to use a CTE with a group of several following statements? I think this is where you would use a view instead, but would appreciate confirmation.

For complex SELECT queries, I do:

WITH cte1 AS ( /* whatever / ), cte2 AS ( / you can refer to cte1 here / ), cte3 AS ( / you can refer to cte1 and cte2 here */ ), ...

If I need access into multiple statements that I can't refactor into a single one, explicitly using temporary tables is a viable option (some engines implement CTEs and other operations using implicit temporary tables anyway). Otherwise, yes, views.

Wouldn't

SELECT * FROM

(SELECT DENSE_RANK() OVER ( PARTITION BY sd1.sale_person_id ORDER BY sd1.no_products_sold DESC) r ,sd1.sale_person_id ,sd1.sale_person_name ,sd1.no_products_sold ,sd1.commission_percentage ,sd1.sales_department

FROM sales_department_details sd1) sd

Where sd.r = 1

do the same without the inner join?

Yes, you actually know SQL, unlike other people who don't even know window functions exist.

DENSE_RANK is not ANSI SQL, also you're implicitly performing a join with your "where" clause.

Which SQL engine supports none but ANSI key words? You're implicitly commenting in binary, but you didn't have to do any math.

You could take a look at EdgeDB. It's built on postgres, but uses its own query language and data model. Its data model has build in support for links and polymorphism. The query language EdgeQL makes it easy to follow links and output embedded arrays.

Agreed, EdgeDB[0] looks to be trying to solve a lot of SQL's perceived flaws with EdgeQL[1]. It's not the first attempt and I suspect it won't be the last but composability is explicitly one of the challenges it addresses.

[0] https://www.edgedb.com/ [1] https://www.edgedb.com/showcase/edgeql

>Frankly, it seems like some sort of macro system is needed

Views, materialized views, CTEs.

No, these are all highly coupled to the underlying data structure. Even stored procedures tend to know a bit too much.

Say I have a table with the following properties:

- It has a column that corresponds to an id

- It has a column that corresponds to a category

- It has a column that corresponds to a value

- It has a column that corresponds to a timestamp

- It has a bunch of other columns

I want to have a general transformation that allows me to:

Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max.

The stored procedure ends up being a mashup of string concatenation. Meanwhile, it just feels something is missing to make the language expressive enough.

>Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max.

The min/max aggregations over a period of time per category is pretty straight forward (aggregating rows), but what do you mean by "along with the other columns linked to min/max"? How would other columns come into play after aggregating rows?

Perhaps you're looking for a way of arranging SQL as an AST represented by data structures (or objects) that can be fed to a compiler. HoneySQL[0] is one such implementation of this idea and it makes your general transformation trivial for Clojure programs. You don't need to mess around with string concatenation because you have a predictable and extensible compiler for data structures (which are themselves easily composable/transformable/storable with Clojure) that you can trust to do the right thing. If you're using some weird database or need an esoteric syntax, extending the compiler to your clause is easy to do[1].

[0] https://github.com/seancorfield/honeysql

[1] https://github.com/seancorfield/honeysql#extensibility

I've started to toy with this already and using a tool like sql parse to abstract existing queries.

Like say you have a query that applies to one table and potentially others where the column names are slightly different. Why cant you just port that query?

> No, these are all highly coupled to the underlying data structure

Difficult to be sure what you mean, but I think you're wrong. You can select from any table source (edit: result set, if you prefer that term).

> Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max.

   select min(category) as mincat, max(category) as maxcat,
      min(value) as minval, -- etc
   where ts between <start> and <end>

I am not sure what you're asking for if the above is not it. Can you give an example please?

He wants to do the same thing, but on 100 different tables. The only thing that changes is the tablename and column names involved.

You can copy, paste and edit 100 times and create 100 view objects

Or in eg numpy-python, you’d write a single function taking the table/columns as arguments, and reuse it.

I think the usual SQL solution would be to implement a stored function and start smashing strings together

That makes sense -- in that case my first instinct would be to attempt using the "information schema" (metadata about tables and their columns) to get the tables I'm interested in (and their columns). Luckily all that metadata is presented as regular old tables itself, so it's pretty straightforward to query!

That’s what he means by it’s coupled to the underlying data structure; you can’t simply define a general purpose utility function to accomplish this (without smashing strings), that could be applied to arbitrary tables — eg on an arbitrary schema, or even on unnamed tables (eg resultset of a select query)

Which doesn’t matter in many of these simple examples, but this is the thing that really stops you from getting to have your import gravity ecosystem of utilities that modern application programming enjoys. And the continuing buildup of libraries on top of libraries for further complexity.

Instead you’re limited to extensions/packages, which are coupled tightly to the RDBMS internals (which is why you don’t see much of SQL extensions shared across DBs, despite at the end of the day operating all on the same shared framework [relations]) and whatever functionality the database provider has deemed fit to provide (perhaps at cost)

So you do that on 100 different tables - then what? Join them, return them to the client, what? Stuff's missing here.

Let’s say three usecases:

1. To Union them into multiple views

2. To instantiate individual views per query, to reuse in other queries

3. To return to client directly

I don’t see how this expansion will help you, but go wild. The problem remains that you can’t separate the logic of the view from the schema it’s acting upon

I've written about this topic a fair amount; first examining some of the criticisms of SQL that have been present since the language's inception [1], then looking at a new project called Malloy that I'm quite excited about, and think has a lot of potential to address some of the problems of SQL as it relates to data analysis [2].

[1]: https://carlineng.com/?postid=sql-critique#blog [2]: https://carlineng.com/?postid=malloy-intro#blog

I think the main problem with SQL is how it tries to be too “human like” and “non-programmers friendly” with all the English words, when it’s still used by programmers in the end.

I don’t see Malloy fixing it as it’s still using many English words and trying to be too much “sentence like”, but maybe I read it wrong

I agree that a major problem with SQL is its misguided attempt to be natural language-like. However skimming malloy examples it looks like they are just trying to be strongly declarative but not making the natural language mistake.

What do you think of PRQL ("prequel")?

https://github.com/prql/prql

Relevant discussion: https://news.ycombinator.com/item?id=30060784

The idea that you can visualize your pipeline and then "put it to paper" is pretty nice.

Cool name ;)

This is a very poorly written SQL.. Try some modern syntax: select DISTINCT sale_person_id, sale_person_name, max(no_products_sold) over (partition by sale_person_id) AS max_products_sold, commission_percentage, sales_department from no_products_sold;

I think every dialect has an idiomatic way to do what you're asking. e.g., in Snowflake it's `select * from sales_department_details qualify no_products_sold = max(no_products_sold) over (partition by sales_person_id)`, PSQL `select distinct on (sales_person_id) * from sales_department_details order by sales_person_id, no_products_sold desc`, ...

Postgres can do the"function over (partition by col_a order by col_b) as well.

Agree, SQL can already do what the OP proposes in almost the exact same syntax.

and together with CTEs, intermediate temp tables or views, you can totally avoid the spaghetti and break down the queries in manageable chunks.

postgres has window functions but it does not have the `qualify` clause.

True, but you can simulate it by running the query with the window function in a CTE and then make a separate SELECT with the appropriate WHERE clause. More verbose, but the same effect and result. Using NOT MATERIALIZED on the CTE can also potentially let the planner do better query optimization on it.

It sounds like you just reinvented VIEWs?

VIEWs and generated columns should be able to help with these problems?

https://www.postgresql.org/docs/14/ddl-generated-columns.htm...

> A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables.

Unfortunately with Postgres, generated columns are always stored on disk, so you lose some storage space.

Just make a function with the table name as a parameter. Same effect as what you're looking for except not included in SELECT *.

eg.

SELECT col1, col2, col3(t) FROM table_name t;

This is how Postgraphile solved the problem to great effect. https://www.graphile.org/postgraphile/computed-columns/

Finally, something for mariadb fans to hold on to in the flame war.

I prefer Postgres, and the thing I most envy from Maria is first class bitemporal tables. Granted it took me probably a week curiously returning to the topic to fully grasp their value, and I don’t think I’m alone in that lack of immediate intuition. And granted non-SQL databases like Datomic are much better positioned for use cases where people realize they want the feature. And granted it definitely can’t be more storage efficient to store bitemporal data than not to. But I think it’s a much more compelling feature, from the outside.

As far as memory complexity, this got me thinking a userspace implementation (I’ve prototyped one for Postgres using VIEWs for fun and personal education) could reasonably borrow optimization techniques from persistent data structures common in FP languages. But I don’t know how well a query planner would cope with that, and my instinct is that the optimization would be better applied at the storage engine level (again as I see it, advantage: Maria).

The key to temporal tables in Postgres is the following:

• add a timestamp range column

• create a history table that matches the main table

• using table inheritance, have the live table inherit from the history table

At this point all queries on the main table are "live" and queries on the history include past and present data.

• add an insert trigger that initializes the live table's timestamp range to start "now()"

• add AFTER UPDATE/DELETE/TRUNCATE triggers to insert the OLD record into the history table with appropriate timestamp ranges

Bonus

• have event triggers automatically set up the temporal tables on CREATE TABLE

So for temporal data this is a pretty solid approach. For bitemporal data, it’s more complicated to do in plain SQL (though it can be done). To clarify: I’m using temporal here the same way you are, as an immutable/append-only record of state as it existed at the time. Whereas bitemporal data has that property plus a separate historical record of when data was/should have been/should be or optimistically will be effectively true.

The thing which helped me grasp this was correcting historical accounting according to the domain (schema), eg updating meeting records. You of course want to preserve the previous record upon update, but the “current” state of what was decided a week ago might still change.

My reason for liking MariaDB is that MySQL uses a B-Tree for primary storage. In Postgres if you want a table with large keys, you have to store them twice once in the index once in primary storage (hash table). In MySQL there’s no hash table you basically just store the rows in an index. Could be as much as 40% reduction in storage… 400GB instead of 1TB.

Postgres does have index compression as of a recent version, but I’m not sure how much that would improve random keys.

A 40% reduction would be 600GB, and an index that took up 40% of the total would be excessive/unrealistic as well.

You're right of course that Postgres lacks true clustered indexes. The closest thing it has is covering indexes, which don't help at all with saving space; quite the opposite in fact.

Thanks. You’re right. And yeah, the 40% ish reduction would only happen if the table has for ex every column in a unique index. Not very common.

Haha I guess :-D. Though I have a workaround for complicated generated columns that I don't wish to store: using a function. So my select would look like:

  SELECT a, b, something_complicated(table) FROM table.

Of course you have to remember to do that but hey it's something.

Elixir's Ecto library basically lets you write sql in elixir while abstracting away all the stuff op talking about.

you can simply assign a subquery to a variable and reuse it.

PS: a lot of you are saying use views. thats dangerous. In most cases, thats a nice way to end up with ballooning autovacume processes as your databasse gets bigger. Materialized views are fine as long as your use case doesn't need realtime accuracy.

create view max_products_sold as select sale_person_id, max(no_products_sold) AS no_products_sold from sales_department_details group by sale_person_id;

select sale_person_id, sale_person_name, no_products_sold, commission_percentage, sales_department from sales_department_details natural join max_products_sold;

I am using JooQ now at work to write SQL statements in a Java DSL. Java’s type system mostly works as intended and is helpful, particularly in conjunction with an IDE. Many kinds of metaprogramming are possible with JooQ.

Learn Datalog Today: http://www.learndatalogtoday.org/

Put your example on https://www.db-fiddle.com/ or http://sqlfiddle.com/ or some other sql fiddle then we can all experiment with it and come up with leaner solutions.

Just asking for mooar build in stuff just leads to bloated software.

Absolutely.

https://www.scattered-thoughts.net/writing/against-sql (found from HN a week or so ago)

Pine [1] and jq [2] are combinator query languages.

Every expression represents a filter of some kind.

In SQL (and in relational algebra), every expression represents a dataset.

That means composing SQL expressions is composing data, not composing operations on data.

Since there is a somewhat low, backwards-compatible barrier to entry to build a combinator language on top of SQL (Pine is an example), whether or not trading the increased learning curve of using combinators for better composability is a good idea can stand the test of time.

[1]: https://github.com/pine-lang/pine [2]: https://stedolan.github.io/jq/

Check out dbt (not dbt-cloud), great open-source SQL tool, macro system is included in the ever-growing set of features. It helps managing SQLs a lot.

Nicer formatting of OPs woes:

    SELECT 
        sd1.sale_person_id,
        sd1.sale_person_name, 
        sd1.no_products_sold, 
        sd1.commission_percentage, 
        sd1.sales_department 
    FROM 
        sales_department_details sd1 
    INNER JOIN (
        SELECT 
            sale_person_id, 
            MAX(no_products_sold) AS no_products_sold 
        FROM sales_department_details 
        GROUP BY sale_person_id
    ) sd2 
    ON 
            sd1.sale_person_id = sd2.sale_person_id
        AND sd1.no_products_sold = sd2.no_products_sold;

With fear of syntax errors, I propose the following:

    WITH max_sold_per_id AS (
        SELECT 
        sale_person_id, 
        MAX(no_products_sold) AS no_products_sold 
        FROM sales_department_details 
        GROUP BY sale_person_id
    )

    SELECT 
        sd1.sale_person_id,
        sd1.sale_person_name, 
        sd1.no_products_sold, 
        sd1.commission_percentage, 
        sd1.sales_department 
    FROM 
        sales_department_details AS sd1 
    INNER JOIN max_sold_per AS sd2
    ON 
            sd1.sale_person_id = sd2.sale_person_id
        AND sd1.no_products_sold = sd2.no_products_sold;

Which still isn't nice to "bang out" in an exploratory analysis. Especially because the thinking goes "Get the max sales numbers and associated IDs, and then add the meta data", but the code is still "with the meta data, join the max sales data" regardless whether you prepare the max sales data "at the top" (which I did) or in the join (as OP did)

In dplyr, which imho has a nice syntax (but tons of other problems) it would be:

    sales_department_details %>% 
      group_by(sale_person_id) %>% 
      filter(no_products_sold == max(no_products_sold)) %>% 
      select(
        sale_person_id,
        sale_person_name,
        no_products_sold,
        commission_percentage,
        sales_department 
      )

First we group by ID, then on each group, we filter for the maximum sales, and finally we select the columns we are interested in. Super easy to bang out (and probably inefficient as we are moving tons of unnecessary columns between statements, but that is not the syntax fault)

What you described is already solved by window functions.

That’s because you are 1) writing naive SQL and 2) forcing naive SQL on an arbitrary data structure. You may use VIEWs, CTEs, GROUP BY CUBE/ROLLUP for 1) and rethink your structures for 2).

I think it would be neat if columns of a resultset could contain other resultsets (relvars?) as values. This would be the natural outcome of a join operation, and give greater flexibility on how you want to reduce those sub-relvars down. It also makes recursive queries more natural, yielding a "tree" of results. You could even build a reducer for a recursive query that concatenated rows to a string to serialize into whatever: json, XML, etc.

This is essentially how EdgeDB’s “EdgeQL” language [0] works. Pretty much everything it deals with is a set. It maps much nicer onto many types of application code.

[0] https://www.edgedb.com/

I'm not an expert but whenever I need to use SQL it seems to be complicated enough as I need at the time to do the job. When the job becomes more complicated, so does SQL.

I think it is a remarkably well designed language. I often get decent results for minimal effort. It isn't something I use day to day but more often month to month and I generally forget what I learned a couple of years ago.

For example, I wanted to clear down some records for a defunct client from our backup results database. I started by using SELECT to reliably find the customer (not as simple as it might sound). Then I construct an INNER JOIN (in this case most joins are the same) to link to the jobs. A SELECT COUNT(*) experiment looks about right. I snapshot the VM and change my experiment to a DELETE.

SQL is a bit strange but it lends itself to experimentation ... to results, way better than any other language I know.

Then you get into functions and views and the like. SQL has just enough complication for whatever job you have at hand and no more.

I recently ran up a query in Icinga2 (Director) against a MariaDB and it seemed to write itself. I get far fewer syntax errors in SQL than anything else that I try to abuse!

From https://bitbucket.org/djarvis/rxm/src/master/ :

    root               > people,           # "root" keyword starts the document
    person             > person,           # maps table context to a node
    .age               > @age,             # @ maps a column to an attribute node 
    .first_name        > name/first,       # maps a column to a node
    .last_name         > .../last,         # ... reuses the previous node's path
    account.person_id +> person.person_id, # +> performs an INNER JOIN
    account            > account,          # context is now "account" node
    .id                > @id,              # account id attribute
    ^,                                     # pop stack to previous table context
    address            > address,          # switch context to "address" node
    .*,                                    # glob remaining columns
    ;                                      # Denotes optional WHERE clause

The query produces:

    <people>
      <person age="42">
        <name>
          <first>Charles</first>
          <last>Goldfarb</last>
        </name>
        <account id="123"/>
        <address>
          <id>456</id>
          <street>123 Query Lane</street>
          <city>San Francisco</city>
        </address>
      </person>
    </people>

It wouldn't take much to make that a JSON document.

1) templating constructs or env vars? The programming languages can provide that too, but... prepared statements and variable-based substitution was a huge improvement to SQL code from programming languages (at least in java and ruby). Why wasn't that part of the SQL standard, why did it have to evolve organically? If it was in the specification, so many SQL injection vulns would have been avoided.

2) SQL statement syntax should start with the table specification so you can get autocompletion help when doing the columns and other expressions. The INSERT statement has it right, while SELECTs suck because the columns are specified before the WHERE clause, so there is no context. At least provide alternate syntaxes like SELECT FROM <table> COLUMNS <cols> WHERE <whereclause>, it would be minimal for the DB vendors to support.

3) SQL standards have been too nice to vendors, especially Oracle. The lack of portable SQL between databases has hamstrung the industry.

4) Stored procedure standards should have been formalized, as should standard apis. JDBC/ODBC were ok, but why are programming languages doing major enhancements/de facto standards setting?

In the semantic web, RDF can put every table in the universe in a unique namespace and OWL can bind together multiple tables and do inference at a high level, then you can query it with SPARQL which is quite similar to SQL at heart.

The Department of Defense was asked by Congress to answer “Where does the money go?” and tried to use OWL and SPARQL queries across all the relational databases they owned but they couldn’t get it to work.

I can’t help but think something along those lines could present as a ‘low code’ data tool.

You can accomplish something similar with views, foreign tables, stored procedures, user defined functions, and other mechanisms which are common in databases like PostgreSQL, Microsoft SQL, etc.

I find PostgreSQL pretty handy because it supports embedded data structures such as lists in columns, supports queries over JSON documents in columns, etc.

My favorite DB for side projects is ArangoDB which uses collections of JSON objects like tables and the obvious algebra over them which lets you query and update like the best relational dbs but you don’t have the programmability of views, stored procedures, etc.

I read this like "DoD tried to do quite a complicated semantic layer over tables project but failed because the RDF cluster of technologies is horrible. It would have been easier to code from scratch with SQL and python".

I think you are pretty familiar with those technologies. Do you think they have any value?

I see the problem of the DoD being asked where the money goes as an intractable problem, not least because they have more than one reason that they don't want to tell you.

That is, it will defeat any technology that you bring to the table.

My own feelings about RDF are mixed. On one hand I've accomplished quite a few things with it, for instance, developing a technique for bringing multiple schema systems (say EMOF, XML Schema, and FIX Orchestra) into a single framework but it has been like

https://supernatural.fandom.com/wiki/Binb%C5%8Dgami

for me in that I have put huge amounts of time and money into it with very little return, not because of technical problems per se but because of problems marketing it.

There's a certain kind of stupidity inside the community that keeps it from making tools that are truly effective but there is even more stupidity outside the community that makes it seem it won't ever be understood. The semantic web seems to be cursed that it will never develop a brand like GraphQL, Docker, mongodb, or "FAANG" that works like a Jedi mind trick to bring people into its domains.

For instance I saw GraphQL as beneath contempt for many years because there was no specification, it was just "Facebook will return any answers that further their business interests if and when they want to" and somehow people are seduced by that the same way that they are seduced by Donald Trump or will cut out their left eyeball because Matt Butts said that is how to raise your PageRank by 0.1% next Thursday.

Sorry for the slow response and thank you for the long one

If you make the joining keys have the same name, you can use USING(col_foo) instead of table1.col_foo = table2.col_foo. It's one of the reasons I always use tablename_id as primary key name and foreign key name. Doesn't always work (for example 2 foreign keys linking to the same table but represent something different like created_by and modified_by).

There are a number of query languages to address this type of reuse and composability for analytical query workloads.

- MDX: created by Microsoft to provide a dimensional query language. The language is incredibly powerful, but depends on your understanding of dimensional modeling (go read Kimball is the best starting point for learning MDX). There are several tools, both commercial and open source, which implement an MDX interface to data.

- DAX: Microsoft's attempt to make MDX more approachable. A relational, functional language built on top of an in-memory columnstore relational storage engine, and used in several Microsoft products (Power BI, Analysis Services Tabular mode).

- Qlik has its own expression language whose name I am not sure of.

- Looker has LookML

There are a lot of BI tools out there. Not all have an expression language to support reusable measures/calculations, but many do. You may want to look into one.

Thanks! MDX and DAX look powerful but also unreasonably complicated. LookML seems even more constrained. I'll keep digging.

Such languages offload more work onto the data model than SQL does.[0] For example, in the Tabular model (where you would use DAX), relationships are defined in the model (and supported by an in-memory data structure that you can shortcut-think of as a pre-computed hash-join) and implicit in (most) expressions you write.

If you think of SQL as a general-purpose query language, these BI-centric languages are DSLs for analytical queries. They are most useful for workloads where you have many queries that all use the same data model and are reasonably served by a pre-defined set of calculations/metrics.

[0] Do not mistake me, good data modeling is important for SQL as well.

Procedural SQL is how you package reusable/common operations in the DB world. In terms of programming languages, you'd use an ORM.

SQL isn't as broken as you think, it does what it's supposed to do very well. There are many ways to work with SQL such as procedural SQL and ORMs that build on it.

Check my project:

https://tablam.org

---

Exist 2 major ways to solve this: You do a transpiler (like most ORM are, actually) or you go deeper and fix from the base.

The second could yield much better results. It will sound weird at first, but not exist anything that block the idea of "a SQL" language work for make the full app, with UI and all that.

SQL as-is is just too limited, in some unfortunate ways even for the use-case of have a restricted language usable for ad-hoc queries, but that is purely incidental.

The relational model is much more expressive than array, functional models (because well, it can supersede/integrate both) and with some extra adjustment you can get something like python/ML that could become super-productive.

I agree 100%. This post from the EdgeDB team makes a great case why SQL is outdated [1]

[1] https://www.edgedb.com/blog/we-can-do-better-than-sql

> Frankly, it seems like some sort of macro system is needed.

Check out stored procedures and views.

Here's a great summary: https://stackoverflow.com/a/5195020

From my own experience, SQL is solid. The problem that is see the most is the one we are taught in programming 101: atomic data.

The information you seek above is computed and therefore justifies a view table instead of a "real one", because you really are doing something "complex". after that you can always select yourdata from yourview and you should be fine.

Relational data can only be really efficient if its relational and sometimes, computed tables are also a nice thing to have but even better: sometimes a porgramm needs to aggregate the data periodically and put it into the database relationally.

    SELECT 
        sale_person_id, 
        max(no_products_sold) as max_sold, 
        link(sale_person_name, max_sold), 
    FROM sales_department_details

I'm not sure what `link` is supposed to be, but wouldn't the dream syntax modification to SQL simply be

    SELECT 
        sale_person_id, 
        no_products_sold as max_sold, 
        sale_person_name, commission_percentage, sales_department,
    FROM sales_department_details
    GROUP BY sale_person_id
    WHERE no_products_sold = max(no_products_sold)

You sound like the exact target for Malloy, which was posted to HN recently:

https://github.com/looker-open-source/malloy

SQL is great, but you do have to dig deeper and use more of its features, like many other comments mentioned, like WITH & VIEWs.

On the other hand, maybe the time is ripe for you to "graduate" to using Datalog via other kind of databases, like Datomic, XTDB (fka Crux) and the likes (DataScript, Datalevin, etc)

Of course knowing Datalog won't help you with your SQL issues, but you can look into changing jobs, if you like what you see or maybe do some ETL job to spill over some data into the mentioned DBs and see whether querying it with Datalog is any better.

The problem with SQL is that it's all about transforming data, and that would better be modelled as a pipeline.

https://prql-lang.org/

Use composition, views are great for that, modern databases will optimize trough views as well so it's not like the day of old where a view with a join was an all consuming monster.

We already had the best minds in the world to try and replace sql, but because sql is rooted in group theory and math is hard to budge they all eventually had to get back and implement the group bys and havings and whatnot that complicate the sql syntax, except tackled on and with their proprietary syntax.

> Fundamentally, SQL lacks meaningful abstraction, and there's no sane way to package very common operations.

Tables are the unit of abstraction.

You’re on the right track with your instinct that you should be able to pull repeated work into a reusable unit. In many DB systems you can just register these sub queries as views and treat them as real tables. In the ETL world that I’m typically working in, you go one step further and just write pipelines to make useful intermediate tables.

Reuse tables, not subqueries.

The one that drives me nuts is

   select this,count(*) from that group by this

which is the most common data exploration query of them all which makes you type ‘this’ twice.

  select this, count(*) from that group by 1

Tested this out on Postgres and it works:

  SELECT
    name, count(1)
  FROM 
    (VALUES ('Kev',12), ('Meg', 14), ('Kev', 14)) 
  people(name, age)
  GROUP BY
    1

Produces: [[Kev, 2], [Meg, 1]].

But I don't get it. Could someone explain how this works?

Most SQL dialects accept a positional or a direct reference to the column name. “1” represents the first column created by the select statement, “name”.

Ah I see. Is that how `count(1)` also works? Or is that different.

Different. SQL's syntax is funky.

Please never allow this into production. I believe it is deprecated, it certainly is fragile in general.

I actually find this less readable. i have to go back and scan the select to see what column this index corresponds to. Totally breaks the flow of reading.

What would you prefer to see instead, bearing in mind that anything you will suggest is likely to bsave minimal typing but introduce new complexity into sql which everyone agrees is already creaking like a constipated elephant.

How about always returning the "group" column by default. If the user wants to get rid of it later (much less frequent) they can do an outer select or remove the column themselves.

Please give an example.

something like a function or macro

   CREATE MACRO count_values(this, that) WITH DEFINITION
       SELECT this,COUNT(*) FROM that GROUP BY this

Yes, I can't believe that the awesome power of relational DBs is still hindered by the major flaws of SQL. Here is my attempt at something better: https://docs.racket-lang.org/plisqin/Read_Me_First.html The feature I am most pleased with is that joins are values (or expressions, if you prefer) which can be then refactored using normal techniques.

I agree that additional abstraction and convenience features would be nice to have in SQL.

I disagree that it needs "help", which suggests that there is something fundamentally wrong with SQL that needs to change, possibly with help from outside of the SQL community.

On the contrary, I think SQL is great, but it could always be better.

Generally, I find that if you do anything too complicated with SQL it won't be scalable because the optimizer will start making bad decisions and you should just do it in the app tier where you can tune the algorithm more easily.

Thus, if you need abstraction, you probably are doing it wrong already.

Isn't SQL a nice target for a no-code visual language to compile to?

I also don't understand how does "SQL lacks meaningful abstraction". Most of serious RDBMSes have user-defined functions and stored procedures, that's just the ORM enthusiasts who are unwilling to use them.

Really would like "select * except ..." and a non-dynamic-sql way to remap column names

Except in ad hoc queries you shouldn't be using * at all so it isn't really an interesting problem to solve.

That SQL has * as the only abstraction for column names is quite frustrating.

I can think of so many other ways of dereferencing column names, from using a map/or lookup table to using types.

For example, selecting the unique id of a table regardless of what its name is.

Something like that seems reasonable until you realize that the unique id of a table could be a composite key and then things get complicated.

I translated this into "I don't really know SQL but I think I do".

SQL is 4th generation programming language and turing complete. It is developer who needs help by knowing more of the inbuilt features and keywords of SQL and how to compose them in effective manner.

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.

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.

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.

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

You can use stored procs and/or views to hide the details of queries if you want.

the specifics of an inner join vs outer join vs left join etc are crucial though. It makes sense to be able to specify them.

Feel you. I've long moved to jupyter notebooks as my de-facto database tool. I use python functions that generate the SQL, which I then execute in jupyter and load the results into pandas.

I’m raising my eyebrow here as far I can. Care to share what sort of use case this is for? Seems…elaborate.

SQL does seem antiquated, I know a lot of people who have worked with it for ages are in the 'don't fix if it ain't broken' camp but consider even completely basic things that you cannot do, such as get autocomplete for what you want to select.

Because you are forced to specify what you want to SELECT before the FROM, you are left guessing the column names.

In contrast, in a programming language or ORM, you'd do something like (pseudocode) Entity.Select( ... ) and can get suggestions of the fields, but with SQL you are forced to do it backwards.

> such as get autocomplete for what you want to select.

By "autocomplete" do you mean like column/table names ?

Yeah, column names. For me, it would make much more sense to start the statement with FROM instead of SELECT, to let whatever client you're using know which table you're selecting from, and then it can help you select the columns. Hope I explained this well enough.

I'm wondering how this could be done in an editor. I guess if you could pump it into a LSP, maybe it could have knowledge of the tables/views/etc database internals..

Some people put these inside strings though.. Like this:

https://www.w3schools.com/php/php_mysql_select.asp

(good practice arguments aside), Its a non trivial problem to solve across all languages.

Check out https://human.software, a visual language replacement (no code)

No wonder you think it needs help if you write it like that in one line with no indentation and insufficient capitalization.

There needs to be a Pandas to SQL translator.

There need to be more people who know both, so they aren’t trying to jam square pegs into round holes.

With dynamic sql you can write your own. I know you don't have time, just throwing it out there.

> Frankly, it seems like some sort of macro system is needed.

Like dynamic SQL? That has its own problems.

Rather than trying to extend SQL I think that people should stop trying to use SQL/RDBMSs for every task.

SQL was meant to be a hosted language from the get-go though, so any kind of macro-like feature was expected to be done in the host language.

Of course it's a bit painful and error prone, given SQL is a textual language, but as HoneySQL (https://github.com/seancorfield/honeysql) shows, you can represent SQL statements as data and generate them programmatically in a safe manner. I think it's compatible with https://babashka.org/ so you don't need a full Java environment to use it.

I wish programming was easier sometimes too.

There’s a LAST_VALUE statement for this kind of thing that most databases support. Partition by sale person name order by number of products.

This question breaks down across 3 completely different lines.

But first, SQL is a descriptive language: You describe what you want, you don't describe how you want it.

Instead, you rely on extremely complicated optimisers and a lot of using `EXPLAIN` to see what's happening under the hood. This is fundamental in the very _point_ of SQL.

Keeping that in mind, what do you mean:

[1] You feel the general notion of doing relational queries using a descriptive language is just broken. In which case, the fact that so many 'simpler' systems decided to add SQL support instead of just relying on the fact that folks will program their way into efficient queries is telling. People like being able to do this.

[2] You think the general notion is great, just, SQL specifically is badly designed. This then breaks down into two explanations.

[2a] You haven't actually read the spec, specifically your DB engine's spec. There's a ton DB engines can do - you can make your own procedures, your own aggregator functions, add triggers, and use VIEWs to combine it all (and VIEWs can act entirely as tables, including letting you INSERT or UPDATE them). For example, I'm pretty sure you can do precisely what you want, or at least get quite close, by only adding a little bit of extra definitions in postgresql. Either you just don't know enough (I'm just guessing here, based on very little info, please don't take it personally), or you do, but you find all that 'needlessly complicated'. In which case I think you're just misjudging how varied (and therefore complicated) your average DB user's needs are. Add the ability to do everything you want, but then for all use cases of people like you, and it is so complicated, you yourself would say 'yeah but not so complicated', and we're right back where we started.

[2b] No you really do know your way around SQL, including all the various exotic features that DB engines, particularly really good ones like psql, offer. And it's not good enough and you think you have a good grasp on how to make SQL specifically better. In which case: That has been done. Multiple times. The XKCD with the 14 standards comes to mind. Here is a REALLY long list of query languages: https://en.wikipedia.org/wiki/Query_language - pick your poison.

But first, think about the fact you didn't know about any of these. Whatever you're planning to 'replace' or 'fix' SQL, isn't that just doomed to be the 19th entry on that wikipedia list, just as forgotten as all the others?

In which case, [A] many, many db engines have extra syntax you might want to look at. You can make recursive queries, windowing functions, define your own procedures, and more - and use VIEWs to let you do precisely what you want. I'm pretty sure you can do precisely what you desire with postgres, using only a fairly simplistic amount of

Reply on news.ycombinator.com