> "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

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.

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.

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.

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.

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.

For this to be true (which it is in some contexts), the important question is what the boundary of the "system" is.

I agree.

On reflection, I'd also add that I do design systems "data first" however to me this doesn't mean "database first". It's important to think about how data flows through the system, how it gets transformed, and what parts of the system "own" which data.

All of this requires understanding of the boundaries of the various systems that make up the whole, which I think is exactly your point.

A more specific example is the idea of sitting down and listing all the attributes that define a customer profile (name, addresses, date of birth, etc) and then designing the CRUD around those things. Contrast this to starting with what are the interactions that a customer actually needs to worry about (send me this product to the address in my PayPal* account)

*or whatever external system.

If you had to make a guess at how many different pieces of information you have about you in databases for services worldwide (and how many of those are inaccurate, or unnecessary to provide the service), that number would be fairly ridiculous for most people (OOM thousands or tens of thousands).

Taking the address idea - no e-commerce site really needs to store this as a profile item. It needs to be in my browser so I can autocomplete for any shipping form.

The second point I’m making is the same as your last. “How it gets transformed” is often more important to start with than how it is at rest.

I actually agree with all of this.

Ultimately where I’m coming from is that the flow of data through a system and how it is transformed is actually what’s important. For me that’s taking a “data first” approach, to use your term, but I took the term to mean something different to what you meant.

You are totally right about things like customer info. If you have an email component then that’s where email addresses live. If you have a fulfilment component then that’s where physical addresses live. 100% agree that they don’t belong together, at least not by default.

In fact, I tend to think of entities like customers as just a uuid. The bits and pieces we attach to that uuid depend on the system. But many domain objects should be treated in a very abstract way.

I’m not sure this is widely accepted, but it’s where I got to.

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.

Beware the old man in a young man's game. You will be tempted to underestimate him. As many unremembered before you have done.

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?

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.

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.

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.

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.

> I'm familiar with a Java package that boots up an embedded PostgreSQL, obviously Docker is now in vogue, etc, etc.

I think you are talking about Testcontainers, although it only matches the second part of the comment (can manage different external moving parts in containers for integration tests): https://www.testcontainers.org/

I'd be interested in seeing an embedded Postgres library; I only see H2 and maybe Derby being used for tests and "getting to know the ropes" use cases, eg. a default install.

Pretty sure they are talking about https://github.com/zonkyio/embedded-postgres-binaries. It's not exactly embedded postgresql since it still runs the full postgresql in a separate process but it is close enough for most purposes.

Cool, thank you!

Let’s pretend at least half of this is true. With that, ORM pretty much adds a layer of complexity on top and hides it, opening a door for a lot more well complex behaviour. That’s leaky abstraction. As often is the case, adding on top of that person using an ORM without actually knowing _any_ SQL and/or what’s behind ORM just rolls the ball more into the mud.

So on one hand, most of the times people are doomed to re-invent an ORM.. poorly. On another, if you don’t have people knowing behind the scenes using “a proper ORM”(tm) they will roll mud around, and if you do have people knowing, most will object ORM in the beginning and start, over time, to roll their own. Overall, there must be a thin line where one should walk on, but it’s unclear exactly where since it’s highly project-specific.

Here we go…

SQL is composable through views, CTEs, temp tables, and set-returning functions. It's a set-oriented language, so its composability is going to be based on sets, not the constructs in your favorite general purpose programming language using a completely different paradigm. Just because that's unfamiliar to you doesn't deny its composability. Impedance mismatch.

Regarding syntax, the best "solution" I've seen are query builders that put the FROM first but fail to fully implement the full range of functionality available to an engine, mostly due to catering to the lowest common denominator of functionality. (Looking at you, MySQL!)

This "I hate the syntax" is generally a petty argument. No popular multiuser database allows direct access to its internals, but I don't see Cypher or Mongo's QL getting anywhere near the same level of critical scrutiny SQL gets, and it's not due to their clarity, elegance, or comprehensiveness. You simply want a functional (or OO or whatever) syntax model, and it's not gonna happen because the problem SQL is solving doesn't match up. Impedance mismatch.

Unit testing: https://pgtap.org/ https://sqitch.org/docs/manual/sqitch-verify/ https://learn.microsoft.com/en-us/sql/ssdt/creating-and-defi...

SQL linters suck, I'll grant you that one. At least the syntax and error highlighting is there for good editors like DataGrip, but folks have a hard time accepting a universal style guide for their SQL though it's sorely needed along. Most folks just cobble together stuff with haphazard newlines and spacing then complain about how no one can read each others' SQL.

Wait… you're pissed there's a different set of keywords for generation and modification of structure vs generation and modification of data? You think… they should be the same… because… you think they're related?

> Plus, it's not very readable.

