DEV Community: Eliya Cohen

You may not need an SQL query builder or ORM

Eliya Cohen — Thu, 06 Oct 2022 19:12:58 +0000

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

Raw SQL (also known as native SQL)
Query Builder
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.

Please Don't (Manually) Parameterize Your SQL Queries

Eliya Cohen — Fri, 16 Sep 2022 14:36:01 +0000

Last week, I wrote a post about a plugin (that lints your SQL queries) called SafeQL. While doing so, I wrote this line:

client.query(sql`SELECT * FROM comments WHERE id = ${id}`);

Immediately, I got responses from a few places that I should parameterize my queries since it's open for SQL injections.

While they are not entirely wrong, they are not correct.

I'll explain; writing variables inside a query string is indeed a call for SQL injection:

// ⚠️ Open to SQL injection
client.query(`SELECT * FROM comments WHERE id = ${id}`)
// id = "0 OR (other statement)"

While that is true, it is not what is happening in the code below

client.query(sql`SELECT * FROM comments WHERE id = ${id}`);
          // ^^^ tagged templated literal

What is this syntax?

It is called Tagged templates, and I am quoting MDN:

Tags allow you to parse template literals with a function. The first argument of a tag function contains an array of string values. The remaining arguments are related to the expressions.

Explain to me like I am 5

// this line
client.query(sql`SELECT * FROM comments WHERE id = ${id}`)

// turns into this:
client.query({
  text: "SELECT * FROM comments WHERE id = $1",
  values: [id]
})

sql (which is a tagged template literal) is a function that looks like this:

export function sql(
  template: TemplateStringsArray,
  ...values: unknown[]
);

// Typescript built-in type
interface TemplateStringsArray extends ReadonlyArray<string> {
    readonly raw: readonly string[];
}

Given the following code:

sql`SELECT * FROM comments WHERE id = ${id}`
// template === ["SELECT * FROM comments WHERE id = "]
// values === [id]

template would be equal to ["SELECT * FROM comments WHERE id = "] and values to [id].

But how node-postgres or Sequelize handle it?

After we demystified the term tagged template literal, how can we use it to our favor?

node-postgres allows writing SQL queries by:

// string only approach
client.query("select name FROM table_name WHERE id = $1", [name]);

// object approach
client.query({
  text: "select name FROM table_name WHERE id = $1",
  values: [name]
});

Sequelize allows us to write in an object approach as well (among others), but with query rather than a text:

sequelize.query({
  query: "select name FROM table_name WHERE id = $1",
  values: [name]
});

As we might have noticed, the object structure is similar to the same structure that the sql tag has returned. In other words, when using sql tag, it returns an object that resembles a parameterized query.

`sql` implementation in a nutshell

function sql(template: TemplateStringsArray, ...values: unknown[]) {
  let id = 0;
  let text = template[0];

  for (const value of values) {
    query += `$${++id}`;
    query += template[id];
  }

  // we pass both `query` and `text` so it would be compatible with both
  // node-postgres and Sequelize:
  return { text, query: text, values };
}

Why should I use it over manually parameterized queries?

It is easier, safer, less error-prone, and more straightforward. Large queries can go up to dozens of parameters. It is just a matter of time before they will be misplaced. In addition, reading the query while trying to figure out what is $5 or $14 can be challenging.

npm install

Instead of maintaining an sql implementation, there are a few packages that have already done it:

@ts-safeql/sql-tag
sql-template-strings
sql-template-tag

What's next?

If you are using TypeScript and care about type safety, check out SafeQL. It is a free and open-source ESLint plugin that ensures you never misspell a table, column, or any invalid syntax. As a plus, it automatically generates TypeScript types for each query you write.

Write SQL Queries With Confidence (TypeScript + Postgres)

Eliya Cohen — Sat, 10 Sep 2022 22:34:37 +0000

TL;DR- Check out https://safeql.dev

A Problem

Usually, we tend to operate against our database using ORMs such as Prisma, Sequelize, TypeORM, etc.

Most of the time, these libraries deliver a great DX, but in some cases, they can be limited. For example:

The library doesn't support a feature that you need.
The actual query that the library is generating is not optimal.
You have a complex query that writing it using the library is either impossible or hard to maintain.

In all of these cases, we find ourselves writing raw queries. At first, we might even write some tests around it to make sure
we won't mess it up in the future. Then, we might even manually write our types for each query.

Times go by, and you find yourself with a bunch of raw queries, and each time you write another one, you lose confidence. Then, your colleagues write more migrations, which becomes quite hard to follow.

That's where SafeQL comes into play.

What is SafeQL?

SafeQL is a plugin for ESLint that helps you write safe raw queries.

It does so by:

Checking your raw queries for syntax errors (e.g., trying to select a column that doesn't exist).
Warn you about type errors (e.g., trying to compare a string to an integer)
Warn you about missing/incorrect query TS types (and suggest fixes).

What does it look like?



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



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



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

Here's a tweet that demonstrates it on live

// Detect dark theme var iframe = document.getElementById('tweet-1564036318229073921-996'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1564036318229073921&theme=dark" }

Ok, how do I use it?

See documentation

First, you need to install the plugin:



npm install --save-dev @ts-safeql/eslint-plugin libpg-query

Then, you need to add the plugin to your ESLint config:



{
  "plugins": ["@ts-safeql/eslint-plugin"]
}

Finally, it depends on whether you want to get your type validation from a migrations folder or a database URL.
For simplicity's sake, we'll be using Prisma as our ORM and validate against a database URL:



{
  "rules": {
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            "databaseUrl": "postgres://postgres:postgres@localhost:5432/my_database",
            "name": "prisma",
            "operators": ["$queryRaw"]
          }
        ]
      }
    ]
  }
}

What's going on here?

As you might've seen, connections is an array rather than a single object. That's because you can have multiple connections.
For example, you might have a mainClient for your main database and a subClient for your sub-database.
In most cases, you would only have one connection.

Each connection has the following properties:

databaseUrl: The database URL to connect to.
name: The variable name that holds the connection (for example, prisma for prisma.$queryRaw(...)).
operators: An array of operators you use to execute queries (for example, prisma for prisma.$queryRaw(...)).

Take it for a spin

Now you have everything set up!



import { Prisma } from "@prisma/client";

async function getUserById(id: number) {
  const result = await prisma.$queryRaw(
                       ~~~~~~~~~~~~~~~~ // Error: Query is missing type annotation
    Prisma.sql`SELECT * FROM users  WHERE id = ${id}`
  )
}

SafeQL is currently in its very early stages, but we can make it better with the community's help! If you have any ideas/improvements/bugs to share, be sure to file an issue in our GitHub repository.