10 ms·

Composable SQL

271 points by earnestinger 2y ago

earnestinger 2y ago
Attempt at sql composability.
earnestinger 2y ago
prior art: https://research.google/pubs/sql-has-problems-we-can-fix-them-pipe-syntax-in-sql/ https://research.google/pubs/sql-has-problems-we-can-fix-the... https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/ - talks about compositionality https://www.edgedb.com/blog/we-can-do-better-than-sql https://www.edgedb.com/blog/we-can-do-better-than-sql (2019) (List taken from https://lobste.rs/s/vmyk7d/composable_sql#c_layb8n https://lobste.rs/s/vmyk7d/composable_sql#c_layb8n)
default-kramer 2y ago
Very nice. Adding this one to my list of "things other people wrote that nicely elucidate a shortcoming of SQL" > Why would this be useful? Because SQL tables are global variables. By vanishing global variables, we automatically make every query fully testable. And even if you don't care about testability, certainly you can appreciate that global variables are bad for composability.
summerlight 2y ago
Isn't it table-valued function? IIRC, the SQL standard still doesn't have it but it's almost universally supported extension across vendors.
- zetalyrae 2y ago
  At least in Postgres, table-valued functions can't take tables as arguments, only scalars. That's the main difference: functors can not just return tables, but take tables satisfying some interface as arguments. https://www.postgresql.org/docs/7.3/xfunc-tablefunctions.html https://www.postgresql.org/docs/7.3/xfunc-tablefunctions.htm... I thought I had written a footnote or appendix about this but I guess I forgot.
  - dagss 2y ago
    MSSQL can take tables as arguments if they are temporary tables declared to be of a certain type. But that restriction limits their use a lot.
throwaway201606 2y ago
I understand the primary premise about the difficulty with testing SQL and fully agree with it. I do have a question though - while I understand how functors can help make the problem easier to tackle, I am not sure I fully understand how functors are different from a similar existing tool - stored procedures. Some DB flavors: - can take tables as arguments to stored procedures - can return tables - also offer the additional benefit of being able to run almost all flavors of SQL commands ( DDL, DML, DQL, DCL, TCL) in those stored procedures Netezza stored procedures, for example, can do what you describe here: https://www.ibm.com/docs/en/netezza?topic=nsg-return-result-set https://www.ibm.com/docs/en/netezza?topic=nsg-return-result-... As can SQL Server & Oracle (which both return cursors, which are just ordered tables): https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/s... https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#CIHDDIFG https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg... Am I missing something fundamental here? How are functors different from stored procedures? To me, they seem to be just a sub-class / sub-type of stored procedures.
- andyferris 2y ago
  They seem somewhat like stored procedures, but not stored? As in a query can contain a functor in it and then use it immediately. I didn't see those `create functor` statements as anything other than ephemeral - or am I wrong? EDIT: also stored procs that use imperative logic and cursors can be quite a bit slower than queries that achieve the same logic - the capability here is purposefully a subset of that and is just to help build standard SQL queries that can go through the standard query planner.
  - throwaway201606 2y ago
    I think they have to be long lived else they cannot make sense for performant testing. ie they are created as DB objects, using DDL, in the same way tables, views, functions etc are made. They can certainly be created at test run time but that would slow things down a lot - you would essentially be creating a ton of objects every time you run the test which means having a setup to test if they exist or not, take them down if they do or fix them if they don't match spec ( e.g. column and data type changes etc etc ) The more I think about this, the more complicated I realize it would be to manage this dynamically: You essentially have to build a test harness enviroment that figures out your testing elements dynamically from your data environment (with some kind of parameterization engine and data set to tell it what to look for so as to "make functors and run them" (e.g. all PKs of FKs or all columns starting with a certain prefix or all columns of a certain data type etc etc), gets the most up to date definitions of those elements from system tables and uses that data to create or update or drop functor objects ... wow, ok, this is getting complicated, I am going to stop now before I see the void.
feoren 2y ago
SQL cannot be "fixed". It's too broken, too ubiquitous, too customized, too optimized. Will these functors integrate with the thousands of optimizations modern database systems rely on? This post has the format: "SQL is extremely terribly awful at writing business logic. But if people implemented this one half-baked, unrealistic idea, then it would suck a little bit less!" The first part is spot-on: SQL is a horrible language for writing business logic. So: stop. Stop doing that. Stop writing business logic in SQL! Imagine you joined a new team, and they said: our program uses a bunch of XML files as its data format, and that's why most of our business logic is implemented in XPath. Just sit there and ponder that situation for a little bit. This is what you all sound like to me. Stop it! > Testing the simplest query requires building up a massive object graph, just to test an infinitesimal slice of it. Yes, correct, because SQL is awful at representing business logic, and foreign key constraints are business logic! The fact that an employee has one supervisor who is also an employee (FK Employee.SupervisorID -> Employee.ID) is a business constraint. (And one that can easily change, by the way.) All these database engineers who can't pull themselves away from their stored procedures will argue with me to their graves about foreign key constraints, but they're generally terrible. They're a business constraint that just happens to be supported by the database engine, but they're still a business constraint. Stop putting your business logic in your persistence layer. We've been getting better at building programming languages that actually support software engineering every year. Use one of those. SQL is an output of one of your program layers. Just like XPath would be an output of your program. Compose SQL in an actual real programming language that isn't horrible instead of doing any of this.
- liontwist 2y ago
  What is business logic? What specifically is SQL bad at? I’m not sold by your foreign key example. Constraints are great. I guess you can do that in python? Good like getting ACID right As soon as your data stops resembling a tree and starts looking like a graph than all your “business logic” looks a lot like operations on relations and you are back to where you started.
- hobs 2y ago
  I don't know why you are getting downvoted, and I generally don't like a lot of business logic in the persistence layer, but representing the things you absolutely dont want another part of your app doing via FKs is...pretty normal. If you are arguing it spreads out your business logic into multiple places, that's fair, but so does... a lot of things. Databases with decent fks and constraints have a tendency to outlive the apps that build atop them, and communicating these constraints in long lived fashions can be ... good actually.
brianhama 2y ago
MS-SQL would seem to already support everything you’ve suggested.
r1b 2y ago
Nodding along furiously. One area where this comes up is when you want to export some piece of logic that effectively “returns a table”. For example, I work with a source of data that requires a particularly hairy JOIN strategy. The logic is the same for any table with the same “shape”, but no one can reuse it without copy / paste.
- virtualwhys 2y ago
  See Esqueleto in Haskell, Slick and Quill in Scala, probably LINQ or some variant on .NET in C#/F#. All support building up arbitrarily complex queries based on statically typed query fragments. String fragment "composition" is like banging rocks together.
froderick 2y ago
I already have functors, effectively, by writing sql queries in clojure with honeysql. The clojure data structures make behavior composition from reusable sql snippets easy, and the testing of sql snippets is just based on literals as the article describes. Works really well.
- nateroling 2y ago
  I’ve done the same with SQLAlchemy in Python and SQLKata in C#. Sadly the whole idea of composable query builders seems to have fallen out of fashion.
  - jaza 2y ago
    If composable query builders have fallen out of fashion, that's news to me. I've used SQLAlchemy in Python fairly heavily over the years, it's gotta be one of the best out there, I plan to continue using it for many more years. And I've recently gotten my feet wet with Ecto in Elixir, I'm quite impressed with it too.
