DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 966,155 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Cover image for Write SQL Queries With Confidence (TypeScript + Postgres)
Eliya Cohen
Eliya Cohen

Posted on

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
Enter fullscreen mode Exit fullscreen mode
function getById(id: number) {
    client.query(sql`SELECT * FROM comments WHERE body = ${id}`);
                                                       ~
                     // Error: operator does not exist: text = integer
}
Enter fullscreen mode Exit fullscreen mode
client.query(sql`SELECT id FROM comments`);
~~~~~~~~~~~~ // Error: Query is missing type annotation
Enter fullscreen mode Exit fullscreen mode

Here's a tweet that demonstrates it on live

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
Enter fullscreen mode Exit fullscreen mode

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

{
  "plugins": ["@ts-safeql/eslint-plugin"]
}
Enter fullscreen mode Exit fullscreen mode

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"]
          }
        ]
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

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}`
  )
}

Enter fullscreen mode Exit fullscreen mode

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.

Top comments (2)

Collapse
 
raibtoffoletto profile image
RaΓ­ B. Toffoletto

Very interesting!! Will check it out. Thanks πŸŽ‰

I like TypeORMs query build but still sometimes when you need to do a raw query, would be great to have a linter.

Does it support checking of parameters as well!? Because that variable in literal string query is just a call for sql injection.

Collapse
 
newbie012 profile image
Eliya Cohen Author

It won't be, since it's a template literal. Parameter support is definitely in my roadmap.

If you're interested, be sure to follow at github.com/ts-safeql/safeql/issues/14

Visualizing Promises and Async/Await 🀯

async await

☝️ Check out this all-time classic DEV post