Write SQL Queries With Confidence (TypeScript + Postgres)
Tags: typescript, postgres, javascript, sql
Published: 2022-09-10 22:34:37

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](https://safeql.dev) 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?

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

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

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

Here's a tweet that demonstrates it on live

{% embed https://twitter.com/CoEliya/status/1564036318229073921 %}

## Ok, how do I use it?

[See documentation](https://www.safeql.dev/guide/getting-started.html)

First, you need to install the plugin:

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

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

```json
{
  "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](https://www.prisma.io/) as our ORM and validate against a database URL:

```json
{
  "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!

```typescript
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](https://github.com/ts-safeql/safeql/issues/new/choose) in our GitHub repository.