gremgoth 2y ago
SQL select-from-where queries can indeed be formalized as "pro-functors": https://arxiv.org/pdf/1602.03501 https://arxiv.org/pdf/1602.03501
geysersam 2y ago
Similar functionality already exists in some SQL implementations, notably DuckDB: create table macro author_books() as ( select a.name as author, b.title, from a inner join b on b.author_id = a.author_id ); is the same as the example in the article and it is called like: with a as (...), b as (...) from author_books(); https://duckdb.org/docs/sql/statements/create_macro.html#table-macros https://duckdb.org/docs/sql/statements/create_macro.html#tab...
- _dark_matter_ 2y ago
  I really dislike leading off with "create table". My brain is unable to read it except as DDL.
- krembo 2y ago
  Isn't this an abstraction of functions?
  - tankenmate 2y ago
    It is an abstraction, it may be arguable if it is an abstraction of functions. I once built a web app where we had a library of CTEs that we used to manage all the layers of indirection in the data, it was performant and made it much easier for users to be able to search and manipulate the data; especially for ad hoc data analysis.
- n_plus_1_acc 2y ago
  How does it differ from a view?
  - geysersam 2y ago
    It can take arguments. I didn't use any though so it's not a good example
- typ 2y ago
  DuckDB macro doesn't support instantiating a table macro using the `query_table` function. So, it's not possible to expand a table macro like `foo(bar(baz()))`, making its use limited.
iforgot22 2y ago
I prefer to keep the SQL itself as simple as possible. I've gotten by OK by composing SQL in my outer code as needed. Despite relying crazy heavily on SQL, that's rarely needed. And the outer code has tests. It'd just be nice if Postgres could loosen the syntax in a few annoying places, like allowing a trailing comma in "WITH q1 AS (...), q2 AS (...)" Or if something could be done to avoid needing the "WHERE true AND" trick.
agentultra 2y ago
Ah but “functor,” is a name from category theory and algebraic topology. I had read along thinking that this would come up in the conclusion! Instead it seems it’s not even mentioned in the sources for the name. There’s potentially something like this in the “esqueleto” package for Haskell. But it has some limitations. It composes on syntactic fragments. Although speaking of functors, the category theory ones, maybe categorical databases would fit the bill: https://categoricaldata.net/ https://categoricaldata.net/
beart 2y ago
On the testing aspect - The TSQLT assembly (https://tsqlt.org/ https://tsqlt.org/) is an attempt at unit testing within MSSQL. It works okayish for what it is. I've at least found it to be nice when testing inputs for SQL functions and procedures, as well as some business rules within tables and views. A few IDEs provide integrations for TSQLT: Management Studio with the redgate extension, Datagrip, maybe others.
willvarfar 2y ago
The article talks about how CTEs are efficiency minefields and how Postgres doesn't do a good job of pushing down predicates, for example. TBH, the solution is to improve the Postgres engine in this regard? For example, in Bigquery I often use CTEs and see predictable speedups that are explained by predicate pushdown when slapping a WHERE clause on the user of a CTE etc. Bigquery has plenty of performance gotchas but I don't think CTEs are one of them; they seem to be transparent to the optimiser. Something else that Bigquery supports is user-defined 'table' functions. These are like the 'functors' the article creates. However, in practice they aren't very widely used, and I think this hints that they aren't as useful as the article is hoping for. The thing I _really_ want is scoping. If only functions could be described like CTE WITH blocks, e.g. WITH visits AS ( SELECT ... FROM web_logs WHERE ... ), foo(bar INT, baz STRING) AS ( ... function body here ) SELECT ..., foo(a, b), ... FROM visits WHERE ... Apart from the tidiness of this, this allows inlining of functions into the SQL statement body rather than as separate statements. Often various tooling e.g. DBT makes it really hard to with separate function definitions etc, and being able to inline logic would make things possible. Bigquery also supports user-defined aggregate functions. Although there is rarely the need to stray from the standard builtin aggregate functions, when you need them they are brilliant. (Anyone working _on_ Bigquery reading? If so, please ping me, I have loads of feedback on what works well and where the user pain and toil is ;) )
- sa46 2y ago
  As of Postgres 12 (Oct. 2019) CTEs are no longer materialized by default and are eligible for predicate push down. The planner may still struggle but it’s not true that a CTE is an optimization fence. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- vprcic 2y ago
  Functions in WITH clause really are a blessing. Oracle DB supported these for a decade now and that was a gamechanger for me. https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#functions https://oracle-base.com/articles/12c/with-clause-enhancement...
brikym 2y ago
This what Kusto Query Language looks like: StormEvents | summarize TotalStormsInState = count(), StormsWithCropDamage = countif(DamageCrops > 0) by State | extend PercentWithCropDamage = round((todouble(StormsWithCropDamage) / TotalStormsInState \* 100), 2) | sort by StormsWithCropDamage I find it to be much more readable. With SQL CTEs is as close as you can get.
- __thecoldwine__ 2y ago
  Kusto is fine, but joins are quite horrendous there. To be frank, I don't really get complains about "SQL is not readable". Most of the time SQL is the most compact, clear and to the purpose way to fetch the data (given the data had been modeled in a manner suitable for queriying). Luckily, most of the tables for KQL in Azure are flat and not complex and that is partially what makes kusto so compelling.
mrtimo 2y ago
Malloy is a newish data language with semantic models... Perhaps what semantic SQL would look like: https://www.malloydata.dev/ https://www.malloydata.dev/
- 392 2y ago
  Somehow Malloy always gets called out in these threads but not PRQL. https://prql-lang.org/ https://prql-lang.org/
revskill 2y ago
I tried before with https://yaml2sql.netlify.app/ https://yaml2sql.netlify.app/
zapkyeskrill 2y ago
If you're creating fake tables for your solution, couldn't you then also create a fake table books(title) to satisfy the first problem statement?
tie-in 2y ago
While I appreciate the functional approach outlined in the article, there are alternative and, in my opinion, more practical ways to write composable and functional SQL queries embedded in a host programming language using query builders. For example: https://news.ycombinator.com/item?id=42265668 https://news.ycombinator.com/item?id=42265668
dgan 2y ago
I was thinking about this exact problem, one year ago, and even started to sketch out what a better "meta-sql" would look like. However, i did lose myself in the weeds. Notably : - the metasql has to be embedded in the host language, the way normal sql is. This means, it also has to handle in/out bound parameters from the host. I guess this could be handled by a codegen step, where the functions of the host language are generated based on metasql. The alternative is to provide a runtime for each host language.. So this one is out - i wanted a way to compose "columns sets". This is because you might want for example, to indicate that the query returns "all the columns in this table, minus thia and that". Or, for example "the union of this column set union in-column set". This feature looks like adding another meta-language on top of the metasql already lol I stopped at that point because i could find a graceful/looknice solution, let alone implement it.
- gregw2 2y ago
  I implemented a data access permissions layer in SQL stored procs with setbased logic with allow/deny and include/exclude functionality and some 'security rules composability'. Implementing it definitely took some of the hardest thinking at that job. But it's possible.
