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

On the other side you have juniors, just starting development and working with an ORM/ODM the first time. It's not uncommon for them to spend years in development while learning very little SQL at all, hardly understanding the database (apart from what they learn in school). I don't blame them. I hardly know TCP/IP at all and still I write technology for it everyday. But ORMs are a more leaky abstraction; a lot of complexity is made easier by just switching to SQL.

More principal points:

- Why do we want to abstract away further from the most important part of the business: data. Data usually outlives the applications built on top of it.

- Best practice is to keep backend API's stateless and requests short-lived. ORM's promote the use of state. In client-side applications state is a much more interesting long-lived thing, but a backend API these days? It has actually become a lot simpler there since I've started doing development; few modern backend servers render views these days.

I understand why the author (re-)embraced stored procedures but I never will. Blame the overzealous PL/SQL Oracle seniors I've met, god forbid any human bestows such complexity on it's fellow colleagues. It's also hard to automate tests for them and promote it through DTAP alongside an application, hence I keep putting all logic in the application.

I can imagine if the language and ecosystem are very strongly geared towards ORM you shouldn't try to do anything else. That's just painful. But in most languages other than Java and C# I'd make a good consideration if you really need/want an ORM (coming from Java, I never took non ORM design seriously there, but perhaps I shouldn't have).

Not sure where I'll stand in a few years time on this… but for now just happy with plain old SQL and query builders.

It wasn't about speed of execution, but expressiveness when coding. Later on they even added proper type system support.

Common operations were things like map/project, extend, filter, join, collect-by-key / expand, etc.

Just as Codd pointed out in his original papers, relations allow you to not have to make a choice about a hierarchy for your data.

Using key-value store like a hash-table in your program, or the much vaunted has-a relationship between objects would force you to make these choices. Thus making interacting with the data awkward for all but one access pattern.

Relations work best when your program is written in a style that deals largely in immutable data. (What we call 'purely functional', but people in dysfunctional languages have also picked up on the advantages recently.)

- Are maintainable by a team. "Oh, because that seemed faster at the time."

- Are unit tested: eventually we end up creating at least structs or objects anyway, and then that needs to be the same everywhere, and then the abstraction is wrong because "everything should just be functional like SQL" until we need to decide what you called "the_initializer2".

- Can make it very easy to create maintainable test fixtures which raise exceptions when the schema has changed but the test data hasn't.

- Prevent SQL injection errors by consistently parametrizing queries and appropriately quoting for the target SQL dialect. (One of the Top 25 most frequent vulnerabilities). This is especially important because most apps GRANT both UPDATE and DELETE; if not CREATE TABLE and DROP TABLE to the sole app account.

- Make it much easier to port to a new database; or run tests with SQLite. With raw SQL, you need the table schema in your head and either comprehensive test coverage or to review every single query (and the whole function preceding db.execute(str, *params))

- May be the performance bottleneck for certain queries; which you can identify with code profiling and selectively rewrite by hand if adding an index and hinting a join or lazifying a relation aren't feasible with the non-SQLAlchemy ORM that you must use.

- Should provide a way to generate the query at dev or compile-time.

- Should make it easy to DESCRIBE the query plans that code profiling indicates are worth hand-optimizing (learning SQL is sometimes not the same as learning how a particular database plans a query over tables without indexes)

- Make managing db migrations pretty easy.

- SQLAlchemy really is great. SQLAlchemy has eager loading to solve the N+1 query problem. Django is often more than adequate; and has had prefetch_related() to solve the N+1 query problem since 1.4. Both have an easy way to execute raw queries (that all need to be reviewed for migrations). Both are much better at paging without allocating a ton of RAM for objects and object attributes that are irrelevant now.

- Make denormalizing things from a transactional database with referential integrity into JSON really easy; which webapps and APIs very often need to do.

Is there a good JS ORM? Maybe in TypeScript?

EDIT: Sorry, my mistake. The partent is talking about the parent comment, not the FTA