Type-safe SQL

This is a type-safe query builder for SQL written in TypeScript. It has built-in support for Postgres and MySQL, and it shouldn't be hard to adopt it for other database engines.

Installing

npm install type-sql

Examples

letresult=awaitdb.from(BOOK).where(BOOK.author.lower().like('%john%').and(BOOK.price.lt(10).or(BOOK.available.eq(true))).and(BOOK.date.gte(newDate('2016-10-23T19:11:25.342Z')))).groupBy(BOOK.author,BOOK.available).having(BOOK.price.sum().between(1000,2000)).orderBy(BOOK.author.asc().nullsFirst(),BOOK.price.sum().desc()).offset(20).limit(10).select(BOOK.author,BOOK.available,BOOK.price.sum().as('sum_price'));awaitdb.table(BOOK).where(BOOK.author.eq('John Smith'),BOOK.price.lte(200)).update({author: 'John X.',available: false});awaitdb.table(BOOK).where(BOOK.title.isNull()).delete();letgeneratedId=awaitdb.table(BOOK).insert({title: '...',author: '...'});letbook=awaitdb.table(BOOK).get(generatedId);

Table definition

You have to define the structure of the tables with a table object, and optionally an entity interface. The table object is used for the builder, while the entity interface is used as an input type for INSERT and UPDATE queries, and as a result type of SELECT queries when all columns of a single table are queried.

(DDL generation from or to the table object is not supported)

import{QueryTable,StringColumn,NumberColumn,BooleanColumn,DateColumn,BasicColumn}from"type-sql";exportinterfaceBook{id?: number,title: string,author: string,price?: number,available?: boolean,date?: Date,data?: {x: number,y: number}}exportclassBookTableextendsQueryTable<Book,number>{id=newNumberColumn(this,'id');title=newStringColumn(this,'title');author=newStringColumn(this,'author');price=newNumberColumn(this,'price');date=newDateColumn(this,'date');available=newBooleanColumn(this,'available');data=newBasicColumn<this,any>(this,'data');$id=this.id}exportconstBOOK=newBookTable('Book');

Query executor

The database object that acts as the source of the query builder must be initialized with a Postgres or MySQL client:

PostgreSQL:

import{Client}from'pg';import{PgQuerySource}from"type-sql";letclient=newClient();client.connect();letdb=newPgQuerySource(client);

MySQL:

importmysql= require('mysql');import{MySqlQuerySource}from"type-sql";letclient=mysql.createConnection(...);client.connect();letdb=newMySqlQuerySource(client);

Features

The are many more examples among the tests, with features including

• joins
• more functions on columns
• composite ID
• inserting multiple entities
• shortcuts for deleting/updating/querying a single entity by ID
• aliased column name, for example to map snake_case columns to camelCase JavaScript object fields

SQL injection

The library uses "parameterized" queries by default, so that the input parameters are passed to the client separately from the sql string.

If for some reason you want to switch off the parameter escaping, then you can do it by passing the "parameterized: false" flag to the query source. Note that the query builder will still throw an error if the parameter's type is incorrect, so for example you can't pass a string to a number column even if the parameters are not escaped.

letdb=newPgQuerySource(client,{parameterized: false});

Logging

You can log the SQL queries performed by the library two different ways: By passing the logging flag to the query source object, and then the queries will be written to the console by the debug tool under the 'type-sql' tag:

letdb=newPgQuerySource(client,{logging: true});

Or you can pass a custom logger function to the query source:

letdb=newPgQuerySource(client,{logger: (sql: string,params?: any[])=>{console.log(sql,params);}})

Licensing

MIT License