• Stars
    star
    126
  • Rank 284,543 (Top 6 %)
  • Language
    TypeScript
  • Created about 5 years ago
  • Updated about 1 year ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

TypeScript types for raw PostgreSQL queries

sqltyper - Type your SQL queries!

tests

SQL is a typed language, but most solutions for using an SQL database from typed languages don't make use of that typing information in a way that would actually help you catch bugs during development.

sqltyper takes raw PostgreSQL queries and generates TypeScript functions that run those queries AND are typed correctly. The typings are generated by analyzing the schema from a running database.

This way, your SQL queries and the TypeScript code that uses them are validated at compile time. No more runtime errors because of SQL queries!

For example, assume your PostgreSQL database has a table like this:

 Table "person"

 Column      Type      Nullable
-----------+---------+----------
 name      | text    | not null
 age       | integer | not null
 shoe_size | integer | nullable

The following SQL query in find-persons.sql:

SELECT
  initcap(name) as name_capitalized,
  age,
  shoe_size
FROM person
WHERE
    name LIKE ${namePattern} AND
    age > ${minimumAge}

Converts to find-persons.ts:

import { ClientBase } from 'pg'

interface ResultRow {
  name_capitalized: string
  age: number
  shoe_size: number | null
}

interface Params {
  namePattern: string
  minimumAge: number
}

export function findPersons(client: ClientBase, params: Params): Promise<ResultRow[]> {
  ...
}

sqltyper analyses the query without actually executing it, so it's perfectly safe to use it with any query.

Installation

npm install --save-dev sqltyper

The generated TypeScript code uses node-postgres, postgres.js, or pg-promise to execute the queries, so either pg, postgres, or pg-promise is a required runtime dependency:

npm install --save pg
# or
npm install --save postgres@beta
# or
npm install --save pg-promise

At the time of writing, you need to install the @beta verson of postgres.js to get TypeScript support.

Tutorial

Assuming you have a TypeScrip app and a bunch of SQL queries, put them in files in a single directory, like this:

src/
|-- app.ts
|-- ...
`-- sqls/
    |-- my-query.sql
    `-- other-query.sql

In the SQL files, input parameters can be specified with either ${paramName} or :paramName syntax.

Run sqltyper on the sqls directory:

npx sqltyper --database postgres://user:pass@host/dbname src/sqls

# or yarn sqltyper, or ./node_modules/.bin/sqltyper, ...

sqltyper connects to the PostgreSQL database you give in the --database option, finds out the input and output types of each of the SQL queries, and outputs the corresponding TypeScript functions in the same directory.

You should now have the following files:

src/
|-- app.ts
|-- ...
`-- sqls/
    |-- index.ts
    |-- my-query.sql
    |-- my-query.ts
    |-- other-query.sql
    `-- other-query.ts

Each .sql file got a .ts file next to it. Each .ts file exports a single function, whose name is the .sql file name with the extension removed and camelCased. Furthermore, it generates an index.ts file that re-exports all these functions.

In app.ts, import the SQL query functions:

import * as sql from './sql'

And that's it! Now you can use sql.myQuery() and sql.otherQuery() to run the queries in a type-safe manner.

These functions take a Client or Pool from node-postgres as the first argument, and possible query parameters as the second parameter.

They will return one of the following, wrapped in a Promise:

  • An array of result objects, with object keys corresponding to output column names. Note that all of the output columns in your query must have a unique name, because otherwise some of them would be not accessible.

  • A single result object or null if the query only ever returns zero or one row (e.g. SELECT query with LIMIT 1).

  • A number which denotes the number of affected rows (e.g. INSERT, UPDATE or DELETE without a RETURNING clause).

CLI

sqltyper [options] DIRECTORY...

Generate TypeScript functions for SQL statements in all files in the given directories. For each input file, the output file name is generated by removing the file extension and appending .ts.

Each output file will export a single function whose name is a camelCased version of the basename of the input file.

sqltyper connects to the database to infer the parameter and output column types of each SQL statement. It does this without actually executing the SQL queries, so it's safe to run against any database.

Options:

--database, -d

Database URI to connect to, e.g. -d postgres://user:pass@localhost:5432/mydb. If not given, uses the connecting logic of node-postgres that relies on libpq environment variables.

--ext, -e

File extensions to consider, e.g. -e sql,psql. Default: sql.

--verbose, -v

Give verbose output about problems with inferring statement nullability. Default: false.

--watch, -w

Watch files and run the conversion when something changes. Default: false.

--target, -t

Whether to generate code for pg (node-postgres), postgres (postgres.js), or pg-promise (pg-promise). Default: pg.

--module, -m

Where to import node-postgres or postgres.js from. Default: pg for node-postgres, postgres for postgres.js.

