ID manipulation

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 = type.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 = type.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.

Note

The custom type name must start with ID: for the conversion logic to recognize and apply the encryption/decryption rules correctly.

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