hediet-typed-sql
A fully typed sql builder. Works best with TypeScript an Visual Studio Code. Currently only has support for PostgreSql, however, it should be very easy to implement SQL Generators for other SQL dialects.
Installation
hediet-typed-sql
can be installed via the node package manager using the command npm install hediet-typed-sql --save
.
Usage
This documentation is far from complete. However, most of the features are self-explanatory and can easily be explored by using the intellisense. Intellisense works great when using this library from TypeScript in VS Code.
Preparation
For proper typing, all used tables must be defined:
; ; ...
Connection
SQL queries are processed by an instance of DbConnection
.
To construct a DbConnection
, a query service is required:
;; ; ;;
Queries
Queries are independent of DbConnection
, however, an instance of DbConnection
is needed to execute queries.
If q
is a query, it can be executed by one of the methods that DbConnection
provides:
await dbCon.execq; // returns all rowsawait dbCon.firstOrUndefinedq; // returns the first row if there is any, otherwise undefined.await dbCon.firstq; // returns the first row and throws an error if there is no row.await dbCon.singleq; // returns the first row and ensures there is only one.
Select Queries
Basic Select
; // Selects the id column from the contacts table.await dbCon.execfromcontacts.select"id"; fromcontacts.selectcontacts.id.as"myId"; // selects id and renames it to "myId"fromcontacts.selectcontacts.$all; // selects all columns // Even complex expressions can be selectedfromcontacts.selectconcatcontacts.firstname, " ", contacts.lastname.as"fullName";
Where
// a where clause takes any expression of type boolean.fromcontacts.wherecontacts.name.isLike"Jon%";
Joins
; fromcontacts .leftJoinp.on p.id.isEqualTocontacts.mother_id.orp.id.isEqualTocontacts.father_id
Delete
deleteFromcontacts .wherecontacts.id.isIn .returning"id"
Insert
; insertIntocontacts.valuesFrom fromcontacts .select"firstname", "father_id", "mother_id" .selectconcatcontacts.lastname, "2".as"lastname"
Update
updatecontacts .set .where