8 ms·

Relational is more than SQL

136 points by tysont 3y ago

dagss 3y ago
Nitpick, but relational does not mean joins, it means tables/rows of tuples. A "relational document database" which is the slogan of Fauna it seems is a contradiction in terms.
- contrast 3y ago
  That’s technically correct, and I think the author would say he’s aware of that definition. The article as I read it is trying to make a broader point, that there are underlying mathematical principles that inspired Codd’s relational model. I’ve never had cause to explore it, but my understanding is that there’s nothing in those principles that require tables/rows of tuples. One goal of the article seems to be to inspire a curiosity in knowledgeable readers: what happens if you build a document database that also supports the same mathematical principles that inspired the relational model?
  - gregjor 3y ago
    > there’s nothing in [Codd’s] principles that require tables/rows of tuples. Have you read Codd’s Rules #1 and #2? Pretty clear on this point. https://en.wikipedia.org/wiki/Codd%27s_12_rules https://en.wikipedia.org/wiki/Codd%27s_12_rules Technically the relational model uses the term relation to refer to an unordered set of tuples, where every tuple has a key (one or more elements) to uniquely identify it, and every tuple has the same number of items, of the same type. Tables are relations. So are the results of a query, which can include joins.
    - ghusbands 3y ago
      > > there’s nothing in [Codd’s] principles that require tables/rows of tuples. You're misquoting; is that intentional? The post you're replying to says the principles that inspired/predate Codd don't require tables/tuples. Thus, the details of Codd's relational model are irrelevant.
      - bazoom42 3y ago
        What principles are you talking about then?
      - gregjor 3y ago
        If I misunderstood the principles you refer to I apologize. Codd's relational rules and designs for relational databases developed from relational algebra theory, which was introduced by... Edgar F. Codd. https://en.m.wikipedia.org/wiki/Relational_algebra https://en.m.wikipedia.org/wiki/Relational_algebra If you meant to refer to some other principles can you name them or give a link or something?
  - bazoom42 3y ago
    A relation is by definition a set of tuples (informally called a table where the tuples are the rows). Codds relational database model adds the further constraint that nested tables are not allowed (first normal form), instead representing relationships through foreign keys. Codds motivation for disallowing nested tables is that it makes query languages much simpler. He develops relational algebra which is the foundation behind SQL, which is why SQL does not allow nested tables. Document databases does not follow first normal form and allows nested structures, so they cannot be queried with relational algebra, since it doesnt have a way to “drill down” into nested structures. It is unclear to me what “mathematical principles” remain if you remove the notion of relations from the relational model.
    - _a_a_a_ 3y ago
      Out of my depth here (no experience) but "Codds relational database model adds the further constraint that nested tables are not allowed" may be wrong. He allowed nested stuff, it's just that SQL didn't support it. Can anyone elucidate? Please don't shout that I'm wrong because there was something there in his first paper.
      - bazoom42 3y ago
        No, he explicitly disallows nested relations. This is the definition of first normal form. Hierarchical databases (which predate relational) can be understood as nested relations, and Codds first example of normalization is how to extract the nested relations in such a database into seperate tables and instead express the relationships through foreign keys.
        _a_a_a_ 3y ago
        Thanks for a polite disagreement, but I believe you are wrong (not saying you are!). IIRC Codd defined relation valued attributes and also associated operators Group and Ungroup. https://www.oreilly.com/library/view/sql-and-relational/9781449319724/ch07s10.html https://www.oreilly.com/library/view/sql-and-relational/9781... also https://shark.armchair.mb.ca/~erwin/RA_Intro.htm https://shark.armchair.mb.ca/~erwin/RA_Intro.htm " Relations are, themselves, values too, and relation attributes can therefore be declared to be of another relation type. Such attributes are called 'Relation-valued attributes' (RVA's for short). In the RA, two operators are available that allow us to manipulate relations in connection with RVA's : GROUP and UNGROUP " Like I said, I'm a bit out of my depth here so take the above as evidence rather than proof that such things existed, but I'm pretty sure I saw this, hand-drawn, in one of Codd's original papers. . Edit: you are right "Codd proposed a normal form thathe called first normal form (1NF), and he included a requirement for 1NF in his definitions for 2NF,3NF, and subsequently BCNF. Under 1NF as he defined it, relation-valued attributes were “outlawed”;that is to say, a relvar having such an attribute was not in 1NF." https://fliphtml5.com/qprz/cxon/basic/201-235 https://fliphtml5.com/qprz/cxon/basic/201-235
  - dragonwriter 3y ago
    > The article as I read it is trying to make a broader point, that there are underlying mathematical principles that inspired Codd’s relational model. The relational model is a direct product of a set of mathematical principles Codd put together called relational algebra, which deals with sets of tuples called relations. Nothing in the article addresses any of the mathematical underpinnings of the relational model. Its blowing smoke at an audience that it expects to know next to nothing about the topic. > One goal of the article seems to be to inspire a curiosity in knowledgeable readers: what happens if you build a document database that also supports the same mathematical principles that inspired the relational model The features of RDBMSs that they seem to be suggesting FQL supports are ACID transactions. While that's an important feature of RDBMSs, it isn’t the same thing as the mathematical principles addresses by the relational model, whether relational algebra or the more general set theory that inspires it. The article isn't directed at knowledgable readers.
