9 ms·

Oops, You Wrote a Database

105 points by swyx 4y ago

roenxi 4y ago
I have a huge amount of sympathy for people who accidentally attempt to implement a database. I believe they are usually motivated by their desire to avoid SQL - an extremely worthy goal. The industry, by and large, puts a crazy amount of effort into avoiding SQL by writing ORMs, translation layers and similar tech because it really is that bad. But unfortunately databases have solved common-as-mud problems to do with data, disks, multiple users (which, if your data is worth storing, you will eventually have) and the interactions thereof. It is worth putting up with SQL to get all the lessons of the 70s, 80s, 90s, 00s, 10s and now 20s in how to store data without creating buggy software. People probably have these problems and it is usually faster to solve them the usual way that works. Write some SQL and maul a stress ball, it'll be easier long term.
- JPLeRouzic 4y ago
  > "The industry, by and large, puts a crazy amount of effort into avoiding SQL by writing ORMs, translation layers and similar tech " In my experience (I am retired from a telecom operator) using a database made applications quite inflexible yet real life implies constant changes in their data model and as a result when there was a "database migration" there was always some information lost. On contrary Pick databases (MultiValue databases) were very useful. https://en.wikipedia.org/wiki/Pick_operating_system https://en.wikipedia.org/wiki/Pick_operating_system
  - sirtaj 4y ago
    I'm of the opposite opinion. Database migrations force you to think through the changes in your data more explicitly. You catch logic oversights, boundary conditions and implicit transformations much more thoroughly and with fewer errors later on.
  - joshka 4y ago
    I've seen a lot of different enterprise systems. In my experience it's not the db that's causes this inflexibility issue, but designing the architecture of systems data-first (using Data Dictionaries / ER diagrams etc.) rather than use-case-first that leads to this sort of problem. When a data schema is the foundation of a system, it's often the most difficult thing to change.
    - doctor_eval 4y ago
      I don’t know how that works. Your data schema is the foundation of almost any system, regardless of how you generate it. I think this is a common mistake. People think their code is what’s important. But it’s the data and how it gets transformed that is the core of most applications.
      - esailija 4y ago
        Data-based architecture means that you have all data about an entity in a single place. Even though if you analyze the domain and all use cases you find that there are slices of that data that never interact together. Example is storing customers address and purchase history in same place, only because they "belong to customer" in a data sense. This is very bad because it leads to entangled architecture where everything depends on everything and you cannot extract truly independent modules. The project implemented like this simply cannot scale beyond a certain size. So no, data is not important but how it is used.
        doctor_eval 4y ago
        Ok. Perhaps I’m not familiar with this concept of “data-based architecture”. The GP talked about designing around features, which I think is also bad. But maybe they meant more like a DDD approach? So - I certainly agree 100% with what you say about keeping thing independent. Maybe I’m just unfamiliar with the terms.
      - jen20 4y ago
        For this to be true (which it is in some contexts), the important question is what the boundary of the "system" is.
  - doctor_eval 4y ago
    I have done, quite literally, thousands of database migrations in a telco context and we never - not one time - lost information. I don’t think there is any particular problem, or solution. Applications get old, assumptions change, the world moves in. It’s true that our database design became inflexible over time and needed to be hoisted. But so too did our authentication layer (hand built - JaaS - JWT), the UI (AWT - Swing - Web 1.0 - Web 2.0), the deployment model (on prem - cloud - docker), even the API scheme (RMI - soap - rest - graphql). Any application that lives long enough is going to go through this sort of change. The only constant in all of the above was SQL.
    - ttfkam 4y ago
      Beware the old man in a young man's game. You will be tempted to underestimate him. As many unremembered before you have done.
