3 ms·

You can only go so far with SQL as far as readability goes, and is ensuring everyone in the team writes readable queries. Problem is, inheriting a big lump of q

You can only go so far with SQL as far as readability goes, and is ensuring everyone in the team writes readable queries. Problem is, inheriting a big lump of queries is totally different from inheriting a big lump of code: you can write tests (even if a little black-boxed) and refactor the lump until it makes sense. It is far more convoluted in SQL. I don't mean testing/debugging as in stepping through code that has been just written, but code that has been automatically submitted to the database during the night and for some reason has failed. The goal is to never be in that situation: with SQL you need to be way more careful than with other languages.

vog 10y ago
> You can only go so far with SQL as far as readability goes This. Exactly this is the problem. SQL as a language fails to provide you with simple means of composition. And this is where libraries like Python's SQLAlchemy come into play. Note that it is not necessary to use SQLAlchemy as an ORM. Even if you use it "just" as a query builder, it will simplify a lot. Composition is really where the syntax of SQL fails utterly. This is quite surprising, because the foundation of SQL, relational algebra, excels at composability: You have many operations which all operate on the same type of data (namely, sets of tuples, or in math/cs speak: "relations"). As such, you can combine them in any way you want. For example: * "Filter" takes a relation and returns a relation, just with fewer rows. * "Projection" takes a relations and returns a relation, just with fewer columns * "Cross Join" takes two relations and returns one relation. * "Inner Join" is really just a combination of "Cross Join" and "Filter" * ... and so on. SQL's attempt to be more "human readable" than those nested operations fails to preserve that. Yes, we have sub selects, but can't just stick together the operations we want. For example, assume you have an existing query (perhaps more complex than this one): select a, b from t where a > 0 and want to apply a simple filter "b > 0" on top of it: (select a, b from t where a > 0) where b > 0 This kind of composition is not allowed. You either have to either give up composition (no reuse of the existing query) and combine the filters by hand: select a, b from t where a > 0 and b > 0 Or, you have to write a larger sub select: select * from (select a, b from t where a > 0) as temp where b > 0 In relational algebra, the first statement would have been: Projection[a,b](Filter[a>0](t)) Or, using ">" for nested function calls (function composition): t > Filter[a>0] > Projection[a,b] For the task at hand, you just compose it with your additional filter and be done with it, reusing 100% of your existing query: t > Filter[a>0] > Projection[a,b] > Filter[b>0] However, SQL forces you to either rewrite this query: t > Filter[a>0 and b>0] > Projections[a,b] Or to apply a sub select, which means adding nonsense operations such as naming a purely temporary intermediate result and projecting to all columns: t > Filter[a>0] > Projection[a,b] > Name[temp] > Filter[b>0] > Projection[*] In my view, the task of SQL query builders (such as the one in SQLAlchemy) is to restore the ability for programmers to form their query in relational algebra, without having to worry about the quirks added by the SQL language.

vog 10y ago

> You can only go so far with SQL as far as readability goes This. Exactly this is the problem. SQL as a language fails to provide you with simple means of composition. And this is where libraries like Python's SQLAlchemy come into play. Note that it is not necessary to use SQLAlchemy as an ORM. Even if you use it "just" as a query builder, it will simplify a lot. Composition is really where the syntax of SQL fails utterly. This is quite surprising, because the foundation of SQL, relational algebra, excels at composability: You have many operations which all operate on the same type of data (namely, sets of tuples, or in math/cs speak: "relations"). As such, you can combine them in any way you want. For example: * "Filter" takes a relation and returns a relation, just with fewer rows. * "Projection" takes a relations and returns a relation, just with fewer columns * "Cross Join" takes two relations and returns one relation. * "Inner Join" is really just a combination of "Cross Join" and "Filter" * ... and so on. SQL's attempt to be more "human readable" than those nested operations fails to preserve that. Yes, we have sub selects, but can't just stick together the operations we want. For example, assume you have an existing query (perhaps more complex than this one): select a, b from t where a > 0 and want to apply a simple filter "b > 0" on top of it: (select a, b from t where a > 0) where b > 0 This kind of composition is not allowed. You either have to either give up composition (no reuse of the existing query) and combine the filters by hand: select a, b from t where a > 0 and b > 0 Or, you have to write a larger sub select: select * from (select a, b from t where a > 0) as temp where b > 0 In relational algebra, the first statement would have been: Projection[a,b](Filter[a>0](t)) Or, using ">" for nested function calls (function composition): t > Filter[a>0] > Projection[a,b] For the task at hand, you just compose it with your additional filter and be done with it, reusing 100% of your existing query: t > Filter[a>0] > Projection[a,b] > Filter[b>0] However, SQL forces you to either rewrite this query: t > Filter[a>0 and b>0] > Projections[a,b] Or to apply a sub select, which means adding nonsense operations such as naming a purely temporary intermediate result and projecting to all columns: t > Filter[a>0] > Projection[a,b] > Name[temp] > Filter[b>0] > Projection[*] In my view, the task of SQL query builders (such as the one in SQLAlchemy) is to restore the ability for programmers to form their query in relational algebra, without having to worry about the quirks added by the SQL language.