- marcosdumay 3y ago
  Whatever you want to point from theory, the one single distinctive feature of the relational model is the "mostly free" interdependency between the relations. AKA, the fks and joins.
herodoturtle 3y ago
This is a very interesting way to promote a product, credit to the author (who is an industry veteran it seems). I had no idea what Fauna was. I just clicked the link here because the title caught my eye (I work with databases quite a bit). The opening paragraph immediately grabbed my attention - "My first deep dive into SQL was in 1987, just before I became the first technical person at Microsoft to work on SQL Server." - woah! So I read this entire article, which is very well written and easy to read but mostly affirms what I already know. And then I get to the final section where they promote Fauna - and so now I know about Fauna too. Kudos to these folks, in my humble opinion, this is marketing done right.
- probablypower 3y ago
  This is interesting, because I have the exact opposite response to these sorts of articles. I think any bias or personal interest should be declared upfront in media (articles, videos, podcasts, ...) rather than appear as a 'common consumer' talking about a pain point in a relatable way. It really rubs me the wrong way when an article ends with a bait-and-switch, where you realise the entire article was manufactured to make you relate to their product's business case. Obviously this method must resonate with people, like yourself, otherwise it wouldn't become so common. I guess I'm just the 'B' in the A/B testing that results in this type of marketing.
  - xwowsersx 3y ago
    I don't think there's any bait-n-switch unless you're unaware you're on fauna.com. Content marketing is well understood and at least in this case it isn't trash content someone spit out in 2 mins in order to lure you to their site. I don't see anything wrong with a company/product delivering value to you in the form of quality content and in exchange they are afforded an opportunity to run your eyes over their product offering.
  - zzzeek 3y ago
    agree, I have to look at the domain name, the title / sidebar , etc. to see up front, "OK this is yet another 'we think we have a better SQL' startup", then I skip the whole thing. A site that's about "here's our product and why you might like it!" without getting into some "SQL, well you know, it has shortcomings" which is just unnecessary.
ako 3y ago
Seems like a lot of what fauna does by storing documents isn’t really new, oracle, Postgres and others have provided this for a long time. I was really surprised by the performance of json queries [1], opens the doors to using Postgres as a client api cache, storing the payload in a table, and doing deserialization using (materialized) views. Difference seems to be the approach to minimize number of calls from your application, get all require session data in one call, similar to what graphql is doing for api calls. They’re also using http as the protocol for database connectivity. [1]: https://ako.github.io/blog/2023/08/25/json-transformations.html https://ako.github.io/blog/2023/08/25/json-transformations.h...
- robertlagrant 3y ago
  Yes - the difference you mention seems to be the main difference.
  - ttfkam 3y ago
    CTEs and query pipelining are not sufficient?
- default-kramer 3y ago
  Using FQL instead of SQL seems to be a pretty big difference too.
  - ako 3y ago
    Postgres has procedural languages and enables you to return complex json structures combined of relational data and json documents both with its procedural languages and regular sql. Sure, the syntax is different, but not sure if the difference makes a big impact.
ghusbands 3y ago
> Most importantly, SQL databases made supporting highly consistent ACID transactions easy. The default transaction isolation level for every major database is not ACID. Enabling the required serializability tends to make performance terrible, and so most don't.
- iudqnolq 3y ago
  that's presumably why the author said "made supporting... easy", not "is"?
  - ghusbands 3y ago
    Most people believe that databases are ACID by default, so it's worth bringing up when an article even subtly implies otherwise.
    - bhattid 3y ago
      Damn, this is news to me. Seems I have some reading to do, thanks for pointing that out.
