**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.
select-products.sql file.
sql
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. _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: 'pg', 'mysql2', 'better-sqlite3', 'libsql', 'bun:sqlite' or 'd1'. The authToken configuration is used only for the libsql client.
`json
{
"databaseUri": "mysql://root:password@localhost/mydb",
"sqlDir": "./sqls",
"client": "mysql2",
"authToken": "authtoken",
"includeCrudTables": []
}
`
You can use environment variables in typesql.json with the ${VAR_NAME} syntax for databaseUri and authToken.
To load variables from a .env file, pass the --env-file flag:
`sh
typesql --env-file=.env compile
`
3. 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
`
4. 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
`
5. Now you can import and use the generated code.
`
const products = await selectProducts(...
const updateResult = await updateProduct(...
``