When building Node.js applications that interact with relational databases, you have a large variety of tools at your disposal to manage and execute queries.

The three most common approaches — raw SQL, query builders, and Object-Relational Mappers (ORMs) — offer unique advantages and challenges, making it difficult to decide which is best.

In this guide, we will compare the strengths, trade-offs, and use cases of all three approaches. This will help you understand the nuances of each option and determine which approach best suits your needs.

Let's dive in!

Understanding Raw SQL

Raw SQL refers to writing and executing SQL queries directly against a database without the use of any abstractions. In this approach, you manually craft SQL queries as plain text strings and send them directly to a database to execute:

Before you can execute raw SQL queries from your Node.js application, you'll need to establish a connection between your Node app and your preferred SQL flavor through the appropriate database driver. Popular choices include:

• mysql2 for MySQL
• pg for PostgreSQL
• better-sqlite3 for SQLite

And many others!

After connecting to the database, you can directly execute SQL queries using the provided connection object. You'll construct your queries as strings, incorporating placeholders for any dynamic values to prevent SQL injection attacks. Then, you'll pass your query, along with any necessary parameters, to the database driver's query execution method.

The driver will send the query to the database, receive the results, and return them to your application, often in the form of an array of objects representing the retrieved data.

Here's a basic example that uses the better-sqlite3 driver against an SQLite database file:

import Database from "better-sqlite3";
const db = new Database("chinook.sqlite");
 
const selectAlbumByID = "SELECT * FROM Album WHERE AlbumId = ?";
 
const row = db.prepare(selectAlbumByID).get(1);
console.log(row.AlbumId, row.Title, row.ArtistId);

Now that you understand how raw SQL works, let's dig into the pros and cons of using it exclusively to interface with a SQL database.

👍 Pros of Raw SQL

One of the primary advantages of writing raw SQL queries is the level of transparency and control it provides. With raw SQL, you have full visibility into each database operation, allowing you to see exactly how data is stored, structured, and retrieved.

This direct approach reduces the surprises that often come with abstraction layers, and allows you to craft highly optimized queries (thus avoiding inefficiencies that can result from automated query generation tools). The ability to fine-tune queries is especially helpful in scenarios that require complex data retrieval or manipulation.

Raw SQL also grants you unmatched flexibility since you're not constrained by the limitations of any abstraction layer. You can tap into the full spectrum of your database engine's capabilities and run complex, database-specific queries that might not be supported or as easily accessible through higher-level abstractions.

Finally, working exclusively with raw SQL will help deepen your understanding of SQL and how databases work: valuable knowledge for any developer, especially where performance and optimization are concerned.

👎 Cons of Raw SQL

While raw SQL boasts numerous advantages, it is not without its challenges. One of the key issues is the complexity of writing and maintaining queries. Raw SQL queries can often become lengthy and difficult to manage, especially when handling complex relationships, nested queries, or retrieving data from multiple tables.

Another consideration is the steep learning curve it presents for developers who may not have a strong background in SQL. In the Node.js ecosystem, there is generally a strong preference towards using ORMs, query builders, and similar abstractions. Finding resources and support for raw SQL patterns is more challenging compared to these more widely adopted approaches.

Furthermore, writing raw SQL can leave you susceptible to errors and security vulnerabilities, such as SQL injection, if queries are not properly sanitized. You need to manually handle these by using parameterized queries or prepared statements, and ensure that all user inputs are carefully sanitized to prevent malicious code from being executed within your database.

Source: Bobby Tables XCKD

Finally, working with raw SQL often involves manipulating queries as plain strings, which can lead to subtle bugs (such as typos in column names or incorrect data types) that might go unnoticed until runtime. If you're using TypeScript, you can take a look at tools like PgTyped to use raw SQL in your application in a type-safe manner.

Who Should Use Raw SQL?

Raw SQL is best in scenarios where performance optimization and fine-grained control are paramount, or when dealing with complex, non-standard queries that cannot be easily handled by ORMs.

Understanding Query Builders

Instead of writing raw SQL, you can opt for query builders to interact with your database:

They provide a more structured and secure way to compose queries, while stripping away some of the complexities of manual SQL string building.

Query builders typically offer an API where you can chain methods to build complex queries step-by-step. This approach helps prevent common errors like SQL injection vulnerabilities and simplifies the process of incorporating dynamic data into your queries.

The real beauty of query builders is that they strike a balance between abstraction and control. You still engage with familiar database concepts like tables, columns, and relationships, but in a more JavaScript-friendly way. This translates to increased safety and convenience without sacrificing a clear understanding of the underlying database operations.

