Advanced usage

Custom booleans values

Sometimes, especially in Oracle databases, you need to represent a boolean with other values except true or false. For example, if your field in the database represents the true value with the char Y and the false value with the char N.

For example:

import { Table } from "ts-sql-query/Table";
import { CustomBooleanTypeAdapter } from "ts-sql-query/TypeAdapter";

const tCustomCompany = new class TCustomCompany extends Table<DBConnection, 'TCustomCompany'> {
    id = this.autogeneratedPrimaryKey('id', 'int');
    name = this.column('name', 'string');
    isBig = this.column('is_big', 'boolean', new CustomBooleanTypeAdapter('Y', 'N'));
    constructor() {
        super('custom_company'); // table name in the database
    }
}();

The table custom_company the field is_big accepts the values Y and N. This field represents a boolean type, and on the JavaScript side, it will be mapped as boolean. But, on the database side, the field will be treated with appropriated values. The conversion between values will be performed by ts-sql-query automatically; you don't need to be worried about the type mismatching even if you try to assign the value to another field with a different way of representing booleans.

You can perform an insert in this way:

const insertCustomCompany = connection.insertInto(tCustomCompany).set({
        name: 'My Big Company',
        isBig: true
    }).returningLastInsertedId()
    .executeInsert();

The executed query is:

insert into custom_company (name, is_big) 
values ($1, case when $2 then 'Y' else 'N' end) 
returning id

The parameters are: [ 'My Big Company', true ]

The result type is:

const insertCustomCompany: Promise<number>

Or a select:

const selectAllBigCompanies = connection.selectFrom(tCustomCompany)
    .where(tCustomCompany.isBig)
    .select({
        id: tCustomCompany.id,
        name: tCustomCompany.name,
        isBig: tCustomCompany.isBig
    }).executeSelectMany();

The executed query is:

select id as id, name as name, (is_big = 'Y') as isBig 
from custom_company 
where (is_big = 'Y')

The parameters are: []

The result type is:

const selectAllBigCompanies: Promise<{
    id: number;
    name: string;
    isBig: boolean;
}[]>

Synchronous query runners

Some query runners support to execute the queries synchronously if you provide a Promise implementation that supports it, like synchronous-promise.

The query runners that support execute queries synchronously if you specify a synchronous Promise implementation are:

For example:

import { BetterSqlite3QueryRunner } from "ts-sql-query/queryRunners/BetterSqlite3QueryRunner";
import * as betterSqlite3 from "better-sqlite3";
import { SynchronousPromise } from "synchronous-promise";

const db = betterSqlite3('foobar.db', options);

async function main() {
    const connection = new DBConnection(new BetterSqlite3QueryRunner(db, { promise: SynchronousPromise }));
    // Do your queries here,  surrounding it by the sync function. For example:
    const selectCompanies = sync(connection.selectFrom(tCompany)
    .where(tCustomCompany.isBig)
    .select({
        id: tCompany.id,
        name: tCompany.name
    }).executeSelectMany());

    var result = sync(connection.insertInto...)
    result = sync(connection.update...)
    result = sync(connection.delete...)
}

In the case of synchronous-promise, you will need this utility function that transforms a promise in a synchronous output:

/**
 * This function unwraps the synchronous promise in a synchronous way,
 * returning the result.
 */
function sync<T>(promise: Promise<T>): T {
    const UNSET = Symbol('unset');

    let result: T | typeof UNSET = UNSET;
    let error: unknown | typeof UNSET = UNSET;

    promise.then(
        (r) => (result = r),
        (e) => (error = e),
    );

    // Propagate error, if available
    if (error !== UNSET) {
        throw error;
    }

    // Propagate result, if available
    if (result !== UNSET) {
        return result;
    }

    // Note: This wrapper is to be used in combination with the `SynchronousPromise` type,
    // which is not strictly Promise-spec-compliant because it does not defer when calling
    // `.then`. See https://www.npmjs.com/package/synchronous-promise for more details.
    // To ensure that we're indeed using a synchronous promise, ensure that the promise resolved
    // immediately.
    throw new Error(
        'You performed a real async operation, not a database operation, ' +
            'inside the function dedicated to calling the database',
    );
}

Encrypted ID

Sometimes you want to encrypt the ID handled by the database. To do it, you can create a custom data type and define the type conversion using a type adapter or extending the default type adapter. During the type conversion, you can encrypt and decrypt with the strategy you like; for the next example, IDEncrypter will be used (included in ts-sql-query).

