@slonik/typegen

A library that uses slonik to generate typescript interfaces based on your sql queries.

The idea

This library gives you the type-safety of an ORM (in some cases, even more), while maintaining the flexibility of sql. Read @gajus's excellent blog post on why it's a good idea to use sql rather than ORMs or query-builders: Stop using Knex.js.

It will make sure that return values from all your SQL queries have strict, accurate TypeScript interfaces.

It works by scanning your source code, so you don't have to spend any time manually syncing interfaces. Write queries using the sql tag as normal, then run the CLI to apply strong types to them automatically. The compiler will then tell you if you got something wrong.

This method avoids the inner-platform effect that tends to come with ORMs. You can rename columns, call functions, use sub-select statements, do any kinds of join you want, and the types generated will be based on the query, not the table, so you won't be limited by ORM feature-sets.

Select statements, joins, and updates/inserts/deletes using returning are all supported - any sql query that returns a tabular value will have an interface generated for the row type. The interface will be automatically applied to the appropriate query result.

Contents

• The idea
• Contents
• Installation
• Usage
• Configuration
  • Example config
  • CLI options
  • writeTypes
    • Controlling write destination
    • Modifying types
    • Modifying source files
• Examples
• Migration from v0.8.0
• SQL files
• Usage with @typescript-eslint
• Limitations
• How it works
• Recommendations

Installation

npm install @slonik/typegen --save-dev

Usage

npx slonik-typegen generate

The above command will generate types using sensible default values. It will look for code in a src directory, and create interfaces for all sql-tagged queries it finds - e.g.

For a table defined with:

create table test_table(foo int not null, bar text);

comment on column test_table.bar is 'Look, ma! A comment from postgres!'

Source code before:

import {sql, createPool} from 'slonik'

export default async () => {
  const pool = createPool('...connection string...')

  const results = await pool.query(sql`select foo, bar from test_table`)

  results.rows.forEach(r => {
    console.log(r.foo)
    console.log(r.bar)
  })
}

Source code after:

import {sql, createPool} from 'slonik'

export default async () => {
  const pool = createPool('...connection string...')

  const results = await pool.query(sql<queries.TestTable>`select foo, bar from test_table`)

  results.rows.forEach(r => {
    console.log(r.foo) // foo has type 'number'
    console.log(r.bar) // bar has type 'string | null'
  })
}

export declare namespace queries {
  // Generated by @slonik/typegen

  /** - query: `select foo, bar from test_table` */
  export interface TestTable {
    /** column: `example_test.test_table.foo`, not null: `true`, regtype: `integer` */
    foo: number

    /**
     * Look, ma! A comment from postgres!
     *
     * column: `example_test.test_table.bar`, regtype: `text`
     */
    bar: string | null
  }
}

Configuration

The CLI can run with zero config, but there will usually be customisations needed depending on your project's setup. By default, the CLI will look for typegen.config.js file in the working directory. The config file can contain the following options (all are optional):

