[sqlx-ts] compile-time checked queries without DSL in Typescript
Tags: typescript, sql, rust, opensource
Published: 2023-07-24 10:56:28

---
title: [sqlx-ts] compile-time checked queries without DSL in Typescript
published: true
description: 
tags: #typescript #sql #rust #opensource
cover_image: https://i.imgur.com/Xmd3jr0.png
# Use a ratio of 100:42 for best results.
# published_at: 2023-07-02 13:32 +0000
---

## Summary
[[Github](https://github.com/JasonShin/sqlx-ts) | [Demo](https://github.com/JasonShin/sqlx-ts-demo)]

This story is about [sqlx-ts](https://github.com/JasonShin/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)

<video controls>
  <source src="https://i.imgur.com/6Qc7zVr.mp4" type="video/mp4">
</video>

in the video, sqlx-ts performed
1. [SQL check](https://jasonshin.github.io/sqlx-ts/reference-guide/1.sql-check.html) to check if the SQL is compatible with PG
2. [Generate Typescript interfaces](https://jasonshin.github.io/sqlx-ts/reference-guide/4.typescript-types-generation.html) 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

<video controls>
  <source src="https://i.imgur.com/JR7hN8O.mp4" type="video/mp4">
</video>

## Design

<img width="150" src="https://i.imgur.com/2j3Rjd2.png" />
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)
- top-notch performance using [multi-threading](https://doc.rust-lang.org/book/ch16-01-threads.html)

## Installation

For a full guide, [check the official documentation](https://jasonshin.github.io/sqlx-ts/user-guide/1.installation.html)

First, install sqlx-ts

```bash
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](https://jasonshin.github.io/sqlx-ts/user-guide/2.getting-started.html)

Check `--help` for all supported CLI arguments

```bash
npx sqlx-ts --help
```

You need to have a database with schema running locally ([for example](https://github.com/JasonShin/sqlx-ts/blob/main/docker-compose.yml))

```bash
$ 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](https://dev.mysql.com/doc/refman/8.0/en/information-schema.html).

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

```typescript
// 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
```typescript
// 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](https://github.com/JasonShin/sqlx-ts-demo/blob/master/package.json#L9))

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

```typescript
// 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](https://jasonshin.github.io/sqlx-ts/reference-guide/4.5.annotations.html) 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](https://github.com/JasonShin/sqlx-ts/issues)