--pg-module (deprecated)

Alias of --module.

--check,-c

Check whether all output files are up-to-date without actually updating them. If they are, exit with status 0, otherwise exit with status 1. Useful for CI or pre-commit hooks. Default: false.

--prettify, -p

Apply prettier to generated TypeScript files. prettier must be installed and configured for your project. Default: false.

--index

Whether to generate and index.ts file that re-exports all the generated functions. Default: true.

How does it work?

sqltyper connects to your database to look up the schema: which types there are, which tables there are, what columns and constraints the tables have, etc. The only queries it executes look up this information from various pg_catalog.* tables.

First, it substitutes any ${paramName} and :paramName strings with $1, $2, etc.

Then, it creates a prepared statement from the query, and then asks PostgreSQL to describe the prepared statement. PostgreSQL will reply with parameter types for $1, $2, etc., and columns types of the result rows.

However, this is not enough! In SQL basically anything anywhere can be NULL, so if sqltyper stopped here all the types would have to be e.g. integer | null, string | null and so on. For this reason, sqltyper also parses the SQL query with its built-in SQL parser and then starts finding out which expressions can never be NULL. It employs NOT NULL constraints, nullability guarantees of functions and operators, WHERE clause expressions, etc. to rule out as many possibilities of NULL as possible, and amends the original statement description with this information.

It also uses the parsing result to find out the possible number of results. For example, UPDATE, DELETE and INSERT queries without a RETURNING clause will return the number of affected rows instead of any columns. Furthermore, a SELECT query with LIMIT 1 will resolve to ResultRow | null instead of ResultRow[].

Then, it outputs a TypeScript function that is correctly typed, and when run, executes your query and converts input and output data to/from PostgreSQL.

About versioning

sqltyper follows semantic versioning, but enhancements to the parser and inferring logic are considered bug fixes, and thus only the patch version is incremented for releases that only contain these changes. The reasoning behind this is that all PostgreSQL syntax and semantics that sqltyper fails to support is a bug.

Other enhancements, like adding more CLI options, code generation targets, etc. are considered new features as usual.

Prior art

The main motivator for sqltyper was sqlτyped by Joni Freeman. It does more or less the same as sqltyper, but for Scala, and is designed to be used with MySQL. It uses JDBC, and is implemented as a Scala macro rather than an offline code generation tool.

Releasing

$ yarn version --new-version <major|minor|patch>
$ yarn publish
$ git push origin main --tags

Open https://github.com/akheron/sqltyper/releases, edit the draft release, select the newest version tag, adjust the description as needed.

More Repositories

1

jansson

C library for encoding, decoding and manipulating JSON data
C
3,072
star
2

optics-ts

Type-safe, ergonomic, polymorphic optics for TypeScript
TypeScript
769
star
3

typera

Type-safe routes for Express and Koa
TypeScript
175
star
4

cpython

**No longer updated!** CPython has moved to GitHub: https://github.com/python/cpython
Python
61
star
5

sala

Simple encrypted password storage
Rust
28
star
6

typera-openapi

Generate OpenAPI spec from typera routes
TypeScript
23
star
7

stango

Static web site generator for Python programmers
Python
23
star
8

diceware.py

Diceware passphrase generator
Python
20
star
9

multipy

Install multiple Python versions locally
Shell
19
star
10

dependabot-cron-action

Merge Dependabot pull requests in a cron schedule
TypeScript
10
star
11

cram

Git mirror of https://bitbucket.org/brodie/cram/
Python
8
star
12

skijump3-remake

Skijump International v3 (remake)
Rust
5
star
13

hashtable

A hashtable implementation
C
5
star
14

emacs-config

My emacs configuration
Emacs Lisp
4
star
15

xkb

My XKB config
4
star
16

elm-easter

Compute the date of Easter for any given year
Elm
3
star
17

purescript-httpure-extras

Common utilities for HTTPure
PureScript
3
star
18

passman

2
star
19

brainfuck

Brainfuck interpreter written in Elm
Elm
2
star
20

www.digip.org

The Stango project for my personal website
HTML
2
star
21

wings-reverse

Reversing Wings 1.40
Rust
2
star
22

git-hg

Push and pull from a Hg server using git
Shell
2
star
23

sinap

Sinap IRC bot
Python
1
star
24

microposture

A program for 2 micro:bit devices for monitoring your back posture
Python
1
star
25

jansson-debian

Debianization for Jansson
C
1
star
26

jansson-debian-pre2.0

Debianization for Jansson
Shell
1
star
27

sahko

Control electrical devices by turning GPIO pins on and off on a Raspberry Pi based on the electricity spot price
Rust
1
star