6 ms·

While all the criticism is probably correct, and SQL definitely shows its age, it's still very much good enough for pretty much all its current applications. Al

While all the criticism is probably correct, and SQL definitely shows its age, it's still very much good enough for pretty much all its current applications. Also, SQL's basics are very easy to learn. These aspects make it very hard to imagine a language that might gain enough traction to actually replace SQL in a foreseeable future.

cmrdporcupine 4y ago
Many have tried. Most are hobby projects. None have had the full force of a real production ready DBMS behind them. And really, projects like this are fighting the general ignorance the industry as a whole has about what a relational database actually is. It's better since the NoSQL wave crested and we stopped hearing stupid shit like "my data isn't tabular and doesn't fit in a schema", but there's still a preponderance of people who haven't stopped to learn the lessons that Date and Codd tried to teach decades ago, and constantly reach for graph/network/hierarchical databases without understanding why we originally tried to move beyond those back in the 70s. Unfortunately because of this most attempts to do "better than SQL" end up not working from first principles and look like a dogs breakfast of ill-formed "that'd be neat" ideas. And, yeah, as I said the "pure relational" projects that are out there are usually either academic projects or hobby. (Yes I've had a few of my own) I think there might be hope in the Datalog-type systems that have emerged recently.
- cryptonector 4y ago
  Any alternative to SQL has to transpile to SQL in order to gain traction.
  - cmrdporcupine 4y ago
    Which right away rules out a whole bunch of more sophisticated and elegant behaviours, honestly. The other alternative might be to implement one's new thing as a patch to alter the frontend of Postgres. I looked at this many years ago and the engineering effort was immense. But it might be easier now.
    - zasdffaa 4y ago
      Why does it rule out 'more sophisticated and elegant behaviours'?
      - cmrdporcupine 4y ago
        If your language has to interoperate with data in the SQL database, it right away has to deal with SQL's limitations and oddities: 1) SQL's type model has no concept of nested relations or rich datatypes; it's limited to basic scalar types <tho there are vendor additions to this>, so implementing e.g. Date's concept of "relation valued attributes" <essentially tables-in-tables> for example would be impractical 2) SQL's storage model doesn't map 1:1 with e.g. a relational model because SQL tables are bags instead of sets (duplicate rows are possible). This has a couple downsides. a) whatever more expressive and relational algebraic language you built would have to inherit that model and and have some way of dealing with bags instead of proper relational ranges <which are sets> and b) the presence of the possibility of dupes actually makes some query planner optimizations impossible to perform. <CJ Date goes into more depth explaining this, I don't have room for it here> 3) Likewise similar problems with SQL NULLs. NULLs are not in the "pure" relational model, so your language can't be pure. NULLs mess with the type system, and you end up inheriting a bunch of the weird casting/identity/comparison issues that come with NULLS. And NULLs, again mess with the query planner. Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement.
        zasdffaa 4y ago
        1) yeah, probably. 2) Supose you're writing sqlng, compiling it to sql. sqlng simply mandates a primary key on everything. Not a problem. 3) nulls can be a horror but the alternatives aren't pretty. Removing nulls means tables have to be broken up, see https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without.... I believe there are better ways to handle this issue. (final line) Makes no sense, a tab;e doesn't have to be disk-resident. Once read, with sufficient memory, the file sits in ram cache.
        4y ago
- ErwinSmout 4y ago
  :-) And those that are built on "first principles" end up looking like a dog's breakfast just as well because people expect to not have to learn a novel language. People don't want to be told to forget everything they thought they knew. People don"t want to be told they've been wrong for 40 yrs. Even if you add that it's not entirely their own fault because they've been consistently misled by an entire industry. You can crack the problem of supporting CREATE ASSERTION, they'll still dismiss it (handwave handwave) believing you're just bluffing. And I mostly disagree with the optimism built on Datalog.
webmobdev 4y ago
Agreed - my experience with non-SQL tech (ORMs) with Django and ASP.net MVC made me really appreciate SQL so much. Most of the time I felt everything would be so much easier using raw SQL instead of dealing with model objects. It also felt like in their quest to replace SQL and make things "simpler" they were recreating some db features again.
- SoftTalker 4y ago
  Yep. That's why I write most of my logic in stored procedures. Working with tables and queries is so much easier in PL/pgsql than dealing with ORMs and their leaky abstractions. My application code just calls stored procedures. It's unaware of the tables and underlying data model.
  - sivers 4y ago
    Cool! I do this, but I haven't seen anyone else do it. Is any of your code public? I wrote about it at https://sive.rs/pg https://sive.rs/pg and posted my SQL shopping cart at https://github.com/sivers/store https://github.com/sivers/store Please contact me if you'd like to share tips: https://sive.rs/contact https://sive.rs/contact
    - SoftTalker 4y ago
      No I don't really have any public code. I read your blog post and I agree 100% The database is the easiest place to code business logic that pertains to the data. Write it once, it's available for all client applications.
  - 3pm 4y ago
    There is no one-size-fits-all, but most of the time I would be against SP because: 1) SPs usually mix persistence concerns with business logic. Making it harder to understand business intent. I find objects much more expressive than raw data. Sometimes you want to concentrate on the plain logic, without worrying about how something gets saved. Also you will not have to rewrite everything if you ever want to change how something is stored. Sometimes people switch from mysql to pg, or even doc or kv database. Keeping business logic separate enables this. And good ORM enables this separation. 2) Refactoring tooling and unit tests. SPs are lacking here significantly compared to general purpose languages. 3) Business logic outside of DB allows easier horizontal scaling.
