Raw SQL, query builders, and ORM tools

Generally speaking, there are three ways to work with a database from code:

Write raw SQL queries.
Use a query builder.
Use an object–relational mapping (ORM) tool.

In today’s post, we’ll use a PostgreSQL database in a Node.js environment to examine all three methods.

Raw SQL

To write raw SQL queries in Node.js, we need to install a database client. Because we’re using PostgreSQL, we can use node-postgres.

npm install pg

Once we’ve installed the client, we can import it and set up a connection pool. We’ll export a function that invokes the pool.query() method and returns the result. This method supports parameterized queries to prevent SQL injection.

const { Pool } = require("pg");
const pool = new Pool();

exports.query = function (text, params) {
	return pool.query(text, params);
};

Now we can import the function and write some parameterized queries. For example, imagine we have an Express server with a route to get a blog post by its ID. We can get the ID from the route parameter and use it as a query parameter.

const db = require("./db");

// . . .

app.get("/posts/:id", async (req, res) => {
	const { id } = req.params;
	const result = await db.query("SELECT * FROM posts WHERE id = $1", [id]);

	if (!result.rows.length) {
		const code = 404;
		res.status(code).json({ error: http.STATUS_CODES[code] });
		return;
	}

	const [post] = result.rows;
	res.status(200).json(post);
});

Benefits of raw SQL

Flexibility: You have full control over the query structure and can optimize it for performance.
Performance: In some cases, writing raw SQL can lead to more efficient queries compared to using a query builder or an ORM tool.
Familiarity: Developers experienced with SQL may find it more natural to read and write raw SQL.

Drawbacks of raw SQL

Security risks: Raw SQL queries can be vulnerable to SQL injection attacks if not properly sanitized.
Maintenance: Raw SQL can be harder to maintain, especially in large codebases, as changes might require modifications across multiple queries.
Portability: SQL dialects vary across different database systems, potentially making it harder to write queries that work across multiple systems.

Query builders

A query builder provides a layer of abstraction. Instead of writing raw SQL queries, we can use methods provided by the query builder to build the queries programmatically. Knex.js is a popular query builder in the Node.js ecosystem.

npm install knex

Once we’ve installed Knex, we can import it and and set up the connection. We’ll use the same environment variables as node-postgres.

module.exports = exports = require("knex")({
	client: "pg",
	connection: {
		host: process.env.PGHOST,
		port: process.env.PGPORT,
		user: process.env.PGUSER,
		password: process.env.PGPASSWORD,
		database: process.env.PGDATABASE,
	},
});

With the connection configured, we can import the Knex instance and rewrite our previous example. The knex() function accepts a table name as its first argument. The .where() method accepts a column name as its first argument and a column value as its second argument.

const knex = require("./db");

// . . .

app.get("/posts/:id", async (req, res) => {
	const { id } = req.params;
	const [post] = await knex("posts").where("id", id);

	if (!post) {
		const code = 404;
		res.status(code).json({ error: http.STATUS_CODES[code] });
		return;
	}

	res.status(200).json(post);
});

Benefits of query builders

Abstraction: Query builders provide a higher level of abstraction over raw SQL, making it easier to write queries programmatically.
Security: Many query builders offer baked-in protection against SQL injection attacks by automatically sanitizing inputs.
Database agnostic: Query builders often support multiple database systems, allowing you to write queries that work across different systems.

Drawbacks of query builders

Limited expressiveness: Query builders may not support all SQL features, limiting the complexity of the queries you can write.
Performance overhead: Query builders generate SQL code dynamically, which can sometimes result in less efficient queries compared to hand-written SQL.
Learning curve: Developers may need to learn the specific syntax and API of the query builder, which can take time.

ORM tools

Wikipedia has a good definition of object–relational mapping:

“Object–relational mapping … is a programming technique for converting data between a relational database and the heap of an object-oriented programming language. This creates, in effect, a virtual object database that can be used from within the programming language.”

This means we can work with objects that represent rows in our database, and the ORM tool will take care of the SQL queries behind the scenes. Sequelize is a popular ORM tool in the Node.js ecosystem.

npm install sequelize

Once we have installed Sequelize, we can set up the connection and define a model. This is a class that represents an entity in the database. Sequelize has a .sync() method that keeps the database schema in sync with the model.

const { DataTypes, Model, Sequelize } = require("sequelize");
const { PGDATABASE, PGHOST, PGPASSWORD, PGPORT, PGUSER } = process.env;

const sequelize = new Sequelize(PGDATABASE, PGUSER, PGPASSWORD, {
	host: PGHOST,
	port: PGPORT,
	dialect: "postgres",
});

exports.Post = class Post extends Model {
	static {
		Post.init(
			{
				id: {
					type: DataTypes.INTEGER,
					autoIncrement: true,
					primaryKey: true,
				},
				title: {
					type: DataTypes.STRING,
					allowNull: false,
				},
				body: {
					type: DataTypes.STRING,
					allowNull: false,
				},
			},
			{
				sequelize,
			}
		);
	}
};

With the connection configured and the model defined, we can rewrite our previous example again. We can use the static .findByPk() method of the Model class to find a post by its primary key.

const { Post } = require("./db");

// . . .

app.get("/posts/:id", async (req, res) => {
	const { id } = req.params;
	const post = await Post.findByPk(id);

	if (!post) {
		const code = 404;
		res.status(code).json({ error: http.STATUS_CODES[code] });
		return;
	}

	res.status(200).json(post);
});

Benefits of ORM tools

Object-oriented interface: ORM tools map database tables to classes and provide a more intuitive way to interact with the database using object-oriented programming techniques.
Productivity: ORM tools can reduce the amount of boilerplate code required to perform CRUD (Create, Read, Update, Delete) operations.
Cross-platform compatibility: Many ORM tools support multiple database systems, allowing you to switch databases without changing your codebase.

Drawbacks of ORM tools

Performance overhead: ORM tools can introduce performance overhead compared to raw SQL, especially for complex queries or large data sets.
Complexity: ORM tools often abstract away the SQL queries, which can make it harder to optimize queries for performance.
Learning curve: Developers may need to learn the ORM tool’s specific API and conventions. This can be complex, especially for beginners.

Which should you use?

Personally, I like the power and flexibility of raw SQL. Security can be a problem, but any software developer worth their salt (there’s a secure password storage pun in there) should know about parameterized queries.

However, ORM tools are baked into many of the prevalent web frameworks. For example, there’s Active Record for Ruby on Rails, Eloquent for Laravel, Entity Framework for .NET, and more. There’s a good chance your team uses an ORM tool.