Hacker News Reader

Comment by threeseed | Hacker News Reader

> SQL is universal

No it's not. A small subset of it will work consistently across databases.

But if you want to get the most of your database then in almost cases you will be working with proprietary SQL. And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to.

View on HN

> And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to

In theory, in practise my experience is the oppose. It's easier to understand and tweak the SQL to work across DBs. You can easily diff and compare the SQL files. The ORM is another moving part, it adds convenience for simple queries, and complexity for anything advanced or non-standard.

SQL dialects vastly differ especially past SQL-92. And those differences tend to be very annoying (e.g. RETURNING). There is a reason that people do not generally work across different RDBMSes and instead pick and stick to a single RDBMS because it is really a PITA; I do dislike ORMs for usual reasons, but a bare SQL is not the alternative either.

And yet the strategy most ORMs take is the common denominator where you have no idea if it'll actually use RETURNING or fire off a completely separate query with a potential race condition because SELECTs go to the read-only replica.

(Ask me how I know about this kind of problem with ORMs.)

I'd say for most standard use cases, the same SQL can be utilised across different DBs.

With ORMs, my big annoyance is inefficiency. When you start to scale or add complexity, ORM generated SQL queries can be rather expensive when compared to hand crafted ones.

Meaning it’s vastly more universal than your ORM du jour.

> And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to.

I've yet to see or hear of a single project (outside libs and frameworks) that needs to "work across databases with the same ORM".

True, but the only case where yoy really need to work across dbs is if you are creating a product that allows your customers to bring their own db. Otherwise you are much better off picking a db and sticking with it.

Regardless of whether ORMs or SQL are more effectively database-agnostic, how often are you changing databases that this is even a concern?

It's always been the opposite for me. Whenever I had to use something PG-specific in Rails I had to resort to SQL.

I mean universal in the sense that going from mysql to postgres is really not a big shift.

I have worked mainly with postgres/mysql but i would imagine i would be up and running at full speed in days/a few weeks with mssql if i ever chose it for a project.

sure there are syntax differences, but the "how do i do this" translates very well across databases.

How do you make event triggers in MySQL?

How do you make a system-versioned table/query in PostgreSQL?

How do you port regular expressions to MS SQL Server?

How do you set up an exclusion constraint on a range type (without race conditions or invalid data) in anything except Postgres?

Most relational databases fit niches that the other don't. No one RDBMS is best, but they are most certainly not interchangeable.

Even using the lowest common denominator SQL means you'll be missing out on major performance boosts because they each have their own hints/shortcuts.

Well if you are comparing non-standard database features you are out of luck. Im talking about the 95% CRUD stuff that you do. Basic selects/joins/aggregations etc.

Its super rare i see the need to actually change the underlying database from say postgres to mysql. And in this scenario you are still screwed if you did use a ORM, with database X only features.

My point was basic SQL knowledge transfers between databases for the bread and butter 95% of things you need to do.

Reply on news.ycombinator.com