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.
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