I have put SQL DDL+DML+stored procedures in version control, create/run stored procedure (TDD) unit/integration tests on mock data against other stored proceedures, had pass-fail testing/deployment in my CICD tool right alongside native app code, and done rollback, all using Liquibase change sets (+git+Jenkins).

Using Liquibase .sql scripts for version control isn't hard. Testing is always more-work but it's doable.

I don't completely disagree with you on rollback though as hard, at least full pure rollback-from-anything. Having built tooling to do it once with Liquibase I found the effort to guarantee rollback in all circumstances took more effort than it was worth. A lot of DDL and code artifacts and statements like TRUNCATE are not transaction safe and not easy to systematically rollback. Liquibase did let you specify a rollback SQL command for every SQL command you execute so you could make it work if you had the time, but writing+testing a rollback SQL command for every SQL command you execute wasn't worth it and is indeed materially more effort than just rolling back to earlier .war/.jar/.py/docker/etc files. (The latter are easier in part because they are stateless of course.)

In any case, something like Liquibase can get you a long ways if you have the testing mindset. (Basically it lets you execute a series of SQL changesets and you can have preconditions and postconditions for each changeset that cause an abort or rollback.)

And SQL doesn't give you the tools to do that. There's no easy or natural way to split a statement into smaller parts.

Simplest is to use WITH clauses (common table expressions/CTEs). They can help readability a lot and add a degree of composability within a query.

Second, you can split one query into several, each of which creates a temporary (or intermediate physical) table.

Third, you can define intermediate meaningful bits of logic as views to encapsulate/hide the logic from parents. For performance you materialize them.

Fourth, you can create stored procedures which return a result set and like any procedural or functional language chain or nest them.

These techniques are available in most databases. More mature databases support forms of recursion for CTEs or stored procedures or dynamic SQL.

As with most programming, proper decomposition and naming helps a fair bit.

Recently, I've learned that SQLServer supports synonyms. So you version functions / procedures (like MySP_1, MySP_2, etc...) and establish a synonym MySP -> MySP_1. Then you test MySP_2 and when ready, change the synonym to point to MySP_2. Of course, all code uses just the synonym.

https://github.com/vippsas/sqlcode