- _a_a_a_ 3y ago
  > Enabling the required serializability... is trivial, no? > ...tends to make performance terrible I've heard this a lot but never seen any figures - anyone have any numbers/experience? (edit: and most apps I've worked with didn't need serialisability, either because they were working with a snapshot of data or absolutely precise answers weren't needed)
  - ghusbands 3y ago
    I've heard multiple accounts of people being taken by surprise by this and by how transaction isolation actually works in databases and not finding it at all easy to correct it. A famous one is https://blog.codinghorror.com/deadlocked/ https://blog.codinghorror.com/deadlocked/ (On your edit: The problem is not knowing when you're being hit by it. Even just maintaining a limit on total size of uploaded files or such, for example, is nontrivial under default isolation levels.)
snthpy 3y ago
Disclaimer: I'm a core contributor to PRQL [1] and post about it a lot on HN. Apologies for jumping in on other people's threads, but for people interested in the headline, PRQL might be of interest. At PRQL[1] we believe that SQL is a combination of two things: 1. Relational Algebra, which is eternal because it's just maths, and 2. A language designed in the 70s that looks like COBOL. When people say that SQL will never die, they are usually thinking about Relational Algebra because SQL has been used interchangeably with that. With PRQL we agree that Relational Algebra is fundamental to thinking about data and we intend to keep that. However we've learned a lot about programming languages in the last 50 years and so PRQL is a revamp of SQL that brings the composability of functional languages and modern ergonomics to data transformations in order to improve the DX and UX of data scientists, data analysts and analytics engineers. PRQL is simply a compiler that produces SQL so you can use it with whatever database you are currently using. It's completely open source with zero commercial associations and is deeply committed to staying that way forever. 1: https://prql-lang.org/ https://prql-lang.org/
- danielvaughn 3y ago
  That first PRQL code sample is wonderfully readable.
  - Timon3 3y ago
    It is! One suggestion to make it even more convincing: I'd love to see the SQL statement it compiles to.
    - leeuw01 3y ago
      This +1. I've been burned before when using ORMs which translate simple-looking queries to terribly inneficient SQL statements underwater.
      - danielvaughn 3y ago
        Yeah I’m a web dev, and recently I found out the most popular JS ORM doesn’t produce joins. It’ll just execute multiple queries in sequence. I don’t know how common that is in the ORM landscape but for me that’s a deal breaker.
robertlagrant 3y ago
On strong schemas and flexibility: 1. You still have a schema in your code. With weak schemas it's now just harder to know if every record in your database conforms to it. 2. An ORM is a great tool for prototyping. R.g. have SQLAlchemy objects in code, run a command to generate a database migration; run the migration, and you have all your data guaranteed to be compatible with your latest code, and you didn't write any SQL.
- m_mueller 3y ago
  If you program defensively you can save on certain common Schema updates in e.g. a document based data model (e.g. adding more fields). But strong schemas definitely make sense when you’re dealing with relational data from my experience. Earlier in my career I built a relational model on top of CouchDB (due to its strong replication capabilities, including on mobile devices), but it was definitely painful (and less performant) compared to building it in a relational DB.
  - roenxi 3y ago
    > If you program defensively you can save on certain common Schema updates in e.g. a document based data model (e.g. adding more fields) ALTER TABLE whatever ADD COLUMN new_field type DEFAULT NULL; I've seen a lot of people claim that they don't want to waste time clarifying their schema and I'm sure there are edge cases where that is clever. But, in the majority of cases, they are literally risking data integrity for a saving smaller than the time it takes to write a HN comment. Making schema implicit doesn't "save" anything. The schema is still there, now just only insiders who are completely familiar with the code know what it is. And they're going to have a few extra bugs because they'll forget too.
