Connection, tables & views

Defining the connection object

When you define the connection object, you extend your database connection class; that class receives one generic argument with a unique name for the database in your system.

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

class DBConnection extends PostgreSqlConnection<'DBConnection'> { }

Allowing empty string

By default empty string as treated as null, if you want to allow to send and receive empty string to the database set the allowEmptyString property in the connection to true.

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

class DBConnection extends PostgreSqlConnection<'DBConnection'> { 
    allowEmptyString = true
}

Recommendation: Set this flag at the beginning of the project or create a derivated connection if you require to do it. Changing this flag change the way of the SQL query are constructed when you use the methods that the name ends in 'IfValue'.

Insensitive strategies

By default, when you specify that you want to perform an insensitive operation (like startsWithInsensitive) the operation is performed calling the function lower on the affected parts. But, another way to perform it is changing the collation of the text by one insensitive. If you set the insesitiveCollation in the connection, the provided collate will be used instead of calling the lower function.

Providing the collation allows you to perform more advanced insensitive operations like case insensitive and accent insensitive, or even more in some languages (like manage some letter composition alternatives). Some databases offer general collations that are case insensitive and accent insensitive. But it is expected you want to use a collated specific for the language used by the user.

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

class DBConnection extends PostgreSqlConnection<'DBConnection'> { 
    insesitiveCollation = 'insensitive'
}

Usefull collations per database:

The following collations are case insensitive and accent insensitive:

  • PostgreSQL: Starting from PostgreSQL 12 you can create custom non-deterministic collates from ICU database (previously it was OS dependant) with specific rules.
    • For a general collation case insensitive and accent insensitive, you create the collation using:
CREATE COLLATION insensitive (
    provider = 'icu',
    locale = 'und@colStrength=primary', -- or 'und-u-ks-level1'
    deterministic = false
)
    • For a collation specific for one language (in this example: Spanish) case insensitive and accent insensitive, you create the collation using:
CREATE COLLATION es_insensitive (
    provider = 'icu',
    locale = 'es@colStrength=primary', -- or 'es-u-ks-level1'
    deterministic = false
)
    • For more information, visit this blog post
    • Execute SELECT * FROM pg_collation to list the already created collations
  • MySql/MariaDB:
    • utf8_general_ci for utf8 charset
    • utf16_unicode_ci for utf16 charset
    • utf8_spanish_ci for utf8 charset with spanish rules
    • Execute SHOW COLLATION to list the supported locations in your database; all locations ended with _ci are case insensitive and accent insensitive.
  • SqlServer:
    • Latin1_General_CI_AI for a general case insensitive and accent insensitive for Latin alphabet-based languages
    • Modern_Spanish_CI_AI for a specific case insensitive and accent insensitive for the Spanish language only
    • Execute SELECT * FROM sys.fn_helpcollations() to list the supported locations in your database
  • Oracle:
    • binary_ai for a general case insensitive and accent insensitive for Latin alphabet-based languages
    • spanish_m_ai for a specific case insensitive and accent insensitive extended with the Spanish language rules
    • To see the collations list visit the Oracle 11g or Oracle 19 documentation webpage

Note: If you set the startsWithInsensitive property to an empty string (''), the function lower will not be called, neither a collated will be specified. It is useful when you already defined the insensitive collate rules at the database level.

Instantiating the connection with the database connection

const { Pool } = require('pg');
import { PgPoolQueryRunner } from "ts-sql-query/queryRunners/PgPoolQueryRunner";

const pool = new Pool();

async function main() {
    const connection = new DBConnection(new PgPoolQueryRunner(pool));
    // Do your queries here
    /*
     * Maybe you want to perform the queries in a transaction:
     * await connection.transaction(async () => {
     *     // Do your queries here
     * })
     * 
     * You also can manage the transaction at low level:
     * await connection.beginTransaction();
     * await connection.commit();
     * await connection.rollback();
     */
}

Important: A ts-sql-query connection object and the queries runners objects received as constructor's arguments represent a dedicated connection; consequently, don't share connections between requests when you are handling HTTP requests; create one connection object per request with its own query runners. Even when the ts-sql-query connection object uses a query runner that receives a connection pool, the ts-sql-query connection sill represents a dedicated connection to the database extracted automatically from the pool and must not be shared.

Instantiating the connection with a mock database connection

Have a mock database connection is useful when you want to make unit tests. Using a mock connection allows you to test your code against the generated query instead of run the query in the database.

import { MockQueryRunner } from "ts-sql-query/queryRunners/MockQueryRunner";

function test('my db tets', () => {
    const connection = new DBConnection(new MockQueryRunner(
        (type, query, params, index) => {
            switch (index) {
            case 0:
                expect(type).toBe('delete');
                expect(query).toBe('delete from customer where id = $1');
                expect(params).toEqual([10]);
                return 1; // Returns the result of the query execution
            default:
                throw new Error('Unexpected query');
            }
        }
    ));

    // Do your queries here, example:
    const deleteCustomer = connection.deleteFrom(tCustomer)
        .where(tCustomer.id.equals(10))
        .executeDelete();

    return deleteCustomer.then((result) => {
        expect(result).toBe(1);
    });
});

Mapping the tables