One prominent query builder in the Node.js ecosystem is Knex.js, with over 19k GitHub stars and 1.7 million weekly downloads. You need to install the knex package and the relevant database driver for the specific database you're working with (pg, mysql, etc.).

npm install knex sqlite3

You can now write queries like this:

import knex from "knex";
 
const Database = knex({
  client: "sqlite3",
  connection: {
    filename: "./chinook.sqlite",
  },
  useNullAsDefault: true,
});
 
const selectedRow = await Database("Album")
  .where({
    AlbumId: 1,
  })
  .select("*");
 
console.log(selectedRow);

The value of query builders over Raw SQL may not be immediately apparent if you're mostly writing simple, static queries like the one above. But they can quickly prove their worth when there's a need to build queries with dynamic conditions:

let query = knex("users");
 
if (searchCriteria.name) {
  query = query.where("name", "like", `%${searchCriteria.name}%`);
}
 
if (searchCriteria.email) {
  query = query.where("email", searchCriteria.email);
}
 
if (searchCriteria.minAge) {
  query = query.where("age", ">=", searchCriteria.minAge);
}
 
await query.select("*");

Knex's chainable methods allow you to effortlessly build complex queries based on runtime conditions. Contrast this with raw SQL, where achieving the same result through string concatenation is not only less convenient but also prone to security vulnerabilities.

👍 Pros of Query Builders

We've already touched on some compelling reasons to favor query builders over raw SQL: they simplify the construction of dynamic queries based on runtime conditions, and mitigate the risk of SQL injection attacks through their use of parameterized queries.

Another key advantage of query builders is that they tend to be more maintainable in the long run compared to raw SQL strings. By utilizing familiar programming constructs like method chaining, it's easy to compose complex queries into manageable chunks and distinguish between operators and data, while staying faithful to SQL semantics.

knex("users")
  .select("users.id", "users.name", "posts.title")
  .join("posts", "users.id", "posts.author_id")
  .where("posts.published", true)
  .orderBy("posts.created_at", "desc");

Unlike ORMs, query builders also offer transparency into an underlying SQL query. While they use methods to represent SQL primitives, the underlying database operations are unobscured, so anyone familiar with SQL can still understand the query's intent and potential performance implications.

Query builders often also support multiple backends, allowing you to write code that's more portable across different database systems. It's true that database backends are rarely changed once an application is in production. Nonetheless, this feature allows developers working with different databases to avoid learning a new paradigm to write SQL for each one.

While some criticize query builders for not abstracting away enough SQL complexity, I see this as a strength. Relying solely on tools that circumvent learning SQL is detrimental in the long run.

Query builders, when used effectively, still require a foundational understanding of SQL principles. They provide a structured, secure environment to write SQL to improve maintainability, without sacrificing core SQL semantics.

👎 Cons of Query Builders

Compared to writing raw SQL, query builders don't have many cons. Even when dealing with operations demanding queries that a builder does not provide an abstraction for, there's usually a "raw" mode to send queries directly to the backend, bypassing the query builder's typical interface.

knex("users")
  .select("*")
  .where(knex.raw("(age > ? OR email LIKE ?)", [18, "%@gmail.com"]));

In terms of performance, query builders generally keep pace with raw SQL, although there might be scenarios where meticulously hand-optimized raw queries edge out in efficiency.

The primary trade-off lies in comparison to ORMs. Query builders, with their less extensive abstraction, require a deeper understanding of SQL concepts and schema management. You'll miss out on conveniences like automatic object-relational mapping, schema migrations, and the reduction in boilerplate code that ORMs often provide.

Who Should Use Query Builders?

Query builders cater well to anyone seeking a balance between the control of raw SQL and the convenience of ORMs. They're an excellent choice if you want to maintain transparency into underlying SQL concepts while following a more structured and maintainable approach.

If you often work with multiple databases, a query builder also provides a consistent interface for constructing queries, regardless of the underlying system. This reduces the overhead of context switching as you move from project to project. And you can always use raw() when you need to access database-specific capabilities.

Understanding Object Relational Mappers

ORMs bridge the gap between object-oriented programming and relational databases by offering a high level of abstraction:

They present data in an object-oriented paradigm, significantly reducing the amount of boilerplate code required, and thus speeding up development.

By accessing and manipulating data, ORMs alleviate the need to write SQL manually. They translate object-oriented operations into SQL commands that a database can understand, allowing you to focus more on business logic than on database intricacies.

Many ORMs also provide built-in features for managing your database schema, such as creating tables, defining relationships, and handling schema migrations as your application grows and evolves.

There are numerous ORMs available for Node.js. Sequelize is a longstanding option, but newer alternatives like Prisma, MikroORM, and Drizzle are gaining popularity due to their emphasis on developer ergonomics, type safety, and performance.