crabbone 3y ago
Worthless article. Zero useful description of what it's trying to sell. A bunch of disjoint historical facts about relational databases that have nothing to do with the product being sold take about 2/3 of the article. Also the author seems to be very proud of associating themselves with Microsoft's products (w/o even a hint of doubt that that may not show them in favorable light)... Also, marketing-inspired use of pseudo-programming terminology (eg. "dynamic languages"). Ewww.
gigatexal 3y ago
Re PRQL … I see it like my text editor. I’ll stick with vi because it has solved text editing. It’s done. Same with SQL. I’ve not seen anything yet ready to replace it. It’s not perfect. But for what I need from it it’s perfectly serviceable.
xwowsersx 3y ago
I have to say this was written extremely well. Quite cogent and I feel I learned a little something. Bookmarking this as a pretty decent intro to this area that I can refer people to.
slotrans 3y ago
Fixed schemas are good. Document stores are bad. SQL is good. Stop doing this nonsense. It's a step backwards. As the intro points out, hierarchical and graph DBs came first, and relational was built in part to solve their problems. Document DBs just bring those problems back.
- lcnPylGDnU4H9OF 3y ago
  At this point, one chooses the solution for their problem. The reason the fads occur is that a person who vaguely understands both the problem and the solution will write a blog post which happens to go viral talking about how the solution will solve all problems. NoSQL databases aren’t unilaterally worse than relational ones. They just solve different problems.
  - pphysch 3y ago
    > NoSQL databases aren’t unilaterally worse than relational ones. They just solve different problems. I can't prove this, but I assert that a relational database that has solid JSON+text support (e.g. Postgres) is on much better footing than a NoSQL DB that attempts to implement a true relational model. One is a adding a special new datatype, the other is trying to add an entire paradigm. Just use Postgres. If you do need to migrate to Mongo for some reason, dumping your tables into JSON isn't the end of the world.
    - lcnPylGDnU4H9OF 3y ago
      > a relational database that has solid JSON+text support (e.g. Postgres) [vs] a NoSQL DB that attempts to implement a true relational model I think both cases are similar to using a flat-head screwdriver for a phillips screw. It will technically work but you might run into otherwise avoidable problems. Use the right tool for the job. Though generally a relational database with JSON support will better serve most web applications, a nosql db might better serve niche internal processing.
- xtracto 3y ago
  I am tech advisor to a bunch of startups. One of them doing stock buy/sell came to me with their MongoDB based system. The first thing I told them is that using a document based db for oltp for their use case was going to give them problems. I saw it first hand 10 years ago, and had to do a migration. Their justification for using mongo was that their system is very dynamic so their data changes a lot and sql based DBs dont allow that. I told them about DBA migrations and whatnot, but I just haven't been able to convince them. It's sad seeing how they are digging into the same hole I had to digg out myself from a decade ago.

136 points by tysont 3y ago

Nitpick, but relational does not mean joins, it means tables/rows of tuples. A "relational document database" which is the slogan of Fauna it seems is a contradiction in terms.

contrast 3y ago
That’s technically correct, and I think the author would say he’s aware of that definition. The article as I read it is trying to make a broader point, that there are underlying mathematical principles that inspired Codd’s relational model. I’ve never had cause to explore it, but my understanding is that there’s nothing in those principles that require tables/rows of tuples. One goal of the article seems to be to inspire a curiosity in knowledgeable readers: what happens if you build a document database that also supports the same mathematical principles that inspired the relational model?
- gregjor 3y ago
  > there’s nothing in [Codd’s] principles that require tables/rows of tuples. Have you read Codd’s Rules #1 and #2? Pretty clear on this point. https://en.wikipedia.org/wiki/Codd%27s_12_rules https://en.wikipedia.org/wiki/Codd%27s_12_rules Technically the relational model uses the term relation to refer to an unordered set of tuples, where every tuple has a key (one or more elements) to uniquely identify it, and every tuple has the same number of items, of the same type. Tables are relations. So are the results of a query, which can include joins.
  - ghusbands 3y ago
    > > there’s nothing in [Codd’s] principles that require tables/rows of tuples. You're misquoting; is that intentional? The post you're replying to says the principles that inspired/predate Codd don't require tables/tuples. Thus, the details of Codd's relational model are irrelevant.
    - bazoom42 3y ago
      What principles are you talking about then?
    - gregjor 3y ago
      If I misunderstood the principles you refer to I apologize. Codd's relational rules and designs for relational databases developed from relational algebra theory, which was introduced by... Edgar F. Codd. https://en.m.wikipedia.org/wiki/Relational_algebra https://en.m.wikipedia.org/wiki/Relational_algebra If you meant to refer to some other principles can you name them or give a link or something?
