[sqlx-ts] compile-time checked queries without DSL in Typescript

Jason Shin — Mon, 24 Jul 2023 10:56:28 +0000

Summary

This story is about sqlx-ts. It is a CLI tool that provides

Compile-time checked queries in your TypeScript code
TypeScript type generation against those SQLs
Database agnostic API
Work with both Typescript and Javascript

In short, it enables you to never ship broken and unoptimized queries to production. Here is an example of sqlx-ts running against PostgreSQL (but it works with MySQL as well)

in the video, sqlx-ts performed

SQL check to check if the SQL is compatible with PG
Generate Typescript interfaces so you can use the type to keep your code type-safe

As a result, you can never deploy SQL queries that are broken or unoptimized. Optimizing query is easier with raw SQLs as you can directly run SQL analyzer instead of grabbing a generated SQLs from ORMs.

sqlx-ts can work with complex queries as well such as joins

Design

sqlx-ts core is written in Rust. This enables a few key fundamentals

pattern matching to never miss on any AST pattern (when dealing with SQL and Typescript)
top-notch performance using multi-threading

Installation

For a full guide, check the official documentation

First, install sqlx-ts

npm install sqlx-ts
# or if yarn
yarn add sqlx-ts

installing sqlx-ts NPM module also installs Rust binary of sqlx-ts.

How to use

Check the full getting started guide

Check --help for all supported CLI arguments

npx sqlx-ts --help

You need to have a database with schema running locally (for example)

$ npx sqlx-ts <path to project> --db-host=127.0.0.1 --db-port=4321 --db-type=postgres --db-pass=postgres --db-user=postgres --db-name=sqlx-ts

Above command will search the target directory and validate all detected SQLs in-code against the target database's information_schema.

That's it! All your queries are valid if sqlx-ts did not throw an error.

Next, we can try generating TypeScript types against the detected SQLs

// src/app/index.ts
(async () => {
    const someQuery = await client.query(sql`
        SELECT items.id
        FROM items
    `)
    for (const row of someQuery.rows) {
        const { tableId, points } = row
        console.log(tableId, points)
    }
})();

Run npx sqlx-ts <path to project> --db-host=127.0.0.1 --db-port=4321 --db-type=postgres --db-pass=postgres --db-user=postgres --db-name=sqlx-ts -g *note that I added -g in the end.

The command generated

// src/app/index.queries.ts
export type SomeQueryParams = [];


export interface ISomeQueryResult {
    food_type: string;
    id: number;
    points: number;
    table_id: number;
    time_takes_to_cook: number;
};


export interface ISomeQueryQuery {
    params: SomeQueryParams;
    result: ISomeQueryResult;
};

(If you don't like the generated type's formatting, you can simply post-process them using prettier)

Now you can use the generated types against the database queries in-code

// src/app/index.ts
import { ISomeQueryResult } from './index.queries'

(async () => {
    const someQuery = await client.query<ISomeQueryResult>(sql`
        SELECT items.id
        FROM items
    `)
    for (const row of someQuery.rows) {
        const { tableId, points } = row
        console.log(tableId, points)
    }
})();

As a result, any changes in the schema would break the query during build-time.

You can also use annotations to

override database target
provide custom name for queries
override generated types

Supported features

You can imagine 95% of SQL features are supported for both SQL check and type-generation features.

feature	SQL check	TS generation	Future Roadmap
SELECT	yes	yes	N/A
UPDATE	yes	yes	N/A
DELETE	yes	yes	N/A
INSERT	yes	yes	N/A
JOINs	yes	yes	N/A
enums	yes	not yet	Will be supported in the near future
SQL analyzer	no	N/A	Will be supported in the near future as this will be a unique feature that sqlx-ts provides
Process .sql files	no	N/A	Will be added soon

Motivations

The project is largely inspired by a library in Rust sqlx. I'm always interested in how to keep code safe especially during compile time. Rust and its ecosystem always have something to learn and port over into TypeScript world for greater good.

Thank you and please report any issues on Github