- mike_hearn 2y ago
  Sounds a bit like the Active Record/Repository pattern? The "meta sql" in that case is a natural language DSL, e.g. @Repository interface Employees : CrudRepository<Employee, Integer> { fun findBySalaryGreaterThanForUpdate(salary: Int): List<Employee> data class NameAndSalary(val name: String, val salary: Int) fun findByDepartment(department: String): List<NameAndSalary> } The compiler reads the method name and writes the equivalent SQL query at compile time. The custom data class acts as a projection. This sort of thing can be found in Micronaut Data, or Spring or similar.
yujzgzc 2y ago
I work on a project that does tons of SQL based data transformations (thousands). A lot of it isn't backed by unit tests. Some of our people with more of a C++ background have got the idea that this is crazy, every bit of business logic should be encapsulated into a function just like in every other civilized language, so that it could be unit tested. They built a framework like this for SQL and implemented quite a few jobs with it. The problem is most of the SQL-first devs hate it and would rather write untested code, or rely on alternative test automation techniques like bringing up a local DB instance with fake data, than have to refit their business logic into pure functions. The truth is that it's just not a way of working that comes naturally to people who write a lot of SQL for a living.
- svilen_dobrev 2y ago
  long time ago i had similar need.. did not want to directly churn SQL, for hundreds of small filters. So i did an expression parser and translator, and wrote all things in simple python functions, then translated those into SQL automatically. Both funcs and the translator were heavily tested. Apparently, that parser also grew few other "renderers" - like evaluator, documenter and what not. https://github.com/svilendobrev/svd_util/blob/master/expr.py https://github.com/svilendobrev/svd_util/blob/master/expr.py
- TachyonicBytes 2y ago
  I assume that framework is not open-source or somewhere I can look at it?
  - mousetree 2y ago
    You could take a look at the open-source dbt framework. They have a good implementation of unit testing for sql https://docs.getdbt.com/docs/build/unit-tests https://docs.getdbt.com/docs/build/unit-tests
  - yujzgzc 2y ago
    I guarantee you wouldn't want to use it
- 2y ago
yujzgzc 2y ago
There are a lot more composability problems with SQL. As an example, can you write a function that you can invoke on a column in a select statement and will compute the cumulative distribution function of that column? The expression to do this is enormous "SUM(column) OVER (ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(column) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)" and there is no choice but to inline it. I think it would be reasonable to expect users to be able to describe this and then write simply "CDF(column)". The only systems that do this I believe are text processing macros on top of SQL...
- reshlo 2y ago
  `CUME_DIST() OVER (ORDER BY column)` https://modern-sql.com/caniuse/cume_dist https://modern-sql.com/caniuse/cume_dist
  - yujzgzc 2y ago
    My point is that in most other languages, this kind of functionality can easily be factored into libraries available to the community. SQL doesn't have the composability so every single of these features requires to be added directly into the language. Libraries and package tooling is practically non-existent in SQL land.
    - earnestinger 2y ago
      Actually some do, but… - non standard sql - not concise - has limitations - installing a function is a db migration I had dreams. A lot of them. I still do some times. “Normal language” functions accept/return: primitives, objects(, functions, types). Sql functions could accept/return: scalars/primitives, a row, set of rows, a table (maybe set of tables or related rows), for aggregating functions need to group by some columns, sliding window functions need to keep track of the window. Let’s add some kind of interfaces/traits and generics. If we are extending current sql, then I’m certain there will be corners cases for various clauses sql clauses (select, from, on, where) And all of that on top of basic sqls (sel/ins, checks, access controls, query execution planning) Quite quickly c++ starts to look like an exercise in language minimalism.
RyanHamilton 2y ago
He got so close to the right answer but went the wrong direction. "Imagine a programming language without functions.". Imagine SQL was instead based on an actual programming language, with variables and functions. that would solve all the problems you mention. Kdb+ and Dialog already knew this 20+ years ago. I wish someone else will recreate this in an open source library. Now with Arrow format in memory the best hope may be that all languages will be able to act on shared memory data frames bringing tables to every language within the database.
- zetalyrae 2y ago
  > Imagine SQL was instead based on an actual programming language, with variables and functions. This is what the entire article is about. That paragraph is meant to illustrate the problem of SQL through an analogy.
  - RyanHamilton 2y ago
    The article is about functors. Some new concept that gives you a tiny subset of the functionality that a full language provides. Most languages have loops, control structures, the ability to store any data as a variable.
maxulite 2y ago
Imagine a programming language without functions...This would be tiresome. But that’s what SQL is SQL limitations encourage creative solutions. I once implemented a simple "functional" framework as a virtual module in SQLite. Some background. SQLite's virtual tables were always "functional". To make a virtual table function you basically require that particular constraint from where clause is present otherwise you will report a failure. Having this, you may create for example a table of all primes but with two columns, the prime value (obviously) and the count, but the column count will be mandatory (so the query for showing first 10 primes will be 'select value from primes where count=10'). Later SQLite added table-valued syntax and hidden column which allows the use of the syntax 'Select value from primes(10)'. But finally what made me implement a functional framework were eponymous virtual tables. Usually any virtual table should be present in schema, but eponymous tables don't require this and exist just by virtue of the corresponding module being registered. So the framework uses all this machinery and a simple table is used for storing "functions". The table has two columns, query name and query body. The body may contain numbered SQLite parameters (?1, ?2, ?3). So this single row will automatically create a virtual table with the name from the query name column while all numbered parameters in the body will be converted into "input" parameters of this table (No need to manually parse, SQLite will take care of this thanks to its API). Sure, the bodies can use other queries from this library, SQLite handles this nicely.
cess11 2y ago
I think the Ecto query DSL is much nicer. But I'm also fine with writing SQL by hand and don't really feel an attraction to these kinds of more complicated things. I find automating pulling out EXPLAIN and schema into doc blocks is more useful.
branko_d 2y ago
From the article: > What you hope happens is that Postgres will recursively inline every view, merge them all together into a gigaquery, and shuffle the predicates up and down to maximally optimize the query. That was the conceit. And of course that never happens. The author seems to be describing a Progress-specific problem. Oracle, SQL Server (and probably other "enterprise" databases as well) are pretty good at inlining views and then optimizing the whole resulting gigaquery as if the views were not there in the first place. > The central problem is that views have to be written for the general case, and then you filter on the view’s output. Views can’t take parameters. SQL Server supports inline table-valued functions, which are exactly that - parametrized and inlinable views. Unfortunately, they are not as composable as they could be. For example, a function returning table and a function accepting the same type of a table as input cannot be directly composed - you'd first need to store the first function's result in a temporary table or table variable. But despite the limitations, functions are extremely useful for constructing complex queries which reuse logic.
- bob1029 2y ago
  Oracle, MSSQL, DB2, et. al., are essentially like magic when it comes to making hopeless piles of SQL run quickly. This is because these engines have all ostensibly targeted the business people over the decades more than the tech people. Features like dynamically optimizing queries as they return results [0,1,2] isn't something you chase if your audience is willing to rewrite the universe at the first sign of incongruity. [0] https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing https://learn.microsoft.com/en-us/sql/relational-databases/p... [1] https://www.ibm.com/docs/en/i/7.5?topic=overview-adaptive-query-processing https://www.ibm.com/docs/en/i/7.5?topic=overview-adaptive-qu... [2] https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=344 https://apex.oracle.com/pls/apex/features/r/dbfeatures/featu...
