ORM and database access: Objects instead of SQL – pros and cons

In short: An ORM maps database tables to objects so that you query data with code instead of SQL; this saves writing but can cost performance and understanding.

In more detail: ORM stands for object-relational mapping: tables become classes, rows objects, columns properties. Examples: Hibernate/JPA (Java), Entity Framework (C#), SQLAlchemy and Django ORM (Python), Prisma, Drizzle, TypeORM (JavaScript/TypeScript), ActiveRecord (Ruby), Eloquent (PHP).

In Depth

Example

// Drizzle (TypeScript)
const active = await db
  .select()
  .from(users)
  .where(and(eq(users.active, true), gt(users.age, 17)))
  .orderBy(users.name)
  .limit(10);

The tool turns this into SQL with parameters (protection against SQL injection), executes it and returns typed objects. Schema migrations and relationships (1:n, n:m) are also supported.

Advantages

• Less boilerplate: no manual mapping of rows to objects.
• Type safety and autocompletion in the editor.
• Security: parameterised queries by default.
• Migrations, relationships, validation.
• Database-independent code (within limits).

Disadvantages and pitfalls

• N+1 problem: for each object another query is executed (list of orders plus one query each for customers). Solution: eager loading (JOIN, include, joinedload).
• Thoughtless queries: all columns are loaded, huge amounts in memory.
• Leaky abstraction: complex analyses, window functions, special functions are hard to express; then raw SQL.
• Learning curve of the ORM in addition to SQL; you still must understand SQL to judge performance (indexes, EXPLAIN).
• Magic (lazy loading, implicit transactions) leads to surprising behaviour.

ORM or SQL?

ORM for everyday CRUD operations, few complex queries. Query builders (Kysely, jOOQ, Knex) are a middle way: close to SQL but type-safe. Raw SQL for reports, analyses, performance bottlenecks. Look at the generated SQL queries (logging), plan indexes and migrations deliberately. Whoever knows SQL uses ORMs better.

See also: N+1 problem, SQL injection, Database migration, Transactions and ACID