wsporto/typesql

TypeSQL

TypeSQL generates typesafe Typescript APIs from your SQL statements. Write your queries in raw SQL and TypeSQL generates the type-safe APIs to execute those queries.

Access your database directly without a heavy ORM, gain effortless type-safety, and make maintaining your SQL simple.

TypeSQL supports multiple SQL database backends:

PostgreSQL (Experimental)

• pg - PostgreSQL client for node.js.

MySQL

• mysql2 - the standard driver for mysql in NodeJS

SQLite

• better-sqlite3 - the fastest SQLite driver for NodeJS
• bun:sqlite - Bun's official high-performance SQLite driver

LibSQL

• libsql - the NodeJS driver for libSQL, the open-source fork of SQLite powering Turso

Cloudflare D1

• @cloudflare/d1 - Serverless SQLite-compatible database from Cloudflare.

Example

Having the following query in select-products.sql file.

SELECT
  id,
  product_name,
  list_price
FROM products
WHERE discontinued = 0
  AND list_price BETWEEN :minPrice AND :maxPrice

TypeSQL will generate the types and function in the file select-products.ts. Then you can import the generate code and execute as following:

deno syntax:

Some features:

• Do not restrict the use of SQL You dont need to learn any new query language, you can use SQL with all its power and expressiveness.

• Infer parameters and columns types. SELECT DATEDIFF(:date1, :date2) as days_stayed will resolve the date1 and date2 parameters to the type Date and the function return type as number.

• Infer parameter and column nullability. The nullable database column email will generate a nullable field for the query SELECT email FROM mytable, but will generate a non-nullable field for the query SELECT email FROM mytable WHERE email is not null;

• Infer the query return type (single row vs multiple rows). If the id is a primary key or unique key, then function for the query SELECT * FROM Books where id = :id will return Book|null, instead of Book[]. The same is true for filters with LIMIT 1;

• Allow the use of dynamic ORDER BY with auto-completion and compile-time verification. See here.

Usage

1. Install typesql globally:

npm install -g typesql-cli

1. Add the typesql.json configuration file in project root folder. You can generate an template with cli command typesql init.

{
  "databaseUri": "mysql://root:password@localhost/mydb",
  "sqlDir": "./sqls",
  "client": "mysql2",
  "authToken": "authtoken",
  "includeCrudTables": []
}

Options:

Option	Description	Example
client	Database client driver to use.	pg mysql2 better-sqlite3 libsql bun:sqlite d1
databaseUri	Connection string for the database. Supports environment variables (${VAR_NAME}).	mysql://root:password@localhost/mydb ./database.sqlite
sqlDir	Directory where SQL queries are stored. Will search recursively by appending the **/*.sql glob pattern.	./src
authToken	Authentication token. Required only for the libsql client. Supports environment variables (${VAR_NAME}).
includeCrudTables	Generates select, insert, update, and delete queries for specified tables.	['users', 'permissions', 'tags']
generateIndexFiles	Whether to recursively generate index.ts barrel files in each output folder. Defaults to true if not specified.	true

To load variables from a .env file, pass the --env-file flag:

typesql --env-file=.env compile

1. Write your queries in the folder specified in the configuration file. You can also use the cli to scaffold the queries.

sqls\
    select-products.sql
    insert-product.sql
    update-product.sql

1. Then run typesql compile --watch to start typesql in watch mode. After that you will have one Typescript file for each query file.

sqls\
    select-products.sql
    select-products.ts
    insert-product.sql
    insert-product.ts
    update-product.sql
    update-product.ts

1. Now you can import and use the generated code.

const products = await selectProducts(...

const updateResult = await updateProduct(...

Examples

Query scaffolding

INSERT

IN/NOT IN Clause

MySQL FUNCTIONS

ORDER BY and LIMIT clauses

LIKE

Nested Query Result

Project Status: Under Active Development

WARNING: This is a work-in-progress experimental project. It is under active development and its API might change.

Issues reports and feature requests are welcome.