You can create the connection and define the rules to handle a type called encryptedID:

import { PostgreSqlConnection } from "ts-sql-query/connections/PostgreSqlConnection";
import { IDEncrypter } from "ts-sql-query/extras/IDEncrypter";

class DBConnection extends PostgreSqlConnection<'DBConnection'> { 

    // PasswordEncrypter requires two strings of 16 chars of [A-Za-z0-9] working as passwords for the encrypt process
    private encrypter = new IDEncrypter('3zTvzr3p67VC61jm', '60iP0h6vJoEaJo8c');

    protected transformValueFromDB(value: unknown, type: string): unknown {
        if (type === 'encryptedID') {
            const id = super.transformValueFromDB(value, 'bigint');
            if (typeof id === 'bigint') {
                return this.encrypter.encrypt(id);
            } else {
                // return the value as is, it could be null
                return id;
            }
        }
        return super.transformValueFromDB(value, type);
    }
    protected transformValueToDB(value: unknown, type: string): unknown {
        if (type === 'encryptedID') {
            if (value === null || value === undefined) {
                // In case of null or undefined send null to the database
                return null;
            } else if (typeof value === 'string') {
                const id = this.encrypter.decrypt(value);
                return super.transformValueToDB(id, 'bigint');
            } else {
                throw new Error('Invalid id: ' + value);
            }
        }
        return super.transformValueToDB(value, type);
    }
}

You can create the table, specifying the id type as custom or customComparable with type name encryptedID and data type string (the type of the encrypted data):

import { Table } from "ts-sql-query/Table";

const tCompany = new class TCompany extends Table<DBConnection, 'TCompany'> {
    id = this.autogeneratedPrimaryKey<string>('id', 'customComparable', 'encryptedID');
    name = this.column('name', 'string');
    constructor() {
        super('company'); // table name in the database
    }
}();

const tCustomer = new class TCustomer extends Table<DBConnection, 'TCustomer'> {
    id = this.autogeneratedPrimaryKey<string>('id', 'customComparable', 'encryptedID');
    firstName = this.column('first_name', 'string');
    lastName = this.column('last_name', 'string');
    birthday = this.optionalColumn('birthday', 'localDate');
    companyId = this.column<string>('company_id', 'customComparable', 'encryptedID');
    constructor() {
        super('customer'); // table name in the database
    }
}();

If you execute an insert that returns the id, the id will be encrypted:

const id = await connection
            .insertInto(tCompany)
            .values({ name: 'ACME' })
            .returningLastInsertedId()
            .executeInsert()

The returned id will be uftSdCUhUTBQ0111 for id 1 in the database.

You can perform a select using the encrypted id:

let company = await connection
            .selectFrom(tCompany)
            .where(tCompany.id.equals('uftSdCUhUTBQ0111'))
            .select({
                id: tCompany.id,
                name: tCompany.name
            })
            .executeSelectOne()

The id used in the query will be sent to the database decrypted.

See IDEncrypter for more information to know how the password is encrypted.

Globally Encrypted ID

Sometimes you want to encrypt the ID handled by the database and ensure is globally unique. To do it, you can create a custom data type and define the type conversion using a type adapter or extending the default type adapter. During the type conversion, you can encrypt and decrypt with the strategy you like; for the next example, IDEncrypter will be used (included in ts-sql-query). You will need to define a prefix per table. In the example the custom data type name follows the pattern ID:prefix

You can create the connection and define the rules to handle the ID type:

import { PostgreSqlConnection } from "ts-sql-query/connections/PostgreSqlConnection";
import { IDEncrypter } from "ts-sql-query/extras/IDEncrypter";

class DBConnection extends PostgreSqlConnection<'DBConnection'> { 

    // PasswordEncrypter requires two strings of 16 chars of [A-Za-z0-9] working as passwords for the encrypt process
    private encrypter = new IDEncrypter('3zTvzr3p67VC61jm', '60iP0h6vJoEaJo8c');

