We're planting a tree for every job application! Click here to learn more

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

Jason Shin

30 Jul 2023

3 min read

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

Summary

[Github | Demo]

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

  1. SQL check to check if the SQL is compatible with PG
  2. 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](https://www.rust-lang.org/). This enables a few key fundamentals - [pattern matching](https://github.com/JasonShin/sqlx-ts/blob/main/src/ts_generator/sql_parser/expressions/translate_expr.rs) to never miss on any AST pattern (when dealing with SQL and Typescript) - system0level performance using [multi-threading](https://doc.rust-lang.org/book/ch16-01-threads.html) - asynchronous

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.

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

Did you like this article?

Jason Shin

Node, Python, Rust, Machine Learning, Functional Programming, React, Vue, Kubernetes and Scala.

See other articles by Jason

Related jobs

See all

Title

The company

  • Remote

Title

The company

  • Remote

Title

The company

  • Remote

Title

The company

  • Remote

Related articles

JavaScript Functional Style Made Simple

JavaScript Functional Style Made Simple

Daniel Boros

12 Sep 2021

JavaScript Functional Style Made Simple

JavaScript Functional Style Made Simple

Daniel Boros

12 Sep 2021

WorksHub

CareersCompaniesSitemapFunctional WorksBlockchain WorksJavaScript WorksAI WorksGolang WorksJava WorksPython WorksRemote Works
hello@works-hub.com

Ground Floor, Verse Building, 18 Brunswick Place, London, N1 6DZ

108 E 16th Street, New York, NY 10003

Subscribe to our newsletter

Join over 111,000 others and get access to exclusive content, job opportunities and more!

© 2023 WorksHub

Privacy PolicyDeveloped by WorksHub