How can I make my Node.js backend code cleaner without using an ORM when working directly with SQL?

Question

I'm building a Node.js backend using plain SQL (no ORM, just mysql2 wrapped in a small helper). However, as my project grows, my route handlers start looking messy — full of raw SQL strings embedded in JavaScript.

For example, this route fetches configuration data by joining two tables (function and permission):

router.get("/config", async (req, res) => { const sql = ` SELECT \`function\`.\`function_key\` AS \`key\`, GROUP_CONCAT(DISTINCT \`permission\`.\`role_id\` ORDER BY \`permission\`.\`role_id\` ASC) AS permission FROM \`function\` LEFT JOIN \`permission\` ON \`function\`.\`function_id\` = \`permission\`.\`function_id\` GROUP BY \`function\`.\`function_id\` `; const { err, rows } = await db.async.all(sql, []); if (err) { console.error(err); return res.status(500).json({ code: 500, msg: "Database query failed" }); } const config = rows.map(row => ({ key: row.key, permission: row.permission ? row.permission.split(',').map(id => Number(id)) : [] })); return res.status(200).json({ code: 200, config }); });

While this works fine, I feel the backend looks like a bunch of SQL statements glued together with JavaScript. I want to keep using plain SQL (no Sequelize, Prisma, etc.), but I also want my code to look structured, maintainable, and testable.

What are some best practices or architectural patterns to organize raw SQL queries in a Node.js project?

@EddieReasoner Because I’m familiar with SQL, I don’t want to incur additional learning costs by switching to another technology. — OuterTowner
– OuterTowner, Commented Oct 24 at 2:05

Bill Karwin · Accepted Answer · 2025-10-24 02:14:12Z

You don't need to have direct SQL queries in your route handlers.

Develop a Data Access Layer to encapsulate your SQL queries and other code to work with the database.

This is old news to developers from more "enterprise" platforms like Java or C#. It might seem like a lot of work to add layers and layers of abstraction classes. It's like the old joke about "it's turtles all the way down."

But a little bit of abstraction can be a good thing. You keep cluttered-looking code out of your route handlers, make certain data operations reusable so you can invoke them from multiple routes, and it also makes your code easier to write tests for.

For ideas on how to design these classes, I recommend studying the patterns in Martin Fowler's books. Start here: https://martinfowler.com/eaaCatalog/

Thanks for your answer, can I understand it as I should move away from writing raw SQL directly and instead encapsulate and abstract it?
No — you can still write SQL directly, within classes you design for your data access layer. Then in your route handlers, invoke your data access classes to do things.
And if you make your data access layer easily extensible with generalised operations, then you have an ORM :)

Collectives™ on Stack Overflow

How can I make my Node.js backend code cleaner without using an ORM when working directly with SQL?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related