@pgkit/typegen

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

The idea

This library gives you the type-safety of an ORM, 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 - Complex config types - Testing psqlCommand
  • Example config
  • Advanced Configuration
    • Controlling write destination
    • Modifying types
    • Modifying source files
• Enhancing Return Types
• Ignoring Queries
• Examples
• Migration from @slonik/typgen
• Migration from v0.8.0 of @slonik/typegen
• SQL files
• Usage with @typescript-eslint
• Limitations
• How it works
• Recommendations

Installation

npm install @pgkit/typegen --save-dev

Usage

npx pgkit-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 '@pgkit/client'

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 '@pgkit/client'

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 @pgkit/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, exporting an object containing the properties below.

Some options are only available via CLI, some are only available in the config. CLI arguments will always have precedence over config options.

|Option|CLI Argument            |Type|Default|Description| |-|-|-|-|-| |rootDir|--root-dir|string|'src'|Source root that the tool will search for files in.| |include|--include|string[]|['**/*.{ts,sql}']|Glob patterns for files to include in processing. Repeatable in CLI.| |exclude|--exclude|string[]|['**/node_modules/**']|Glob patterns for files to exclude from processing. Repeatable in CLI.| |since|--since|string \| undefined|undefined|Limit matched files to those which have been changed since the given git ref. Use "HEAD" for files changed since the last commit, "main" for files changed in a branch, etc.| |connectionURI|--connection-string|string|'postgresql:// postgres:postgres @localhost:5432/ postgres'|URI for connecting to psql. 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 pgkit to connect to the database.| |psqlCommand|--psql|string|'psql'|The CLI command for running the official postgres psql CLI client.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| |defaultType|--default-type|string|'unknown'|TypeScript type when no mapping is found. This should usually be unknown (or any if you like to live dangerously).| |poolConfig||PoolConfig \| undefined(see below)|undefined|Pgkit 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.| |logger||Logger(see below)|console|Logger object with debug, info, warn and error methods. Defaults to console.| |writeTypes(experimental)||WriteTypes(see below)|typegen. defaultWriteTypes|Control how files are written to disk. See the Advanced Configuration section.| ||--config|string|'typegen.config.js'|Path to configuration file.| ||--migrate|'<=0.8.0'|disabled|Before generating types, attempt to migrate a codebase which has used a prior version of this tool.| ||--watch|CLI argument|disabled|Run in watch mode.| ||--lazy|CLI argument|disabled|Skip initial processing of input files. Only useful with '--watch'.| ||--skip-check-clean|CLI argument|disabled|If enabled, the tool will not check the git status to ensure changes are checked in.|

Documentation for CLI arguments can be printed to your terminal with npx pgkit-typegen generate --help.

Complex config types

type Logger = Record<'error' | 'warn' | 'info' | 'debug', (msg: unknown) => void>;
type WriteTypes = (queries: AnalysedQuery[]) => Promise<void>;

Testing psqlCommand

You can check if your psql is working, and that your postgres version supports \gdesc with your connection string using this shell command:

echo 'select 123 as abc \gdesc' \| psql "postgresql://postgres:postgres@localhost:5432/postgres" -f -

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.

Example config

Here's a valid example config file.

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

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

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

Advanced Configuration

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/pgkit/discussions so 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 typegen = require('@pgkit/typegen')
const path = require('path')

/** @type {import('@pgkit/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('@pgkit/typegen')

/** @type {import('@pgkit/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('@pgkit/typegen')

/** @type {import('@pgkit/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('@pgkit/typegen')
const path = require('path')

/** @type {import('@pgkit/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('@pgkit/typegen')

/** @type {import('@pgkit/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('@pgkit/typegen')

/** @type {import('@pgkit/typegen').Options} */
module.exports.default = {
  writeTypes: typegen.defaultWriteTypes({
    writeFile: async (filepath, content) => {
      content = content
        .replaceAll(
          'declare module queries',
          'declare module some_other_naming_convention',
        )
        .replaceAll('queries.', '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('@pgkit/typegen')
const yourCustomLinter = require('@your-project/custom-linter')
const fs = require('fs')
const path = require('path')

/** @type {import('@pgkit/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)
    },
  }),
}

Enhancing Return Types

Typegen is designed to output types only to the degree it's certain they are correct.

Let's say in a complex query it can determine that a specific column will return a string, but isn't sure if it is also nullable, it will extract the type as { column: string | null }, just to be on the safe side. When it encounters columns where it is unable to even determine the basic type, i.e. json columns, it will return :shrug: (Ok, actually the typescript equivalent, which is unknown).

In these cases you likely know more about the actual return type than typegen and you might feel the urge to overwrite the types. Yet you shouldn't touch generated code, as your changes will be removed again on the next run.

Instead what you should do is add (one or more) intersection types to the sql literal, specifying the columns where you want to help typegen out by increasing specificity. The resulting type will be a combination of the extracted types and your enhancements. Check out the typescript docs on intersection types to learn more.

Imagine this is your code after running typegen.

sql<queries.ExtractedResult>`select string_col, json_col from table`

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

  /** - query: `select string_col, json_col from table` */
  export interface TestTable {
    /** column: `example_test.table.string_col`, regtype: `character_varying` */
    string_col: string | null,
    /** column: `example_test.table.json_col`, regtype: `jsonb` */
    json_col: unkown
  }
}

You can enhance the return type like this:

sql<queries.ExtractedResult & { json_col: string[] }>`[query]`

- or, if you prefer -

interface EnhancedResult {
  json_col: string[]
}
sql<queries.ExtractedResult & EnhancedResult>`[query]`

Either way the resulting type will be this:

type ResultingType = {
  string_col: string | null,
  json_col: string[]
}

On subsequent runs typegen will only update the first intersection type and leave all following intersections untouched.

This also means you can make the column string_col non-nullable by intersecting it with { string_col: string }.

Note that you can't completely change a property type (say from string to number) this way. This is by design, because if you could, a change in the underlying table might cause typegen to detect a new type, which would be ignored, had you overwritten it. This would cause type changes to go unnoticed and we can't have that. With intersections, the resulting property will be of type never, when an underlying column type changes. This will alert you to the change, so you can update your manual enhancements.

Ignoring Queries

For file-based ignores, you can use the exclude option to set a pattern or specific file(s) to be ignored via the config file or using the CLI option.

Typegen also automatically ignores all queries with zero chance of returning a result (i.e. sql fragments).

If you want to exclude a specific query from processing, you can add a --typegen-ignore or /* typegen-ignore */ comment anywhere in the query.

Examples

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

Migration from @slonik/typgen

@pgkit/typegen at time of writing is a like-for-like replacement for 0.15.0 of @slonik/typegen. In future, it will diverge, but will remain backwards-compatible.

Migration from v0.8.0 of @slonik/typegen

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 pgkit 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 include 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 '@pgkit/client'

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 result in an error:

import {sql} from '@pgkit/client'

sql`
  update table set col=1 where id=1 returning 1;
  update table set col=2 where id=2 returning 2;
`

The return type is not clearly assigned here. Every literal should only contain one query statement.

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 '@pgkit/client'

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 '@pgkit/client'

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 use of of the ignore options to skip processing the concerned queries.

Custom interceptors. Some interceptors 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 pgkit-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 typeParsers configured (see pgkit 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 pgkit-typegen generate
git diff --exit-code