TypeSQL

Generate Typescript API from raw SQL. Supports MySQL, Sqlite and LibSQL (Tu...

SQL
wsporto/typesqltypesql.pages.devtypesql-cli

README

typesql


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.

  1. ```sql
  2. SELECT
  3.   id,
  4.   product_name,
  5.   list_price
  6. FROM products
  7. WHERE discontinued = 0
  8.   AND list_price BETWEEN :minPrice AND :maxPrice
  9. ```

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.

Usage


1. _npm install -g typesql-cli_

2. 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: 'mysql', 'sqlite' or 'libsql'. The authToken configuration is used only for the libsql client.

  1. ```json
  2. {
  3.   "databaseUri": "mysql://root:password@localhost/mydb",
  4.   "sqlDir": "./sqls",
  5.   "target": "node",
  6.   "client": "mysql",
  7.   "authToken": "authtoken",
  8.   "includeCrudTables": []
  9. }
  10. ```

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

  1. ```
  2. sqls\
  3.     select-products.sql
  4.     insert-product.sql
  5.     update-product.sql
  6. ```

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

  1. ```
  2. sqls\
  3.     select-products.sql
  4.     select-products.ts
  5.     insert-product.sql
  6.     insert-product.ts
  7.     update-product.sql
  8.     update-product.ts
  9. ```

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

  1. ```
  2. const products = await selectProducts(...

  4. const updateResult = await updateProduct(...
  5. ```

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.