Tradukisto

Generate Typescript types and Zod schemas for SQL queries

Tradukisto is a tool for generating Typescript types and Zod schemas for arbitrary SQL queries based on the Schema of a live Postgres database, plus some basic templating utilities.

Disclaimer

This library is currently in early development and may contain bugs, incomplete features, or breaking changes without notice. It is not yet production-ready and should be used with caution. Use of this library is at your own risk. The author accepts no responsibility for any data loss, corruption, or other damages that may occur as a result of using this software. By using this library, you acknowledge that you understand these risks.

Installation

npm install tradukisto or yarn add tradukisto

Usage

Run Tradukisto with npx tradukisto. You can optionally pass in the name of a config file to use as the first argument npx tradukisto tradukisto.config.json. If no config file is specified, Tradukisto will try to read ./tradukisto.config.json before falling-back to the default config.

The connection string for your database must be in an environment variable (by default DATABASE_URL). If you store your environment variables in a .env file you can run Tradukisto using a tool such as env-cmd:

npx env-cmd -X tradukisto tradukisto.config.json

Tradukisto uses glob from node:fs/promises internally which way cause an "ExperimentalWarning" error message in some Node versions. This warning can be silenced with npx --node-options='--no-warnings=ExperimentalWarning' tradukisto.

Configuration

Tradukisto can optionally use a JSON config file. The default values are shown below:

{
    // List of SQL input files using glob syntax. Can be a string or a string[].
    "files": "src/**/*.sql",
    // The name of the environment variable containing the Postgres connection string
    "connectionVariableName": "DATABASE_URL",
    // The maximum stack depth for partial expansions
    "partialStackDepth": 100,
    // Generate zod schemas for input and output types
    "zod": true,
}

Watch mode

Start tradukisto in watch mode by setting the WATCH environment variable to any truthy value:

WATCH=true npx tradukisto

Note that watch mode watches files, but you'll still need to manually restart after making database schema changes.

SQL files

In general, your SQL files should start with a @repo declaration to name the output. The repo name should start with an upper-case letter A-Z, followed by 0 or more upper- or lower-case letters, numbers, or underscores:

-- @repo UsersRepo

All SQL queries must be named by a @query declaration. The query name should start with a lower-case letter a-z, followed by 0 or more upper- or lower-case letters, numbers, or underscores:

-- @query getAllUsers
SELECT * FROM users

Arguments should be named and prefixed with a colon. Where possible, Tradukisto will automatically infer the types of parameters, but you can also explicitly cast the parameter to be sure:

-- @query getUserById
SELECT * FROM users WHERE id = :id::TEXT

By default, parameters must be defined and cannot be null. The make a nullable parameter, simply add an underscore to the end of the name. The underscore will not be in the name in the generated typescript; it just tells Tradukisto to make the parameter nullable:

-- @query getUserById
SELECT * FROM users WHERE status = COALESCE(:status_::INTEGER, 2)

Section of SQL that you wish to reuse may be defined using @partial. Note that the replacement is text-based rather than AST-based.

-- @partial verifiedUsersFilter(user_table, confirmations_table)
user_table.verified IS TRUE AND confirmations_table.confirmed IS TRUE

-- @query getVerifiedUsers
SELECT u.*
FROM users u
JOIN user_confirmations uc ON u.id = uc.user_id
WHERE verifiedUsersFilter(u, uc)

You can include other SQL files with @include which takes a relative path. Note that this only includes partials into the current file, not queries or repo declarations:

-- @include ../helpers.sql

License

Tradukisto is free software under the GNU AGPLv3. See the LICENSE file for details.

Note on Generated Output

While Tradukisto is licensed under the AGPLv3, any SQL, Typescript or other code generated by you through the use of this library is not considered part of the library itself and is not subject to the AGPLv3. You are free to license, use, and distribute generated output under terms of your own choosing.