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

Install typesql globally:

npm install -g typesql-cli

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. | <ul><li>pg</li><li>mysql2</li><li>better-sqlite3</li><li>libsql</li><li>bun:sqlite</li><li>d1</li></ul> | | databaseUri | Connection string for the database. Supports environment variables (${VAR_NAME}). | <ul><li>mysql://root:password@localhost/mydb</li><li>./database.sqlite</li></ul> | | 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'] |

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

typesql --env-file=.env compile

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

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

Now you can import and use the generated code.

const products = await selectProducts(...

const updateResult = await updateProduct(...

Examples

ORDER BY and LIMIT clauses

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.

Typesql

Install / Use

README