    protected transformValueFromDB(value: unknown, type: string): unknown {
        if (type.startsWith('ID:')) {
            const id = super.transformValueFromDB(value, 'bigint');
            if (typeof id === 'bigint') {
                const prefix = encrypted.substring(3) // 'ID:'.length
                return this.encrypter.encrypt(id, prefix);
            } else {
                // return the value as is, it could be null
                return id;
            }
        }
        return super.transformValueFromDB(value, type);
    }
    protected transformValueToDB(value: unknown, type: string): unknown {
        if (type.startsWith('ID:')) {
            if (value === null || value === undefined) {
                // In case of null or undefined send null to the database
                return null;
            } else if (typeof value === 'string') {
                const prefix = encrypted.substring(3) // 'ID:'.length
                const id = this.encrypter.decrypt(value, prefix);
                return super.transformValueToDB(id, 'bigint');
            } else {
                throw new Error('Invalid id: ' + value);
            }
        }
        return super.transformValueToDB(value, type);
    }
}

You can create the table, specifying the id type as custom or customComparable with type name that starts with ID: followed by a prefix and data type string (the type of the encrypted data):

import { Table } from "ts-sql-query/Table";

const tCompany = new class TCompany extends Table<DBConnection, 'TCompany'> {
    id = this.autogeneratedPrimaryKey<string>('id', 'customComparable', 'ID:co');
    name = this.column('name', 'string');
    constructor() {
        super('company'); // table name in the database
    }
}();

const tCustomer = new class TCustomer extends Table<DBConnection, 'TCustomer'> {
    id = this.autogeneratedPrimaryKey<string>('id', 'customComparable', 'ID:cu');
    firstName = this.column('first_name', 'string');
    lastName = this.column('last_name', 'string');
    birthday = this.optionalColumn('birthday', 'localDate');
    companyId = this.column<string>('company_id', 'customComparable', 'ID:co'); // Be careful, use the proper prefix
    constructor() {
        super('customer'); // table name in the database
    }
}();

If you execute an insert that returns the id, the id will be encrypted:

const id = await connection
            .insertInto(tCompany)
            .values({ name: 'ACME' })
            .returningLastInsertedId()
            .executeInsert()

The returned id will be coFJL3xTJZvP6Kd30d for id 1 in the database.

You can perform a select using the encrypted id:

let company = await connection
            .selectFrom(tCompany)
            .where(tCompany.id.equals('coFJL3xTJZvP6Kd30d'))
            .select({
                id: tCompany.id,
                name: tCompany.name
            })
            .executeSelectOne()

The id used in the query will be sent to the database decrypted.

See IDEncrypter for more information to know how the password is encrypted.

Extract columns from an object

Extract columns

Sometimes could be useful to extract all columns available in an object, like a table or view; this allows to use in a select, ensuring the select uses all columns defined in the provided object. For this purpose you can find the function extractColumnsFrom in the file ts-sql-query/extras/utils. This function receives the object that contains the columns as its first argument and optionally, as its second argument, an array with the name of the properties to exclude.

import { extractColumnsFrom } from "ts-sql-query/extras/utils";

const selectAll = connection.selectFrom(tCustomer)
    .select(extractColumnsFrom(tCustomer))
    .where(tCustomer.id.equals(9))
    .executeSelectOne();

The executed query is:

select id as id, first_name as firstName, last_name as lastName, birthday as birthday, company_id as companyId, first_name || $1 || last_name as name, calculateAge(birthday) as age 
from customer 
where id = $2

The parameters are: [ " ", 9 ]

The result type is:

const selectAll: Promise<{
    companyId: number;
    id: number;
    name: string;
    firstName: string;
    lastName: string;
    birthday?: Date;
    age?: number;
}

Additionally, if you want to get an array with the column names, you can call the function extractColumnNamesFrom in the file ts-sql-query/extras/utils.

import { extractColumnNamesFrom } from "ts-sql-query/extras/utils";

const tCustomerColumnNames = extractColumnNamesFrom(tCustomer);

Extract writable columns

Sometimes could be useful to extract all columns available in an object, like a table or view, excluding the one that cannot be use in an insert or update. This function is analogous to extractColumnsFrom but ignoring computed ad virtual columns. For this purpose you can find the function extractWritableColumnsFrom in the file ts-sql-query/extras/utils. This function receives the object that contains the columns as its first argument and optionally, as its second argument, an array with the name of the properties to exclude.

import { extractWritableColumnsFrom } from "ts-sql-query/extras/utils";

const selectAll = connection.selectFrom(tCustomer)
    .select(extractWritableColumnsFrom(tCustomer))
    .where(tCustomer.id.equals(9))
    .executeSelectOne();

The executed query is:

select id as id, first_name as firstName, last_name as lastName, birthday as birthday, company_id as companyId 
from customer 
where id = $1

