What ORMs have taught me: just learn SQL (2014)

It's not about avoiding to write SQL, it's about to have standardized API on which all your architecture can count.

Why do you think Django was so successful ?

Because it was built in a way that allowed a rich and powerful ecosystem to flourish.

The Django ORM is not the best out there and it's doing plenty of silly things. If you don't know SQL and you use it you will be in a world of pain.

However.

Because Django features this ORM it can:

- provide auto-generated forms from db model, outputting HTML and validating user inputs, saving changes automatically to the DB.

- provide auto-generated CRUD views from the db model, that you can extend at will.

- provide auto-generated admin

- provide tookits and helpers to deal with your data: signals, various forms of getters, native object casting, advanced validation, better error messages...

- provide entry points for extending the data manipulation API, in a generic way (fields, managers, etc)

- provide tooling for migrations

- provide auth and permissions

- provide user input cleaning and escaping

- automatically deals with value normalization: encoding, timezones, text/number formats, currencies... There is one entry points for those where you can put custom code, and you don't need custom code most of the time since somebody did the work for you more often than not.

- ensure all django projects look the same, so that it's very easy to move from team to team or train people

- formalize the schema, which became a the documentation and only source of truth for your data, that is commited to your VCS. Wannan know what a Django project is all about ? Check urls.py, settings.py and models.py. Done.

The cherry for this cake is of course the fact 3rd party modules (so called django "apps") can leverage that, which lead to the amazing ecosystem Django has.

- auto-generate REST views from model (eg: django-rest-framework), again that you can tweak as much as you want.

- dozens of auth backends.

- data manipulation (workflow, filtering, dashboard, analytics) that just work.

- tags, search, comments, registration and all those stuff you alway rewrite otherwise.

And because they all use the ORM, they are all compatible with each others. And they all work on Mysql, Oracle, SQlite and Postgres, like the entire rest of the framework, out of the box, for free.

You want to do that in any other framework (except RoR) ? You'll get a lib that do half of it, and let the persistence and API integration work to you. And it will not play with others. And that will be integrated differently on another project. If you have a lib at all ! Oh, and you have to use the proper DB. If you are corporate or startup, it won't be the same one and you better hope the lib author is in your shoes.

All that stuff is easy in Django because you have a centralized, easy to inspect, standard, shareable definition of each of your model in one place.

That's what ORM are for. Not "doh, SQL is hard".

Now you could get some part of those benefits by creating central models using schemas untied to the DB, such as marshmallow. It would be an interesting take, but my guess is that you will end up with interfacing it with your DB with some kind of layer, that would look like an ORM anyway.

For example, in Go, I use Gorp, which has a Select() function where you pass in the SELECT query string (plus bound values) and the target type, and it loads every result row into an object of that type. So you can have an arbitrarily complex SQL query as long as it starts with `SELECT one_table.* FROM`. That's a marvelous design.

And when you have to do a query that returns results from multiple tables? Guess what, you just use the normal SQL module from the standard library.

the point is: use sql, not orms!

migrations are best done in pure sql, i'll contend that model-inflation is best done in pure sql also.

another thing: if the format is json, that's already a nested "joined" blob of usable data! it's what the end result of a sql join would achieve, the client often just has to drill into that data blob and everything needed for the entity in question is already there!

This is great advice in general for everyone in a role that even slightly touches on ops.

In my day-to-day work, I frequently observe that knowing some SQL (esp. joins, and aggregate functions like SUM/MAX with GROUP BY and HAVING) turns you into some sort of mighty wizard for most people. They're trying to debug a problem in their service and not making progress for hours, and you just walk straight into psql, take a look at the schema, do a few SELECTs, and zoom in on the problem.

Yet nobody seems to consider SQL a valuable skill. I guess it's not buzzwordy enough.

I worked on a project with a home-grown ORM (in C; it was horrible) that abstracted over both MySQL and Postgres ... except the overarching application required Postgres-specific column types and functions.