Skip to content

A fully typed sql builder. Not maintained anymore, use it for your inspiration.

Notifications You must be signed in to change notification settings

hediet/ts-typed-sql

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

36 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

@hediet/typed-sql

Build Status Coverage Status

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:

import { table, column, tText, tInteger } from "@hediet/typed-sql";

const contacts = table({ name: "contacts", schema: "public" },
	{
		firstname: tText,
		lastname: tText,
		mother_id: tInteger.orNull(),
		father_id: tInteger.orNull(),
	},
	{ id: tInteger }
);

...

Connection

SQL queries are processed by an instance of DbConnection. To construct a DbConnection, a query service is required:

import { DbConnection, PostgreQueryService } from "@hediet/typed-sql";
import pg = require("pg");

const pool = new pg.Pool({
	database: "postgres",
	user: "postgres",
	password: "FXLjrQ0"
});

const queryService = new PostgreQueryService(pool, { shortenColumnNameIfUnambigous: true, skipQuotingIfNotRequired: true });
const dbCon = new DbConnection(queryService);

Some 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.exec(q); // returns all rows
await dbCon.firstOrUndefined(q); // returns the first row if there is any, otherwise undefined.
await dbCon.first(q); // returns the first row and throws an error if there is no row.
await dbCon.single(q); // returns the first row and ensures there is only one.

Select Queries

Basic Select
import { select, concat } from "hediet-typed-sql";

// Selects the id column from the contacts table.
await dbCon.exec(from(contacts).select("id"));

from(contacts).select(contacts.id.as("myId")); // selects id and renames it to "myId"
from(contacts).select(contacts.$all); // selects all columns

// Even complex expressions can be selected
from(contacts).select(concat(contacts.firstname, " ", contacts.lastname).as("fullName"));
Where
// a where clause takes any expression of type boolean.
from(contacts).where(contacts.name.isLike("Jon%");
Joins
const p = contacts.as("parents");

from(contacts)
	.leftJoin(p).on(
		p.id.isEqualTo(contacts.mother_id).or(p.id.isEqualTo(contacts.father_id))
	)

Delete

deleteFrom(contacts)
	.where(contacts.id.isIn([1, 2, 3]))
	.returning("id")

Insert

const id = await dbCon.firstValue(
	insertInto(contacts)
		.value({ firstname: "Hoster", lastname: "Tully", father_id: null, mother_id: null })
		.returning("id")
);

insertInto(contacts).valuesFrom(
	from(contacts)
		.select("firstname", "father_id", "mother_id")
		.select(concat(contacts.lastname, "2").as("lastname"))
)

Update

update(contacts)
	.set({ firstname: "test" })
	.where({ id: 1 })

About

A fully typed sql builder. Not maintained anymore, use it for your inspiration.

Resources

Stars

Watchers

Forks

Packages

No packages published