SQLx-ts is a CLI application featuring compile-time checked queries without a DSL and generates types against SQLs to keep your code type-safe

• Compile time checked queries - never ship a broken SQL query to production (and sqlx-ts is not an ORM)
• TypeScript type generations - generates type definitions based on the raw SQLs and you can use them with any MySQL or PostgreSQL driver
• Database Agnostic - support for PostgreSQL and MySQL (and more DB supports to come)
• TypeScript and JavaScript - supports for both TypeScript and JavaScript

sqlx-ts is not an ORM!

sqlx-ts supports compile-time checked queries and generated types against SQLs. It does not, however, do this by providing DSL (domain-specific language) for building queries. Instead, it provides macros that take regular SQL as input and ensure that it is valid against the target database. The way it works is that sqlx-ts connects to your local or development database at compile-time and have database itself to verify the queries, also generate types based on information_schema. This can have many benefits that typical ORMs cannot provide

• Since sqlx-ts simply sends the queries back to the DB, almost any SQL syntax can be used in sqlx-ts (including things added by database extensions)
• You can easily optimize the queries as the SQLs are not built using a query-builder or an ORM interface
• The generated types work well with almost any database driver or even ORMs if they have good typescript support (that allows you to override input and output types)

but it comes with some implications

• The amount of information that sqlx-ts retrieves depend on the type of the database (e.g. MySQL vs PostgreSQL)

If you are looking for an ORM, you can checkout Sequelize or Prisma.

Using sqlx-ts in vanilla JavaScript

If you are trying to use sqlx-ts against vanilla JS, the TypeScript type-generation feature wouldn't be helpful. You can simply use SQL Check feature to keep your SQLs in JS code safe and compile-time checked.

Motivation

I would like to bring the powerful compile-time safety ideas to Node.js. sqlx is a great example of this, as it provides compile-time check of SQLs within your Rust code and Rust itself provides a great environment for tools like sqlx. sqlx-ts is greatly inspired by sqlx, but solves additional problems of generating TypeScript interfaces based on the SQL queries that are present in your code.

Introduction

sqlx-ts is a standalone CLI tool to validate and type generate against SQLs during compile-time of your Node/TypeScript projects. sqlx-ts is database agnostic (works with MySQL, Postgres and more).

validates SQLs in your code at compile time

generate types for type-safety

works with multiple types of databases

supports SELECT, UPDATE, DELETE, and INSERT statements

Getting Started

Prerequisites

Make sure that you have Node.js (> v12) and TypeScript installed. Also, you must have curl available as it is required during the installation steps.

Installation

With npm:

$ npm install sqlx-ts

With yarn:

$ yarn add sqlx-ts

Verify the installation of sqlx-ts binary by running

$ npx sqlx-ts --version

🚀 you now have sqlx-ts installed 🚀

curl installation approach

You can decide to install sqlx-ts independently using a simple curl command.

NOTE: This is not an usual way to install to use sqlx-ts. However, it can be used in an environment without NPM.

Examples:

# The following command will install sqlx-ts (v0.9.0) for MacOS (darwin) with M1 CPU (arm64)
# overwrite the existing sqlx-ts (-f) at the default installation location (pwd)
curl -LSfs https://jasonshin.github.io/sqlx-ts/install.sh | bash -s -- \
    --os darwin
    --cpu arm64
    --tag v0.9.0
    -f

You can find more information about the install.sh

curl -LSfs https://jasonshin.github.io/sqlx-ts/install.sh | bash -s -- \
    --help

You can find the latest releases of sqlx-ts here to find an appropriate value for --tag

Configuration

Example 1: Single database connection using CLI options

Run this in your terminal

sqlx-ts ./src/app \
    --db-host=127.0.0.1 \ 
    --db-port=54321 \
    --db-type=postgres \
    --db-user=postgres \
    --db-pass=postgres \
    --generate-types

The CLI can be triggered in this format sqlx-ts check the CLI Options page for more details.

Example 2: single database connection using .sqlxrc.json file

