Skip to content

Custom booleans values

Sometimes, especially when working with Oracle databases, booleans are stored using custom values rather than standard true and false. For instance, a field might use the character 'Y' to represent true and 'N' to represent false.

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 (?, case when ? then 'Y' else 'N' end) 
returning id

insert into custom_company (`name`, is_big) 
values (?, case when ? then 'Y' else 'N' end)
-- Last inserted ID returned by the database got from the connection

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

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

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

insert into custom_company (name, is_big) 
output inserted.id 
values (@0, case when (@1 = 1) then 'Y' else 'N' end)

The parameters are: [ 'My Big Company', true ] (On Oracle, output parameters are added at the corresponding position with the structure {dir:3003})

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'

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

select 
    id as "id", 
    name as "name", 
    case when is_big = 'Y' then 1 else 0 end as "isBig" 
from custom_company 
where (is_big = 'Y')

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

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

select 
    id as id, 
    name as name, 
    cast(case when is_big = 'Y' then 1 else 0 end as bit) 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;
}[]>