Testing SQL is easy. E.g. the JVM has H2, which works for simple stuff, or you can use testcontainers, or just spin up a container and run your tests against that. You just run your migrations, insert mock data, and run your test.

In fact testability is one of the best parts. You can safely test read-only queries against a prod secondary database to see that it gives reasonable results on real data, and use the repl to explore parts of your query to really get a sense that things are working.

SQL queries yes, what Bury above probably is talking about is obscure triggers/stored-procs,etc that encodes the business logic inside the database far away from any version control or sane ways to track it.

Tracking triggers etc is pretty straightforward, just put them in a repo and apply them as part of a schema change. Testing them can be tricky, as unit tests aren't as likely to capture the locks/time that can be an issue when the database is under load. Tools like pgreplay can be helpful here.

It's not like tracking events in application logic is somehow just a bucket of unicorn farts and rainbows. At least triggers are deterministic and tracked within transactions. Can't tell you how often folks update the DB with an ORM in application logic but forget to update dependent records in other tables in their one-off branch of logic.

Sometimes it's a hard problem.

That said, I think triggers need very well-maintained dependency charts in the docs/comments to ensure they don't ever go cyclical. Those are bad days.

(But again, events in app logic are in no way shape or form immune to cyclic runaways.)

Agree re trigger maintenance

Re orm, I'm strongly biased towards only using them when they (1) are absolutely necessary or provide some extreme benefit (2) are able to output the raw SQL to be executed on the db (3) the SQL to be executed is understood.

I've seen them used as a substitute for understanding SQL/databases and, personally, think that's a pretty bad idea.

And in any other language this wouldn’t result in multi-thousand lines of code with historical business logic and edge cases?

The difference is that most people don't write their business logic in a single flat function in most programming languages, and there is a good chance that individual pieces would have tests.

Neither do good SQL developers: you can easily structure database logic in multiple stored functions, stored procedures, packages, and multiple files. Unit test frameworks are also available.

Testing code is pretty popular in most non-SQL environments.

Testing SQL is pretty popular in most pro-SQL environments as well. There are many tools for that even if many developers appear to remain willfully ignorant of them.

Glad that doesn't happen any where else!

Most other programming languages are more composable than a SQL query. You can make functions, split logic into different classes, extract common logic, etc. Much harder with big sql queries

You can define functions in all major databases

And how many non-technical users do you know who do this?

And how many non-technical users do you know that write good javascript/java/.net/etc code?

I think SWEs think of databases only as a kind of generic persistence layer and aren't super interested in a lot of the details (more than one has said to me databases are just an implementation detail) or additional capabilities, which I think is a very limited view. Databases are essentially interpreters, just like Python or Ruby. They come with access to a multiuser persistence (etc) service or engine (super handy!) but offer all the affordances of other interpreters. In fact, plpython on PostgreSQL will just use the Python packages on your system.

Do you have to read about it? Sure, just like all programming languages and platforms. But, interestingly, SQL was designed for non-technical analysts, so (to your point) it's more accessible than those others.

Mediocre coders fixate on algorithms. Great coders concentrate on data structures.

SQL schema creation and maintenance is all about choosing the right data structures for the job at hand.

If you neglect attention to your data structures, no algorithms will save you, and you really only have yourself to blame. It's a still to be learned and honed like any other.

Use views and stored procedures if you need composability.

Counterpoint: don’t do this. The path to hell is paved with good intentions.

Some co-workers and I inherited a code-base where the authors went down the views and stored procedures route. It was basicallly impossible to untangle; there was no knowing what relied on a view or a proc, so you couldn’t touch them at all, there were no docs, there were duplicates of everything (and again, no way to know what’s used). A good number of them had all kinds of side effects and weird performance characteristics. They weren’t version controlled.

The issues just kept going.

If your colleagues are not applying basic engineering rules to views and stored procedures, it's not fair to say that this is a problem with views and sprocs.

They can and should most definitely be testable, documentable, trackable and version controlled.

Should one also judge all backend programming by PHP standards circa 1998?

I tend to judge things by the average-worst-case that they enable and how easily things go wrong, and IME stored procs specifically, go badly real quick.

Data structures can be a hard part of programming. That doesn't mean folks don't chose the wrong data structures all the time in any language.

An array instead of a linked list or vice versa. A dequeue instead of a set or vice versa. A binary tree instead of a B+ tree. You can't just throw column names at a DB schema and hope for the best. There's actual engineering to be done, even if many programmers refuse to admit it when interacting with a relational database engine.

You're blaming the language for the bad coding practices of the developers. You can build maintainable database code, and modern SQL IDEs like Datagrip provide similar tools for SQL like you have for other languages, e.g., refactoring, dependency/usage info, versioning, etc. The downsides you mention are not inherent to SQL.

What you need is a semantic layer on top of SQL

SQL is the semantic layer on top of relational algebra. Adding another layer on top is like having Google translate from Spanish to Russian before translating back to English. Each level of indirection just opens the door for more nonsense to creep in.

You can write code in any language without defining functions or modularizing in any way. You can write bad code in any language.

For some reason, folks assume SQL must be written badly since they have only written it badly or seen it written so.

It is absolutely possible and preferable to write maintainable SQL logic into user defined functions, stored procedures, views, materialized views, CTEs, temporary tables, etc. If you're looking at one huge pile of monolithic, untestable SQL, the problem isn't the SQL.

One doesn't write O(n^3) algorithms in C++ and then blame C++ for it being slow. For some reason, folks seem pleased with themselves to do as such with SQL though every day and twice on Sunday.

Got subselects in each of the fifteen outer joins with NULLs all over your schema, and now you're upset performance is horrible and inconsistent? PEBKAC.