The parameters are: [ 9 ]

The result type is:

const selectAll: Promise<{
    id: number;
    firstName: string;
    lastName: string;
    companyId: number;
    birthday?: Date;
}>

Additionally, if you want to get an array with the writable column names, you can call the function extractWritableColumnNamesFrom in the file ts-sql-query/extras/utils.

import { extractWritableColumnNamesFrom } from "ts-sql-query/extras/utils";

const tCustomerWritableColumnNames = extractWritableColumnNamesFrom(tCustomer);

Extract writable shape

This function returns the insert or update shape where the property name in the value object and the remaped property are the same, like {property: 'property'}. This function receives the object that contains the columns as its first argument and optionally, as its second argument, an array with the name of the properties to exclude.

import { extractWritableShapeFrom } from "ts-sql-query/extras/utils";

const tCustomerWritableShape = extractWritableShapeFrom(tCustomer);

Extract id columns

Sometimes could be useful to extract all primary key columns available in an object, like a table or view. This function is analogous to extractColumnsFrom but only returning primary key columns. For this purpose you can find the function extractIdColumnsFrom in the file ts-sql-query/extras/utils. This function receives the object that contains the columns as its first argument and optionally, as its second argument, an array with the name of the properties to exclude.

import { extractIdColumnsFrom } from "ts-sql-query/extras/utils";

const idColumns = extractIdColumnsFrom(tCustomer);

Additionally, if you want to get an array with the writable column names, you can call the function extractIdColumnNamesFrom in the file ts-sql-query/extras/utils.

import { extractIdColumnNamesFrom } from "ts-sql-query/extras/utils";

const tCustomerIdColumnNames = extractIdColumnNamesFrom(tCustomer);

Extract autogenerated id columns

Sometimes could be useful to extract all primary key columns marked as autogenerated available in an object, like a table or view. This function is analogous to extractColumnsFrom but only returning autogenerated primary key columns. For this purpose you can find the function extractAutogeneratedIdColumnsFrom in the file ts-sql-query/extras/utils. This function receives the object that contains the columns as its first argument and optionally, as its second argument, an array with the name of the properties to exclude.

import { extractAutogeneratedIdColumnsFrom } from "ts-sql-query/extras/utils";

const autogeneratedIdColumns = extractAutogeneratedIdColumnsFrom(tCustomer);

Additionally, if you want to get an array with the writable column names, you can call the function extractAutogeneratedIdColumnNamesFrom in the file ts-sql-query/extras/utils.

import { extractAutogeneratedIdColumnNamesFrom } from "ts-sql-query/extras/utils";

const tCustomerAutogeneratedIdColumnNames = extractAutogeneratedIdColumnNamesFrom(tCustomer);

Extract provided id columns

Sometimes could be useful to extract all primary key columns not marked as autogenerated (the value must be provided) available in an object, like a table or view. This function is analogous to extractColumnsFrom but only returning non-autogenerated primary key columns. For this purpose you can find the function extractProvidedIdColumnsFrom in the file ts-sql-query/extras/utils. This function receives the object that contains the columns as its first argument and optionally, as its second argument, an array with the name of the properties to exclude.

import { extractProvidedIdColumnsFrom } from "ts-sql-query/extras/utils";

const providedIdColumns = extractProvidedIdColumnsFrom(tCustomer);

Additionally, if you want to get an array with the writable column names, you can call the function extractProvidedIdColumnNamesFrom in the file ts-sql-query/extras/utils.

import { extractProvidedIdColumnNamesFrom } from "ts-sql-query/extras/utils";

const tCustomerProvidedIdColumnNames = extractProvidedIdColumnNamesFrom(tCustomer);

Merge types

DEPRECATED: Use dynamicBooleanExpressionUsing instead

Note: Before use merge types verify:

First: If you can use IfValue functions described at Easy dynamic queries instead.
Second: If you can use dynamicBooleanExpresionUsing function described at Complex dynamic boolean expressions instead.

Sometimes, when you write advanced dynamic queries, you end in a situation when you have a variable with type a union of several types of value source, and you want to use it in a query. ts-sql-query doesn't handle the union of value source types due to the complexity of inferring the proper resulting type. However, the function mergeType (defined in the ts-sql-query/extras/utils file) can merge the types allowing you to use the value source in other parts of the query.

import { mergeType } from "ts-sql-query/extras/utils";

