Will your application code stand the test of time—or will it go the way of the dinosaurs? Whether you’re a business owner or a Node.js consultant, the longevity of your project is strongly linked with the maintainability of your code. When the data layer of your app changes in quality, the effects ripple throughout your entire app. Using an ORM can help you write more maintainable code.
Recently, my team faced a challenge. We were refactoring a giant project into an easier-to-manage state. The project used only raw SQL to communicate with the database, which drastically increased the complexity of such a big project.
Writing every single query by hand is tedious upfront and requires even more work in the long term.
To deal with complex databases, we generally end up writing complex helpers. Such helpers are often so complex that they tend to look almost like half-functional, error-prone ORMs. That’s exactly what we were facing on our particular project. Our system, a multi-tenant data server with one database per client, hundreds of clients, tables, and relations, proved itself too hard to navigate and refactor. We lost control over our SQL generation. It was then that we decided to implement an ORM to help standardize that complexity.
So let’s take a look at what an ORM is, compare ORMs to raw SQL in concept, then finally compare actual code using an ORM called Sequelize.
🤷♀️ What is an ORM?
In the modern programming world, where computer resources are more abundant, along with performance and speed, it is very relevant to consider development experience and how bug-prone certain choices are. Building tools from zero with very complex tech is not always the best decision, especially if we already have well-supported, open-sourced, community-tested tools.
The standard language for accessing and manipulating databases is SQL (Structured Query Language). You can write SQL queries like this, to generate cool drawings like the one below.
Look at how powerful SQL is! It can seemingly do almost anything. But with great power comes great responsibility.
Mandelbrot set generated with SQL
An ORM (Object-Relational Mapping) is a technique way simpler than what the name may imply: while most developers are very proficient with complex object oriented concepts, not so many are that proficient with advanced SQL concepts (like those used to make the Mandelbrot set above).
When your system and its interactions with the database grow, you’ll end up needing an abstraction between them: that is what an ORM is; a library that fills that role while it lets you use the object-oriented paradigm to realize operations.
While in SQL you’d have something along these lines:
const user = await connection.query('SELECT name, age, created_date, subscription_id FROM
users WHERE id = 5bb2bb0d-b74b-4ae6-9a46-e6a4a9581f8c')
Using an ORM, you could have something more like this:
return await orm("users").columns([name, age, created_date, subscription_id])
.where({ id: '5bb2bb0d-b74b-4ae6-9a46-e6a4a9581f8c' });
While using RAW SQL will almost always be faster and performance can make for a great user experience, it may, unintentionally, make your app slower. Let’s take a look at how that’s possible.
🤯 ORM vs Raw SQL
Let’s compare the pros and cons of using ORM instead of SQL.
Pros of ORM over SQL
-
Productivity - The developers need to write and maintain less code to realize database operations, generally.
-
Intuitiveness - It’s easier to identify what is happening in the surface of a big ORM query than in a big SQL query because of their object oriented nature (this is not true for every dev).
-
Standardization - Since the schema is defined on the code, it’s easier to identify all of a table’s relations. A schema is basically a collection of tables.
-
Simplicity - queries are written in an object oriented, intuitive syntax.
-
Different databases - It’s easier to migrate databases if you’re using an ORM and this ORM supports the target database.
-
Flexibility - If you have specific queries that you want to write yourself in SQL, be it for performance or any other reason, you may run them through your ORM. Sequelize, for example, does support them in the form of “literals”.
-
Security - ORMs are community tested and deal with a lot of security issues that could appear from amateur, or even professional SQL writing.
Cons of ORM over SQL
-
Abstraction cost - An ORM brings a new layer of abstraction, which will mostly make your code slower.
-
Lack of control - While ORMs make your life easier, especially if you lack experience with SQL, they can prove more difficult and non-flexible when writing complex queries (with aggregations, sub-queries, joins, etc…). Because they hide the query details, you lose control of what you’re writing.
-
Inefficiency - ORMs can lead to inefficient querying. Because you’re not seeing the exact query that is running, it may prove harder to debug and improve query performance.
-
Implementation cost - even though a lot of ORMs can (sort of) auto-map the tables into models for you, for projects with poorly structured databases, implementing an ORM may be a very painful chore.
-
New language - if you’re already familiar with SQL learning a new ORM can seem like a waste of time. Different projects may use different ORMs that differ vastly. And by using only ORMs, you may end up forgetting, not understanding well, or even not ever learning the SQL that is happening behind the screen.
With all of those pros and cons in mind, consider combining both implementations in your project and extract the best of both methods. Use ORMs for more repetitive, simple operations, and raw SQL for when you need extra performance/control.
🧐 Why Sequelize?
Now let’s take a look at the ORM that best matched our project: Sequelize. Sequelize is a very popular ORM for Node.js projects, it supports most necessary operations. Especially important for our project, it supports both raw queries and multi-tenancy done with one database per client. It was the perfect tool for our necessity. Another ORM may be a better fit for your project. The intention of this post is not to advocate for one over the other.
So let’s take a brief look at part of our implementation. While implementing Sequelize, you can add this flag before you initialize your models:
sequelize.dialect.supports.schemas = true;
Sequelize lets you modify your base schema, allowing for multi-schema querying, like this:
model.schema('SCHEMA_A').unscoped()
Remember that in MySQL, a schema is a collection of tables, kind of like a virtual database. The above code lets us switch to a different set of tables to perform queries for another client.
Now let’s compare some actual code.
🙌 Hands on Sequelize
Below are some examples of simple things done with pure SQL vs with Sequelize
Connecting to the Database
Here is how you would connect to the database with pure SQL:
const { DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD } = process.env
const connection = await
mysql.createConnection({
host: DATABASE_HOST,
user: DATABASE_USER,
password: DATABASE_PASSWORD,
});
And this is how you would connect to the database and initialize your models with Sequelize:
const { DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD } = process.env
const sequelize = new Sequelize(DATABASE_DATABASE, DATABASE_USER, DATABASE_PASSWORD, {
dialect: 'mysql',
host: DATABASE_HOST,
});
await sequelize.authenticate();
sequelize.dialect.supports.schemas = true;
initModels(sequelize);
Joining Data between Schemas
This is how you would join data from two different schemas with SQL:
router.get('/multi/employees', async (request: Request, response: Response) => {
const { filter }: Filter = request.query;
const limit = getLimit(request);
const clients = filter?.client?.split(',');
if (clients.length < 2) {
response
.status(418)
.json({ error: 'You need to pass multiple companies on the filter.' });
}
const multipleSelects = clients.map(
(client: string) =>
`(SELECT * FROM ${connection.escapeId(client)}.employees LIMIT ${limit})`
);
const unionAllQuery = multipleSelects.join(' UNION ALL ');
const employees = await query(unionAllQuery);
response.status(200).json({ employees });
});And here is how you’d join data from two different schemas with Sequelize:
router.get('/multi/employees', async (request: Request, response: Response) => {
const { filter }: any = request.query;
const limit = getLimit(request);
const clients = filter?.client?.split(',');
if (clients.length < 2) {
response
.status(418)
.json({ error: 'You need to pass multiple companies on the filter.' });
}
const employeeModel = sequelize.models.employees;
const [employees, employees2] = await Promise.all(
clients.map((client: string) => {
return employeeModel.schema(client).unscoped().findAll({ limit });
})
);
response.status(200).json({ employees: [...employees, ...employees2] });
});Returning specific columns
Here’s how you would return specific columns, using a LIKE statement on the WHERE clause with SQL:
router.get('/employees', async (request: Request, response: Response) => {
const limit = getLimit(request);
const employees = await query(
`SELECT first_name AS probablyMary, emp_no AS id
FROM employees_client_A.employees
WHERE first_name LIKE 'ma%'
LIMIT ?`,
[String(limit)]
);
response.status(200).json({ employees });
});This is how you return specific columns using a LIKE statement on the WHERE clause with Sequelize:
router.get('/employees', async (request: Request, response: Response) => {
const limit = getLimit(request);
const employees = await sequelize.models.employees.findAll({
attributes: [
['emp_no', 'id'],
['first_name', 'probablyMary'],
],
where: { first_name: { [Op.like]: 'ma%' } },
limit,
});
response.status(200).json({ employees });
});Using Raw Queries
Okay, here’s a mysterious SQL query:
router.get('/secret/:size', async (request: Request, response: Response) => {
const verySecretSymbol = await query(
`WITH RECURSIVE cte AS
(
SELECT 1 AS n, CAST('*' AS CHAR(100)) AS str
UNION ALL
SELECT n + 1, concat('* ', str) FROM cte WHERE n < ?
)
SELECT str FROM cte
`,
[String(request.params.size)]
);
const parsedSymbol = verySecretSymbol
.map(function (line: any) {
return `${line.str} \n`;
})
.join('');
response.type('text/plain').status(200).send(parsedSymbol);
});And here’s another mysterious query, but with Sequelize literals:
router.get('/secret/:size', async (request: Request, response: Response) => {
const verySecretSymbol = await sequelize.query(
`WITH RECURSIVE cte AS
(
SELECT 1 AS n, CAST('*' AS CHAR(100)) AS str
UNION ALL
SELECT n + 1, concat('* ', str) FROM cte WHERE n < ?
)
SELECT str FROM cte
`,
{
replacements: [String(request.params.size)],
type: QueryTypes.SELECT,
}
);
const parsedSymbol = verySecretSymbol
.map(function (line: any) {
return `${line.str} \n`;
})
.join('');
response.type('text/plain').status(201).send(parsedSymbol);
});Conclusion
The debate over doing SQL or using an ORM is as old as the world. In our case, it made sense to implement an ORM. The sweet spot for most projects is likely to combine the power of both ORM and SQL. You don’t necessarily need to only query through your ORM, nor to write only raw SQL for everything. The most important thing is to understand what you’re doing and not limit flexibility. Allow yourself to enjoy the best of both worlds.
It’s in the nature of most big apps to grow in ways that are hard to predict, no matter how good the developers are. Starting your project with an ORM may help you better organize its structure, boost your team’s productivity and improve the developers' quality of life.
Need more help?
Bitovi has expert Node.js and other backend consultants ready to dive in and assist you with your project! Schedule a free consultation to get started.
node.js
Previous Post