strbean 4y ago
COBOL also fits this description, down to the awkward attempts at matching natural language. I think the staying power of SQL comes from its broader audience - programmers, analysts, and executives all use it. It's much easier to motivate programmers to learn a new, superior language than it is to motivate executives to learn a new technology that only gives ergonomic improvements.

cmrdporcupine 4y ago

Many have tried. Most are hobby projects. None have had the full force of a real production ready DBMS behind them. And really, projects like this are fighting the general ignorance the industry as a whole has about what a relational database actually is. It's better since the NoSQL wave crested and we stopped hearing stupid shit like "my data isn't tabular and doesn't fit in a schema", but there's still a preponderance of people who haven't stopped to learn the lessons that Date and Codd tried to teach decades ago, and constantly reach for graph/network/hierarchical databases without understanding why we originally tried to move beyond those back in the 70s. Unfortunately because of this most attempts to do "better than SQL" end up not working from first principles and look like a dogs breakfast of ill-formed "that'd be neat" ideas. And, yeah, as I said the "pure relational" projects that are out there are usually either academic projects or hobby. (Yes I've had a few of my own) I think there might be hope in the Datalog-type systems that have emerged recently.

cryptonector 4y ago
Any alternative to SQL has to transpile to SQL in order to gain traction.
- cmrdporcupine 4y ago
  Which right away rules out a whole bunch of more sophisticated and elegant behaviours, honestly. The other alternative might be to implement one's new thing as a patch to alter the frontend of Postgres. I looked at this many years ago and the engineering effort was immense. But it might be easier now.
  - zasdffaa 4y ago
    Why does it rule out 'more sophisticated and elegant behaviours'?
    - cmrdporcupine 4y ago
      If your language has to interoperate with data in the SQL database, it right away has to deal with SQL's limitations and oddities: 1) SQL's type model has no concept of nested relations or rich datatypes; it's limited to basic scalar types <tho there are vendor additions to this>, so implementing e.g. Date's concept of "relation valued attributes" <essentially tables-in-tables> for example would be impractical 2) SQL's storage model doesn't map 1:1 with e.g. a relational model because SQL tables are bags instead of sets (duplicate rows are possible). This has a couple downsides. a) whatever more expressive and relational algebraic language you built would have to inherit that model and and have some way of dealing with bags instead of proper relational ranges <which are sets> and b) the presence of the possibility of dupes actually makes some query planner optimizations impossible to perform. <CJ Date goes into more depth explaining this, I don't have room for it here> 3) Likewise similar problems with SQL NULLs. NULLs are not in the "pure" relational model, so your language can't be pure. NULLs mess with the type system, and you end up inheriting a bunch of the weird casting/identity/comparison issues that come with NULLS. And NULLs, again mess with the query planner. Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement.
      - zasdffaa 4y ago
        1) yeah, probably. 2) Supose you're writing sqlng, compiling it to sql. sqlng simply mandates a primary key on everything. Not a problem. 3) nulls can be a horror but the alternatives aren't pretty. Removing nulls means tables have to be broken up, see https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without.... I believe there are better ways to handle this issue. (final line) Makes no sense, a tab;e doesn't have to be disk-resident. Once read, with sufficient memory, the file sits in ram cache.
      - 4y ago
ErwinSmout 4y ago
:-) And those that are built on "first principles" end up looking like a dog's breakfast just as well because people expect to not have to learn a novel language. People don't want to be told to forget everything they thought they knew. People don"t want to be told they've been wrong for 40 yrs. Even if you add that it's not entirely their own fault because they've been consistently misled by an entire industry. You can crack the problem of supporting CREATE ASSERTION, they'll still dismiss it (handwave handwave) believing you're just bluffing. And I mostly disagree with the optimism built on Datalog.

webmobdev 4y ago

Agreed - my experience with non-SQL tech (ORMs) with Django and ASP.net MVC made me really appreciate SQL so much. Most of the time I felt everything would be so much easier using raw SQL instead of dealing with model objects. It also felt like in their quest to replace SQL and make things "simpler" they were recreating some db features again.

SoftTalker 4y ago
Yep. That's why I write most of my logic in stored procedures. Working with tables and queries is so much easier in PL/pgsql than dealing with ORMs and their leaky abstractions. My application code just calls stored procedures. It's unaware of the tables and underlying data model.
- sivers 4y ago
  Cool! I do this, but I haven't seen anyone else do it. Is any of your code public? I wrote about it at https://sive.rs/pg https://sive.rs/pg and posted my SQL shopping cart at https://github.com/sivers/store https://github.com/sivers/store Please contact me if you'd like to share tips: https://sive.rs/contact https://sive.rs/contact
  - SoftTalker 4y ago
    No I don't really have any public code. I read your blog post and I agree 100% The database is the easiest place to code business logic that pertains to the data. Write it once, it's available for all client applications.
- 3pm 4y ago
  There is no one-size-fits-all, but most of the time I would be against SP because: 1) SPs usually mix persistence concerns with business logic. Making it harder to understand business intent. I find objects much more expressive than raw data. Sometimes you want to concentrate on the plain logic, without worrying about how something gets saved. Also you will not have to rewrite everything if you ever want to change how something is stored. Sometimes people switch from mysql to pg, or even doc or kv database. Keeping business logic separate enables this. And good ORM enables this separation. 2) Refactoring tooling and unit tests. SPs are lacking here significantly compared to general purpose languages. 3) Business logic outside of DB allows easier horizontal scaling.

strbean 4y ago

COBOL also fits this description, down to the awkward attempts at matching natural language. I think the staying power of SQL comes from its broader audience - programmers, analysts, and executives all use it. It's much easier to motivate programmers to learn a new, superior language than it is to motivate executives to learn a new technology that only gives ergonomic improvements.