const firstNameContains = 'ohn';
const lastNameContains = null;
const birthdayIs = null;
const searchOrderBy = 'name insensitive, birthday asc nulls last';
const hideId = false

let searchedCustomersWhere
if (firstNameContains) {
    searchedCustomersWhere = tCustomer.firstName.contains(firstNameContains)
} else {
    searchedCustomersWhere = connection.noValueBoolean()
}
if (lastNameContains) {
    searchedCustomersWhere = mergeType(searchedCustomersWhere).or(tCustomer.lastName.contains(lastNameContains))
}
if (birthdayIs) {
    searchedCustomersWhere = mergeType(searchedCustomersWhere).and(tCustomer.birthday.equals(birthdayIs))
}
searchedCustomersWhere = mergeType(searchedCustomersWhere)

let idColumn
if (hideId) {
    idColumn = connection.optionalConst(null, 'int')
} else {
    idColumn = tCustomer.id
}
idColumn = mergeType(idColumn)

const searchedCustomers = connection.selectFrom(tCustomer)
    .where(searchedCustomersWhere)
    .select({
        id: idColumn,
        name: tCustomer.firstName.concat(' ').concat(tCustomer.lastName),
        birthday: tCustomer.birthday
    })
    .orderByFromString(searchOrderBy)
    .executeSelectMany()

The executed query is:

select id as id, first_name || $1 || last_name as name, birthday as birthday 
from customer 
where first_name like ('%' || $2 || '%') 
order by lower(name), birthday asc nulls last

The parameters are: [ ' ', 'ohn' ]

The result type is:

const searchedCustomers: Promise<{
    id?: number;
    name: string;
    birthday?: Date;
}[]>

Utility types

Column keys

This type return the key name (properties in the object) of the columns contained a an object like a table or view definition.

import { ColumnKeys } from 'ts-sql-query/extras/types';

// Alias to type tCustomerColumns = "id" | "firstName" | "lastName" | "companyId" | "birthday" | "name" | "age"
type tCustomerColumns = ColumnKeys<typeof tCustomer>;

Writable column keys

This type return the key name (properties in the object) of the columns contained a an object like a table or view definition that can be use in an insert or update. This type is analogous to ColumnKeys but ignoring computed ad virtual columns.

import { WritableColumnKeys } from 'ts-sql-query/extras/types';

// Alias to type tCustomerWritableColumns = "id" | "firstName" | "lastName" | "companyId" | "birthday"
type tCustomerWritableColumns = WritableColumnKeys<typeof tCustomer>;

Writable shape

This type returns the insert or update shape where the property name in the value object and the remaped property are the same, like {property: 'property'}.

import { WritableShapeFor } from 'ts-sql-query/extras/types';

type WritableCompanyShape = WritableShapeFor<typeof tCompany>;

Id column keys

This type return the key name (properties in the object) of the id / primary keys columns contained a an object like a table or view definition. This type is analogous to ColumnKeys but returning primary key columns.

import { IdColumnKeys } from 'ts-sql-query/extras/types';

// Alias to type tCustomerColumns = "id"
type tCustomerIdColumns = IdColumnKeys<typeof tCustomer>;

Autogenerated id column keys

This type return the key name (properties in the object) of the id / primary keys columns marked as autogenerated contained a an object like a table or view definition. This type is analogous to ColumnKeys but returning autogenerated primary key columns.

import { AutogeneratedIdColumnKeys } from 'ts-sql-query/extras/types';

// Alias to type tCustomerAutogeneratedIdColumns = "id"
type tCustomerAutogeneratedIdColumns = AutogeneratedIdColumnKeys<typeof tCustomer>;

Provided id column keys

This type return the key name (properties in the object) of the id / primary keys columns not marked as autogenerated (the value must be provided) contained a an object like a table or view definition. This type is analogous to ColumnKeys but returning non-autogenerated primary key columns.

import { ProvidedIdColumnKeys } from 'ts-sql-query/extras/types';

// In case tCustomer were not using an autogenerated primary key this will be an alias to type tCustomerAutogeneratedIdColumns = "id"
type tCustomerProvidedIdColumns = ProvidedIdColumnKeys<typeof tCustomer>;

Insertable values

This type returns the object type with the values that can be used in an insert for the provided table.

import { InsertableValues } from 'ts-sql-query/extras/types';

type InsertableCompany = InsertableValues<typeof tCompany>;