A Short Story About SQL’s Biggest Rival

Computing is inherently tied to math. There’s no getting away from that.

The SQL data types consist of a few primitives like ints strings and chars placed in higher order data types that are tables. These types are easily isomorphic to data structures and primitive types in traditional programming languages. There is zero mismatch here, in fact the data structures in application programming languages tend to be much richer than SQL.

See orms for reference. ORMS have their own set of problems but data mismatches are not part of those problems. If the programming language enables the creation of sum types like rust or haskell than there can never be a mismatch as these languages can produce virtually any type.

>SQL queries are generally really short. Rarely more than a few lines (might be different for people doing ad-hoc analysis instead of making a db backed application). I don't need modularity for a program that is only a few lines long.

For complex applications this is not true. In general the classic model for web development is to place as much logic as possible into the SQL query and as little logic as possible into your heavy Python web app. The web app is suppose to serve as something that routes IO the bulk of your code/logic and heavy lifting should be shifted to the database. Simple apps can avoid this but in general complex apps cannot.

Case in point do you put your where clause in the application program than download the entire table? Or do you throw as much logic as possible into the query so the database outputs a result as close as possible to what you need? The later statement is the right answer.

>I don't really feel like composability/modularity is all that important in SQL.

You're not completely wrong. The bigger issue is SQL optimization. Because Databases form the backbone of computation for a standard web app SQL provides a layer of indirection that makes optimization harder. For C++ optimization is built into the syntax itself, you make choices while coding to optimize things. For SQL you open up the black box and look into the query planner to see what your High level code is compiling too. SQL is a really bad interface for doing optimizations but that's a topic for another day. The topic of this post is modularity and he's not wrong... SQL is not a composable language and there's no performance loss in making it more composeable.

Not even sure if stored procedures are part of the sql standard.... these seem to me to be just specific syntax additions added on by specific databases.

I mean it works so why not. I could code all my SQL this way.

Hence why i say why there is a data model mismatch.

E.g. i wouldn't say that assembly and haskell have a compatible data model just because assembly is a sequence of bytes, and haskell is a superset of that.

Not that a data model is solely about the types involved.

> See orms for reference. ORMS have their own set of problems but data mismatches are not part of those problems.

ORMs are notorious for being a leaky abstraction. Largely because the object and relational data model dont entirely match.

> In general the classic model for web development is to place as much logic as possible into the SQL query and as little logic as possible into your heavy Python web app.

When was the last time you wrote a 500 line sql query? A thousand line? My point is that sql queries are short enough, that further abstraction is not really missed. That doesn't mean you should put 0 logic in your query.

> Case in point do you put your where clause in the application program than download the entire table? Or do you throw as much logic as possible into the query so the database outputs a result as close as possible to what you need? The later statement is the right answer.

This is a strawman.

> The bigger issue is SQL optimization. Because Databases form the backbone of computation for a standard web app SQL provides a layer of indirection that makes optimization harder

I disagree. At scale you have data that has mixed cardinality. The indirection allows the db to chose the best algorithm given the size of underlying data at runtime. Sometimes that doesn't work properly, but the vast majority of time it is a significant benefit. Its sort of like how sometimes compilers dont work properly and you need to hand optimize, but in practise that is rare and you wouldn't throw out the compiler because the other 95% of time its better and lower effort than if you had to always do it by hand.