Create a file called .sqlxrc.json at the root level of your project where you will run sqlx-ts binary

{
  "generateTypes": {
    "enabled": true
  },
  "connections": {
    "default": {
      "DB_TYPE": "postgres",
      "DB_HOST": "127.0.0.1",
      "DB_PORT": 54321,
      "DB_USER": "postgres",
      "DB_PASS": "postgres",
      "DB_NAME": "postgres"
    }
  }
}

Then run this in your terminal

cargo run ./src/app --config .sqlxrc.json

Example 3: Multiple database connections using .sqlxrc.json file

Create a file called .sqlxrc.json at the root level of your project where you will run sqlx-ts binary

{
  "generateTypes": {
    "enabled": true
  },
  "connections": {
    "default": {
      "DB_TYPE": "postgres",
      "DB_HOST": "127.0.0.1",
      "DB_PORT": 4321,
      "DB_USER": "postgres",
      "DB_PASS": "postgres",
      "DB_NAME": "postgres"
    },
    "mysql": {
      "DB_TYPE": "mysql",
      "DB_HOST": "127.0.0.1",
      "DB_PORT": 3306,
      "DB_USER": "root",
      "DB_NAME": "mysql"
    }
  }
}

Then run this in your terminal

cargo run ./src/app --config .sqlxrc.json

Notes

By default, sql check operation ignores node_modules. Any extra ignore paths can be specified using --ignore=<path>.

Connecting to databases

sqlx-ts supports the following approach connect to the database

1. File based configuration
2. CLI options
3. Environment variables

If you provide database host by a CLI option and an environment variable, CLI option will take the priority over the environment variable.

1. File based config

If your project needs connections to multiple databases for SQL check, you have to use the file based config and specify connection details for those databases. Please check here for more details

2. CLI options

You can only configure the primary database connection through CLI options. Please check here for more details

3. Environment variables

You can only configure the primary database connection through environment variables. Please check here for more details

File Base Configuration for connecting to Multiple DB

If you have a project that you need requires connections to multiple databases, you can support that by using file based configuration.

By default, configuration file is named .sqlxrc.json and sqlx-ts will try to find a file with this name, unless you give it a custom path to override it using --config CLI option.

$ sqlx-ts --config <path to a custom .sqlxrc.json>

Example .sqlxrc.json

{
  "generate_types": {
    "enabled": true,
    "convertToCamelCaseColumnName": true
  },
  "connections": {
    "default": {
      "DB_TYPE": "mysql",
      "DB_USER": "root",
      "DB_HOST": "127.0.0.1",
      "DB_PORT": 3306
    },
    "postgres": {
      "DB_TYPE": "postgres",
      "DB_USER": "postgres",
      "DB_PASS": "postgres",
      "DB_HOST": "127.0.0.1",
      "DB_PORT": 4321,
      "PG_SEARCH_PATH": "public,myschema"
    },
    "some_other_db": {
      "DB_TYPE": "mysql",
      "DB_USER": "app_user",
      "DB_PASS": "password",
      "DB_HOST": "127.0.0.1",
      "DB_PORT": 3307,
      "POOL_SIZE": 20
    }
  }
}

(required) "connections"

For default database, you must call it default like example above. Any extra DB connections should have its own unique name such as postgres or some_other_db

Along with the configuration above, when writing SQLs in your codebase, you need to provide supportive comment in your raw SQL, indicate which database the query should point.

For example,

import { sql } from 'sqlx-ts'

// targets the default DB
const defaultDbSQL = sql`SELECT * FROM test;`
// targets the config with the name `postgres`
const postgresSQL = sql`
 -- @db: postgres
 SELECT * FROM other_table;
`

Supported fields of each connection include

• DB_TYPE: type of database connection (mysql | postgres)
• DB_USER: database user name
• DB_PASS: database password
• DB_HOST: database host (e.g. 127.0.0.1)
• DB_PORT: database port (e.g. 4321)
• PG_SEARCH_PATH: PostgreSQL schema search path (default is "$user,public") https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
• POOL_SIZE: Size of the connection pool to establish per connection type