- SigmundA 2y ago
  I mentioned this down thread, MSSQL and Oracle cache query plans allow their optimizers to take their time which I imagine helps with doing more sophisticated optimization. PG always re optimizes on every call unless you manually prepare the statement and that only last for the session. So PG's optimizer is designed to be very fast not very thorough.
noisy_boy 2y ago
Stop trying to fix SQL. Stop trying to make it do things that belong with a full fledged unit testable language. It is mainly a way to view, filter and aggregate data - nothing more. Having dealt with untestable thousand lines stored procedures (each of which are copy of a similar stored procedure with subtle variations), just don't put yourself in a world of pain by doing overcomplicated things at the database end. And when you want to query data, use a view so that a) you don't need to deal to idiosyncrasies of your ORM deciding to do weird/suboptimal joins b) if tomorrow you have to make database side changes or optimize your view, all you need to care about is to keep the select clause the same c) you can just query/profile the view and be assured that what you see is what your application will see too (in general). But don't keep nesting them like mad and expect the database to do magic.
- asqueella 2y ago
  There's a need to do complex data processing (not sure if it falls under your "view, filter and aggregate data - nothing more"), and it's an open question whether "full fledged" languages (presumably with an API, like pandas, ibis or spark), improved SQL, or another language focused on data transformation will be a better fit for this task.
- paulddraper 2y ago
  > It is mainly a way to view, filter and aggregate data It looks like it’s viewing filtering and aggregating data to me.
  - noisy_boy 2y ago
    It is basically describing a wishlist where you can pass arbitrary sql as source for your "main" sql via valid sql itself without resolving to dynamic sql generation - presumably with support for type checks etc. Basically a level above a custom sql generator. Notwithstanding the fact that no database supports the described approach, this combined with flexibility of sql itself could give rise to many convoluted setups. Someone builds arbitrary nesting on top of this or does complex conditional constructs - suddenly you have doubts about what is the actual sql that will be produced/executed. It is not like you can write a unit test and attach a debugger to the sql to see whats going on within the nested levels. Redirecting to temporary tables and print based debugging is only available for procedural sql which is not the target here. What I meant by "mainly a view to view, filter and aggregate data" is that we should keep those core actions as simple as possible. Move the complexity to the application logic written in whatever language - then atleast you'll be able to test/troubleshoot it much better and gain from the existing mature approaches/tooling to do such things. Support for such things is grossly underdeveloped for sql and it gets hairy real fast when it comes to building layers with sql.
alex_duf 2y ago
The one thing I do not like about this approach is that the functor is being created as a part of the schema. In other words, the schema carries the logic as well as the data. And from experience with mixing data and logic, is that it's a bad idea, especially if your team size > 1. You always end up having maintenance difficulties due to the fact that your app has a part of the logic, and now your DB too. So where's the bug? in the app? in the DB? usually it's a mixture of the two. Is it possible do it properly? yes probably, but I wouldn't recommend it. So counter proposition: We use functors, but they are just another way of executing SQL requests. And at this point there's really no difference to creating the table without the foreign key checks.
efromvt 2y ago
In trying to tackle this in the past, I've attempted to break down the things that SQL does well (and it does a lot well, I really like SQL) and how they could be individually more composable/reusable. Semantic Information: - Express relationships between tabular assets [PKs, FKs, Joins] - Express relationships within tabular assets [row relations] Transformational Expressiveness: - The general syntax - functions, aggregations, subqueries, grouping, etc - I think this is more of what functors get at, though the table valued cases blur it to some degree SQL has some very nice tools for managing semantic layer abstractions, such as views and stored procedures, though they can suffer from change-management, opaqueness, and performance issues. I agree with some of the sentiments expressed in comments that enforced PKs are downstream of the semantic/business logic - they're more of tests than where you want your true business definitions to live. Ideally they'd be created automatically from your semantic definitions. Functions are reasonable transformational expressiveness tools, but suffer from rarely being defined inline so it's hard to 'get' a statement that heavily uses them. Temporary functions are a really nice middle ground here. In an ideal world, I think you can separate out composition across both dimensions - a semantic model, which I think requires decoupling from physical tables to some degree - views are an okay implementation, but I think DBT/others have shown the desire for this to exist outside the database layer to promote a better development lifecycle. - a functional reuse model, which is also ultimately more about the development you can associate with it and the iteration and experience. I think having this not be coupled to the database itself but defined at the caller/sql layer is important to make it maintainable - some degree of imports/reuse would be helpful here. Obligatory plug: I've written about experimenting with solving both: https://trilogydata.dev/blog/composability_reuse.html https://trilogydata.dev/blog/composability_reuse.html
mandarlimaye 2y ago
Putting lipstick on a pig
sixdimensional 2y ago
I found the argument that it’s impossible to test SQL (and some other hard statements in this article) a bit too absolute. Testing SQL is not impossible – SQL unit testing is a thing – identify a unit of SQL, such as a query, function, or stored procedure - mock the necessary inputs and any state that is modified and check the result of the unit just as you would in any unit testing framework (e.g. arrange, act, assert). Perhaps it’s a little difficult, or different to wrap our heads around (and many do not take the time to see how this can work) but it’s not impossible. Also, as others have already pointed out, depending on the implementation of the SQL engine itself, different engines can have very different behaviors, some of which make composable SQL possible already today (e.g. table valued functions, CTEs that are inlined, stored procedures, etc). And for what it’s worth, if you look at a lot of the data processing engines we use today (such as Apache Spark), they really solve the problem of being able to compose and modularize code and SQL together, through data frames or things like language integrated query, integrations of languages that compile down to code that can run and be orchestrated on common execution engines, and so forth.
deleted 2y ago
[deleted]
alexandrehtrb 2y ago
SQL Server has table-valued parameters, they are close to the functors described in the article, with the difference that TVPs define the whole table structure, instead of a subset of columns, as proposed. I liked the functors idea, to be honest, I've always wondered why this doesn't exist in SQL. https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/t...
naiquevin 2y ago
I've implemented a CLI tool[1] that tries to address similar problems with SQL as mentioned in the article: testing, business logic and re-usability. The approach is different though - you write SQL and pgTAP tests using jinja templates and the tool generates 1. SQL queries that can be used by the application code using libs such as yesql, hugsql etc. and, 2. pgTAP tests, ensuring that they test the exact same SQL queries that are actually run by your application I haven't used it in a /real/ production setting though, so I'd still call it an experiment. But I have been using it in a personal project with a handful of users (friends and family). Most of the business logic is implemented in SQL so the queries are fairly intricate (CTEs, sub queries, multiple joins). Occasionally, I push some features/improvements/tweaks and I am able to do so with high confidence, which I believe is mainly thanks to pgTAP tests. I am quite happy with it so far although that's just my experience. Haven't tried it on a large project with many contributors. [1]: https://github.com/naiquevin/tapestry https://github.com/naiquevin/tapestry
george_ciobanu 2y ago
I built a 100% composable SQL at www.human.software
jojo_ 2y ago
You can do this with SQLite: https://ricomariani.github.io/CG-SQL-author https://ricomariani.github.io/CG-SQL-author This is a fork of a facebook project.
monkfish328 2y ago
Ain't noobboddy goott tiimmeeee

