- General ugliness of server-side SQL (stored procedures and functions).
--------
The first one isn't really solved by ORM tools, AFAICT. You can't simply write the SQL query and transpile it into a nice, statically-typed method (with types derived from the actual database structure!) that you can call directly from your language.
For example, the database structure (I'm using T-SQL types here):
CREATE TABLE T (
A int PRIMARY KEY,
B bigint NOT NULL
);
And the query (T-SQL style parameter syntax):
SELECT A, B FROM T WHERE A = @a;
Would produce the following method after transpilation (C#, hopefully self-explanatory):
IEnumerable<(int A, long B)> Query(int a);
But when the database structure changes, that would automatically be reflected in the client language (after a build). For example, making B NULL-abe would produce:
IEnumerable<(int A, long? B)> Query(int a);
--------
The second gripe is not very important if you use SQL just as a client-side query language. But making the database "defend" its data (in presence of complex business logic, or security requirements, not fully expressible through declarative constraints) is still best achieved by "funnelling" all clients through an API of stored procedures/functions/views, IMHO. As a bonus, this approach also tends to lower database round-trips.
There seems to be a general lack of composability/reusability:
- E.g. one stored procedure returning a set of rows cannot just "pipe" them into another procedure or query - it must first copy the rows into a (temporary) table.
- If your dialect allows you to declare a table variable, you cannot just assign it to another, you have to INSERT.
- You can reuse the same SQL fragment multiple times in the same query (through WITH), but not in different queries without encapsulating it in a function (and good luck with performance if your dialect doesn't inline function query plans or doesn't support functions at all).
- You cannot parametrize ORDER BY, GROUP BY, IN...
And myriad of other problems:
- The syntax is stuck in the '80ties, not well suited for auto-completion, no type inference.
- Lack of simple struct/tuple types in some dialects (may lead to huge parameter lists).
- Inconsistent exception / error handling behavior (sometimes the transaction is aborted, sometimes it isn't).
- Silent data truncation in some cases.
- NULL sometimes meaning "unknown" and sometimes "empty".
- No boolean expressions (e.g. you can't write A IS NULL = B IS NULL).
- And probably many more that are currently not at the top of my head... <RANT CLOSED>
It strikes me that we can do better on all these fronts (and more), without abandoning the "good" parts of relational databases.
- Lack of interoperability with other languages.
- General ugliness of server-side SQL (stored procedures and functions).
--------
The first one isn't really solved by ORM tools, AFAICT. You can't simply write the SQL query and transpile it into a nice, statically-typed method (with types derived from the actual database structure!) that you can call directly from your language.
For example, the database structure (I'm using T-SQL types here):
And the query (T-SQL style parameter syntax): Would produce the following method after transpilation (C#, hopefully self-explanatory): But when the database structure changes, that would automatically be reflected in the client language (after a build). For example, making B NULL-abe would produce: --------The second gripe is not very important if you use SQL just as a client-side query language. But making the database "defend" its data (in presence of complex business logic, or security requirements, not fully expressible through declarative constraints) is still best achieved by "funnelling" all clients through an API of stored procedures/functions/views, IMHO. As a bonus, this approach also tends to lower database round-trips.
There seems to be a general lack of composability/reusability:
- E.g. one stored procedure returning a set of rows cannot just "pipe" them into another procedure or query - it must first copy the rows into a (temporary) table.
- If your dialect allows you to declare a table variable, you cannot just assign it to another, you have to INSERT.
- You can reuse the same SQL fragment multiple times in the same query (through WITH), but not in different queries without encapsulating it in a function (and good luck with performance if your dialect doesn't inline function query plans or doesn't support functions at all).
- You cannot parametrize ORDER BY, GROUP BY, IN...
And myriad of other problems:
- The syntax is stuck in the '80ties, not well suited for auto-completion, no type inference.
- Lack of simple struct/tuple types in some dialects (may lead to huge parameter lists).
- Inconsistent exception / error handling behavior (sometimes the transaction is aborted, sometimes it isn't).
- Silent data truncation in some cases.
- NULL sometimes meaning "unknown" and sometimes "empty".
- No boolean expressions (e.g. you can't write A IS NULL = B IS NULL).
- And probably many more that are currently not at the top of my head... <RANT CLOSED>
It strikes me that we can do better on all these fronts (and more), without abandoning the "good" parts of relational databases.