generate_types

{
  "generateTypes: {
    enabled: true|false,
    columnNamingConvention: "upper | lower | title | camel | pascal | snake | kebab"
  },
  "connections": {
    ...
  }
}

Support for configuration of generate types operations.

• enabled (default: false): enables type generation via config
• columnNamingConvention (optional): When generating field name based on table's column name, you can pass in a type of naming convention to be used
  • oneOf: upper | lower | title | camel | pascal | snake | kebab

sqlx-ts CLI Options

To check the latest CLI options available, please run

$ sqlx-ts --help

Environment variables

.sqlxignore

you can use .sqlxignore file in order to ignore certain files from being processed.

By default, *.queries.ts are ignored by sqlx-ts as these files are the default extension of generated types.

You can include more files in the ignore list by creating .sqlxignore

*.queries.ts
*.something.*

* ignores all matching patterns.

• The first one ignores all files with .queries.ts extension
• The second one ignores all files that has .something. as part of the file path / name

SQL Check

Checking SQL is the core functionality of sqlx-ts. It is the default command when you run sqlx-ts binary as well.

By default, you pass in connection options to connect to the primary database. You can configure how sqlx-ts figures out database connection configurations by checking out the configuration page

Running sqlx-ts to check SQLs

sqlx-ts <path-to-node-project> \
  --config .sqlxrc.json \
  -g

As a result, you'd get, for example:

TypeScript Types Generation

sqlx-ts provides the powerful Typescript type generation based on the raw SQLs that you write in your code. The feature greatly enhances type safety of the raw SQL queries, ultimately replaces the need of ORM completely.

Without type generation, you would maintain type definition of each raw SQL manually, which is a brittle process and becomes an extra management in large projects.

Getting started

The simplest way to use type generation feature of sqlx-ts is by setting -g or --generate-types flag when running sqlx-ts CLI.

$ cargo run ./samples/type-generator-test --generate-types --config=<path to .sqlxrc.json>

By default, sqlx-ts will generate type

Please read more about .sqlxrc.json here

What's possible

sqlx-ts supports following type generations and other SQL syntax will be ignored from type generation

• SELECT statements
• INSERT statements
• UPDATE statements
• DELETE statements

Also type generation supports parameterised query as per the requirements of PREPARE statements of the databases that sqlx-ts currently supports.

• MySQL PREPARE
• Postgres PREPARE

in your codebase, if you have the following SQL query

const simpleQuery = sql`
SELECT *
FROM items
WHERE points > ?
AND points < ?
`

by running sqlx-ts type generation against the query, it would generate

export type ISimpleQueryParams = [number, number]

export interface ISimpleQueryResult {
    id: string
    points: number
}

export interface ISimpleQueryQuery{
    params: ISimpleQueryParams;
    result: ISimpleQueryResult;
}

sqlx-ts will pick up name of the variable and use it when generating the type definitions. You can override the type name by setting the @name annotation, you can read more in the section below.

Configuration

You can configure type generation in .sqlxrc.json; available configurations:

Todo We need configurations for following:

• generate type paths
• file names (by default, it should be file_name.types.ts)

Q/A

Why doesn't SQLx support named parameterised queries? e.g. SELECT * FROM items WHERE point = :point

We believe that there is no official way in Typescript to ensure the value level type-safety yet and providing named parameter does not guarantee the true type safetiness that we are trying to achieve. Furthermore, name parameter is not the syntax supported by native database drivers, but they are an additional syntax supported by popular ORM libraries such as Sequelize.

The closest type safety that exists in TypeScript world is by emulating Opaque type e.g. https://github.com/sindresorhus/type-fest/blob/main/source/opaque.d.ts. In the future, sqlx-ts will support Opaque type overrides natively to solve this matter.

SELECT statement

Parameter is way to denote value replacements in a SQL query. Each database uses different placeholder symbol:

Based on your target database, you should use its known placeholder symbol. sqlx-ts is able to handle both MySQL and PostgreSQL placeholder syntax.