- bazoom42 3y ago
  A relation is by definition a set of tuples (informally called a table where the tuples are the rows). Codds relational database model adds the further constraint that nested tables are not allowed (first normal form), instead representing relationships through foreign keys. Codds motivation for disallowing nested tables is that it makes query languages much simpler. He develops relational algebra which is the foundation behind SQL, which is why SQL does not allow nested tables. Document databases does not follow first normal form and allows nested structures, so they cannot be queried with relational algebra, since it doesnt have a way to “drill down” into nested structures. It is unclear to me what “mathematical principles” remain if you remove the notion of relations from the relational model.
  - _a_a_a_ 3y ago
    Out of my depth here (no experience) but "Codds relational database model adds the further constraint that nested tables are not allowed" may be wrong. He allowed nested stuff, it's just that SQL didn't support it. Can anyone elucidate? Please don't shout that I'm wrong because there was something there in his first paper.
    - bazoom42 3y ago
      No, he explicitly disallows nested relations. This is the definition of first normal form. Hierarchical databases (which predate relational) can be understood as nested relations, and Codds first example of normalization is how to extract the nested relations in such a database into seperate tables and instead express the relationships through foreign keys.
      - _a_a_a_ 3y ago
        Thanks for a polite disagreement, but I believe you are wrong (not saying you are!). IIRC Codd defined relation valued attributes and also associated operators Group and Ungroup. https://www.oreilly.com/library/view/sql-and-relational/9781449319724/ch07s10.html https://www.oreilly.com/library/view/sql-and-relational/9781... also https://shark.armchair.mb.ca/~erwin/RA_Intro.htm https://shark.armchair.mb.ca/~erwin/RA_Intro.htm " Relations are, themselves, values too, and relation attributes can therefore be declared to be of another relation type. Such attributes are called 'Relation-valued attributes' (RVA's for short). In the RA, two operators are available that allow us to manipulate relations in connection with RVA's : GROUP and UNGROUP " Like I said, I'm a bit out of my depth here so take the above as evidence rather than proof that such things existed, but I'm pretty sure I saw this, hand-drawn, in one of Codd's original papers. . Edit: you are right "Codd proposed a normal form thathe called first normal form (1NF), and he included a requirement for 1NF in his definitions for 2NF,3NF, and subsequently BCNF. Under 1NF as he defined it, relation-valued attributes were “outlawed”;that is to say, a relvar having such an attribute was not in 1NF." https://fliphtml5.com/qprz/cxon/basic/201-235 https://fliphtml5.com/qprz/cxon/basic/201-235
- dragonwriter 3y ago
  > The article as I read it is trying to make a broader point, that there are underlying mathematical principles that inspired Codd’s relational model. The relational model is a direct product of a set of mathematical principles Codd put together called relational algebra, which deals with sets of tuples called relations. Nothing in the article addresses any of the mathematical underpinnings of the relational model. Its blowing smoke at an audience that it expects to know next to nothing about the topic. > One goal of the article seems to be to inspire a curiosity in knowledgeable readers: what happens if you build a document database that also supports the same mathematical principles that inspired the relational model The features of RDBMSs that they seem to be suggesting FQL supports are ACID transactions. While that's an important feature of RDBMSs, it isn’t the same thing as the mathematical principles addresses by the relational model, whether relational algebra or the more general set theory that inspires it. The article isn't directed at knowledgable readers.
marcosdumay 3y ago
Whatever you want to point from theory, the one single distinctive feature of the relational model is the "mostly free" interdependency between the relations. AKA, the fks and joins.

herodoturtle 3y ago

This is a very interesting way to promote a product, credit to the author (who is an industry veteran it seems). I had no idea what Fauna was. I just clicked the link here because the title caught my eye (I work with databases quite a bit). The opening paragraph immediately grabbed my attention - "My first deep dive into SQL was in 1987, just before I became the first technical person at Microsoft to work on SQL Server." - woah! So I read this entire article, which is very well written and easy to read but mostly affirms what I already know. And then I get to the final section where they promote Fauna - and so now I know about Fauna too. Kudos to these folks, in my humble opinion, this is marketing done right.

probablypower 3y ago
This is interesting, because I have the exact opposite response to these sorts of articles. I think any bias or personal interest should be declared upfront in media (articles, videos, podcasts, ...) rather than appear as a 'common consumer' talking about a pain point in a relatable way. It really rubs me the wrong way when an article ends with a bait-and-switch, where you realise the entire article was manufactured to make you relate to their product's business case. Obviously this method must resonate with people, like yourself, otherwise it wouldn't become so common. I guess I'm just the 'B' in the A/B testing that results in this type of marketing.
- xwowsersx 3y ago
  I don't think there's any bait-n-switch unless you're unaware you're on fauna.com. Content marketing is well understood and at least in this case it isn't trash content someone spit out in 2 mins in order to lure you to their site. I don't see anything wrong with a company/product delivering value to you in the form of quality content and in exchange they are afforded an opportunity to run your eyes over their product offering.
