ts-sql-plugin
TypeScript Language Service Plugin for SQL with a tagged template strings SQL builder. Inspired by andywer/squid
check sql error demo:
autocomplete demo:
Usage
run as a language service plugin
Install the plugin, run:
npm install ts-sql-plugin -D
Then, configure the plugins
section in your tsconfig.json:
{
"compilerOptions": {
"module": "commonjs",
"target": "es5",
"plugins": [
{
"name": "ts-sql-plugin",
"command": "psql -c", // optionnal
"tags": { // optionnal
"sql": "sql",
"raw": "raw",
"cond": "cond",
"and": "and",
"or": "or",
"ins": "ins",
"upd": "upd",
"mock": "mock"
},
"mock": "0",
"cost_pattern": "/\\(cost=\\d+\\.?\\d*\\.\\.(\\d+\\.?\\d*)/",
"error_cost": null, // 100,
"warn_cost": null, // 50,
"info_cost": null, // -1,
"schema_command": "pg" // pg | mysql | custom - read the source
}
]
}
}
Note: If you're using Visual Studio Code, you'll have to use the first approach above, with a
path to the module, or run the "TypeScript: Select TypeScript Version" command and choose "Use
Workspace Version", or click the version number between "TypeScript" and
run as a command line application
Install the plugin, run:
npm install ts-sql-plugin -g
Then run:
ts-sql-plugin -p ./my_ts_project -c 'psql -U postgres -c'
Then in your code:
In the code below, some of the table names or column names are intentionally wrong. ts-sql-plugin
will show you the errors.
import sql from 'ts-sql-plugin/sql';
sql`select * from wrong_table_name where wrong_column_name=${name}`;
// { text: "select * from wrong_table_name where wrong_column_name=??", values: [name] }
// you should transform the text to pg specific or mysql specific query text
// sql.and
sql`select * from person where ${sql.and({ wrong_column_name: value, name: name })}`;
// sql.or
sql`select * from person where ${sql.or([{ 'name like': 'abc%', age: 23 }, { 'age >': 23 }])}`;
sql`select * from person where (name like ${'abc%'} and age=${23}) or age > ${23}`;
// sql.ins
sql`insert into person ${sql.ins({ id: uuid(), name: name, ageeee: wrong_column_name_value })}`;
sql`insert into person ${sql.ins([{ id: uuid(), name, age: 23 }, {id, name:'ppp', age:30}])}`;
// sql.upd
sql`update person set ${sql.upd({ wrong_name_column: name, age: 23 })} where id=${id}`;
// like, >, < etc
sql`select * from person where ${sql.and({ 'name like': '%'+name_like+'%', 'ageee >': age_bigger_than })}`;
// sql.raw with ?: operator
sql`select * from person order by age ${reverse ? sql.raw`desc` : sql.raw`asc`}`;
// sql.cond
sql`select * from person where name=${name} ${sql.cond(!!age_bigger_than)` and ageeee > ${age_bigger_than}`}`;
// ! where in will produce error because of node-postgres doesn't support it. use where column=any()
sql`select * from person where id in (${[uuid(), uuid()]})`;
sql`select * from person where id = any(${[uuid(), uuid()]})`;
sql`select * from ${sql.mock<'person' | 'book'>(sql.raw([table_name]))}`
sql`select * from person where age = ${sql.mock<'12'>(some_value_can_not_use_the_default_mock__0)}`
// you can use sql queries inside each other
const s1 = sql`select * from subscriptions.attribute where entity_id = any(${[7045]})`;
const s2 = sql`select * from (${s1}) attr where attribute_id = any(${[7049, 7050]})`;
// ignore cost for query stuff implemented
const s1 = sql`
-- ts-sql-plugin:ignore-cost
select * from subscriptions.attribute
`;
// you can emit sql to explicit file
// this will be emitted to emit-sql/AllAttribute.sql file
// you may change `emit-sql` folder to another via `--emit_dir` option of cli
// also `--watch` option of cli can be used to emit it in realtime
const s1 = sql`
/* ts-sql-plugin:emit("AllAttribute") */
/* @name AllAttribute */
select * from subscriptions.attribute
`;
And there is a complete example using ts-sql-plugin and skm_ts in folder test_ts_sql_plugin.
Generate types from emitted sql
You can use pgtyped tool for generating types from your emitted sql via directive:
/* ts-sql-plugin:emit("SomeInterface") */
/* @name SomeInterface */
For generate types in realtime, use --watch
option of cli.
VSCode syntax highlight extension
https://marketplace.visualstudio.com/items?itemName=darky.vscode-ts-sql-plugin