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

3

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/

answered Oct 24 at 2:14

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

OuterTowner Oct 24 at 5:40

Thanks for your answer, can I understand it as I should move away from writing raw SQL directly and instead encapsulate and abstract it?

Bill Karwin Oct 24 at 6:10

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.

Shadow Oct 24 at 11:44

And if you make your data access layer easily extensible with generalised operations, then you have an ORM :)

OuterTowner Oct 24 at 12:33

Got it, thanks for the clarification!

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related