Ever notice how someone in every thread on every language says, "I hate the syntax. I think it should look like <insert personal favorite language>." Just accept that your difficulty with the language is a personal preference or reflection of your familiarity with it, not some kind of objective fact. It's like claiming Chinese is less understandable than English when you were born and raised in London.

> …when you discover yet another edge case of your query planner…

That's not SQL's fault. Any query language would do this. If you had to write accessors by hand such as for DynamoDB, you'd find that the original algorithm you wrote is no longer effective because the dataset you're querying got much larger. Fix your indexes, vacuum, and reset statistics. Stop blaming your tools.

You don't like SQL? That's fine. But that hatred is also not universal. I and many other folks LOVE SQL. That isn't to say it's flawless or cannot be improved. But most folks always seem to focus on those aspects that probably don't matter as much, missing the relational forest for syntactical trees.

"Beware the old man in a young man's game. He is always much more capable than he seems."

> It's like claiming Chinese is less understandable than English when you were born and raised in London.

There is a proverbial consensus that written Chinese is very difficult to understand [0, 1]. It is a supremely challenging script. A well educated Chinese person can conceivably encounter words in a written text that they know the meaning of but they cannot understand or verbalise without consulting a reference book.

[0] https://en.wikipedia.org/wiki/Greek_to_me

[1] https://flowingdata.com/wp-content/uploads/2015/04/Greek-to-...

And folks speaking any of the variations of Chinese speech, eg. Cantonese, can still read the ideographs. That's the tradeoff.

Whereas in English, a well-educated American can (and does) conceivably encounter words in written English that they neither know the meaning of, understand, or pronounce correctly without consulting a dictionary.

Hell, English pronounces the exact same written words in wildly different ways as well as multiple words spelled differently but spoken identically to the point that a non-trivial part of the written language is merely memorized rather than learned.

But we learned English when we were young, so I guess it seems "normal" that "Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo" is a grammatically correct sentence.

https://en.wikipedia.org/wiki/Buffalo_buffalo_Buffalo_buffal...

Or how animal poops can have so many semantic meanings.

https://youtube.com/shorts/z_AGi2diHt8?feature=share

I wish SQL operated on sets but it really operates on tables that have an order and may contain duplicates.

LATERAL, VALUES, et al would object to that narrow designation.

Tables are just sets of records. Nothing more. Nothing less. SQL references tables as sets of records, but JOINs are referential subsets of tables, not complete tables themselves. And LATERALs may not even have a table underneath at all! Same with set-returning functions. generate_series(…) doesn't have any table under the covers; it's purely a dynamic set of values generated on the fly.

It's turtles all the way down.

As for duplicates, that's up to your defined constraints to sort out. You're right that SQL DML knows nothing about whether the underlying set of records contains duplicates. The DDL however can trivially define a set of records to have no duplicates based upon varying conditions/attributes.

Whether a set is ordered or not or has duplicates are not has no bearing whatsoever on whether the data is set-oriented or not.

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.

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.

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.

> 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...

Yeah, the "lacks type safety" comment threw me too. SQL has some of the most rigid type safety in my daily interactions in computing.

    • "Exactly one"
    • "Optional"
    • "Text but matching this pattern"
    • "A 16-bit integer from 1 to 1000"
    • "Many to many without any duplicates"
    • "A unique combination of id and a time range"
    • "This field can be NULL but only if this other field is NOT NULL"

Now store a 64-bit unsigned integer in Postgres.

CREATE DOMAIN unsigned_64bit_int AS numeric CONSTRAINT range CHECK (VALUE >= 0 AND VALUE < 2^64 AND round(VALUE) = VALUE);

Use bigint with check-between constraint?

Maybe inconvenient, but not impossible... but if this is the kind of problem you have, then, try in your favorite language to create integers that are 63 bit long or 65, and see how it goes? Looks like the comparison won't be in that language's favor.

Postgres bigint is a signed 64-bit integer, not an unsigned one. https://www.postgresql.org/docs/current/datatype-numeric.htm...

Trying to use it to store the full range of unsigned values would mean awkward math everywhere, to convert between the two ranges.

Lots of software uses uint64s, for e.g. as identifiers handed out by some API, and not being able to store them conveniently definitely hurts.

Now store a 64-bit unsigned integer in Java. Or TypeScript. Or Lua. Or Python.

Now explain why you need an unsigned bigint in this set-oriented language. What's your use case?

That said, not a limitation of SQL, just Postgres, who avoided it for similar maintenance and interoperability issues that Java avoided them. MySQL supports unsigned bigint just fine. There is nothing in the SQL spec that forbids unsigned integer values.

Apparently there's an extension for that! https://github.com/petere/pguint

SQL, as a set-oriented language, composes just fine.

• views • CTEs • temporary tables • set-returning functions

It will not compose like an OO language or like a functional language because it isn't one of those. It's set-oriented. This is the biggest hangup I've seen from folk who hate SQL: mistaking it for something it isn't, never was, and was never intended to be given its problem domain.

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.