TypeSQL: An alternative to access MySQL databases without an ORM. Write your queries in raw SQL and TypeSQL will generate a type-safe API to execute the queries.

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

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. The client option can be: 'mysql2', 'better-sqlite3', 'libsql' or 'bun:sqlite'. The authToken configuration is used only for the libsql client.

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

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

WARNING: This is a WIP experimental project. It is under active development and its API might change.

Issues reports and feature requests are welcome.