271 points by earnestinger 2y ago

Attempt at sql composability.

prior art: https://research.google/pubs/sql-has-problems-we-can-fix-them-pipe-syntax-in-sql/ https://research.google/pubs/sql-has-problems-we-can-fix-the... https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/ - talks about compositionality https://www.edgedb.com/blog/we-can-do-better-than-sql https://www.edgedb.com/blog/we-can-do-better-than-sql (2019) (List taken from https://lobste.rs/s/vmyk7d/composable_sql#c_layb8n https://lobste.rs/s/vmyk7d/composable_sql#c_layb8n)

default-kramer 2y ago

Very nice. Adding this one to my list of "things other people wrote that nicely elucidate a shortcoming of SQL" > Why would this be useful? Because SQL tables are global variables. By vanishing global variables, we automatically make every query fully testable. And even if you don't care about testability, certainly you can appreciate that global variables are bad for composability.

summerlight 2y ago

Isn't it table-valued function? IIRC, the SQL standard still doesn't have it but it's almost universally supported extension across vendors.

zetalyrae 2y ago
At least in Postgres, table-valued functions can't take tables as arguments, only scalars. That's the main difference: functors can not just return tables, but take tables satisfying some interface as arguments. https://www.postgresql.org/docs/7.3/xfunc-tablefunctions.html https://www.postgresql.org/docs/7.3/xfunc-tablefunctions.htm... I thought I had written a footnote or appendix about this but I guess I forgot.
- dagss 2y ago
  MSSQL can take tables as arguments if they are temporary tables declared to be of a certain type. But that restriction limits their use a lot.

throwaway201606 2y ago

I understand the primary premise about the difficulty with testing SQL and fully agree with it. I do have a question though - while I understand how functors can help make the problem easier to tackle, I am not sure I fully understand how functors are different from a similar existing tool - stored procedures. Some DB flavors: - can take tables as arguments to stored procedures - can return tables - also offer the additional benefit of being able to run almost all flavors of SQL commands ( DDL, DML, DQL, DCL, TCL) in those stored procedures Netezza stored procedures, for example, can do what you describe here: https://www.ibm.com/docs/en/netezza?topic=nsg-return-result-set https://www.ibm.com/docs/en/netezza?topic=nsg-return-result-... As can SQL Server & Oracle (which both return cursors, which are just ordered tables): https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/s... https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#CIHDDIFG https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg... Am I missing something fundamental here? How are functors different from stored procedures? To me, they seem to be just a sub-class / sub-type of stored procedures.

andyferris 2y ago
They seem somewhat like stored procedures, but not stored? As in a query can contain a functor in it and then use it immediately. I didn't see those `create functor` statements as anything other than ephemeral - or am I wrong? EDIT: also stored procs that use imperative logic and cursors can be quite a bit slower than queries that achieve the same logic - the capability here is purposefully a subset of that and is just to help build standard SQL queries that can go through the standard query planner.
- throwaway201606 2y ago
  I think they have to be long lived else they cannot make sense for performant testing. ie they are created as DB objects, using DDL, in the same way tables, views, functions etc are made. They can certainly be created at test run time but that would slow things down a lot - you would essentially be creating a ton of objects every time you run the test which means having a setup to test if they exist or not, take them down if they do or fix them if they don't match spec ( e.g. column and data type changes etc etc ) The more I think about this, the more complicated I realize it would be to manage this dynamically: You essentially have to build a test harness enviroment that figures out your testing elements dynamically from your data environment (with some kind of parameterization engine and data set to tell it what to look for so as to "make functors and run them" (e.g. all PKs of FKs or all columns starting with a certain prefix or all columns of a certain data type etc etc), gets the most up to date definitions of those elements from system tables and uses that data to create or update or drop functor objects ... wow, ok, this is getting complicated, I am going to stop now before I see the void.

feoren 2y ago

SQL cannot be "fixed". It's too broken, too ubiquitous, too customized, too optimized. Will these functors integrate with the thousands of optimizations modern database systems rely on? This post has the format: "SQL is extremely terribly awful at writing business logic. But if people implemented this one half-baked, unrealistic idea, then it would suck a little bit less!" The first part is spot-on: SQL is a horrible language for writing business logic. So: stop. Stop doing that. Stop writing business logic in SQL! Imagine you joined a new team, and they said: our program uses a bunch of XML files as its data format, and that's why most of our business logic is implemented in XPath. Just sit there and ponder that situation for a little bit. This is what you all sound like to me. Stop it! > Testing the simplest query requires building up a massive object graph, just to test an infinitesimal slice of it. Yes, correct, because SQL is awful at representing business logic, and foreign key constraints are business logic! The fact that an employee has one supervisor who is also an employee (FK Employee.SupervisorID -> Employee.ID) is a business constraint. (And one that can easily change, by the way.) All these database engineers who can't pull themselves away from their stored procedures will argue with me to their graves about foreign key constraints, but they're generally terrible. They're a business constraint that just happens to be supported by the database engine, but they're still a business constraint. Stop putting your business logic in your persistence layer. We've been getting better at building programming languages that actually support software engineering every year. Use one of those. SQL is an output of one of your program layers. Just like XPath would be an output of your program. Compose SQL in an actual real programming language that isn't horrible instead of doing any of this.

liontwist 2y ago
What is business logic? What specifically is SQL bad at? I’m not sold by your foreign key example. Constraints are great. I guess you can do that in python? Good like getting ACID right As soon as your data stops resembling a tree and starts looking like a graph than all your “business logic” looks a lot like operations on relations and you are back to where you started.
hobs 2y ago
I don't know why you are getting downvoted, and I generally don't like a lot of business logic in the persistence layer, but representing the things you absolutely dont want another part of your app doing via FKs is...pretty normal. If you are arguing it spreads out your business logic into multiple places, that's fair, but so does... a lot of things. Databases with decent fks and constraints have a tendency to outlive the apps that build atop them, and communicating these constraints in long lived fashions can be ... good actually.

brianhama 2y ago

MS-SQL would seem to already support everything you’ve suggested.

r1b 2y ago

Nodding along furiously. One area where this comes up is when you want to export some piece of logic that effectively “returns a table”. For example, I work with a source of data that requires a particularly hairy JOIN strategy. The logic is the same for any table with the same “shape”, but no one can reuse it without copy / paste.

virtualwhys 2y ago
See Esqueleto in Haskell, Slick and Quill in Scala, probably LINQ or some variant on .NET in C#/F#. All support building up arbitrarily complex queries based on statically typed query fragments. String fragment "composition" is like banging rocks together.

froderick 2y ago

I already have functors, effectively, by writing sql queries in clojure with honeysql. The clojure data structures make behavior composition from reusable sql snippets easy, and the testing of sql snippets is just based on literals as the article describes. Works really well.

nateroling 2y ago
I’ve done the same with SQLAlchemy in Python and SQLKata in C#. Sadly the whole idea of composable query builders seems to have fallen out of fashion.
- jaza 2y ago
  If composable query builders have fallen out of fashion, that's news to me. I've used SQLAlchemy in Python fairly heavily over the years, it's gotta be one of the best out there, I plan to continue using it for many more years. And I've recently gotten my feet wet with Ecto in Elixir, I'm quite impressed with it too.