• rootDir - Source root that the tool will search for files in. Defaults to src. Can be overridden with the --root-dir CLI argument.
• glob - Glob pattern of files to search for. Defaults to searching for .ts and .sql files, ignore node_modules. Can be overridden with the --glob CLI argument.
• connectionURI - URI for connecting to psql. Defaults to postgresql://postgres:postgres@localhost:5432/postgres. Note that if you are using psql inside docker, you should make sure that the container and host port match, since this will be used both by psql and slonik to connect to the database.
• poolConfig - Slonik database pool configuration. Will be used to create a pool which issues queries to the database as the tool is running, and will have its type parsers inspected to ensure the generated types are correct. It's important to pass in a pool confguration which is the same as the one used in your application.
• psqlCommand - the CLI command for running the official postgres psql CLI client. Defaults to psql. You can test it's working, and that your postgres version supports \gdesc with your connection string using: echo 'select 123 as abc \gdesc' | psql "postgresql://postgres:postgres@localhost:5432/postgres" -f -. Note that right now this can't contain single quotes. This should also be configured to talk to the same database as the pool variable (and it should be a development database - don't run this tool in production!). If you are using docker compose, you can use a command like docker-compose exec -T postgres psql
• logger - Logger object with debug, info, warn and error methods. Defaults to console.
• writeTypes (advanced/experimental) - Control how files are written to disk. See the writeTypes section.

Example config

Here's a valid example config file.

const yourAppDB = require('./lib/db')

/** @type {import('@slonik/typegen').Options} */
module.exports.default = {
  rootDir: 'source', // maybe you don't like using `src`
  glob: ['{queries/**.ts,sql/**.sql}', {ignore: 'legacy-queries/**.sql'}],
  connectionURI: 'postgresql://postgres:postgres@localhost:5432/postgres',
  poolConfig: yourAppDB.getPool().configuration,
}

Note that the /** @type {import('@slonik/typegen').Options} */ comment is optional, but will ensure your IDE gives you type hints.

CLI options

Some of the options above can be overriden by the CLI:

usage: slonik-typegen generate [-h] [--config PATH] [--root-dir PATH]
                               [--connection-uri URI] [--psql COMMAND]
                               [--default-type TYPESCRIPT] [--glob PATTERN]
                               [--since REF] [--migrate {<=0.8.0}]
                               [--skip-check-clean] [--watch] [--lazy]
                               

Generates a directory containing with a 'sql' tag wrapper based on found 
queries found in source files. By default, searches 'src' for source files.

Optional arguments:

  -h, --help            Show this help message and exit.

  --config PATH         Path to a module containing parameters to be passed 
                        to 'generate'. If specified, it will be required and 
                        the export will be used as parameters. If not 
                        specified, defaults will be used. Note: other CLI 
                        arguments will override values set in this module

  --root-dir PATH       Path to the source directory containing SQL queries. 
                        Defaults to "src" if no value is provided

  --connection-uri URI  URI for connecting to postgres. Defaults to 
                        URI for connecting to postgres. Defaults to 

  --psql COMMAND        psql command used to query postgres via CLI client. e.
                        g. 'psql -h localhost -U postgres postgres' if 
                        running postgres locally, or 'docker-compose exec -T 
                        postgres psql -h localhost -U postgres postgres' if 
                        running with docker-compose. You can test this by 
                        running "<<your_psql_command>> -c 'select 1 as a, 2 
                        as b'". Note that this command will be executed 
                        dynamically, so avoid using any escape characters in 
                        here.

  --default-type TYPESCRIPT
                        TypeScript fallback type for when no type is found. 
                        Most simple types (text, int etc.) are mapped to 
                        their TypeScript equivalent automatically. This 
                        should usually be 'unknown', or 'any' if you like to 
                        live dangerously.

  --glob PATTERN        Glob pattern of source files to search for SQL 
                        queries in. By default searches for all ts and sql 
                        files under 'rootDir'

  --since REF           Limit affected files to those which have been changed 
                        since the given git ref. Use "--since HEAD" for files 
                        changed since the last commit, "--since main" for 
                        files changed in a branch, etc.

  --migrate {<=0.8.0}   Before generating types, attempt to migrate a 
                        codebase which has used a prior version of this tool

  --skip-check-clean    If enabled, the tool will not check the git status to 
                        ensure changes are checked in.

  --watch               Run the type checker in watch mode. Files will be run 
                        through the code generator when changed or added.

  --lazy                Skip initial processing of input files. Only useful 
                        with '--watch'.

There are some more configuration options documented in code, but these should be considered experimental, and might change without warning. You can try them out as documented below, but please start a discussion on this library's project page with some info about your use case so the API can be stabilised in a sensible way.

writeTypes

The writeTypes option allows you to tweak what's written to disk. Note that the usage style isn't finalised and might change in future. If you use it, please create a discussion about it in https://github.com/mmkal/slonik-tools/discussions so that your use-case doesn't get taken away unexpectedly.

Controlling write destination

By default, interfaces for SQL queries are added to a module at the end of the typescript file they're found in. You can tell the CLI to write the interfaces to a separate file instead using writeTypes:

const path = require('path')
const typegen = require('@slonik/typegen')

/** @type {import('@slonik/typegen').Options} */
module.exports.default = {
  writeTypes: typegen.defaultWriteTypes({
    queriesPathFromTS: filepath => path.join(path.dirname(filepath), '__sql__', path.basename(filepath)),
  }),
}

The interfaces will be written to a separate file under a __sql__ folder next to the source, and will be imported via import * as queries from './__sql__/file-name'.

Modifying types

You can modify the types generated before they are written to disk by defining a custom writeTypes implementation.

For example, you can create branded types (see what this outputs in tests):

const typegen = require('@slonik/typegen')

/** @type {import('@slonik/typegen').Options} */
module.exports.default = {
  writeTypes: queries => {
    queries.forEach(query => {
      query.fields.forEach(field => {
        // add a `_brand` to all string id fields:
        if (field.typescript === 'string' && field.column && field.column.name === '.id') {
          field.typescript = `(${field.typescript} & { _brand: ${JSON.stringify(field.column)} })`
        }
      })
    })

    return typegen.defaultWriteTypes()(queries)
  }
}

Or you could mark all fields as non-null (but probably shouldn't!):

const typegen = require('@slonik/typegen')

/** @type {import('@slonik/typegen').Options} */
module.exports.default = {
  writeTypes: queries => {
    queries.forEach(query => {
      query.fields.forEach(field => {
        field.nullability = 'assumed_not_null'
      })
    })

    return typegen.defaults.defaultWriteTypes()(queries)
  }
}

Or you could be more granular. If, for example, there's a particular file with a lot of nullable types that you can't (yet) add full strict typing to:

const typegen = require('@slonik/typegen')
const path = require('path')

/** @type {import('@slonik/typegen').Options} */
module.exports.default = {
  writeTypes: queries => {
    queries.forEach(query => {
      const filesWithLegacyNullableFields = [
        path.resolve(__dirname, 'path/to/file1.ts'),
        path.resolve(__dirname, 'path/to/file2.ts'),
      ]
      if (filesWithLegacyNullableFields.includes(query.file)) {
        query.fields.forEach(field => {
          if (field.nullability === 'unknown') {
            field.nullability = 'assumed_not_null'
          }
        })
      }
    })

    return typegen.defaults.defaultWriteTypes()(queries)
  }
}

Or you could use a custom type for json fields:

const typegen = require('@slonik/typegen')

/** @type {import('@slonik/typegen').Options} */
module.exports.default = {
  writeTypes: queries => {
    queries.forEach(query => {
      query.fields.forEach(field => {
        if (field.regtype === 'json' || field.regtype === 'jsonb') {
          field.typescript = `import('@your-project/custom-types').YourCustomType`
          // For more customisation, you could look up which type to use based on `field.column`.
        }
      })
    })

    return typegen.defaults.defaultWriteTypes()(queries)
  }
}

Modifying source files

You can also use writeTypes to define a hook that runs before writing to disk:

const typegen = require('@slonik/typegen')

/** @type {import('@slonik/typegen').Options} */
module.exports.default = {
  writeTypes: typegen.defaultWriteTypes({
    writeFile: async (filepath, content) => {
      content = content
        .replace(/declare module queries/g, 'declare module some_other_naming_convention')
        .replace(/queries\./g, 'some_other_naming_convention.')
      await typegen.defaults.defaultWriteFile(filepath, content)
    },
  })
}

Or you could override the default formatter (which uses prettier, if found):

const typegen = require('@slonik/typegen')
const yourCustomLinter = require('@your-project/custom-linter')
const fs = require('fs')
const path = require('path')

/** @type {import('@slonik/typegen').Options} */
module.exports.default = {
  writeTypes: typegen.defaults.defaultWriteTypes({
    writeFile: async (filepath, content) => {
      content = await yourCustomLinter.fix(filepath, content)
      await fs.promises.mkdir(path.dirname(filepath), {recursive: true}) // since you're not using the built-in `writeFile` you should explicitly call mkdir with {recursive: true}
      await fs.promises.writeFile(filepath, content)
    },
  })
}

Examples

The tests and corresponding fixtures are a good starting point to see what the code-generator will do.

Migration from v0.8.0

Version 0.8.0 and below of this library used a different style of code-generation. It had several drawbacks - it was a runtime dependency, and required queries to be actually run before types could be inferred. It also created a global repository of types, meaning two queries in separate locations which shared a name could clash with each other. It also required changing the way your code was written.

Conceptually, this library now does more work so you don't have to worry about it so much. Just write slonik code/queries as normal, and then run the CLI to add types to them. If you add a new column to any query, run it again to update the interfaces.

If you previously used the old version of the tool, you can run it once with the --migrate v0.8.0 CLI argument to automatically attempt to codemod your project. Note that this will, by default, check that your git status is clean before running since it modifies code in place. The codemod isn't advanced enough to find all usages of the old API, so have a look through what it does after running it to make sure the changes look OK. If they aren't, reset the git changes and either apply them manually and/or pass in a different glob value to avoid files that were incorrectly modified.

SQL files

The tool will also search for .sql files, and generate some typescript helpers for running the queries contained in them. Any parameters ($1, $2 etc.) will also be strongly typed, and become required inputs for running the query. See the SQL file fixtures for some examples, and the generated SQL usage test to see how it can be used.

Usage with @typescript-eslint

The default ruleset for @typescript-eslint/eslint-plugin prevents usage of typescript namespaces. To avoid lint errors for inline type declarations (which are perfectly valid!), add this to your eslint config:

"@typescript-eslint/no-namespace": ["warn", {"allowDeclarations": true}],

Limitations

Some dynamically-generated queries will not receive a type. One example is any query where the template express parameters are identifiers rather than values, e.g.

import {sql} from 'slonik'

const tableName = Math.random() < 0.5 ? 'foo' : 'bar'

export default sql`select * from ${sql.identifier([tableName])}`

In the above example, no type can be inferred because it's impossible to know whether the query will return values from table foo or bar.

---

Queries with multiple statements will also not receive a type:

import {sql} from 'slonik'

sql`
  insert into foo(a, b) values (1, 2);
  insert into foo(a, b) values (3, 4);
`

This kind of query does not return a value when executed anyway.

---

Queries using the pg_temp schema will usually not be typeable since the schema is ephemeral and only can be queried within a single session that psql doesn't have access to.

import {sql} from 'slonik'

sql`select * from pg_temp.my_temp_table`

---

Invalid SQL syntax will also be left untouched (they will result in an error being logged when running the CLI):

import {sql} from 'slonik'

sql`this is not even valid SQL!`

If you see errors being logged for SQL that you think is valid, feel free to raise an issue. In the meantime, you can create a variable const _sql = sql and use the _sql tag in the same way as sql. _sql will not be detected by the tool and can be used as normal.

---

Custom interceptors. Some interceptors, such as slonik-interceptor-field-name-transformation change the runtime shape of query results. You could try to match its behaviour with a custom writeTypes implementation, but it's recommended to just not using the interceptor in the first place. All it does is transform from snake-case to camel-case.

---

Finally, for some complex queries, static parsing might fail, making it not possible to determine statically if a column is nullable. If this happens, it will still receive a valid type, but the type will be string | null rather than string.

If you find such a case, please raise an issue to see if it's possible to handle - under the hood this library uses pgsql-ast-parser and you might have found an edge case which that library doesn't handle yet.

How it works

When you run slonik-typegen generate, the tool will scan your source files, and traverse their ASTs using the TypeScript compiler API. Note that typescript is a peer dependency for this reason.

On finding a sql query, it will issue a psql command using the flag \gdesc, which responds with a table of the columns and their corresponding types contained in the query. The query itself is never actually run.

The postgres type is then converted into typescript using an in-built mapping. Any slonik typeParsers configured (see slonik docs for more info) are inspected to infer the type of the value that will be returned by the query.

To determine whether query columns are nullable, the query is parsed using pgsql-ast-parser. Some more queries are sent to postgres to figure out whether query column can be null - in general, postgres is only able to guarantee if a query column is null if it comes directly from a table which declares that column non-null too.

Recommendations

1. Check in the types to source control. They're generated code, but it makes it much easier to track what was happened when a query was update, and see those changes over time.
2. After running CI, it's worth making sure that there are no working copy changes. For git, you can use git diff --exit-code:

npx slonik-typegen generate
git diff --exit-code