Hacker News Reader

Comment by rjbwork | Hacker News Reader

Until any other RDBMS has the combination of now-basic features like SQL:2011, declarative schema design with terraform-like plan/apply migrations (ssdt, azure data studio, DACPAC, etc.), easily configurable hosted cloud databases, actual good MERGE syntax, and more, I'll keep using it. Attempting to use Maria was a nightmare.

I'd like Postgres more (and really want to) if they would at least implement more basic stuff like SQL:2011 and/or there was a good hosted deploy story - RDS/Aurora is fairly complex.

View on HN

I wonder why you are so focused on SQL:2011? Since then there was SQL:2016 and now there is SQL:2023.

Also, which features in particular are you missing in PostgreSQL? Merge was added with PostgreSQL 15 a year ago: https://modern-sql.com/caniuse/merge

Temporal tables, most likely. Although even SQL Server only supports system time versioning, not full bitemporal tables (as per the spec).

The existing implementations (Oracle DB, SQL Server, MariaDB, Big Query) come with their problems too. I was a big fan of the new features when it came out in 2011, but pratically there is an unsolved elephant in the room: It doesn't cover schema changes.

> there is an unsolved elephant in the room: It doesn't cover schema changes.

100% agreed. It's remarkable how Datomic also arrived on the scene in the same era (2012) but actually managed to solve a lot of these hard issues of immutable versioning + schema evolution via a clean EAV-based information model and an emphasis on accrete-only schema changes.

I'm a big fan of your work by the way :)

2016/2023 got pretty widespread support across databases for the functionality I care about, both before and after adoption - namely the JSON stuff. MSSQL also already had pretty good graph support AFAIK, although I haven't used it that much.

I find the temporal table stuff really useful and they drastically simplify a number of requirements, so it's annoying that the only non-proprietary DB that supports it is maria.

> actual good MERGE syntax

While not the shit-show of bugs it was upon introduction in SQL Server 2008, there are still reasons to be careful with MERGE in SQL Server: it can still deadlock with itself in some circumstances, has issues with filtered indexes, can cause problems with CDC (wrong operation(s) get logged), …

(I actually like SQL Server, but the implementation of MERGE found there-in is certainly not one of the things I like about it!)

Why won't SQL Server implement the ANSI standard for Persistent Stored Modules (PSM)?

https://en.wikipedia.org/wiki/SQL/PSM

This is so pervasive in both free and commercial databases, that it is a gaping hole in SQL Server where obvious functionality should be.

I have thousands of lines of this stuff that will never see the light of day in a conversion because of Sybase Transact-SQL.

Why is Microsoft addicted to this very much not standard language, that they did not even originate?

Could you expand on the declarative schema features? Isn't SQL more or less declarative by default?

You can compare the database schema in source control against what's been deployed, and automatically generate deployment scripts.

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpa...

The APIs were historically part of Visual Studio, and only split out into dacfx recently.

Someone braver than me could consider automated deployment from github actions etc.

The hard part is that a lot of database schema changes cause data truncation (e.g. reducing the size of a column or re-ordering columns).

You still need someone to review the generated code if it throws an error for potential data loss.

>You still need someone to review the generated code if it throws an error for potential data loss.

Yeah, exactly what I meant by the terraform-like plan/apply stages. You generate the change script and save it as your "plan", and then have an apply step that takes a backup and runs the script.

Reply on news.ycombinator.com