I remain baffled that standard SQL isn't more supported by some of the newer tools coming out. If you are targeting a standard SQL dialect, it is basically trivial to standup an local database to test against during every build.
I remember using https://sqlfairy.sourceforge.net/ back in the day to help test locally against mysql/postgres, but deploy to oracle. There were hiccups, but it felt like the world would mostly converge onto smaller differences between the offerings and that kicking off a test database on every build should be easier as time goes on.
Instead, it feels like we have done as much as we can to make all of this harder. I loved AWS Athena when I was able to use it, but trying to figure out a local database for testing that supported the same dialect it used seemed basically impossible. It was baffling.
> I remain baffled that standard SQL isn't more supported by some of the newer tools coming out.
Because "standard SQL", assuming it means ANSI SQL, is quite limited on its own. Every relational database has its own syntax for DDLs. It's not an exaggeration to say each mainstream relational database has its own SQL dialect (e.g. MySQL and SQL Server have ISNULL but ANSI SQL has COALESCE). There is no way to portably write a query that e.g. constructs a table with a foreign key constraint. The best libraries can do is target the dialects they care about or the dialects they expect everybody to use (e.g. MySQL and PostgreSQL).
Having specific DDL would be fine for most purposes. As I said in a sibling response, I don't mind the differences in how tables are defined or in the speed at which they run. I get that you need runtime statistics, likely on your live tables, to get good performance guarantees. What drives me bonkers is the silly differences like ISNULL v COALESCE. That one, at least, has a mostly easy mechanical change to get between the two.
Actually, what really drives me bonkers is when there are no local options. The aggregate functions such as https://trino.io/docs/current/functions/aggregate.html#appro... are super convenient for reporting purposes. And I can't think of any reason I can't run some of those queries against trivial data locally to show/confirm what a report is supposed to be doing.
And if I'm doing advanced queries, I largely sympathize. Basic group by and such, though? I get why cube and roll-up aren't always there, maybe. The rest, though?
If I was demanding equivalent speed, it would be one thing. I am fine needing an integration environment for that. I only want a test environment to show queries return as expected. Is also good documentation for reporting focused queries.
If you're doing very basic things, the same exact syntax will probably work on multiple. But even then, implementation details like isolation level come into play.
I feel that. Postgres has dump/restore at least, though that's less portable than a unit testing fake. And the thing I use at work lacks a dump/restore feature somehow :/
I remember using https://sqlfairy.sourceforge.net/ back in the day to help test locally against mysql/postgres, but deploy to oracle. There were hiccups, but it felt like the world would mostly converge onto smaller differences between the offerings and that kicking off a test database on every build should be easier as time goes on.
Instead, it feels like we have done as much as we can to make all of this harder. I loved AWS Athena when I was able to use it, but trying to figure out a local database for testing that supported the same dialect it used seemed basically impossible. It was baffling.