To ORM or not to ORM

When it comes to database interactions in software development, one of the most debated choices is whether to use an ORM (Object-Relational Mapping) tool or stick to raw SQL. Both approaches have their strengths and trade-offs, and the best choice often depends on the project’s requirements, your team’s expertise, and personal preferences. In this post, I'll explore both sides of the debate and share my experiences transitioning between these approaches in different projects.

ORMs accelerate development

For many developers, ORMs are a lifesaver, especially when starting a new project from scratch. They abstract the underlying database operations, allowing you to define models and interact with the database using high-level APIs. This abstraction significantly speeds up development by reducing the need for boilerplate SQL code.

One area where ORMs shine is schema migrations. Altering tables frequently during early development becomes much easier with built-in migration tools provided by ORMs. Instead of manually writing SQL scripts to add or modify columns, you can use ORM commands to handle these changes programmatically. This can be a huge time-saver in fast-paced projects where the database schema is still evolving.

My preference for raw SQL

Despite the convenience of ORMs, I often find raw SQL more intuitive. Writing queries directly in SQL allows me to visualize exactly how the database will execute them. This clarity helps me debug issues and optimize queries more effectively.

In fact, I’ve noticed a pattern in my workflow: I write raw SQL to test and validate a query’s behavior, then convert it into an ORM query. While this ensures accuracy, it can feel counterproductive, as I’m essentially duplicating effort. For developers like me, who are comfortable with SQL, this overhead makes raw SQL a more appealing choice in many cases.

// query using ORM on TypeScript project
const transactions = await db
  .select()
  .from(transactionsTable)
  .where(
    and(
      eq(transactionsTable.user_id, jwtPayload.sub),
      eq(transactionsTable.is_active, true),
      eq(transactionsTable.id, id)
    )
  )
  .then((res) => res[0]);

// the same query that i write on Go project
query := `
  SELECT
    id, user_id, type, amount, category, date,
    notes, is_active, created_at, updated_at
  FROM swordfish.transactions
  WHERE user_id=$1 AND is_active=true AND id=$2
`

Using ORMs for type safety

One undeniable advantage of ORMs is their ability to leverage type safety, especially in languages like TypeScript. By defining models and relationships in the ORM, you gain compile-time guarantees that help catch errors early in the development process. This is particularly valuable when working in a team, as it reduces the likelihood of runtime errors caused by typos or mismatched data types.

However, type safety isn’t exclusive to ORMs. Libraries like Drizzle ORM, which I’ve used in TypeScript projects, provide a middle ground by offering type-safe raw SQL queries. These tools combine the best of both worlds but may not yet support all the features of a fully-fledged ORM.

When ORMs fall short

While ORMs are great for standard CRUD operations, they can struggle with more complex queries. In my experience, certain ORM libraries don’t offer sufficient flexibility or support for advanced SQL features, forcing me to fall back on raw SQL. This is especially true when working with features like window functions, recursive queries, or highly optimized joins.

In these scenarios, using raw SQL directly gives me complete control and avoids the overhead of trying to work around ORM limitations. While some modern ORMs are improving in this regard, they’re not always on par with the capabilities of raw SQL.

Raw SQL that I write because the ORM didn't have the feature (yet!).

My experience working with two approaches

To illustrate these points, let me share a real-life example from my recent projects from previous blog post. Initially, I built a backend service using TypeScript, Bun, Hono.dev, Drizzle ORM, and PostgreSQL. The ORM simplified development, particularly with its type safety and migration tools. However, as the project grew, I encountered scenarios where Drizzle ORM lacked the features I needed for more complex queries.

I rewrote the service in Go using the Gin framework, opting for raw SQL instead of an ORM. The switch gave me finer control over the database interactions. While this approach required more effort upfront, it paid off in terms of efficiency and flexibility for the project’s specific needs. And for me I enjoy it more because I don't have to convert the SQL to ORM notation. It's faster to write only once.

I depends...

Both ORMs and raw SQL have their place in a developer’s toolbox. Here are some guidelines to help you decide:

• Use an ORM if you’re building an MVP, working with a rapidly evolving schema, or prioritizing development speed and type safety.
• Use raw SQL if your application involves complex queries, requires optimal performance, or you’re comfortable managing schema migrations and database interactions manually.

Ultimately, the choice isn’t binary. Many developers, myself included, use a hybrid approach: relying on ORMs for routine tasks and falling back on raw SQL for edge cases. By understanding the trade-offs and leveraging the strengths of each approach, you can build robust, efficient applications tailored to your project’s needs.