Example database

Throughout this section of the guide, we will be using the following database ERD

Binary operations

If you have the follow MySQL query in typescript

// example
const someQuery = sql`
SELECT *
FROM items
WHERE points > ?;

would generate

export type SomeQueryParams = []

export interface ISomeQueryResult {
    id: string
    points: number
}

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

IN list query

If you have the following MySQL query

// example
const someQuery = sql`
SELECT *
FROM items
WHERE id IN (?);

would generate following typescript types

export type SomeQueryParams = [Array<number>]

export interface ISomeQueryResult {
    id: string
    points: number
}

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

Subqueries

Query params within subqueries are interpreted as well. If you have the following MySQL query

const someQuery = sql`
SELECT id, points
FROM items
WHERE id IN (
    SELECT id FROM items
    WHERE
        points > ?
        AND id IN (SELECT id FROM items WHERE food_type = ?)
)
AND points < ?
`

would generate following type definitions

export type SomeQueryParams = [number, string, number]

export interface ISomeQueryResult {
    id: string
    points: number
}

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

Note that QueryParams array respects the order of params present in the query above

INSERT statement

To read more about how sqlx-ts translates query parameters, visit this page

MySQL

Query params within an insert statement can be converted into TypeScript types as well

const someQuery = sql`
INSERT INTO items (id, food_type, time_takes_to_cook, table_id, points)
VALUES
    (?, ?, ?, ?, ?),
    (?, ?, ?, ?, ?);
`

would generate following

export type SomeQueryParams = [
    [number, string, number, number, number],
    [number, string, number, number, number]
]

export interface ISomeQueryQuery {
    params: SomeQueryParams
    result: null
}

PostgreSQL

As query params of PostgresSQL uses the numbered parameters, it's meaningless to generate a nested array respresnetation of them.

If you have

const somePostgresInputQuery = sql`
INSERT INTO items (id, food_type, time_takes_to_cook, table_id, points)
VALUES
($2, $1, 2, $3, 2),
($5, 'test', $4, $7, $6);
`

Above query will generate the following typings

export type SomePostgresInputQueryParams = [string, number, number, number, number, number, number];

export interface ISomePostgresInputQueryResult {
    
};

export interface ISomePostgresInputQueryQuery {
    params: SomePostgresInputQueryParams;
    result: ISomePostgresInputQueryResult;
};

DELETE statement

To read more about how sqlx-ts translates query parameters, visit this page

MySQL

Query params within a DELETE statement will be converted Typescript types

const someQuery = sql`
DELETE FROM items WHERE id = ?
`

would generate following

export type SomeQueryParams = [number]

export interface ISomeQueryQuery {
    params: SomeQueryParams
    results: null
}

PostgreSQL

If you the following query in your Typescript code

const someQuery = sql`
DELETE FROM items WHERE id = $1;
`

would result in

export type SomeQueryParams = [number]

export interface ISomeQueryQuery {
    params: SomeQueryParams
    results: null
}

UPDATE statement

To read more about how sqlx-ts translates query parameters, visit this page

MySQL

Query params within an update statement can be converted into TypeScript types as well

const someQuery = sql`
UPDATE items
JOIN tables ON tables.id = items.table_id
SET items.food_type = ?
WHERE tables.id = ?
`

would generate following

export type SomeQueryParams = [string, number]

export type SomeQueryResult = number

export interface ISomeQueryQuery {
    params: SomeQueryParams
    result: SomeQueryResult
}

PostgreSQL

As query params of PostgresSQL uses the numbered parameters, it's meaningless to generate a nested array respresnetation of them.

If you have

const someQuery = sql`
UPDATE items
JOIN tables ON tables.id = items.table_id
SET items.food_type = $2
WHERE tables.id = $1
`

Above query will generate the following typings

export type SomePostgresInputQueryParams = [string, number, number, number, number, number, number];

export interface ISomePostgresInputQueryResult {
    
};