gremgoth 2y ago

SQL select-from-where queries can indeed be formalized as "pro-functors": https://arxiv.org/pdf/1602.03501 https://arxiv.org/pdf/1602.03501

geysersam 2y ago

Similar functionality already exists in some SQL implementations, notably DuckDB: create table macro author_books() as ( select a.name as author, b.title, from a inner join b on b.author_id = a.author_id ); is the same as the example in the article and it is called like: with a as (...), b as (...) from author_books(); https://duckdb.org/docs/sql/statements/create_macro.html#table-macros https://duckdb.org/docs/sql/statements/create_macro.html#tab...

_dark_matter_ 2y ago
I really dislike leading off with "create table". My brain is unable to read it except as DDL.
krembo 2y ago
Isn't this an abstraction of functions?
- tankenmate 2y ago
  It is an abstraction, it may be arguable if it is an abstraction of functions. I once built a web app where we had a library of CTEs that we used to manage all the layers of indirection in the data, it was performant and made it much easier for users to be able to search and manipulate the data; especially for ad hoc data analysis.
n_plus_1_acc 2y ago
How does it differ from a view?
- geysersam 2y ago
  It can take arguments. I didn't use any though so it's not a good example
typ 2y ago
DuckDB macro doesn't support instantiating a table macro using the `query_table` function. So, it's not possible to expand a table macro like `foo(bar(baz()))`, making its use limited.

iforgot22 2y ago

I prefer to keep the SQL itself as simple as possible. I've gotten by OK by composing SQL in my outer code as needed. Despite relying crazy heavily on SQL, that's rarely needed. And the outer code has tests. It'd just be nice if Postgres could loosen the syntax in a few annoying places, like allowing a trailing comma in "WITH q1 AS (...), q2 AS (...)" Or if something could be done to avoid needing the "WHERE true AND" trick.

agentultra 2y ago

Ah but “functor,” is a name from category theory and algebraic topology. I had read along thinking that this would come up in the conclusion! Instead it seems it’s not even mentioned in the sources for the name. There’s potentially something like this in the “esqueleto” package for Haskell. But it has some limitations. It composes on syntactic fragments. Although speaking of functors, the category theory ones, maybe categorical databases would fit the bill: https://categoricaldata.net/ https://categoricaldata.net/

beart 2y ago

On the testing aspect - The TSQLT assembly (https://tsqlt.org/ https://tsqlt.org/) is an attempt at unit testing within MSSQL. It works okayish for what it is. I've at least found it to be nice when testing inputs for SQL functions and procedures, as well as some business rules within tables and views. A few IDEs provide integrations for TSQLT: Management Studio with the redgate extension, Datagrip, maybe others.

willvarfar 2y ago

The article talks about how CTEs are efficiency minefields and how Postgres doesn't do a good job of pushing down predicates, for example. TBH, the solution is to improve the Postgres engine in this regard? For example, in Bigquery I often use CTEs and see predictable speedups that are explained by predicate pushdown when slapping a WHERE clause on the user of a CTE etc. Bigquery has plenty of performance gotchas but I don't think CTEs are one of them; they seem to be transparent to the optimiser. Something else that Bigquery supports is user-defined 'table' functions. These are like the 'functors' the article creates. However, in practice they aren't very widely used, and I think this hints that they aren't as useful as the article is hoping for. The thing I _really_ want is scoping. If only functions could be described like CTE WITH blocks, e.g. WITH visits AS ( SELECT ... FROM web_logs WHERE ... ), foo(bar INT, baz STRING) AS ( ... function body here ) SELECT ..., foo(a, b), ... FROM visits WHERE ... Apart from the tidiness of this, this allows inlining of functions into the SQL statement body rather than as separate statements. Often various tooling e.g. DBT makes it really hard to with separate function definitions etc, and being able to inline logic would make things possible. Bigquery also supports user-defined aggregate functions. Although there is rarely the need to stray from the standard builtin aggregate functions, when you need them they are brilliant. (Anyone working _on_ Bigquery reading? If so, please ping me, I have loads of feedback on what works well and where the user pain and toil is ;) )

sa46 2y ago
As of Postgres 12 (Oct. 2019) CTEs are no longer materialized by default and are eligible for predicate push down. The planner may still struggle but it’s not true that a CTE is an optimization fence. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
vprcic 2y ago
Functions in WITH clause really are a blessing. Oracle DB supported these for a decade now and that was a gamechanger for me. https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#functions https://oracle-base.com/articles/12c/with-clause-enhancement...

brikym 2y ago

This what Kusto Query Language looks like: StormEvents | summarize TotalStormsInState = count(), StormsWithCropDamage = countif(DamageCrops > 0) by State | extend PercentWithCropDamage = round((todouble(StormsWithCropDamage) / TotalStormsInState \* 100), 2) | sort by StormsWithCropDamage I find it to be much more readable. With SQL CTEs is as close as you can get.

__thecoldwine__ 2y ago
Kusto is fine, but joins are quite horrendous there. To be frank, I don't really get complains about "SQL is not readable". Most of the time SQL is the most compact, clear and to the purpose way to fetch the data (given the data had been modeled in a manner suitable for queriying). Luckily, most of the tables for KQL in Azure are flat and not complex and that is partially what makes kusto so compelling.

mrtimo 2y ago

Malloy is a newish data language with semantic models... Perhaps what semantic SQL would look like: https://www.malloydata.dev/ https://www.malloydata.dev/

392 2y ago
Somehow Malloy always gets called out in these threads but not PRQL. https://prql-lang.org/ https://prql-lang.org/

revskill 2y ago

I tried before with https://yaml2sql.netlify.app/ https://yaml2sql.netlify.app/

zapkyeskrill 2y ago

If you're creating fake tables for your solution, couldn't you then also create a fake table books(title) to satisfy the first problem statement?

tie-in 2y ago

While I appreciate the functional approach outlined in the article, there are alternative and, in my opinion, more practical ways to write composable and functional SQL queries embedded in a host programming language using query builders. For example: https://news.ycombinator.com/item?id=42265668 https://news.ycombinator.com/item?id=42265668

dgan 2y ago

I was thinking about this exact problem, one year ago, and even started to sketch out what a better "meta-sql" would look like. However, i did lose myself in the weeds. Notably : - the metasql has to be embedded in the host language, the way normal sql is. This means, it also has to handle in/out bound parameters from the host. I guess this could be handled by a codegen step, where the functions of the host language are generated based on metasql. The alternative is to provide a runtime for each host language.. So this one is out - i wanted a way to compose "columns sets". This is because you might want for example, to indicate that the query returns "all the columns in this table, minus thia and that". Or, for example "the union of this column set union in-column set". This feature looks like adding another meta-language on top of the metasql already lol I stopped at that point because i could find a graceful/looknice solution, let alone implement it.