- zzzeek 3y ago
  agree, I have to look at the domain name, the title / sidebar , etc. to see up front, "OK this is yet another 'we think we have a better SQL' startup", then I skip the whole thing. A site that's about "here's our product and why you might like it!" without getting into some "SQL, well you know, it has shortcomings" which is just unnecessary.

ako 3y ago

Seems like a lot of what fauna does by storing documents isn’t really new, oracle, Postgres and others have provided this for a long time. I was really surprised by the performance of json queries [1], opens the doors to using Postgres as a client api cache, storing the payload in a table, and doing deserialization using (materialized) views. Difference seems to be the approach to minimize number of calls from your application, get all require session data in one call, similar to what graphql is doing for api calls. They’re also using http as the protocol for database connectivity. [1]: https://ako.github.io/blog/2023/08/25/json-transformations.html https://ako.github.io/blog/2023/08/25/json-transformations.h...

robertlagrant 3y ago
Yes - the difference you mention seems to be the main difference.
- ttfkam 3y ago
  CTEs and query pipelining are not sufficient?
default-kramer 3y ago
Using FQL instead of SQL seems to be a pretty big difference too.
- ako 3y ago
  Postgres has procedural languages and enables you to return complex json structures combined of relational data and json documents both with its procedural languages and regular sql. Sure, the syntax is different, but not sure if the difference makes a big impact.

ghusbands 3y ago

> Most importantly, SQL databases made supporting highly consistent ACID transactions easy. The default transaction isolation level for every major database is not ACID. Enabling the required serializability tends to make performance terrible, and so most don't.

iudqnolq 3y ago
that's presumably why the author said "made supporting... easy", not "is"?
- ghusbands 3y ago
  Most people believe that databases are ACID by default, so it's worth bringing up when an article even subtly implies otherwise.
  - bhattid 3y ago
    Damn, this is news to me. Seems I have some reading to do, thanks for pointing that out.
_a_a_a_ 3y ago
> Enabling the required serializability... is trivial, no? > ...tends to make performance terrible I've heard this a lot but never seen any figures - anyone have any numbers/experience? (edit: and most apps I've worked with didn't need serialisability, either because they were working with a snapshot of data or absolutely precise answers weren't needed)
- ghusbands 3y ago
  I've heard multiple accounts of people being taken by surprise by this and by how transaction isolation actually works in databases and not finding it at all easy to correct it. A famous one is https://blog.codinghorror.com/deadlocked/ https://blog.codinghorror.com/deadlocked/ (On your edit: The problem is not knowing when you're being hit by it. Even just maintaining a limit on total size of uploaded files or such, for example, is nontrivial under default isolation levels.)

snthpy 3y ago

Disclaimer: I'm a core contributor to PRQL [1] and post about it a lot on HN. Apologies for jumping in on other people's threads, but for people interested in the headline, PRQL might be of interest. At PRQL[1] we believe that SQL is a combination of two things: 1. Relational Algebra, which is eternal because it's just maths, and 2. A language designed in the 70s that looks like COBOL. When people say that SQL will never die, they are usually thinking about Relational Algebra because SQL has been used interchangeably with that. With PRQL we agree that Relational Algebra is fundamental to thinking about data and we intend to keep that. However we've learned a lot about programming languages in the last 50 years and so PRQL is a revamp of SQL that brings the composability of functional languages and modern ergonomics to data transformations in order to improve the DX and UX of data scientists, data analysts and analytics engineers. PRQL is simply a compiler that produces SQL so you can use it with whatever database you are currently using. It's completely open source with zero commercial associations and is deeply committed to staying that way forever. 1: https://prql-lang.org/ https://prql-lang.org/

danielvaughn 3y ago
That first PRQL code sample is wonderfully readable.
- Timon3 3y ago
  It is! One suggestion to make it even more convincing: I'd love to see the SQL statement it compiles to.
  - leeuw01 3y ago
    This +1. I've been burned before when using ORMs which translate simple-looking queries to terribly inneficient SQL statements underwater.
    - danielvaughn 3y ago
      Yeah I’m a web dev, and recently I found out the most popular JS ORM doesn’t produce joins. It’ll just execute multiple queries in sequence. I don’t know how common that is in the ORM landscape but for me that’s a deal breaker.

