Configuration

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-flat-config or @ts-safeql-demos/basic.

// eslint.config.js

export default [
  // ...
  safeql.configs.connections({
    // The URL of the database:
    databaseUrl: "postgres://postgres:postgres@localhost:5432/my_database",
    // Check all of the queries that are used with the `sql` tag:
    targets: [{ tag: "sql" }],
  }),
];

Once you've set up your configuration, you can start linting your queries:

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

After auto-fix

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.

// eslint.config.js

export default [
  // ...
  safeql.configs.connections({
    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.

// eslint.config.js

export default [
  // ...
  safeql.configs.connections({
    connections: [
      {
        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 (When VSCode 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.

// eslint.config.js

export default [
  // ...
  safeql.configs.connections({
    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

// eslint.config.js

export default [
  // ...
  safeql.configs.connections({
    connections: [
      {
        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.

// eslint.config.js

export default [
  // ...
  safeql.configs.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.

// eslint.config.js

export default [
  // ...
  safeql.configs.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:

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

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

Note that the auto-generated type is on the function (wrapper) rather than on the tag.

// eslint.config.js

export default [
  // ...
  safeql.configs.connections({
    connections: [
      {
        migrationsDir: "./migrations",
        targets: [
          // Check all of the queries that matches db.queryOne(*`...`)
          { wrapper: "db.queryOne" },
        ],
      },
    ],
  }),
];