Live data from Hacker News

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

woz.posthaven.com

141–150 of 360 posts

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

#141
ORMs were pretty limited at the time I began writing my first platform, phponpie.com . I remember looking at Propel and Doctrine at the time.

Since then, I've written our own ORM which works in both PHP and Node.js, but we're the only ones that use it. It's been battle tested, though, with millions of users and variations. I would say that many of the issues the author brings up were things we had to face, and we solved them.

1) Schema – the ORM should have a script to regenerate base classes from the database, so that your schema only lives in once place. The nice thing is, after that, your IDE can help you out instead of writing sql by hand. It can use your language syntax to catch unbalanced parentheses, and more.

2) Adapters – the ORM should be modular so you can hook in adapters for MySQL, PostGres, SQLite, MongoDB, and various key-value stores.

3) Joins – the ORM is supposed to be smart enough to describe relationships and automatically write the most optimized JOIN queries for you. For example $article->getTags() . You could, of course, implement this stuff yourself manually but it gets tedious, when the code could easily be autogenerated with stuff like $article->hasMany('tags', ...) kind of like this: https://qbix.com/platform/guide/models#relations

4) Insight – using an ORM makes you pass actual values in a structured way, instead of interpolating them in a string. Thus you don't make the catastrophic mistake of forgetting to escape them, allowing SQL injections by Mr Bobby Tables. Also our ORM can do SHARDING in the app layer, especially useful in Node.js where it can issue simultaneous queries to several databases and combine the results. Although I recommend using CockroachDB these days :)

5) Flexibility – the ORM should support fetching partial objects, but with the Primary Key so they can be saved back. Recently we even added support for vector-valued lists, something we needed for extra flexibility.

6) Transactions – the ORM should be smart enough to handle transactions, in fact support nested transactions on various shards. Since the database engine usually does not support nested transactions, you need to emulate that in the app layer. For instance, when you start a session, you might want to begin a transaction and lock the session for update.

7) Methods – objects which are fetched can have user-friendly methods added, like $stream->exportToClient() and so on.

It's free and open source. Here are examples of usage:

https://qbix.com/platform/guide/database

https://qbix.com/platform/guide/models

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

#142
post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

Ok, let’s break this down: > Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. > providing patterns to map object graphs to…

It depends on how willing you are to allow your object graph to match your relational schema. The key is to let SQL be SQL. I wrote one that allows you to load data using standard SQL resource files, but it handles the persistence automatically:

https://github.com/bgard6977/sqorm

Flyway & jOOq take a similar approach:

https://www.jooq.org/

Taking the SQL-first approach also allows you to serialize without circular reference problems, since you don't just load the data, you also define a path to decompose the graph into a DAG.

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

#143

The first time I've seen an ORM I was fascinated by this seemingly beautiful idea. But I have quickly realized that it's almost useless in real life projects, plain old SQL seems just much much better. Now I don't understand why would anybody use an ORM actually. Also, basic SQL can be easily taught in as little as 10 minutes (I have been initially taught it at middle school during MS Office Query, Access and VBA cla…

> But I have quickly realized that it's almost useless in real life projects there are plenty of real life projects out there that would beg to differ

Sure. I mean in all the real life projects I've seen from the inside, using ORM instead of SQL felt like roaming a forest on a gyroboard.

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

#144

Ah yes - the proverbial "ORMs are bad, just learn SQL" post. This is analogous to saying "don't use frameworks". Sound ridiculous? Yes, yes it is. The law of leaky abstractions applies to many, many things, ORMs included. I would also argue they apply in different degrees, usually related to the design of the ORM (the post mentions SQLAlchemy vs. Hibernate, for example). But consider the following: 1) Why do people s…

By this logic, no bad practices exist.

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

#145
post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

zzzeek - can I take this chance to praise your work on SqlAlchemy. People say there's not enough thanks given to open source developers... here's thanks to you. It's the work of a craftsman.

+1 - personally I really like how SQLAlchemy doesn’t abstract away the database so much where all modeling and power is lost. He and the contributors have done an excellent job.

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

#146

Earlier quoted context omitted.

I think there are 3 main reasons ORMs came into common use: 1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so) 2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!) 3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM) 1 has b…

4. The pain in the butt of writing and maintaining your own mapping code. 5. Type checking all your queries. 6. In code query composability. But I totally agree that ORMs are too leaky of an abstraction for 2 to be really useful, and that 3 is much harder than it appears to be.

DALs can be easily written with libraries like MyBatis, jOOQ, Dapper, ....

Just because we are using SQL doesn't mean we have to do the mapping fully manual.

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

#147
post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

Everything that isn't SQL tuned to your environment forces you to sacrifice performance at some point. At some point it does an inefficient join. How can you not go back and fix performance issues is you don't know SQL? (It isn't like you need to fix it in C)

SQLAlchemy makes dropping down to SQL where necessary incredibly easy. There where necessary you can tune the queries, but where not necessary you can let the ORM create them for you.

This gives you a lot of flexibility and power, but as zzzeek mentioned, you need to know SQL to understand how what the ORM is inefficient and to be able to replace it as necessary while still letting the ORM do what it is really good at.

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

#149
> ...in order to use ORMs effectively, you still need to know SQL. My contention with ORMs is that, if you need to know SQL, just use SQL since it prevents the need to know how non-SQL gets translated to SQL.

This is presuming that you will never come across ORM being used almost exclusively in any future projects. After all, ORM doesn't seem to be going anywhere (even with all the hate against it). One could make an argument that learning both effectively would provide a better general foundation.

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

#150

I don't hear people complaining about say LINQ. Maybe it's just that your language and/or ORM or integration thereof suck?

LINQ isn't a ORM. I assume you mean Entity Framework? EF definitely has the foreign key issue. We have around a thousand tables, we tried to generate the classes for all of them including foreign keys, problem is that when you create a context that references even only a single table, it will load everything that is foreign keyed including siblings of siblings of siblings, until you run out of memory. Only way around…

You were doing it wrong then. EF does not eagerly load child collections. You cannot even configure it to do so.

As someone else suggested, you probably had lazy loading enabled, and some of your code tried - e.g. through reflection - to get all properties.

EF has some of it's own issues - but you can most certainly create composite primary keys, composite foreign keys and work with projections right from within the code.

None of the issues the original author had with SQLAcademy and Hibernate are really a pain in EF.

Some prefer to switch off lazy loading in EF and instead either explicit eagerly load specific child collections or explicitly load them right before use.

In EF you can do

  var customers = Customers.Include(c => c.Orders).Single(c => c.CustomerNo == '1234')
This will load Customer '1234' with the Orders collection eagerly loaded.
Post reply on HN