Write SQL Queries With Confidence (TypeScript + Postgres)

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:

1. Checking your raw queries for syntax errors (e.g., trying to select a column that doesn’t exist).
2. Warn you about type errors (e.g., trying to compare a string to an integer)
3. 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

Eliya Cohen

@coeliya

I’ve been experimenting lately with writing an #eslint plugin that generates #typescript types from raw #postgres SQL queries. In addition, it makes sure your query is valid, so you won’t get into trouble on runtime.

23:44 PM – 28 Aug 2022

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.