Like query builders, you need to install both the ORM and the appropriate database driver:

npm install sequelize sqlite3

Once installed, you need to define models that represent the tables and relationships in your database. Afterwards, you can use the ORM's methods to interact with the database — performing queries, and creating, updating, or deleting data — all while managing the schema with ease.

import { DataTypes, Sequelize } from "sequelize";
 
const sequelize = new Sequelize({
  dialect: "sqlite",
  storage: "chinook.sqlite",
});
 
const Album = sequelize.define(
  "Album",
  {
    AlbumId: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    Title: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    ArtistId: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
  },
  {
    timestamps: false,
    tableName: "Album",
  }
);
 
const album = await Album.findByPk(1);
console.log(album.AlbumId, album.Title, album.ArtistId);

In short, ORMs provide a powerful abstraction layer that simplifies database interaction. This makes it easier for developers to manage complex relationships and automate tedious tasks like schema migrations, while still offering flexibility and control when needed.

👍 Pros of ORMs

ORMs offer several advantages that make them a popular choice for Node.js developers working with databases. A primary benefit is the high level of abstraction they provide, which enables database interactions using familiar object-oriented concepts instead of raw SQL.

This abstraction significantly reduces the amount of boilerplate code needed and speeds up development by simplifying common database operations (such as CRUD queries). Their built-in schema management tools also help with database versioning and migrations, while minimizing the risk of errors.

Another major advantage of ORMs is their ability to manage complex relationships between different entities. Instead of manually mapping relationships such as one-to-many or many-to-many in SQL, ORMs automate this process, making it easier to define and navigate these relationships programmatically.

Security is another area where ORMs excel. Since they automatically handle query construction, they mitigate common security vulnerabilities such as SQL injection by default, ensuring that user input is safely handled and sanitized.

In terms of portability, ORMs provide a layer of database-agnostic interaction, meaning you can switch between different database systems with minimal code changes.

Finally, many modern ORMs offer strong type safety and performance optimizations, which help developers catch errors early and optimize database interactions, particularly with newer tools like MikroORM and Drizzle.

👎 Cons of ORMs

While ORMs offer powerful abstractions, they also come with some notable challenges.

One major issue is performance overhead, as ORMs often generate inefficient queries (even for simple tasks) due to their broad design intended to accommodate a wide range of use cases. This can lead to significantly slower performance, particularly in high-traffic applications or when dealing with complex data operations.

Another drawback is the loss of fine-grained control over database operations. While ORMs excel at simplifying common tasks, optimizing certain queries, modeling complex relationships, or leveraging advanced database-specific features can be challenging within an ORM's constraints.

These challenges are often rooted in the friction between the object-oriented paradigm used in application code and the table-based nature of relational databases (AKA object-relational impedance mismatch. This mismatch often necessitates workarounds and compromises when managing complex relationships in a database.

Additionally, ORMs have their own learning curve. You'll need to understand the ORM's specific syntax, conventions, and how it maps objects to database tables. If you're already familiar with SQL, adapting to an ORM's paradigm can sometimes feel like an additional layer of complexity, as you must now juggle both the database and the ORM's internal logic.

Who Should Use ORMs?

ORMs are generally well-suited for situations where you're prioritizing development speed over runtime performance. They're also a great fit for building applications where complex queries are minimal and CRUD operations are common.

Exploring a Hybrid Approach

So far in this article, we've mostly framed the choice between Raw SQL, query builders, and ORMs as an either/or proposition. In practice, however, you can adopt a hybrid approach by combining the strengths of multiple methods to suit the project at hand.

A common hybrid strategy is to rely on an ORM for most data access, while using raw SQL for performance-critical queries or when leveraging database-specific features that the ORM doesn't easily support.

For instance, you might use an ORM for standard CRUD operations and model relationships but switch to raw SQL for complex joins, aggregations, or specialized database operations.

Another variation involves using a query builder as the primary interface for database operations. This allows for easier maintainability and composition while preserving SQL semantics and allows for the flexibility of switching to raw SQL when needed.

A hybrid approach gives you the best of both worlds: the convenience of whatever abstraction you choose, alongside the performance and control of direct database access if needed.

Wrapping Up

In the Node.js ecosystem, there's no one-size-fits-all answer when it comes to database interaction. Raw SQL offers unmatched control and performance but demands expertise. Query builders provide a balance of convenience and flexibility, while ORMs prioritize abstraction and rapid development.

Ultimately, the best choice depends on your project's specific needs, your team's expertise, and the trade-offs you're prepared to accept. Regardless of your chosen path, a strong grasp of SQL remains fundamental for effective relational database management.

Thanks for reading!