- pharmakom 4y ago
  SQL is pretty good though. It could compose better, sure, but it’s well suited to data queries. I think people dislike learning a new language, even to the point of writing bad software. I wonder why?
  - d33 4y ago
    I'd say that pretty much everything about SQL is bad. Apart from composability, the syntax is super annoying (the ordering of keywords), many databases have pretty much no error reporting if you get it wrong and the linters suck. You can't unit test it. It's not very well standardized and it's actually more than one language (DML vs DDL). Plus, it's not very readable. And the declarative nature of the language is eventually going to back-stab you when you discover yet another edge case of your query planner that flips a reasonable plan to one that's literally going to take ages.
    - pharmakom 4y ago
      You can unit test SQL by spinning up Docker containers. You should just write SQL for the database you are using (e.g. PostgreSQL). Switching databases without changing queries is unrealistic anyways. I agree with your other points, but I am yet to see a layer above SQL that improves on them enough to justify its additional complexity.
      - CHY872 4y ago
        I know a team who used JooQ (code-generated query builder for Java) to generate SQL which worked on both Postgres and Oracle and worked well enough that they could support both in production (Oracle for top end perf, Postgres for everything else). There were certain places where they had to switch on SQL format, but over years they felt comfortable enough only unit testing on one kind of database.
    - CHY872 4y ago
      Few things: Regarding unit testing, it's now very possible to productively unit test a SQL database - I'm familiar with a Java package that boots up an embedded PostgreSQL, obviously Docker is now in vogue, etc, etc. It's not quite 'just run a function', but you write your code in the same style as a unit test and you're running hundreds of tests in seconds. You can use transactions to automagically clear out your state, which means performance is also excellent. Regarding the standardization of the language - no, it's not totally standard (although arguably that's a good thing, we don't want 10 implementations of exactly the same thing, they'd never evolve), but because they all work sort-of-the-same, a lot of language support ends up transferrable. You can use ActiveRecord on many backing databases, for example. Query planner problem is definitely true, know folks who've spent a lot of time hinting Oracle and scaring Postgres into the same, though personally only took a few days. However, SQL is IMO the best of a bad bunch. Most non-SQL databases I've used have been extremely sharp cornered, whether this be via: unpredictable/unbounded worst case performance in pessimal storage cases (RocksDB, Cassandra), various parts of the code where unbounded memory/storage usage is required (Cassandra, Spark, Elasticsearch), extremely low level APIs making it easy to make unobservably (as in, you won't notice it and will eat the pain every read) slow APIs (RocksDB). I'm not saying there's nothing better, but it's probably not an all-rounder. There are certainly things I don't like about most SQL databases. Few SQL databases are columnar, which is _the_ key to good performance in the 2010s, and native LZ4-style data compression is also not widely used. But for most engineers they solve a lot of problems for you without having to think about them.
- kindofabigdeal 4y ago
  How is SQL bad? If anything my experience with KV db's is that most of the time they'll eventually end up implementing some form of structured data storage and retrieval that in some way resembles SQL. I love KV's only when I used them as embedded db's like pebble and badger db but you can never beat a good ol' SQL server that just does what you need from it nothing more nothing less. ORM's most of the time aren't that great either you always have to keep up with their new fancy API changes and instead of just doing your work you find yourself relearning the same ORM every few years, not only that but some ORM's are extremely sensitive in how you order your operations that the order in which you write you code can potentially kill your performance or worse accidentally deleting data.
- crabbone 4y ago
  Why is SQL bad? I'll take SQL over almost any language used to write the "application layer" any day... Also, why do you think that databases solved problems with disks? Do you mean disks as in block devices? If so, none of the popular databases ever touches that level, most popular SQL databases work with filesystem.
  - setr 4y ago
    It doesn’t compose properly, lacks type safety, you have to codegen your statements in the application anyways and is generally an utter bitch to do so because it’s horrifically inconsistent (and doesn’t compose properly). You can dump everything into stored procs and functions, but now you get worthless compiler errors, indirect version control at best, and really your whole tooling support is reverted to the 80’s. It’s a pretty shit language — its just the only one we get to have, and the value of what it interfaces with (the RDBMS itself, ACID and the relational algebra) is… invaluable. You can’t get away with not knowing it, and it’s generally dumb to avoid it (because you’re then avoiding the glory behind it) but as a language it’s basically COBOL-tier.
    - crabbone 4y ago
      > It doesn’t compose properly Stored procedures? > lacks type safety How so? Can you give some examples? I have no idea what this is even about. > you have to codegen your statements Never happened to me. Not in any capacity that I would feel like this was a burden. The only cases where I needed to generate SQL code were some fuzzing tests intended to figure out problems with database performance, s.a. feeding a bunch of generated SQL to pgbench to see what may cause the database to slow down / use too many resources. This seems very strange in an application that is simply using SQL database rather than trying to do something weird with it (eg. testing performance). SQL is a very high-level language, which means you don't need to do as much code generation compared to, say, Assembly or even Java. I do see that people do this when they use ORM, but this is a self-inflicted pain. They shouldn't have been doing ORM to begin with. > get worthless compiler errors From what? Are you talking about writing SQL functions (eg. in Java) and then using them from SQL (eg. Oracle)? This doesn't sound like a SQL problem, more like Java compiler problem... But, yeah, I can see how this is a complication and an inconvenient thing to do, so, if that's what you mean, I'm generally with you on this one. Well, I think that the benefits are still worth doing this. > whole tooling support is reverted to the 80’s. Again, I don't see the connection. But, even if it was so... what's the problem? You get to work with an old and reliable technology, with decades of knowledge and experience to support it. And it's not like it died in the 80's. Tons of effort is being invested into this technology, it's amongst the first to get updates and integrations with new hardware and system capabilities (eg. vectorized ISAs support, even GPU access, bet you that when "smart SSD" become common enough, SQL databases will be the first to support them). What's not to like? This is like complaining about C compilers for being "old tech"... for all that's wrong with C (compilers), being old is not one of those things. > It’s a pretty shit language If you were honest, you'd write it like this: "I don't like it". And, well, you are allowed not to like whatever you want. It's subjective, and completely up to you. It just has less of a convincing power. But, really, this is your entire argument...
- eternityforest 4y ago
  I don't see anything particularly bad about SQL. If your problem is a recipe manager CRUD it's trivial. If it's more complex then than, any DIY thing is gonna be hard too. If you need some insane performance, SQL might not be right but any DIY thing will still be hard.
vouwfietsman 4y ago
This is not a bad take, but then every so often I have to insert 10 million rows into a database and for some reason that takes way longer than it should, and now I'm stuck figuring out the particular knobs and buttons needed to make it consume a reasonable amount of time and space. Most databases do suffer from "common denominator" syndrome, and you need to know a LOT, or be comfortable with hybrids, to get the right DB for the job if you want to avoid writing your own.
- iknownothow 4y ago
  What you're describing is the common symptom among many programmers which is their unfamiliarity with disk limitations. It's not the database's fault for not being able to write 10M rows to the disk quickly. Disks, especially the cloud SSDs are quite slow defaulting very often to just 3000 write IOPS. On the other hand, programmers are very familiar with CPU and memory limitations. Very rarely will a programmer try to hold 10M or 1B rows in memory because they intuitively understand memory limitations which is usually around 4GB per app/process.
  - href 4y ago
    This. I moved multiple web applications from bare-metal to the cloud, and many times had to refactor code to do hit the disk less. What worked before: Dozens of SQL queries, or loading session data from a file - for each request - it all was too slow in the cloud. Also: I find that many engineers wouldn't be able to tell you if the storage attached to their VMs was networked, or actual hardware on the hypervisor. A distinction that has big implications for throughput and latency.
  - doctor_eval 4y ago
    Another thing I’ve seen (perhaps even been guilty of :) is assuming we don’t have to worry about our table or index structure. Inserting 10 million narrow rows of integers and enums into a database with just a Pkey index will be super fast. Inserting 10mm wide rows containing json blobs and long text strings will be bad. Inserting 10mm json blobs with a bunch of indexes will be horrible. I’ve been as guilty as anyone for thinking the database can work miracles and that I don’t have to worry about how the underlying hardware works. But it’s that lack of care that is at fault, not the database engine. It’s just doing what you told it to do.
deleted 4y ago
[deleted]
samwillis 4y ago
The same can be said about ORMs... - You start of thinking, my problem is simple/unique so I don't need an ORM - You design your database schema, start building your application, inserting, updating, querying it... - First job is to write a few abstractions that represent a row in each the tables in your db. It handles deserialising/serialising from the database types to you languages types, plus a few of your custom types. - You find yourself duplicating code for simple select/update/insert/delete quires so you build another layer of abstraction so you have only one generic implementation of these that your table row abstractions use. - You start writing more complex queries based on user input, it starts to get a little complex with all the string concatenation. You write a query builder for one area of you application, but soon find yourself longing to use it elsewhere. So you generalise it to be usable on other tables. - Hand writing database migrations is simple to start with, but as your team grows, and you have more deployment environments, and more branches, it becomes a massive headache. Then one day someone runs the wrong migration and drops the main db... you write a database migration tool, reuse a bunch of your application table abstractions as the definitive source of truth for what your tables look like. Job done! Oops, you wrote an ORM... Obviously the world isn't that simple, and there are legitimate reasons not to use one. But the majority of the time it's better to start with an off the shelf ORM than not. The issue with ORMs really come down to restrictions on escape hatches, easy dropping down to SQL but with mapping back to your higher level abstractions.
- ludston 4y ago
  Certainly it depends on the use-case. ORMs are wonderful for CRUD style applications, but tend to be (in my experience) awful for reports. They also tend to discourage developers to think about query performance, because the ORMs query DSLs don't usually have first-class profiling.
  - BiteCode_dev 4y ago
    You missed the last sentence of the comment. Even with custom query, using escape hatch bring a lot to the table because you benefit from the ORM core handling system for configuration, serialization, pooling, etc. Not to mention some core (like sqla core) in fact include a functional query language that is capable of representing low level sql in any form. In which case you benefit even more, and you can do your report perfectly.
deleted 4y ago
[deleted]
warkanlock 4y ago
I believe the article has a wrong take on this, and it seems more like a rant to generate views than an actual analysis of why people write things that already exist in the wild It's not about spending time but considering all the actual real learning of making a complex piece of software (maybe if you focus too many dev cycles into it might become harmful). Still, at the end of the day, you will have a team of almost experts in X topic who can go back and even improve on what they are doing (using any other common, well-known framework) And yes, I agreed with the author that it doesn't make sense to write one just for the sake of it, but as Feymann said: "What I cannot create, I don't understand."
- doctor_eval 4y ago
  I think there’s a big difference between building something to learn about it, vs accidentally building something complex in order to support the thing you’re actually trying to build. The point is, just use a database. We’re talking 15 minutes of effort. Or to paraphrase someone wiser than me, 4 weeks of programming can save 15 minutes of installing.
- pictur 4y ago
  Welcome to the world of today's tech gurus. These guys are busy sharing what's wrong and what's right on Twitter all day. The number of people who talk about real world applications is almost non-existent.
  - swyx 4y ago
    for what its worth i was just writing a database complement to https://rachitnigam.com/post/you-have-built-a-compiler/ https://rachitnigam.com/post/you-have-built-a-compiler/ because i thought the format was really helpful in understanding why we independently evolve the same things from first principles. i’ve done some of these mistakes in real life, but adding anecdotes would have spoiled this particular story format.
mkl95 4y ago
I used to write small 2D engines when I was a teenager. I had no clue about databases at the time, so I ended up writing a NoSQL-ish database to save games. I soon learned that rolling your own db is looking for trouble.
ianpurton 4y ago
While we're on the subject of ORM's I really like the https://github.com/cornucopia-rs/cornucopia https://github.com/cornucopia-rs/cornucopia way of doing things. Basically write SQL in a file and code generate a function that runs the SQL for you and puts it into a struct (this one is for rust) I think there's a library to do the same thing with typescript. For me, the best way to talk to the database is with SQL and I don't have to learn an ORMs way of doing it.
- TN1ck 4y ago
  I used sqlc[1] for golang, while it’s very cool and keeps things simple, at the end I switched to gorm as I needed hooks and dynamic queries where annoying to write. I could have build these things myself on top, but that would have been a homegrown orm in the end. But as every orm, gorm also has its problems, pick you poison. [1] https://sqlc.dev/ https://sqlc.dev/
crabbone 4y ago
Where I see this happen a lot: testing. Initially test automation people think about tests as stateless, and so don't design the framework to have any persistence at all. Eventually, life proves that to be wrong, and test start needing some persistence between sessions... well, it usually starts between different cases, and that's where AQA start building ad hoc "databases". Unfortunately, it almost never downs on them that what they needed all along was a database, so the tests grow a weird slow and buggy persistence layer.
- swyx 4y ago
  this is a brilliant point, i wish i had thought of it and figured out a way to work it into the setup of the story. done this one myself enough times. newer databases like planetscale now offer “branching” which in theory lets you use a copy of production data for tests. not really sure when this does/doesnt make sense but it seems interesting
born_to_flirt 4y ago
[dead]
OneTruePerson 4y ago
[dead]
DungeonDevil3 4y ago
[dead]
SoHapppyy2 4y ago
[dead]
SoHapppyy2 4y ago
[dead]
ImImpatient4 4y ago
[dead]
SoExcited 4y ago
[dead]
FairyTail9 4y ago
[dead]

105 points by swyx 4y ago

I have a huge amount of sympathy for people who accidentally attempt to implement a database. I believe they are usually motivated by their desire to avoid SQL - an extremely worthy goal. The industry, by and large, puts a crazy amount of effort into avoiding SQL by writing ORMs, translation layers and similar tech because it really is that bad. But unfortunately databases have solved common-as-mud problems to do with data, disks, multiple users (which, if your data is worth storing, you will eventually have) and the interactions thereof. It is worth putting up with SQL to get all the lessons of the 70s, 80s, 90s, 00s, 10s and now 20s in how to store data without creating buggy software. People probably have these problems and it is usually faster to solve them the usual way that works. Write some SQL and maul a stress ball, it'll be easier long term.

JPLeRouzic 4y ago
> "The industry, by and large, puts a crazy amount of effort into avoiding SQL by writing ORMs, translation layers and similar tech " In my experience (I am retired from a telecom operator) using a database made applications quite inflexible yet real life implies constant changes in their data model and as a result when there was a "database migration" there was always some information lost. On contrary Pick databases (MultiValue databases) were very useful. https://en.wikipedia.org/wiki/Pick_operating_system https://en.wikipedia.org/wiki/Pick_operating_system
- sirtaj 4y ago
  I'm of the opposite opinion. Database migrations force you to think through the changes in your data more explicitly. You catch logic oversights, boundary conditions and implicit transformations much more thoroughly and with fewer errors later on.
- joshka 4y ago
  I've seen a lot of different enterprise systems. In my experience it's not the db that's causes this inflexibility issue, but designing the architecture of systems data-first (using Data Dictionaries / ER diagrams etc.) rather than use-case-first that leads to this sort of problem. When a data schema is the foundation of a system, it's often the most difficult thing to change.
  - doctor_eval 4y ago
    I don’t know how that works. Your data schema is the foundation of almost any system, regardless of how you generate it. I think this is a common mistake. People think their code is what’s important. But it’s the data and how it gets transformed that is the core of most applications.
    - esailija 4y ago
      Data-based architecture means that you have all data about an entity in a single place. Even though if you analyze the domain and all use cases you find that there are slices of that data that never interact together. Example is storing customers address and purchase history in same place, only because they "belong to customer" in a data sense. This is very bad because it leads to entangled architecture where everything depends on everything and you cannot extract truly independent modules. The project implemented like this simply cannot scale beyond a certain size. So no, data is not important but how it is used.
      - doctor_eval 4y ago
        Ok. Perhaps I’m not familiar with this concept of “data-based architecture”. The GP talked about designing around features, which I think is also bad. But maybe they meant more like a DDD approach? So - I certainly agree 100% with what you say about keeping thing independent. Maybe I’m just unfamiliar with the terms.
    - jen20 4y ago
      For this to be true (which it is in some contexts), the important question is what the boundary of the "system" is.
- doctor_eval 4y ago
  I have done, quite literally, thousands of database migrations in a telco context and we never - not one time - lost information. I don’t think there is any particular problem, or solution. Applications get old, assumptions change, the world moves in. It’s true that our database design became inflexible over time and needed to be hoisted. But so too did our authentication layer (hand built - JaaS - JWT), the UI (AWT - Swing - Web 1.0 - Web 2.0), the deployment model (on prem - cloud - docker), even the API scheme (RMI - soap - rest - graphql). Any application that lives long enough is going to go through this sort of change. The only constant in all of the above was SQL.
  - ttfkam 4y ago
    Beware the old man in a young man's game. You will be tempted to underestimate him. As many unremembered before you have done.
pharmakom 4y ago
SQL is pretty good though. It could compose better, sure, but it’s well suited to data queries. I think people dislike learning a new language, even to the point of writing bad software. I wonder why?
- d33 4y ago
  I'd say that pretty much everything about SQL is bad. Apart from composability, the syntax is super annoying (the ordering of keywords), many databases have pretty much no error reporting if you get it wrong and the linters suck. You can't unit test it. It's not very well standardized and it's actually more than one language (DML vs DDL). Plus, it's not very readable. And the declarative nature of the language is eventually going to back-stab you when you discover yet another edge case of your query planner that flips a reasonable plan to one that's literally going to take ages.
  - pharmakom 4y ago
    You can unit test SQL by spinning up Docker containers. You should just write SQL for the database you are using (e.g. PostgreSQL). Switching databases without changing queries is unrealistic anyways. I agree with your other points, but I am yet to see a layer above SQL that improves on them enough to justify its additional complexity.
    - CHY872 4y ago
      I know a team who used JooQ (code-generated query builder for Java) to generate SQL which worked on both Postgres and Oracle and worked well enough that they could support both in production (Oracle for top end perf, Postgres for everything else). There were certain places where they had to switch on SQL format, but over years they felt comfortable enough only unit testing on one kind of database.
  - CHY872 4y ago
    Few things: Regarding unit testing, it's now very possible to productively unit test a SQL database - I'm familiar with a Java package that boots up an embedded PostgreSQL, obviously Docker is now in vogue, etc, etc. It's not quite 'just run a function', but you write your code in the same style as a unit test and you're running hundreds of tests in seconds. You can use transactions to automagically clear out your state, which means performance is also excellent. Regarding the standardization of the language - no, it's not totally standard (although arguably that's a good thing, we don't want 10 implementations of exactly the same thing, they'd never evolve), but because they all work sort-of-the-same, a lot of language support ends up transferrable. You can use ActiveRecord on many backing databases, for example. Query planner problem is definitely true, know folks who've spent a lot of time hinting Oracle and scaring Postgres into the same, though personally only took a few days. However, SQL is IMO the best of a bad bunch. Most non-SQL databases I've used have been extremely sharp cornered, whether this be via: unpredictable/unbounded worst case performance in pessimal storage cases (RocksDB, Cassandra), various parts of the code where unbounded memory/storage usage is required (Cassandra, Spark, Elasticsearch), extremely low level APIs making it easy to make unobservably (as in, you won't notice it and will eat the pain every read) slow APIs (RocksDB). I'm not saying there's nothing better, but it's probably not an all-rounder. There are certainly things I don't like about most SQL databases. Few SQL databases are columnar, which is _the_ key to good performance in the 2010s, and native LZ4-style data compression is also not widely used. But for most engineers they solve a lot of problems for you without having to think about them.
kindofabigdeal 4y ago
How is SQL bad? If anything my experience with KV db's is that most of the time they'll eventually end up implementing some form of structured data storage and retrieval that in some way resembles SQL. I love KV's only when I used them as embedded db's like pebble and badger db but you can never beat a good ol' SQL server that just does what you need from it nothing more nothing less. ORM's most of the time aren't that great either you always have to keep up with their new fancy API changes and instead of just doing your work you find yourself relearning the same ORM every few years, not only that but some ORM's are extremely sensitive in how you order your operations that the order in which you write you code can potentially kill your performance or worse accidentally deleting data.
crabbone 4y ago
Why is SQL bad? I'll take SQL over almost any language used to write the "application layer" any day... Also, why do you think that databases solved problems with disks? Do you mean disks as in block devices? If so, none of the popular databases ever touches that level, most popular SQL databases work with filesystem.
- setr 4y ago
  It doesn’t compose properly, lacks type safety, you have to codegen your statements in the application anyways and is generally an utter bitch to do so because it’s horrifically inconsistent (and doesn’t compose properly). You can dump everything into stored procs and functions, but now you get worthless compiler errors, indirect version control at best, and really your whole tooling support is reverted to the 80’s. It’s a pretty shit language — its just the only one we get to have, and the value of what it interfaces with (the RDBMS itself, ACID and the relational algebra) is… invaluable. You can’t get away with not knowing it, and it’s generally dumb to avoid it (because you’re then avoiding the glory behind it) but as a language it’s basically COBOL-tier.
  - crabbone 4y ago
    > It doesn’t compose properly Stored procedures? > lacks type safety How so? Can you give some examples? I have no idea what this is even about. > you have to codegen your statements Never happened to me. Not in any capacity that I would feel like this was a burden. The only cases where I needed to generate SQL code were some fuzzing tests intended to figure out problems with database performance, s.a. feeding a bunch of generated SQL to pgbench to see what may cause the database to slow down / use too many resources. This seems very strange in an application that is simply using SQL database rather than trying to do something weird with it (eg. testing performance). SQL is a very high-level language, which means you don't need to do as much code generation compared to, say, Assembly or even Java. I do see that people do this when they use ORM, but this is a self-inflicted pain. They shouldn't have been doing ORM to begin with. > get worthless compiler errors From what? Are you talking about writing SQL functions (eg. in Java) and then using them from SQL (eg. Oracle)? This doesn't sound like a SQL problem, more like Java compiler problem... But, yeah, I can see how this is a complication and an inconvenient thing to do, so, if that's what you mean, I'm generally with you on this one. Well, I think that the benefits are still worth doing this. > whole tooling support is reverted to the 80’s. Again, I don't see the connection. But, even if it was so... what's the problem? You get to work with an old and reliable technology, with decades of knowledge and experience to support it. And it's not like it died in the 80's. Tons of effort is being invested into this technology, it's amongst the first to get updates and integrations with new hardware and system capabilities (eg. vectorized ISAs support, even GPU access, bet you that when "smart SSD" become common enough, SQL databases will be the first to support them). What's not to like? This is like complaining about C compilers for being "old tech"... for all that's wrong with C (compilers), being old is not one of those things. > It’s a pretty shit language If you were honest, you'd write it like this: "I don't like it". And, well, you are allowed not to like whatever you want. It's subjective, and completely up to you. It just has less of a convincing power. But, really, this is your entire argument...
eternityforest 4y ago
I don't see anything particularly bad about SQL. If your problem is a recipe manager CRUD it's trivial. If it's more complex then than, any DIY thing is gonna be hard too. If you need some insane performance, SQL might not be right but any DIY thing will still be hard.

vouwfietsman 4y ago

This is not a bad take, but then every so often I have to insert 10 million rows into a database and for some reason that takes way longer than it should, and now I'm stuck figuring out the particular knobs and buttons needed to make it consume a reasonable amount of time and space. Most databases do suffer from "common denominator" syndrome, and you need to know a LOT, or be comfortable with hybrids, to get the right DB for the job if you want to avoid writing your own.

iknownothow 4y ago
What you're describing is the common symptom among many programmers which is their unfamiliarity with disk limitations. It's not the database's fault for not being able to write 10M rows to the disk quickly. Disks, especially the cloud SSDs are quite slow defaulting very often to just 3000 write IOPS. On the other hand, programmers are very familiar with CPU and memory limitations. Very rarely will a programmer try to hold 10M or 1B rows in memory because they intuitively understand memory limitations which is usually around 4GB per app/process.
- href 4y ago
  This. I moved multiple web applications from bare-metal to the cloud, and many times had to refactor code to do hit the disk less. What worked before: Dozens of SQL queries, or loading session data from a file - for each request - it all was too slow in the cloud. Also: I find that many engineers wouldn't be able to tell you if the storage attached to their VMs was networked, or actual hardware on the hypervisor. A distinction that has big implications for throughput and latency.
- doctor_eval 4y ago
  Another thing I’ve seen (perhaps even been guilty of :) is assuming we don’t have to worry about our table or index structure. Inserting 10 million narrow rows of integers and enums into a database with just a Pkey index will be super fast. Inserting 10mm wide rows containing json blobs and long text strings will be bad. Inserting 10mm json blobs with a bunch of indexes will be horrible. I’ve been as guilty as anyone for thinking the database can work miracles and that I don’t have to worry about how the underlying hardware works. But it’s that lack of care that is at fault, not the database engine. It’s just doing what you told it to do.

deleted 4y ago

[deleted]

samwillis 4y ago

The same can be said about ORMs... - You start of thinking, my problem is simple/unique so I don't need an ORM - You design your database schema, start building your application, inserting, updating, querying it... - First job is to write a few abstractions that represent a row in each the tables in your db. It handles deserialising/serialising from the database types to you languages types, plus a few of your custom types. - You find yourself duplicating code for simple select/update/insert/delete quires so you build another layer of abstraction so you have only one generic implementation of these that your table row abstractions use. - You start writing more complex queries based on user input, it starts to get a little complex with all the string concatenation. You write a query builder for one area of you application, but soon find yourself longing to use it elsewhere. So you generalise it to be usable on other tables. - Hand writing database migrations is simple to start with, but as your team grows, and you have more deployment environments, and more branches, it becomes a massive headache. Then one day someone runs the wrong migration and drops the main db... you write a database migration tool, reuse a bunch of your application table abstractions as the definitive source of truth for what your tables look like. Job done! Oops, you wrote an ORM... Obviously the world isn't that simple, and there are legitimate reasons not to use one. But the majority of the time it's better to start with an off the shelf ORM than not. The issue with ORMs really come down to restrictions on escape hatches, easy dropping down to SQL but with mapping back to your higher level abstractions.

ludston 4y ago
Certainly it depends on the use-case. ORMs are wonderful for CRUD style applications, but tend to be (in my experience) awful for reports. They also tend to discourage developers to think about query performance, because the ORMs query DSLs don't usually have first-class profiling.
- BiteCode_dev 4y ago
  You missed the last sentence of the comment. Even with custom query, using escape hatch bring a lot to the table because you benefit from the ORM core handling system for configuration, serialization, pooling, etc. Not to mention some core (like sqla core) in fact include a functional query language that is capable of representing low level sql in any form. In which case you benefit even more, and you can do your report perfectly.

deleted 4y ago

[deleted]

warkanlock 4y ago

I believe the article has a wrong take on this, and it seems more like a rant to generate views than an actual analysis of why people write things that already exist in the wild It's not about spending time but considering all the actual real learning of making a complex piece of software (maybe if you focus too many dev cycles into it might become harmful). Still, at the end of the day, you will have a team of almost experts in X topic who can go back and even improve on what they are doing (using any other common, well-known framework) And yes, I agreed with the author that it doesn't make sense to write one just for the sake of it, but as Feymann said: "What I cannot create, I don't understand."

doctor_eval 4y ago
I think there’s a big difference between building something to learn about it, vs accidentally building something complex in order to support the thing you’re actually trying to build. The point is, just use a database. We’re talking 15 minutes of effort. Or to paraphrase someone wiser than me, 4 weeks of programming can save 15 minutes of installing.
pictur 4y ago
Welcome to the world of today's tech gurus. These guys are busy sharing what's wrong and what's right on Twitter all day. The number of people who talk about real world applications is almost non-existent.
- swyx 4y ago
  for what its worth i was just writing a database complement to https://rachitnigam.com/post/you-have-built-a-compiler/ https://rachitnigam.com/post/you-have-built-a-compiler/ because i thought the format was really helpful in understanding why we independently evolve the same things from first principles. i’ve done some of these mistakes in real life, but adding anecdotes would have spoiled this particular story format.

mkl95 4y ago

I used to write small 2D engines when I was a teenager. I had no clue about databases at the time, so I ended up writing a NoSQL-ish database to save games. I soon learned that rolling your own db is looking for trouble.

ianpurton 4y ago

While we're on the subject of ORM's I really like the https://github.com/cornucopia-rs/cornucopia https://github.com/cornucopia-rs/cornucopia way of doing things. Basically write SQL in a file and code generate a function that runs the SQL for you and puts it into a struct (this one is for rust) I think there's a library to do the same thing with typescript. For me, the best way to talk to the database is with SQL and I don't have to learn an ORMs way of doing it.

TN1ck 4y ago
I used sqlc[1] for golang, while it’s very cool and keeps things simple, at the end I switched to gorm as I needed hooks and dynamic queries where annoying to write. I could have build these things myself on top, but that would have been a homegrown orm in the end. But as every orm, gorm also has its problems, pick you poison. [1] https://sqlc.dev/ https://sqlc.dev/

crabbone 4y ago

Where I see this happen a lot: testing. Initially test automation people think about tests as stateless, and so don't design the framework to have any persistence at all. Eventually, life proves that to be wrong, and test start needing some persistence between sessions... well, it usually starts between different cases, and that's where AQA start building ad hoc "databases". Unfortunately, it almost never downs on them that what they needed all along was a database, so the tests grow a weird slow and buggy persistence layer.

swyx 4y ago
this is a brilliant point, i wish i had thought of it and figured out a way to work it into the setup of the story. done this one myself enough times. newer databases like planetscale now offer “branching” which in theory lets you use a copy of production data for tests. not really sure when this does/doesnt make sense but it seems interesting

born_to_flirt 4y ago

[dead]

OneTruePerson 4y ago

[dead]

DungeonDevil3 4y ago

[dead]

SoHapppyy2 4y ago

[dead]

SoHapppyy2 4y ago

[dead]

ImImpatient4 4y ago

[dead]

SoExcited 4y ago

[dead]

FairyTail9 4y ago

[dead]