export interface ISomePostgresInputQueryQuery {
    params: SomePostgresInputQueryParams;
    result: ISomePostgresInputQueryResult;
};

Annotations

Annotation is a way to configure custom settings for the type generator. Here are the supported annotations

// example
const someQuery = sql`
-- @name: simpleQuery
-- @db: mysql
SELECT *
FROM items;
`

Above example query will generate following types

export type SimpleQueryParams = []

export interface ISimpleQueryResult {
    id: string
    points: number
}

export interface ISimpleQueryQuery{
    params: SimpleQueryParams;
    result: ISimpleQueryResult;
}

Note that types are generated with SimpleQuery namespace instead of the variable name SomeQuery. This is the result of setting @name annotation in the query. Also, it will use mysql database connection that you configured in .sqlxrc.json as a result of setting @db.

Overrides

sqlx-ts cannot generate typing for complex SQL syntax such as JSON functions. In this scenario, sqlx-ts will return any and you can use annotations to override the result to a type that you anticipate.

Supported types

Type override supports these types string, number, boolean, object, null, any, and never

Overriding results

const someQuery = sql`
-- @result points: number | null
SELECT
    id,
    JSON_EXTRACT(items.c, "$.points") as points
FROM items
`

would generate

export type SimpleQueryParams = []

export interface ISimpleQueryResult {
    id: string
    points: number
}

export interface ISimpleQueryQuery{
    params: SimpleQueryParams;
    result: ISimpleQueryResult;
}

It's important that you give it an alias so sqlx-ts can match it with your custom annotation.

Rust provides pattern matching that enforces you to handle all patterns of an enum. Based on this, we can exhaustively handle all SQL syntax and narrow down the patterns that sqlx-ts cannot handle. If you ever use following SQL syntax, it is encouraged to override the type using @result annotation.

Overriding params

Overriding generated type for a param works by adding an annotation -- @param <index>: type.

• index is the position of the query parameter within your SQL
• type is the type to override

MySQL

If you have a MySQL query like following, you can override param types like in the example below

const someQuery = sql`
-- @db: mysql
-- @param 1: number
-- @param 2: string
SELECT
    id, points
FROM items
WHERE points < ?
AND name = ?
`

it would generate the following type definitions

export type SimpleQueryParams = [number, string]

export interface ISimpleQueryResult {
    id: string
    points: number
}

export interface ISimpleQueryQuery{
    params: SimpleQueryParams;
    result: ISimpleQueryResult;
}

Postgres

If you have a Postgres query like following, you can override param types like in the example below

const someQuery = sql`
-- @db: postgres
-- @param 1: number
-- @param 2: string
SELECT
    id, points
FROM items
WHERE points < $2
AND name = $1
`

it would generate the following type definitions

export type SimpleQueryParams = [string, number]

export interface ISimpleQueryResult {
    id: string
    points: number
}

export interface ISimpleQueryQuery{
    params: SimpleQueryParams;
    result: ISimpleQueryResult;
}

It will respect the order of query parameters set by you $1 and $2, and generate the params in the order that it detects

Errors

Troubleshooting

MacOS

Running the binary simply returns [1] <PID> killed ./sqlx-ts --help

This is because sqlx-ts haven't yet solved the developer license issue described https://users. rust-lang.org/t/distributing-cli-apps-on-macos/70223/13 for MacOS specifically.

To fix this, you will need to goto System Preferences -> Security & Privacy

You will see sqlx-ts binary blocked. Please manually enable it and you can start using sqlx-ts locally.

Limitations

The page aims to list down limitations of sqlx-ts. So for the users who are interested in using the tool, you have a clear idea what are the current limitations. However, as we evolve the tools, some of the limitations would be fixed in the future releases.

1. parsing of SQL is done using sqlparser-rs and any bugs in this modules would be inherited

sqlparser-rs is an essential module of sqlx-ts in order to process SQLs into Typescript type definitions. As a result, any bug in this module will be inherited to sqlx-ts and we will need an update in the module in order to fix the problem. So far, sqlparser-rs is well maintained and being updated in the recent days.