ORMs, and when to write SQL

What an ORM does

You describe each table once as a class or struct in your language. Then, instead of writing SQL, you call methods:

const task = await Task.create({ userId: 7, title: "Buy milk" });
const open = await Task.findAll({ where: { userId: 7, done: false } });

The ORM turns each call into SQL, sends it, and turns the rows that come back into objects. Most ORMs also generate migrations from your classes and pass values to the database safely, so user input can't rewrite your query. Prisma, SQLAlchemy and GORM are examples, for JavaScript, Python and Go.

What it costs

Hidden SQL. One innocent-looking line can become a slow query, or several. You can't fix what you can't see, so learn how to make your ORM log the SQL it sends.

The N+1 problem. This loop looks like one read:

const users = await User.findAll();            // 1 query
for (const user of users) {
  const tasks = await user.getTasks();         // 1 more query per user
}

With 200 users that's 201 round trips to the database. Each is fast alone, but together they make the page slow, and the pages get slower as the data grows. The fix is to ask for users and their tasks together, with one join; most ORMs have an option for it, often called include or eager loading.

Its own way of thinking. Reports, bulk updates and database-specific features are often awkward or slow through an ORM, and knowing the ORM doesn't teach you SQL.

When to write SQL

Use the ORM for everyday reads and writes of single records. Write SQL yourself when:

• a query joins several tables, groups or sums, as reports do;
• you change many rows at once, where one UPDATE beats loading and saving each object;
• a page is slow, and the ORM's SQL shows why;
• you need a feature only your database has.

Every ORM lets you run raw SQL next to its own methods. Whichever you write, pass values as parameters, never glued into the SQL string, so a name like O'Brien or a hostile input can't change the query.