gregw2 2y ago
I implemented a data access permissions layer in SQL stored procs with setbased logic with allow/deny and include/exclude functionality and some 'security rules composability'. Implementing it definitely took some of the hardest thinking at that job. But it's possible.
mike_hearn 2y ago
Sounds a bit like the Active Record/Repository pattern? The "meta sql" in that case is a natural language DSL, e.g. @Repository interface Employees : CrudRepository<Employee, Integer> { fun findBySalaryGreaterThanForUpdate(salary: Int): List<Employee> data class NameAndSalary(val name: String, val salary: Int) fun findByDepartment(department: String): List<NameAndSalary> } The compiler reads the method name and writes the equivalent SQL query at compile time. The custom data class acts as a projection. This sort of thing can be found in Micronaut Data, or Spring or similar.

yujzgzc 2y ago

I work on a project that does tons of SQL based data transformations (thousands). A lot of it isn't backed by unit tests. Some of our people with more of a C++ background have got the idea that this is crazy, every bit of business logic should be encapsulated into a function just like in every other civilized language, so that it could be unit tested. They built a framework like this for SQL and implemented quite a few jobs with it. The problem is most of the SQL-first devs hate it and would rather write untested code, or rely on alternative test automation techniques like bringing up a local DB instance with fake data, than have to refit their business logic into pure functions. The truth is that it's just not a way of working that comes naturally to people who write a lot of SQL for a living.

svilen_dobrev 2y ago
long time ago i had similar need.. did not want to directly churn SQL, for hundreds of small filters. So i did an expression parser and translator, and wrote all things in simple python functions, then translated those into SQL automatically. Both funcs and the translator were heavily tested. Apparently, that parser also grew few other "renderers" - like evaluator, documenter and what not. https://github.com/svilendobrev/svd_util/blob/master/expr.py https://github.com/svilendobrev/svd_util/blob/master/expr.py
TachyonicBytes 2y ago
I assume that framework is not open-source or somewhere I can look at it?
- mousetree 2y ago
  You could take a look at the open-source dbt framework. They have a good implementation of unit testing for sql https://docs.getdbt.com/docs/build/unit-tests https://docs.getdbt.com/docs/build/unit-tests
- yujzgzc 2y ago
  I guarantee you wouldn't want to use it
2y ago

yujzgzc 2y ago

There are a lot more composability problems with SQL. As an example, can you write a function that you can invoke on a column in a select statement and will compute the cumulative distribution function of that column? The expression to do this is enormous "SUM(column) OVER (ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(column) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)" and there is no choice but to inline it. I think it would be reasonable to expect users to be able to describe this and then write simply "CDF(column)". The only systems that do this I believe are text processing macros on top of SQL...

reshlo 2y ago
`CUME_DIST() OVER (ORDER BY column)` https://modern-sql.com/caniuse/cume_dist https://modern-sql.com/caniuse/cume_dist
- yujzgzc 2y ago
  My point is that in most other languages, this kind of functionality can easily be factored into libraries available to the community. SQL doesn't have the composability so every single of these features requires to be added directly into the language. Libraries and package tooling is practically non-existent in SQL land.
  - earnestinger 2y ago
    Actually some do, but… - non standard sql - not concise - has limitations - installing a function is a db migration I had dreams. A lot of them. I still do some times. “Normal language” functions accept/return: primitives, objects(, functions, types). Sql functions could accept/return: scalars/primitives, a row, set of rows, a table (maybe set of tables or related rows), for aggregating functions need to group by some columns, sliding window functions need to keep track of the window. Let’s add some kind of interfaces/traits and generics. If we are extending current sql, then I’m certain there will be corners cases for various clauses sql clauses (select, from, on, where) And all of that on top of basic sqls (sel/ins, checks, access controls, query execution planning) Quite quickly c++ starts to look like an exercise in language minimalism.

RyanHamilton 2y ago

He got so close to the right answer but went the wrong direction. "Imagine a programming language without functions.". Imagine SQL was instead based on an actual programming language, with variables and functions. that would solve all the problems you mention. Kdb+ and Dialog already knew this 20+ years ago. I wish someone else will recreate this in an open source library. Now with Arrow format in memory the best hope may be that all languages will be able to act on shared memory data frames bringing tables to every language within the database.

zetalyrae 2y ago
> Imagine SQL was instead based on an actual programming language, with variables and functions. This is what the entire article is about. That paragraph is meant to illustrate the problem of SQL through an analogy.
- RyanHamilton 2y ago
  The article is about functors. Some new concept that gives you a tiny subset of the functionality that a full language provides. Most languages have loops, control structures, the ability to store any data as a variable.

maxulite 2y ago

Imagine a programming language without functions...This would be tiresome. But that’s what SQL is SQL limitations encourage creative solutions. I once implemented a simple "functional" framework as a virtual module in SQLite. Some background. SQLite's virtual tables were always "functional". To make a virtual table function you basically require that particular constraint from where clause is present otherwise you will report a failure. Having this, you may create for example a table of all primes but with two columns, the prime value (obviously) and the count, but the column count will be mandatory (so the query for showing first 10 primes will be 'select value from primes where count=10'). Later SQLite added table-valued syntax and hidden column which allows the use of the syntax 'Select value from primes(10)'. But finally what made me implement a functional framework were eponymous virtual tables. Usually any virtual table should be present in schema, but eponymous tables don't require this and exist just by virtue of the corresponding module being registered. So the framework uses all this machinery and a simple table is used for storing "functions". The table has two columns, query name and query body. The body may contain numbered SQLite parameters (?1, ?2, ?3). So this single row will automatically create a virtual table with the name from the query name column while all numbered parameters in the body will be converted into "input" parameters of this table (No need to manually parse, SQLite will take care of this thanks to its API). Sure, the bodies can use other queries from this library, SQLite handles this nicely.

cess11 2y ago

I think the Ecto query DSL is much nicer. But I'm also fine with writing SQL by hand and don't really feel an attraction to these kinds of more complicated things. I find automating pulling out EXPLAIN and schema into doc blocks is more useful.

branko_d 2y ago

From the article: > What you hope happens is that Postgres will recursively inline every view, merge them all together into a gigaquery, and shuffle the predicates up and down to maximally optimize the query. That was the conceit. And of course that never happens. The author seems to be describing a Progress-specific problem. Oracle, SQL Server (and probably other "enterprise" databases as well) are pretty good at inlining views and then optimizing the whole resulting gigaquery as if the views were not there in the first place. > The central problem is that views have to be written for the general case, and then you filter on the view’s output. Views can’t take parameters. SQL Server supports inline table-valued functions, which are exactly that - parametrized and inlinable views. Unfortunately, they are not as composable as they could be. For example, a function returning table and a function accepting the same type of a table as input cannot be directly composed - you'd first need to store the first function's result in a temporary table or table variable. But despite the limitations, functions are extremely useful for constructing complex queries which reuse logic.

bob1029 2y ago
Oracle, MSSQL, DB2, et. al., are essentially like magic when it comes to making hopeless piles of SQL run quickly. This is because these engines have all ostensibly targeted the business people over the decades more than the tech people. Features like dynamically optimizing queries as they return results [0,1,2] isn't something you chase if your audience is willing to rewrite the universe at the first sign of incongruity. [0] https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing https://learn.microsoft.com/en-us/sql/relational-databases/p... [1] https://www.ibm.com/docs/en/i/7.5?topic=overview-adaptive-query-processing https://www.ibm.com/docs/en/i/7.5?topic=overview-adaptive-qu... [2] https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=344 https://apex.oracle.com/pls/apex/features/r/dbfeatures/featu...
SigmundA 2y ago
I mentioned this down thread, MSSQL and Oracle cache query plans allow their optimizers to take their time which I imagine helps with doing more sophisticated optimization. PG always re optimizes on every call unless you manually prepare the statement and that only last for the session. So PG's optimizer is designed to be very fast not very thorough.

