TypeScript Types Generation

SQLX-TS provides powerful TypeScript type generation based on the raw SQL queries in your code. This feature significantly enhances type safety, eliminating the need for traditional ORMs.

Without automatic type generation, developers must manually maintain type definitions for each raw SQL query—a fragile and error-prone process that adds unnecessary overhead, especially in large projects. SQLX-TS streamlines this by ensuring type correctness directly from your queries, reducing maintenance and improving reliability.

Getting started

The easiest way to enable type generation in SQLX-TS is by using the -g or --generate-types flag when running the SQLX-TS CLI. It can be configured through configuration file as well, click here to learn more

$ cargo run --generate-types --config=.sqlxrc.json ./src/app

Capabilities

SQLX-TS supports following type generations and other SQL syntax will be ignored from type generation

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

Using Raw SQL Files

Create a SQL file with annotations:

-- users.sql
-- @name: getUserById
SELECT id, name, email FROM users WHERE id = $1;

-- @name: createUser
INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id;

Then, you can use the sqlx-ts CLI to generate TypeScript types and check the SQL queries

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

Database	Parameterised Query	Example
MySQL	?	SELECT * FROM items WHERE points > ? AND points < ?
Postgres	$1 $2 $3	SELECT * FROM items WHERE points > $1 AND points < $2

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.

DELETE statements

Annotations for overrides

Qualified table names

Tables can be qualified with a schema in PostgreSQL, or with a database in MySQL. SQLX-TS reads the columns of the table from that schema or database instead of the default one of the connection.

// PostgreSQL: reads columns of the `announcements` table in the `staff` schema
const announcements = sql`SELECT message FROM staff.announcements WHERE message = $1`

// MySQL: reads columns of the `announcements` table in the `staff` database,
// even if `DB_NAME` of the connection is another database
const announcements = sql`SELECT message FROM staff.announcements WHERE message = ?`

• Unqualified tables are read from the public schema in PostgreSQL and from the DB_NAME database in MySQL
• For database.schema.table names in PostgreSQL, the schema is used
• In SQLite, tables can be qualified with main, e.g. main.items
• The database user of the connection needs access to the schema or database. information_schema only lists tables the user has privileges on, so a table without access is reported as having no columns
• A query cannot use two tables with the same name from different schemas yet, e.g. staff.announcements with public.announcements, or staff.announcements with an unqualified announcements. SQLX-TS warns about it and reads the columns of the first qualified table, so the types of the other table may be incorrect

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.