robertlagrant 3y ago

On strong schemas and flexibility: 1. You still have a schema in your code. With weak schemas it's now just harder to know if every record in your database conforms to it. 2. An ORM is a great tool for prototyping. R.g. have SQLAlchemy objects in code, run a command to generate a database migration; run the migration, and you have all your data guaranteed to be compatible with your latest code, and you didn't write any SQL.

m_mueller 3y ago
If you program defensively you can save on certain common Schema updates in e.g. a document based data model (e.g. adding more fields). But strong schemas definitely make sense when you’re dealing with relational data from my experience. Earlier in my career I built a relational model on top of CouchDB (due to its strong replication capabilities, including on mobile devices), but it was definitely painful (and less performant) compared to building it in a relational DB.
- roenxi 3y ago
  > If you program defensively you can save on certain common Schema updates in e.g. a document based data model (e.g. adding more fields) ALTER TABLE whatever ADD COLUMN new_field type DEFAULT NULL; I've seen a lot of people claim that they don't want to waste time clarifying their schema and I'm sure there are edge cases where that is clever. But, in the majority of cases, they are literally risking data integrity for a saving smaller than the time it takes to write a HN comment. Making schema implicit doesn't "save" anything. The schema is still there, now just only insiders who are completely familiar with the code know what it is. And they're going to have a few extra bugs because they'll forget too.

crabbone 3y ago

Worthless article. Zero useful description of what it's trying to sell. A bunch of disjoint historical facts about relational databases that have nothing to do with the product being sold take about 2/3 of the article. Also the author seems to be very proud of associating themselves with Microsoft's products (w/o even a hint of doubt that that may not show them in favorable light)... Also, marketing-inspired use of pseudo-programming terminology (eg. "dynamic languages"). Ewww.

gigatexal 3y ago

Re PRQL … I see it like my text editor. I’ll stick with vi because it has solved text editing. It’s done. Same with SQL. I’ve not seen anything yet ready to replace it. It’s not perfect. But for what I need from it it’s perfectly serviceable.

xwowsersx 3y ago

I have to say this was written extremely well. Quite cogent and I feel I learned a little something. Bookmarking this as a pretty decent intro to this area that I can refer people to.

slotrans 3y ago

Fixed schemas are good. Document stores are bad. SQL is good. Stop doing this nonsense. It's a step backwards. As the intro points out, hierarchical and graph DBs came first, and relational was built in part to solve their problems. Document DBs just bring those problems back.

lcnPylGDnU4H9OF 3y ago
At this point, one chooses the solution for their problem. The reason the fads occur is that a person who vaguely understands both the problem and the solution will write a blog post which happens to go viral talking about how the solution will solve all problems. NoSQL databases aren’t unilaterally worse than relational ones. They just solve different problems.
- pphysch 3y ago
  > NoSQL databases aren’t unilaterally worse than relational ones. They just solve different problems. I can't prove this, but I assert that a relational database that has solid JSON+text support (e.g. Postgres) is on much better footing than a NoSQL DB that attempts to implement a true relational model. One is a adding a special new datatype, the other is trying to add an entire paradigm. Just use Postgres. If you do need to migrate to Mongo for some reason, dumping your tables into JSON isn't the end of the world.
  - lcnPylGDnU4H9OF 3y ago
    > a relational database that has solid JSON+text support (e.g. Postgres) [vs] a NoSQL DB that attempts to implement a true relational model I think both cases are similar to using a flat-head screwdriver for a phillips screw. It will technically work but you might run into otherwise avoidable problems. Use the right tool for the job. Though generally a relational database with JSON support will better serve most web applications, a nosql db might better serve niche internal processing.
xtracto 3y ago
I am tech advisor to a bunch of startups. One of them doing stock buy/sell came to me with their MongoDB based system. The first thing I told them is that using a document based db for oltp for their use case was going to give them problems. I saw it first hand 10 years ago, and had to do a migration. Their justification for using mongo was that their system is very dynamic so their data changes a lot and sql based DBs dont allow that. I told them about DBA migrations and whatnot, but I just haven't been able to convince them. It's sad seeing how they are digging into the same hole I had to digg out myself from a decade ago.