In order to use the tables in queries, you need to map it in your system. To do it, you need to extend the table class that receives two generic arguments, the first one is the connection class, and the second one is a unique name for the table in your system.

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

const tCompany = new class TCompany extends Table<DBConnection, 'TCompany'> {
    id = this.autogeneratedPrimaryKey('id', 'int');
    name = this.column('name', 'string');
    parentId = this.optionalColumn('parent_id', 'int');
    ubicationLatitude = this.optionalColumn('ubication_latitude', 'string');
    ubicationLongitude = this.optionalColumn('ubication_longitude', 'string');
    ubicationComment = this.optionalColumn('ubication_comment', 'string');
    constructor() {
        super('company'); // table name in the database
    }
}();

Important: The constructor of a table must expect no arguments.

Mapping the views

In order to use the views in queries, you need to map it in your system. To do it, you need to extend the view class that receives two generic arguments, the first one is the connection class, and the second one is a unique name for the view in your system.

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

const vCustomerAndCompany = new class VCustomerAndCompany extends View<DBConnection, 'VCustomerAndCompany'> {
    companyId = this.column('company_id', 'int');
    companyName = this.column('company_name', 'string');
    customerId = this.column('customer_id', 'int');
    customerFirstName = this.column('customer_first_name', 'string');
    customerLastName = this.column('customer_last_name', 'string');
    customerBirthday = this.optionalColumn('customer_birthday', 'localDate');
    constructor() {
        super('customer_company'); // view name in the database
    }
}();

Important: The constructor of a view must expect no arguments.

Mapping constant values as view

You can use a set of data a a view made of constant values that will be executed in a query. To do it, you need to extend the Values class that receives two generic arguments, the first one is the connection class, and the second one is a unique name for the view in your query.

import { Values } from "ts-sql-query/View";

async function yourBuissinessLogic() {
    class VCustomerForUpdate extends Values<DBConnection, 'customerForUpdate'> {
        id = this.column('int')
        firstName = this.column('string')
        lastName = this.column('string')
    }
    const customerForUpdate = Values.create(VCustomerForUpdate, 'customerForUpdate', [
        // Include your constant data here, it can be several records but must contains a least one.
        { 
            id: 1,
            firstName: 'First Name',
            lastName: 'Last Name'
        }
    ])
    // Use customerForUpdate as a view in your query
}

Important:

  • Do not provide or implement a constructor
  • The Values class is simmilar to the View class, with the difference the fuctions that allows to create the columns doesn't receive the column name.
  • Always create it using Values.create
  • This is only supported by PostgreSql, SqlServer and Sqlite
  • In PostgreSql the values included in the query will be forced to generate the type cast; ensure your custom types have a valid type cast. See Column types

Virtual columns

You can create virtual columns in a table or view, where the column doesn't exist in the database, but it is expanded during the query construction. You can create a property in your table or view asigning any computation over an aleady existing column to create a direct virtual column; or, you can call the optionalVirtualColumnFromFragment method to create a column from a SQL fragment.

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

const tCustomer = new class TCustomer extends Table<DBConnection, 'TCustomer'> {
    id = this.autogeneratedPrimaryKey('id', 'int');
    firstName = this.column('first_name', 'string');
    lastName = this.column('last_name', 'string');
    birthday = this.optionalColumn('birthday', 'localDate');
    companyId = this.column('company_id', 'int');

    // Direct virtual column
    name = this.firstName.concat(' ').concat(this.lastName);

    // Virtual column from a sql fragment
    age = this.optionalVirtualColumnFromFragment('int', (fragment) => {
        return fragment.sql`calculateAge(${this.birthday})`
    });

    constructor() {
        super('customer'); // table name in the database
    }
}();

Note: You can use a virtual column from a SQL fragment to call a function that returns a value, like in this example.

When you use in a query, the virtual column definition is expanded in the generated SQL.

const customersWithAge = connection.selectFrom(tCustomer)
    .select({
        id: tCustomer.id,
        name: tCustomer.name,
        age: tCustomer.age,
    })
    .executeSelectMany();

The executed query is:

select id as id, first_name || $1 || last_name as name, calculateAge(birthday) as age 
from customer

The parameters are: [ " " ]

The result type is:

const customersWithAge: Promise<{
    id: number;
    name: string;
    age?: number;
}[]>

Creating methods that allows to call a procedure

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

class DBConnection extends PostgreSqlConnection<'DBConnection'> { 
    myOwnprocedure(param1: number) {
        return this.executeProcedure('myOwnprocedure', [this.const(param1, 'int')]);
    }
}

Executing the procedure:

const result = connection.myOwnprocedure(10);

The executed query is:

call myOwnprocedure($1)

The parameters are: [ 10 ]

The result type is a promise:

const result: Promise<void>

Creating methods that allows to call a function

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

class DBConnection extends PostgreSqlConnection<'DBConnection'> { 
    myOwnFunction(param1: number) {
        return this.executeFunction('myOwnFunction', [this.const(param1, 'int')], 'int', 'required');
    }
}

Executing the function:

const result = connection.myOwnFunction(10);

The executed query is:

select myOwnFunction($1)

The parameters are: [ 10 ]

The result type is a promise with the result returned by the function:

const result: Promise<number>