ts-sql-plugin

TypeScript Language Service Plugin for SQL with a tagged template strings SQL builder. Inspired by andywer/squid

#### check sql error demo:
!readme_0

#### autocomplete demo:
!autocomplete

Usage

run as a language service plugin

Install the plugin, run:

``sh
npm install ts-sql-plugin -D
`

Then, configure the plugins section in your tsconfig.json:

`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 😃 in the lower-right
corner. Otherwise, VS Code will not be able to find your plugin. See https://github.com/microsoft/TypeScript/wiki/Writing-a-Language-Service-Plugin#testing-locally

run as a command line application

Install the plugin, run:

`sh
npm install ts-sql-plugin -g
`

Then run:

`sh
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.

`ts
import sql from 'ts-sql-plugin/sql';

sqlselect * 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
sqlselect * from person where ${sql.and({ wrong_column_name: value, name: name })};

// sql.or
sqlselect * from person where ${sql.or([{ 'name like': 'abc%', age: 23 }, { 'age >': 23 }])};
sqlselect * from person where (name like ${'abc%'} and age=${23}) or age > ${23};

// sql.ins
sqlinsert into person ${sql.ins({ id: uuid(), name: name, ageeee: wrong_column_name_value })};
sqlinsert into person ${sql.ins([{ id: uuid(), name, age: 23 }, {id, name:'ppp', age:30}])};

// sql.upd
sqlupdate person set ${sql.upd({ wrong_name_column: name, age: 23 })} where id=${id};

// like, >, < etc
sqlselect * from person where ${sql.and({ 'name like': '%'+name_like+'%', 'ageee >': age_bigger_than })};

// sql.raw with ?: operator
sqlselect * from person order by age ${reverse ? sql.rawdesc : sql.rawasc};

// sql.cond
sqlselect * 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()
sqlselect * from person where id in (${[uuid(), uuid()]});
sqlselect * from person where id = any(${[uuid(), uuid()]});
sqlselect * from ${sql.mock<'person' | 'book'>(sql.raw([table_name]))}
sqlselect * 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 = sqlselect * from subscriptions.attribute where entity_id = any(${[7045]});
const s2 = sqlselect * 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