You may not need an SQL query builder or ORM

The are three levels of forms to interact with our database:

1. Raw SQL (also known as native SQL)
2. Query Builder
3. ORM

Raw SQL

With great power comes great responsibility. Raw SQL gives us the most low-level form of interaction with our database. This may lead to lots of issues. Namely SQL injections, typos, low-editor support, and typescript types support (we don't want to manually maintain the types).

Query Builder

A query builder can solve most of the Raw SQL approach. It does so by supplying wrapper functions rather than actually writing raw queries. It may look something like this:

const person = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select(['first_name', 'pet.name as pet_name'])
  .where('person.id', '=', id)
  .executeTakeFirst()

Taken from Kysely Minimal example.

While it does solve most of the issues of writing raw SQL, it has its own cons as well (This is not specific to Kysely):

• You don't get complete control over how you run your queries.
• You don't know what SQL query is actually being run.
• You can't simply copy-paste the query into your PgAdmin/DataGrip to debug your query.
• Language-specific syntax is limited (such as coalesce, functions, etc.).
• You're bound to the technology you're using. It can be cumbersome if you switch to a new library after a while.

ORMs

ORMs are usually the most high-level interaction form with your database. It gives you a more declarative approach to interacting with your database. Given the example I shown above, Let's compare it with Prisma:

prisma.person.findFirst({
  select: {
    id: true,
    Pet: {
      select: {
        name: true.
      }
    }
  },
  where: {
    id: id
  }
})

While it's more rows than we've written before, you don't need to define how pet is related to person since the ORM handles it for you. While it's more simplified compared to query builder (which can be controversial), it has its own cons as well:

• It's more limited than a query builder (note that I couldn't add a column alias (pet.name as pet_name).
• All of what I mentioned with query builder cons.

There's no silver bullet. Or is it?

Going back to raw SQL - What if you had a tool that allowed us to keep writing SQL while taking care of the abovementioned issues?

Today, there are popular SQL libraries help you write safe queries from SQL injections (such as Prisma and Postgres.js) using a tagged template syntax. I wrote a more in-depth post about it here:

sql`SELECT name FROM person WHERE id = ${id}`
// Will be evaluated to
// { query: "SELECT name FROM person WHERE id = $1, values: [id] }

But still, we're left with these issues:

• The query is open to typos.
• Missing typescript support.
• Open to runtime errors (such as ambiguous column names).

If there was a tool that plugs into your linter (ESLint) and helps you find and fix these issues, would you use it? Luckily, I just wrote one - SafeQL.

Demo:

Using this plugin will ensure the following:

Your query has neither language typos nor table/column typos.

client.query(sql`SELECT idd FROM comments`);
                        ~~~ Error: column "idd" does not exist

Your query has the correct type annotation. If a query doesn't have a type or has an invalid type, SafeQL will auto-fix it for you.

client.query(sql`SELECT id FROM comments`);
~~~~~~~~~~~~ Error: Query is missing type annotation

Your query won't have invalid where clauses (e.g., comparing an integer column to a boolean value).

function getById(id: number) {
    client.query(sql`SELECT * FROM comments WHERE body = ${id}`);
                                                       ~
                        Error: operator does not exist: text = integer
}

Since SafeQL is a plugin and not an SQL library, it gives you the following benefits:

• If you want to drop the plugin, you can simply remove it from ESLint, and everything should still be ok.
• You can use whatever SQL library you want (Although libraries that don't come with a built-in sql tagged literal will have to install a third-party package, such as @ts-safeql/sql-tag).
• If you care about bundle size, then you might be happy to know that SafeQL adds zero kilobytes to your bundle size since it's not a runtime code (just like any other ESLint plugin/rule).

While it does solve many issues, it's not perfect.

• Unlike ORMs and Query Builders, SafeQL doesn't have an auto-complete syntax.
• SafeQL is still actively developed. Although it hasn't reached a stable release yet, it has proven itself in a large codebase.

If you feel adventurous and want to try out SafeQL, be sure to check out the Getting Started page in the documentation 🙂

Conclusion

Query Builders and ORMs are solutions to problems. SafeQL is another solution. Each one has its downsides. In one project, we may prefer one solution over the other. In a different project, we may prefer mixing two solutions. It's really up to you.

Disclaimer: I'm not against using SQL query builders. While the solution I wrote solves most of what the query builder solves, each solution has its pros and cons.