noisy_boy 2y ago

Stop trying to fix SQL. Stop trying to make it do things that belong with a full fledged unit testable language. It is mainly a way to view, filter and aggregate data - nothing more. Having dealt with untestable thousand lines stored procedures (each of which are copy of a similar stored procedure with subtle variations), just don't put yourself in a world of pain by doing overcomplicated things at the database end. And when you want to query data, use a view so that a) you don't need to deal to idiosyncrasies of your ORM deciding to do weird/suboptimal joins b) if tomorrow you have to make database side changes or optimize your view, all you need to care about is to keep the select clause the same c) you can just query/profile the view and be assured that what you see is what your application will see too (in general). But don't keep nesting them like mad and expect the database to do magic.

asqueella 2y ago
There's a need to do complex data processing (not sure if it falls under your "view, filter and aggregate data - nothing more"), and it's an open question whether "full fledged" languages (presumably with an API, like pandas, ibis or spark), improved SQL, or another language focused on data transformation will be a better fit for this task.
paulddraper 2y ago
> It is mainly a way to view, filter and aggregate data It looks like it’s viewing filtering and aggregating data to me.
- noisy_boy 2y ago
  It is basically describing a wishlist where you can pass arbitrary sql as source for your "main" sql via valid sql itself without resolving to dynamic sql generation - presumably with support for type checks etc. Basically a level above a custom sql generator. Notwithstanding the fact that no database supports the described approach, this combined with flexibility of sql itself could give rise to many convoluted setups. Someone builds arbitrary nesting on top of this or does complex conditional constructs - suddenly you have doubts about what is the actual sql that will be produced/executed. It is not like you can write a unit test and attach a debugger to the sql to see whats going on within the nested levels. Redirecting to temporary tables and print based debugging is only available for procedural sql which is not the target here. What I meant by "mainly a view to view, filter and aggregate data" is that we should keep those core actions as simple as possible. Move the complexity to the application logic written in whatever language - then atleast you'll be able to test/troubleshoot it much better and gain from the existing mature approaches/tooling to do such things. Support for such things is grossly underdeveloped for sql and it gets hairy real fast when it comes to building layers with sql.

alex_duf 2y ago

The one thing I do not like about this approach is that the functor is being created as a part of the schema. In other words, the schema carries the logic as well as the data. And from experience with mixing data and logic, is that it's a bad idea, especially if your team size > 1. You always end up having maintenance difficulties due to the fact that your app has a part of the logic, and now your DB too. So where's the bug? in the app? in the DB? usually it's a mixture of the two. Is it possible do it properly? yes probably, but I wouldn't recommend it. So counter proposition: We use functors, but they are just another way of executing SQL requests. And at this point there's really no difference to creating the table without the foreign key checks.

efromvt 2y ago

In trying to tackle this in the past, I've attempted to break down the things that SQL does well (and it does a lot well, I really like SQL) and how they could be individually more composable/reusable. Semantic Information: - Express relationships between tabular assets [PKs, FKs, Joins] - Express relationships within tabular assets [row relations] Transformational Expressiveness: - The general syntax - functions, aggregations, subqueries, grouping, etc - I think this is more of what functors get at, though the table valued cases blur it to some degree SQL has some very nice tools for managing semantic layer abstractions, such as views and stored procedures, though they can suffer from change-management, opaqueness, and performance issues. I agree with some of the sentiments expressed in comments that enforced PKs are downstream of the semantic/business logic - they're more of tests than where you want your true business definitions to live. Ideally they'd be created automatically from your semantic definitions. Functions are reasonable transformational expressiveness tools, but suffer from rarely being defined inline so it's hard to 'get' a statement that heavily uses them. Temporary functions are a really nice middle ground here. In an ideal world, I think you can separate out composition across both dimensions - a semantic model, which I think requires decoupling from physical tables to some degree - views are an okay implementation, but I think DBT/others have shown the desire for this to exist outside the database layer to promote a better development lifecycle. - a functional reuse model, which is also ultimately more about the development you can associate with it and the iteration and experience. I think having this not be coupled to the database itself but defined at the caller/sql layer is important to make it maintainable - some degree of imports/reuse would be helpful here. Obligatory plug: I've written about experimenting with solving both: https://trilogydata.dev/blog/composability_reuse.html https://trilogydata.dev/blog/composability_reuse.html

mandarlimaye 2y ago

Putting lipstick on a pig

sixdimensional 2y ago

I found the argument that it’s impossible to test SQL (and some other hard statements in this article) a bit too absolute. Testing SQL is not impossible – SQL unit testing is a thing – identify a unit of SQL, such as a query, function, or stored procedure - mock the necessary inputs and any state that is modified and check the result of the unit just as you would in any unit testing framework (e.g. arrange, act, assert). Perhaps it’s a little difficult, or different to wrap our heads around (and many do not take the time to see how this can work) but it’s not impossible. Also, as others have already pointed out, depending on the implementation of the SQL engine itself, different engines can have very different behaviors, some of which make composable SQL possible already today (e.g. table valued functions, CTEs that are inlined, stored procedures, etc). And for what it’s worth, if you look at a lot of the data processing engines we use today (such as Apache Spark), they really solve the problem of being able to compose and modularize code and SQL together, through data frames or things like language integrated query, integrations of languages that compile down to code that can run and be orchestrated on common execution engines, and so forth.

deleted 2y ago

[deleted]

alexandrehtrb 2y ago

SQL Server has table-valued parameters, they are close to the functors described in the article, with the difference that TVPs define the whole table structure, instead of a subset of columns, as proposed. I liked the functors idea, to be honest, I've always wondered why this doesn't exist in SQL. https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/t...

naiquevin 2y ago

I've implemented a CLI tool[1] that tries to address similar problems with SQL as mentioned in the article: testing, business logic and re-usability. The approach is different though - you write SQL and pgTAP tests using jinja templates and the tool generates 1. SQL queries that can be used by the application code using libs such as yesql, hugsql etc. and, 2. pgTAP tests, ensuring that they test the exact same SQL queries that are actually run by your application I haven't used it in a /real/ production setting though, so I'd still call it an experiment. But I have been using it in a personal project with a handful of users (friends and family). Most of the business logic is implemented in SQL so the queries are fairly intricate (CTEs, sub queries, multiple joins). Occasionally, I push some features/improvements/tweaks and I am able to do so with high confidence, which I believe is mainly thanks to pgTAP tests. I am quite happy with it so far although that's just my experience. Haven't tried it on a large project with many contributors. [1]: https://github.com/naiquevin/tapestry https://github.com/naiquevin/tapestry

george_ciobanu 2y ago

I built a 100% composable SQL at www.human.software

jojo_ 2y ago

You can do this with SQLite: https://ricomariani.github.io/CG-SQL-author https://ricomariani.github.io/CG-SQL-author This is a fork of a facebook project.

monkfish328 2y ago

Ain't noobboddy goott tiimmeeee