SafeQL

Appearance

On this page

Configuration ​

Prerequisites ​

Make sure you've added @ts-safeql/eslint-plugin to your ESLint plugins and set parserOptions.project.

json
// .eslintrc.json
{
  "plugins": [..., "@ts-safeql/eslint-plugin"],
  "parserOptions": {
    "project": "./tsconfig.json"
  }
  ...
}

Example 1: Single database connected to your app ​

Connect using the same database and credentials your app uses

DEMO

Check out @ts-safeql-demos/basic for a working example.

json
// .eslintrc.json
{
  // ...
  "rules": {
    // ...
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": {
          // The URL of the database:
          "databaseUrl": "postgres://postgres:postgres@localhost:5432/my_database",
          "targets": [
            // Check all of the queries that are used with the `sql` tag:
            { "tag": "sql" }
          ]
        }
      }
    ]
  }
}

And now SafeQL will be able to lint your queries like so:

typescript
const query = sql`SELECT * FROM users`
              ~~~ Error: Query is missing type annotation (auto-fix)

After auto-fix

typescript
const query = sql<{ id: number; name: string; }>`SELECT * FROM users`
              ^^^ ✅ Query is valid and type-safe!

Example 2: Multiple databases connected to your apps ​

Connect using multiple databases and credentials used by multiple apps

DEMO

Check out @ts-safeql-demos/multi-connections for a working example.

json
// .eslintrc.json
{
  // ...
  "rules": {
    // ...
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            // The URL of the database:
            "databaseUrl": "postgres://postgres:postgres@localhost:5432/my_database_1",
            "targets": [
              // Check all of the queries that matches db1.sql`...`
              { "tag": "db1.sql" }
            ]
          },
          {
            // The URL of the database:
            "databaseUrl": "postgres://postgres:postgres@localhost:5432/my_database_2",
            "targets": [
              // Check all of the queries that matches db1.sql`...`
              { "tag": "db2.sql" }
            ]
          }
        ]
      }
    ]
  }
}

Example 3: Migrations ​

If your project contains .sql migration files, configuring connections.migrationsDir option instead of databaseUrl will automatically synchronize the changes in your migrations to a separate "shadow database", which will also be used to retrieve type information related to your queries.

This is beneficial in cases where it is impossible or inconvenient to manually keep your database in sync with your migrations.

DEMO

Check out @ts-safeql-demos/basic-migrations-raw for a working example.

json
// .eslintrc.json
{
  // ...
  "rules": {
    // ...
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            // The migrations path:
            "migrationsDir": "./migrations",
            "targets": [
              // Check all of the queries that matches db.sql`...`
              { "tag": "db.sql" }
            ]
            // To connect using alternate superuser credentials, see below
            // "connectionUrl": "postgres://pguser:password@localhost:5432/postgres"
          }
        ]
      }
    ]
  }
}

Why do we need a shadow database?

The shadow database is used to run the migrations on it, and then compare the raw queries against it. The shadow database is dropped and recreated every time ESLint initializes the query (When VS Code boots up, or when you run ESLint from the terminal).

What is connectionUrl and should I configure it? ​

TL;DR

If you're using migrations and your PostgreSQL superuser credentials are different than the default below, you will need to configure connectionUrl.

postgres://postgres:postgres@localhost:5432/postgres

The connectionUrl IS NOT the database and credentials your app uses - it is instead the default database and superuser credentials which are used to create the shadow database.

If you don't want to provide superuser credentials, you can also provide a role which has the permissions to run createdb and dropdb.

By default, the connectionUrl is set postgres://postgres:postgres@localhost:5432/postgres, but if you're using a different credentials, you'll need to change it to your needs.

Example 4: Multiple migration configurations ​

DEMO

Check out @ts-safeql-demos/multi-connections for a working example.

json
// .eslintrc.json
{
  // ...
  "rules": {
    // ...
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            "migrationsDir": "./db1/migrations",
            "targets": [
              // Check all of the queries that matches db1.sql`...`
              { "tag": "db1.sql" }
            ]
          },
          {
            "migrationsDir": "./db2/migrations",
            "targets": [
              // Check all of the queries that matches db2.sql`...`
              { "tag": "db2.sql" }
            ]
          }
        ]
      }
    ]
  }
}

Example 5: Mixing databaseUrl and migrationsDir configurations ​

json
// .eslintrc.json
{
  // ...
  "rules": {
    // ...
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            // The URL of the database:
            "databaseUrl": "postgres://postgres:postgres@localhost:5432/my_database",
            "targets": [
              // Check all of the queries that matches db1.sql`...`
              { "tag": "db1.sql" }
            ]
          },
          {
            "migrationsDir": "./packages/a/migrations",
            "targets": [
              // Check all of the queries that matches db2.sql`...`
              { "tag": "db2.sql" }
            ]
          },
          {
            "migrationsDir": "./packages/b/migrations",
            "targets": [
              // Check all of the queries that matches db3.sql`...`
              { "tag": "db3.sql" }
            ]
          }
        ]
      }
    ]
  }
}

Example 6: Using glob pattern ​

SafeQL uses minimatch to match the glob pattern.

json
```json{16}
// .eslintrc.json
{
  // ...
  "rules": {
    // ...
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            // The URL of the database:
            "databaseUrl": "postgres://postgres:postgres@localhost:5432/my_database",
            "targets": [
              // The sql tags that should be checked. 
              // either `db.$queryRaw` or `db.$executeRaw` 
              { "tag": "db.+($queryRaw|$executeRaw)" } 
            ]
          },
        ]
      }
    ]
  }
}

Example 7: Using regex ​

SafeQL can also use regex to match the sql tags.

json
```json{16}
// .eslintrc.json
{
  // ...
  "rules": {
    // ...
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            // The URL of the database:
            "databaseUrl": "postgres://postgres:postgres@localhost:5432/my_database",
            "targets": [
              // The sql tags that should be checked. 
              // either `db.$queryRaw` or `db.$executeRaw` 
              { "tag": { "regex": "db\\.($queryRaw|$executeRaw)" } } 
            ]
          },
        ]
      }
    ]
  }
}

Example 8: Using a wrapper function ​

Sometimes we want to wrap our queries with a function and set the type annotations in the wrapper instead. for example:

typescript
import { db, sql } from "./db";

function getName() {
  return db.queryOne<{name: string }>(
    sql`SELECT name FROM users WHERE id = ${1}`
  );
}

Note that the Typescript type is on the function (wrapper) rather than on the tag.

json
// .eslintrc.json
{
  // ...
  "connections": [
    {
      // ...
      "targets": [
        // Check all of the queries that matches db.queryOne(*`...`) 
        { "wrapper": "db.queryOne" } 
      ]
    },
  ]
}