@mrnafisia/type-query

v4.0.21

Published

3 months ago

mini-orm with full type support for postgres.

0High
0Medium
0Low

mrnafisia

orm query-builder postgres sql type-safe

Documentation

Introduction

Welcome to the documentation for Type-Query, a powerful, type-safe ORM (Object-Relational Mapper) library written in TypeScript. This library bridges the gap between your TypeScript application and your SQL database with a strong emphasis on type safety, validation, and developer experience.

Unlike traditional ORMs that rely on reflection or decorators, Type-Query uses a schema-first approach. You define your table structure once, and the library infers TypeScript types, generates validation rules, and provides a fluent query builder with full IntelliSense support.

Key Features:

100% Type-Safe: Enjoy full autocompletion for column names, operators, and return types.
Schema Validation: Define minLength, maxLength, regex, and nullable constraints directly within your schema.
Model Parsing: Parse and validate incoming API requests before they hit your database with a powerful, error-accumulating parser.
Fluent Query Builder: Use intuitive methods for select, insert, update, delete, and join operations.
Custom Operators: Leverage an advanced expression system for complex where clauses.

Installation

Peer Dependencies:

Install the pg client, as this library is built for PostgreSQL-compatible databases.
If you intend to use the Postgres Number type, install Decimal.js as well.

bun add @mrnafisia/type-query pg decimal.js
bun add --dev @types/pg

npm install @mrnafisia/type-query pg decimal.js
npm install --save-dev @types/pg

yarn add @mrnafisia/type-query pg decimal.js
yarn add --dev @types/pg

Getting Started

Connecting to the Database

Type-Query works directly with the pg library.

db.ts

import { Pool } from 'pg';

const pool = new Pool({
    connectionString: 'postgres://postgres:12345678@localhost:5432/app'
});

export { pool };

Defining a Table

Start by defining your table structure using the createTable function. This defines the schema, column types, and validation rules for the model parser.

User.ts

import { createTable } from '@mrnafisia/type-query';

const UserTable = createTable({
    schemaName: 'public', //    Database schema
    tableName: 'user', //       Table name
    columns: {
        id: {
            type: 'int4', //    PostgreSQL type
            nullable: false,
            default: true,
            primary: true,
            defaultValue: ['auto-increment'] // Special syntax for SERIAL/IDENTITY
        },
        username: {
            type: 'varchar',
            nullable: false,
            default: false,
            minLength: 1,
            maxLength: 24,
            regex: /^[\w-]*$/ //    Alphanumeric, underscore, or dash
        },
        name: {
            type: 'varchar',
            nullable: true, //      This column can be NULL
            default: false,
            minLength: 6,
            maxLength: 100
        },
        isAdmin: {
            type: 'boolean',
            nullable: false,
            default: true,
            defaultValue: ['js', false]
        },
        isActive: {
            type: 'boolean',
            nullable: false,
            default: false
        },
        roles: {
            type: 'jsonb',
            nullable: false,
            default: false,
            // 'narrowType' helps TypeScript narrow the default pg-to-ts type map (e.g., string[])
            narrowType: undefined as unknown as string[]
        }
    }
});

Defining an Entity

After defining a table, create an Entity to perform database operations (SELECT, INSERT, UPDATE, DELETE, and JOIN).

User.ts

import { createEntity } from '@mrnafisia/type-query';

// UserTable definition ...

const User = createEntity(UserTable);

export { User };

The User object now provides methods like .select(), .insert(), .update(), .delete(), and .join().

Performing a SELECT Query

Select specific columns without any filters.

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const selectUser = async (client: PoolClient) => {
    const result = await User.select(
        ['id', 'username'], //  selecting columns
        true //                 WHERE condition (true means no filter)
    ).execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value); // Array of rows: { id: number, username: string }[]
};

pool.connect().then(async client => {
    await selectUser(client);
    client.release();
    await pool.end();
});

Performing an INSERT Query

Insert a single row or multiple rows.

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const insertUser = async (client: PoolClient) => {
    const insertingRow = {
        username: 'john_doe',
        name: 'john',
        isActive: true,
        roles: ['reporter', 'writer']
    };

    const result = await User.insert([insertingRow], ['id']).execute(
        client,
        []
    );
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value); // Array of rows: { id: number }[]
};

pool.connect().then(async client => {
    await insertUser(client);
    client.release();
    await pool.end();
});

Performing an UPDATE Query

Update records that match a condition.

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const updateUser = async (client: PoolClient) => {
    const result = await User.update(
        { isActive: true }, //                          Set column values
        context => context.compare('id', '=', 1), //    Condition: WHERE id = 123
        ['id'] //                                       Return the 'id' column of updated rows
    ).execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value); //                       Array of rows: { id: number }[]
};

pool.connect().then(async client => {
    await updateUser(client);
    client.release();
    await pool.end();
});

Performing a DELETE Query

Delete records matching a condition.

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const deleteUser = async (client: PoolClient) => {
    const result = await User.delete(
        context => context.compare('username', '=', 'john_doe'),
        ['id']
    ).execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value); // Array of rows: { id: number }[]
};

pool.connect().then(async client => {
    await deleteUser(client);
    client.release();
    await pool.end();
});

Using JOINs

The library provides a powerful join system that allows you to join multiple tables with type safety.

Product.ts

import { createTable, createEntity } from '@mrnafisia/type-query';

const Product = createEntity(
    createTable({
        schemaName: 'public',
        tableName: 'product',
        columns: {
            id: {
                type: 'int4',
                nullable: false,
                default: true,
                primary: true,
                defaultValue: ['auto-increment']
            },
            title: {
                type: 'varchar',
                nullable: false,
                default: false
            },
            isDisabled: {
                type: 'boolean',
                nullable: false,
                default: false
            },
            userID: {
                type: 'int4',
                nullable: false,
                default: false
            }
        }
    })
);

export { Product };

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';
import { Product } from './Product';

const joinSelect = async (client: PoolClient) => {
    const result = await Product.join(
        'p', //         Alias for the Product table
        'inner', //     Join type: 'inner', 'left', 'right', 'full'
        User.table, //  The table to join with
        'u', //         Alias for the User table
        ({ pContext, uContext }) =>
            pContext.compare('userID', '=', uContext.column('id'))
    )
        .select(
            [
                'u_id', // 'u_id' means: from the User table (alias 'u'), column 'id'
                'u_username',
                'p_id',
                'p_title'
            ],
            ({ pContext }) => pContext.compare('title', '=', 'chair') // WHERE clause
        )
        .execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value);
    /* Array of rows: {
     *      u_id: number,
     *      u_username: string,
     *      p_id: number,
     *      p_title: string
     *  }[]
     */
};

pool.connect().then(async client => {
    await joinSelect(client);
    client.release();
    await pool.end();
});

Defining a Schema

Schema is a handy type for creating models, complex conditions, expressions, and more.

User.ts

// UserTable definition ...
// User definition ...
type UserSchema = typeof User.table.columns;

export { type UserSchema };

Defining a Model

A Model simplifies working with typed data structures by allowing you to specify required and optional fields.

User.ts

import { ModelWithPrefix } from '@mrnafisia/type-query';

// UserTable definition ...
// User definition ...
// UserSchema definition ...

type UserModel<
    Required extends keyof UserSchema = keyof UserSchema,
    Optional extends keyof UserSchema = never,
    NotNull extends Required | Optional = never,
    Prefix extends string = ''
> = ModelWithPrefix<UserSchema, Required, Optional, NotNull, Prefix>;

export { type UserModel };

Example:

import { UserModel } from './User';

type AddUser = UserModel<
    'username' | 'name', // required
    'roles' //              optional
>; // { username: string, name: string | null, roles?: string[] }

type EditUser = UserModel<
    'id', //                            required
    'username' | 'name' | 'roles', //   optional
    'name' //                           null not allowed
>; // { id: number, username?: string, name?: string, roles?: string[] }

type GetUser = UserModel<
    'id', //                            required
    'username' | 'name' | 'roles', //   optional
    never,
    'u_' //                             alias when selecting using join
>; // { u_id: number, u_username?: string, u_name?: string, u_roles?: string[] }

Defining a Model Parser

A Model Parser validates and transforms raw input data into typed application models.

User.ts

import { createModelParser } from '@mrnafisia/type-query';

// UserTable definition ...
// User definition ...
// UserSchema definition ...
// UserModel definition ...

const UserModelParser = createModelParser(User.table, {
    errorsMap: {
        //  Map database columns to user-friendly error messages
        id: 'Invalid ID format.',
        username:
            'Username must be 1-24 characters and contain only letters, numbers, underscores, or dashes.',
        name: 'Please provide a valid name.',
        isAdmin: 'invalid isAdmin',
        isActive: 'invalid isActive',
        roles: 'At most 5 roles are allowed.'
    },
    parsers: {
        //  Custom parsing logic for specific columns
        roles: v => (v.length < 5 ? v : undefined)
    }
});

export { UserModelParser };

Parsing an entire object:

import { UserModelParser } from './User';

const data = { id: 1 };

const result = UserModelParser.Parse(
    data, //            data must be at least in the form of Record<string, unknown>
    ['id', 'name'], //  required
    ['username'], //    optional
    ['name'] //         null not allowed
);
if (!result.ok) {
    throw new Error(result.error); //      'Please provide a valid name.'
}
console.log(result.value); //   { id: number, name: string, username?: string }

Parsing a single field:

import { UserModelParser } from './User';

const username = 'admin';

const parsedUsername = UserModelParser.username(username);
if (parsedUsername === undefined) {
    throw new Error('username is invalid.');
}
console.log(parsedUsername); // 'admin'

API Reference

Advanced Database Connection

db.ts

import Decimal from 'decimal.js';
import { Pool, Query, types } from 'pg';

// (REQUIRED) Improve the pg parser (refer to the pg documentation for more details)
types.setTypeParser(types.builtins.INT8, v => BigInt(v));
types.setTypeParser(types.builtins.NUMERIC, v => new Decimal(v));
types.setTypeParser(types.builtins.DATE, v => new Date(`${v}T00:00:00Z`));

// (OPTIONAL) Log all executed queries to the console
if (process.env.NODE_ENV === 'development') {
    const submit = Query.prototype.submit;
    Query.prototype.submit = function (
        this: Record<'text' | 'values', string>
    ) {
        console.info(`\x1b[36mQuery: ${this.text}`);
        console.info(`Parameters: ${JSON.stringify(this.values)}\x1b[0m\n`);
        submit.apply(this, arguments as unknown as Parameters<typeof submit>);
    };
}

// Create a pg Pool
const pool = new Pool({
    connectionString: 'postgres://postgres:12345678@localhost:5432/app'
});

export { pool };

Advanced Table Definitions

The createTable function supports a wide range of column options for rigorous data integrity.

Column Options Reference

| Option | Type | Description | | ---------------- | --------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | type | string | See the type Options Reference. | | nullable | boolean | If true, the column can store NULL values. | | primary? | true | Marks the column as a primary key. Applicable only if the column is not null. | | default | boolean | Indicates whether a default value exists. | | defaultValue? | | Applicable only if default: true. See the defaultValue Options Reference. | | min? | number | Decimal (depends on type) | (For Integer, Float and Decimal kinds only) Minimum allowed value. | | max? | number | Decimal (depends on type) | (For Integer, Float and Decimal kinds only) Maximum allowed value. | | minLength? | number | (For String kind only) Minimum string length. | | maxLength? | number | (For String kind only) Maximum string length. | | regex? | RegExp | (For String kind only) Regular expression pattern the value must match. | | sequenceTitle? | string | A custom title for the related sequence. Applicable only if defaultValue is ['auto-increment']. | | precision | number | Refer to the pg documentation. Applicable only if type is decimal. | | scale | number | Refer to the pg documentation. Applicable only if type is decimal. | | length | number | Refer to the pg documentation. Applicable only if type is 'date' \| 'timestamp' \| 'timestamptz'. | | narrowType? | | Helps narrow the base type. (e.g. 1 \| 2 \| 3 for an int2 type or 'allow' \| 'deny' for a string type) | | reference? | | Defines a foreign key. See the createReference Reference. |

`type` Options Reference

| Kind | PG Type | JS Type | Description | | ------------- | ------------------- | --------------------------------------------- | ------------------------------------------------ | | Boolean | boolean | boolean | | | Integer | int2 | number | | | Integer | int4 | number | | | Integer | int8 | number | | | Float | float4 | number | | | Float | float8 | number | | | Decimal | decimal | Decimal | | | String | char | string | | | String | varchar | string | | | String | text | string | | | String | uuid | string | | | Date | date | Date | | | Date/Time | timestamp | Date | | | Date/Time | timestamptz | Date | | | JSON | json | Json | See Json Type | | JSON | jsonb | Json | See Json Type | | Custom | custom(something) | Use the narrowType definition in the table. | For example, custom(Circle) or any custom type |

`defaultValue` Options Reference

| Type | defaultValue Type | | --------------- | ------------------------------------------------------------------------------ | | boolean | ['sql', string] | ['js', boolean] | | int2 | ['sql', string] | ['js', number] | ['auto-increment'] | | int4 | ['sql', string] | ['js', number] | ['auto-increment'] | | int8 | ['sql', string] | ['js', number] | ['auto-increment'] | | float4 | ['sql', string] | ['js', number] | | float8 | ['sql', string] | ['js', number] | | decimal | ['sql', string] | ['js', Decimal] | | char | ['sql', string] | ['js', string] | | varchar | ['sql', string] | ['js', string] | | text | ['sql', string] | ['js', string] | | uuid | ['sql', string] | ['js', string] | | date | ['sql', string] | ['js', Date] | ['created-at'] | ['updated-at'] | | timestamp | ['sql', string] | ['js', Date] | ['created-at'] | ['updated-at'] | | timestamptz | ['sql', string] | ['js', Date] | ['created-at'] | ['updated-at'] | | json | ['sql', string] | ['js', Json] (See Json Type) | | jsonb | ['sql', string] | ['js', Json] (See Json Type) | | Custom | ['sql', string] | ['js', inherits the narrowType definition in the table] |

`createReference` Reference

References are used to define foreign keys.

import { User } from './User';
import {
    createTable,
    createEntity,
    createReference
} from '@mrnafisia/type-query';

const Token = createEntity(
    createTable({
        schemaName: 'public',
        tableName: 'token',
        columns: {
            uuid: {
                type: 'uuid',
                nullable: false,
                default: false,
                primary: true
            },
            userID: {
                type: 'int4',
                nullable: false,
                default: false,
                reference: createReference({
                    table: User.table,
                    column: 'id',
                    onDelete: 'no-action',
                    onUpdate: 'restrict'
                })
            }
        }
    })
);

Advanced Table Definitions Example

import Decimal from 'decimal.js';
import { createEntity, createTable } from '@mrnafisia/type-query';

const Price = createEntity(
    createTable({
        schemaName: 'public',
        tableName: 'token',
        columns: {
            id: {
                type: 'int4',
                nullable: false,
                default: true,
                primary: true,
                min: 1,
                defaultValue: ['auto-increment']
            },
            title: {
                type: 'varchar',
                nullable: false,
                default: false,
                minLength: 3,
                maxLength: 255
            },
            price: {
                type: 'decimal',
                nullable: false,
                default: true,
                precision: 25,
                scale: 15,
                defaultValue: ['js', new Decimal(0)]
            },
            type: {
                type: 'varchar',
                nullable: false,
                default: false,
                narrowType: undefined as unknown as
                    | 'retail'
                    | 'wholesale'
                    | 'mass-production'
            },
            createdAt: {
                type: 'timestamptz',
                nullable: false,
                default: true,
                defaultValue: ['sql', 'now()']
            }
        }
    })
);

Generating Tables and Sequences SQL

import { User } from './User';
import {
    generateDropTableSQL,
    generateCreateTableSQL,
    generateDropSequencesSQL,
    generateCreateSequencesSQL
} from '@mrnafisia/type-query';

const createSequencesSQL: string[] = generateCreateSequencesSQL(User.table, {
    applyIfNotExist: true, //   Optional: apply CREATE SEQUENCE IF NOT EXISTS
    owner: 'app_admin' //       Optional: change owner to: app_admin
});

const dropSequencesSQL: string[] = generateDropSequencesSQL(User.table, {
    applyIfExist: true //       Optional: apply DROP SEQUENCE IF EXISTS
});

const createTableSQL: string = generateCreateTableSQL(User.table, {
    applyIfNotExist: true, //   Optional: apply CREATE TABLE IF NOT EXISTS
    isTemp: true, //            Optional: apply CREATE TEMPORARY TABLE
    owner: 'app_admin' //       Optional: change owner to: app_admin
});

const dropTableSQL: string = generateDropTableSQL(User.table, {
    applyIfExist: true //       Optional: apply DROP TABLE IF EXISTS
});

Setup and Teardown Database Sequences and Tables

ddl.ts

import { pool } from './db';
import { User } from './User';
import {
    generateDropTableSQL,
    generateCreateTableSQL,
    generateDropSequencesSQL,
    generateCreateSequencesSQL
} from '@mrnafisia/type-query';

const Tables = [User.table];

const setupDatabaseSequencesAndTables = () =>
    pool
        .connect()
        .then(client =>
            client
                .query(
                    Tables.flatMap(table => [
                        ...generateCreateSequencesSQL(table),
                        generateCreateTableSQL(table)
                    ]).join(';\n') + ';'
                )
                .finally(() => client.release())
        );

const teardownDatabaseSequencesAndTables = () =>
    pool
        .connect()
        .then(client =>
            client
                .query(
                    Tables.flatMap(table => [
                        ...generateDropSequencesSQL(table),
                        generateDropTableSQL(table)
                    ]).join(';\n') + ';'
                )
                .finally(() => client.release())
        );

export {
    Tables,
    setupDatabaseSequencesAndTables,
    teardownDatabaseSequencesAndTables
};

`execute()` and `getData()`

A complete query object exposes two methods: getData() and execute(). Both methods prepare the query and its associated parameters.

getData() returns the prepared query in the form Result<{ sql: string, params: string[] }, unknown>. This is useful for debugging, logging, or inspecting the generated SQL before execution.
For more details, check out never-catch (inspired by the Rust Result enum).
execute() runs the query against the database using the provided PoolClient.

Both methods may return an error if a problem is detected in the query (e.g., a neutral expression or an invalid configuration).

Expression System

Type-Query provides a rich set of utilities to create complex expressions and where clauses. The U object must be used exclusively within Type-Query functions and contexts. Do not mix it with other JavaScript code.

U.compare

Use U.compare to compare two values using a variety of operators.

| Operators | Expression Type | Example | | ----------------------------------------------------- | ------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | '= null' | '!= null' | null | U.compare(null, '!= null') | | '= true' | '= false' | boolean | U.compare(false, '= true') | | '=' | '!=' | '>' | '>=' | '<' | '<=' | number | bigint | Decimal | string | Date | U.compare(1, '=', 2), U.compare(BigInt(1), '!=', BigInt(2)), U.compare(new Decimal(1.1), '>', new Decimal(2.2)) | | 'in' | 'not in' | number | bigint | Decimal | string | Date | U.compare('a', 'in', ['b', 'c']), U.compare(new Date('2000-01-01T00:00:00.000Z'), 'not in', [new Date('2000-01-02T00:00:00.000Z'), new Date('2000-01-03T00:00:00.000Z')]) | | 'in sub-query' | 'not in sub-query' | number | bigint | Decimal | string | Date | U.compare(1, 'in sub-query', User.select(['id'], true)) | | 'like' | 'like all' | 'like some' | string | U.compare('a', 'like', '%b%'), U.compare('a', 'like all', ['%b%', 'c']), U.compare('a', 'like some', ['%b%', 'c']) | | 'between' | number | bigint | Decimal | string | Date | U.between(1, 'between', 2, 3), U.between(new Date('2000-01-01T00:00:00.000Z'), 'between', new Date('2000-01-02T00:00:00.000Z'), new Date('2000-01-03T00:00:00.000Z')) | | '=' | '!=' | '@>' | '<@' | Json (See Json Type) | U.compare('{ "name": "john" }', '=', '{ "age": 12 }'), U.compare('["blue", "red", "yellow"]', '@>', '["red"]') | | '?' | '?\|' | '?&' | '@@' | Json (See Json Type) | U.compare('["blue", "green", "red"]', '?', "yellow"), U.compare('["blue", "green", "red"]', '?&', '["yellow"]') |

Example:

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(User.select(['id'], U.compare(1, '=', 2)).getData());

U.arithmetic

Use U.arithmetic to apply an arithmetic operator to two values.

| Operators | Expression Type | Example | | ------------------------------------------ | --------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | | '+' | '-' | '*' | '/' | '**' | number | bigint | Decimal | U.arithmetic(1, '+', 2) (= 3), U.arithmetic(1, '+', [2, 3]) (= 6), U.arithmetic(2, '*', 3) (= 6), U.arithmetic(2, '*', [3, 4]) (= 24) |

Example:

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(['id'], U.compare(1, '=', U.arithmetic(2, '+', 3))).getData()
);

U.json

Use U.json to apply jsonb/json-specific operators.

| Operators | Expression Type | Example | | ----------------------------------------------------------- | ------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 'j-' | 'j- Array' | '->' | '-> Array' | '->>' | Json (See Json Type) | U.json('{ "name": "john" }', 'j-', 'name'), U.json('{ "name": "john" }', '->', 'name'), U.json('{ "name": { "first": "John", "last": "Diggle" } }', '-> Array', ['name', 'diggle']) |

Example:

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.compare('John', '=', U.json({ name: 'John' }, '->>', 'name'))
    ).getData()
);

U.fun

Use U.fun to call an SQL function.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.compare(
            12,
            '=',
            U.fun(
                'SUBSTRING', //         Function name
                ['AB123', 3, 4], //     Function parameters
                '::INTEGER' //          SQL cast
            ) as number //              Explicit casting is needed as Type-Query doesn't know the SQL function signature
        )
    ).getData()
);

U.cons

Use U.cons to call an SQL constructor.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.compare(
            1,
            '=',
            U.fun('ANY', [
                U.cons(
                    'ARRAY', //     Constructor name
                    [1, 2, 3] //    Elements
                ) as number[] //    Explicit casting is needed as Type-Query doesn't know the SQL constructor signature
            ]) as number
        )
    ).getData()
);

U.switchCase

Use U.switchCase to define an SQL CASE expression.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.compare(
            1,
            '=',
            U.switchCase(
                [
                    {
                        //  Case 1
                        when: U.compare(2, '=', 3),
                        then: 4
                    },
                    {
                        //  Case 2
                        when: U.compare(5, '=', 6),
                        then: 7
                    }
                ],
                8 // Default value
            )
        )
    ).getData()
);

U.concat

Use U.concat to concatenate strings and JSON values.

| Expression Type | Example | | ------------------------------------ | ------------------------------------------------------------------------------ | | string | U.concat('hello', ' ', 'world!') | | Json (See Json Type) | U.concat('["John"], ["Sam"]), U.concat('{"name": "John" }', '{"age": 10}') |

Example:

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(['id'], U.compare('John', '=', U.concat('Jo', 'hn'))).getData()
);

U.not

Use U.not to negate a boolean expression.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(User.select(['id'], U.not(U.compare(1, '=', 1))).getData());

U.and

Use U.and to apply a logical AND to multiple boolean expressions.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.and(U.compare(1, '=', 1), false, U.compare('A', '!=', 'A'))
    ).getData()
);

U.or

Use U.or to apply a logical OR to multiple boolean expressions.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.or(U.compare(1, '=', 1), false, U.compare('A', '!=', 'A'))
    ).getData()
);

U.subQuery

Use U.subQuery to create a sub-query.
Hint: Prefer using U.compare with the in sub-query operator instead.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.compare(1, 'in', U.subQuery<number[]>(User.select(['id'], true)))
    ).getData()
);

U.subQueryExist

Use U.subQueryExist to create an EXISTS sub-query.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(['id'], U.subQueryExist(User.select(['id'], true))).getData()
);

U.raw

Use U.raw to bypass Type-Query and write raw SQL directly into the query text. Use the function form if you need to use a parameterized query. Ensure you follow the paramsStart number to avoid mixing up parameters with others in the query.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(['id'], context =>
        U.compare(
            U.raw<Date>(paramsStart => ({
                expression: `($${paramsStart++} + $${paramsStart++})`,
                params: ['2000-01-01T00:00:00.000Z', '2000-01-02T00:00:00.000Z']
            })),
            '>',
            U.raw<Date>('(SELECT NOW())')
        )
    ).getData()
);

U.ignore

U.ignore is one of the magical utilities in Type-Query that provides absolute safety and saves you a ton of boilerplate code.

There are certain scenarios that create neutral expressions, which can silently affect your queries:

context.compare('id', 'in', ids) - What happens if ids is an empty array?
context.columnsAnd({ username: ['like some', targets] }) - What happens if targets is an empty array?
U.and(...rules) - What happens if rules is an empty array?

We call these neutral expressions, and there is no way to catch them at compile time.

By default, Type-Query will create a run-time error when it receives a neutral expression instead of simply ignoring it. This is the safe behavior. While it ensures confidence that an entire table's rows will not be deleted if you pass a neutral condition to a delete query, it can become a nightmare in select queries that involve many optional filters.

Use U.ignore to wrap an expression and safely ignore any neutral expressions within it.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

const ids: number[] = [];
const usernames: string[] = [];
const names: string[] = [];

console.log(
    User.select(['id'], context =>
        U.ignore(
            U.and(
                context.compare('id', 'in', ids),
                context.compare('username', 'like all', usernames),
                context.compare('name', 'like all', names)
            ),
            true // Fallback expression if the entire expression is neutral (e.g., no filters provided).
        )
    ).getData()
);

U.column

Use U.column to access the columns of a table.
Hint: This is a low-level API and is used internally. Prefer using context.column instead.

| Parameters | Type | Description | | ---------- | --------- | --------------------------------------------------------------------------------------------------------------- | | table | Table | The table definition. | | column | string | The column name. | | full? | boolean | The full form, which includes the schema and table name as a prefix. | | alias? | string | An alias to replace the schema and table name. Useful when using aliases for tables in JOIN and SELECT queries. |

Example:

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.compare(U.column(User.table, 'id', false), '=', 1)
    ).getData()
);

U.value

Use U.value to wrap a value explicitly. Generally, Type-Query detects types properly, and there is usually no need to wrap your values.
Attention: USING U.value IS REQUIRED ONLY WHEN PASSING A JSON ARRAY. (Why? It interferes with Type-Query's internal type system.)

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(
        ['id'],
        U.and(
            U.compare(
                U.value(1), //          Unnecessary wrapping
                '=',
                2
            ),
            U.compare(
                U.value(['green']), //  Correct and required wrapping
                '=',
                ['blue', 'red'] //      Unwrapped value when wrapping is necessary. This causes unexpected behavior!
            )
        )
    ).getData()
);

Context

The context object provides access to table columns and allows you to create advanced where clauses and expressions. Methods like context.compare, context.columnsAnd and context.columnsOr follow the same signature as the U functions.

import { User } from './User';
import { U } from '@mrnafisia/type-query';

console.log(
    User.select(['id'], context =>
        U.and(
            U.compare(context.column('id'), '=', 1),
            context.compare('id', '=', 1),
            context.columnsAnd({
                id: ['=', 1],
                name: ['like', 'John%']
            }),
            context.columnsOr({
                id: ['=', 1],
                isAdmin: ['= true']
            })
        )
    ).getData()
);

For reusability, you can define a where clause separately.

import { type UserSchema, User } from './User';
import { U, type Context } from '@mrnafisia/type-query';

const where = (context: Context<UserSchema>) =>
    U.and(
        U.compare(context.column('id'), '=', 1),
        context.compare('id', '=', 1),
        context.columnsAnd({
            id: ['=', 1],
            name: ['like', 'John%']
        }),
        context.columnsOr({
            id: ['=', 1],
            isAdmin: ['= true']
        })
    );

console.log(User.select(['id'], where).getData());

Execution Mode

Type-Query returns a Result<V, E> as output for all queries (select, insert, update, delete). For more details, check out never-catch (inspired by the Rust Result enum).

Execution mode is a post-query operation that controls the output type and helps you remove boilerplate code.

Example: ['get', 1]

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const selectUser = async (client: PoolClient) => {
    const result = await User.select(['id'], true).execute(client, ['get', 1]);
    if (!result.ok) {
        console.log('Query failed!');
        if (result.error === false) {
            console.log(
                'The query was successful on the database, but the number of fetched rows was not 1.'
            );
        } else {
            console.log(`Database failed with error: ${result.error}`);
        }
    } else {
        console.log('Query succeeded and the number of fetched rows is 1.');
        console.log(result.value); // { "id" }
    }
};

pool.connect().then(async client => {
    await selectUser(client);
    client.release();
    await pool.end();
});

Example: ['get', Exclude<number, 1>]

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const selectUser = async (client: PoolClient) => {
    const result = await User.select(['id'], true).execute(client, ['get', 5]);
    if (!result.ok) {
        console.log('Query failed!');
        if (result.error === false) {
            console.log(
                'The query was successful on the database, but the number of fetched rows was not 5.'
            );
        } else {
            console.log(`Database failed with error: ${result.error}`);
        }
    } else {
        console.log('Query succeeded and the number of fetched rows is 5.');
        console.log(result.value[0]); // First element: { "id" }
        console.log(result.value[3]); // Fourth element: { "id" }
    }
};

pool.connect().then(async client => {
    await selectUser(client);
    client.release();
    await pool.end();
});

Example: ['count', 2]

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const selectUser = async (client: PoolClient) => {
    const result = await User.select(['id'], true).execute(client, [
        'count',
        2
    ]);
    if (!result.ok) {
        console.log('Query failed!');
        if (result.error === false) {
            console.log(
                'The query was successful on the database, but the number of fetched rows was not 2.'
            );
        } else {
            console.log(`Database failed with error: ${result.error}`);
        }
    } else {
        console.log('Query succeeded and the number of fetched rows is 2.');
        console.log(result.value); // undefined
    }
};

pool.connect().then(async client => {
    await selectUser(client);
    client.release();
    await pool.end();
});

Example: []

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const selectUser = async (client: PoolClient) => {
    const result = await User.select(['id'], true).execute(client, []);
    if (!result.ok) {
        console.log(
            `Query failed! Database failed with error: ${result.error}`
        );
    } else {
        console.log('Query succeeded.');
        console.log(result.value[0]); // First element: { "id" }
        console.log(result.value[3]); // Fourth element: { "id" }
    }
};

pool.connect().then(async client => {
    await selectUser(client);
    client.release();
    await pool.end();
});

SELECT Operations

SELECT Parameter: `returning`

returning is an array of columns to return. You can pass the array directly (R) or use a generator function to access the context (context => R[]). The returning parameter can be columns directly (e.g., 'id') or virtual columns like { name: 'avg', expression: U.fun('AVERAGE', [context.column('age')]) }.

SELECT Parameter: `where`

where is a boolean expression that is checked for every row. You can pass the condition directly (e.g., true) or use a generator function to access the context (e.g., context => boolean).

SELECT Parameter: `selectOptions`

SelectOptions.distinct?

If distinct is true, Type-Query uses DISTINCT in the final query. If you pass an array of columns, Type-Query uses DISTINCT ON () in the final query. You can pass a custom expression instead of direct columns in the form of { expression: value }. You can also use a generator function to access the context.

SelectOptions.groupBy?

You can pass an array of columns or a custom expression directly, or use a generator function to access the context.

SelectOptions.orders?

You can pass an array of Order objects to specify your column or expression, the sort direction, and the null position directly, or use a generator function to access the context.

SelectOptions.start?

start specifies how many of the first rows to skip. The default is 0.

SelectOptions.step?

step specifies how many rows to fetch. The default is all rows.

SelectOptions.customQueryBuilder?

customQueryBuilder gives you full access to the query-building mechanism. If using U.raw does not meet your needs, you can use customQueryBuilder for writing CTEs, complex sub-queries, recursive queries, and more. Type-Query provides a default customQueryBuilder that you can call with your changes or rewrite entirely from scratch.

type CustomQueryBuilder = (
    parts: Record<
        `${'distinct' | 'returning' | 'from' | 'where' | 'groupBy' | 'orders' | 'pagination'}Part`,
        string
    >,
    params: string[]
) => { sql: string; params: string[] };

const defaultCustomQueryBuilder: CustomQueryBuilder = (parts, params) => {
    const tokens = ['SELECT'];
    if (parts.distinctPart !== '') {
        tokens.push(parts.distinctPart);
    }
    tokens.push(
        parts.returningPart,
        'FROM',
        parts.fromPart,
        'WHERE',
        parts.wherePart
    );
    if (parts.groupByPart !== '') {
        tokens.push('GROUP BY', parts.groupByPart);
    }
    if (parts.ordersPart !== '') {
        tokens.push('ORDER BY', parts.ordersPart);
    }
    if (parts.paginationPart !== '') {
        tokens.push(parts.paginationPart);
    }

    return {
        sql: tokens.join(' '),
        params
    };
};

Example of SELECT Operations

Example 1:

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';
import { U } from '@mrnafisia/type-query';

const selectUser = async (client: PoolClient) => {
    const result = await User.select(
        context => [
            'id',
            'name',
            {
                name: 'isActiveAndIsAdmin',
                expression: U.and(
                    context.column('isActive'),
                    context.column('isAdmin')
                )
            }
        ],
        context => context.compare('username', 'like', 'john%'),
        {
            distinct: true,
            orders: context => [
                {
                    by: 'name',
                    direction: 'desc',
                    nullPosition: 'last'
                },
                {
                    by: {
                        expression: U.and(
                            context.column('isActive'),
                            context.column('isAdmin')
                        )
                    },
                    direction: 'desc'
                }
            ],
            start: BigInt(30),
            step: 25
        }
    ).execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value);
    /*  Array of rows: {
     *       id: number,
     *       name: string | null,
     *       isActiveAndIsAdmin: boolean
     *   }[]
     */
};

pool.connect().then(async client => {
    await selectUser(client);
    client.release();
    await pool.end();
});

Example 2:

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';
import { U } from '@mrnafisia/type-query';

const selectUser = async (client: PoolClient) => {
    const result = await User.select(
        context => [
            'name',
            {
                name: 'isActiveAndIsAdmin',
                expression: U.and(
                    context.column('isActive'),
                    context.column('isAdmin')
                )
            }
        ],
        true,
        {
            distinct: ['name'],
            groupBy: context => [
                'name',
                {
                    expression: U.and(
                        context.column('isActive'),
                        context.column('isAdmin')
                    )
                }
            ]
        }
    ).execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value);
    /*  Array of rows: {
     *       name: string | null,
     *       isActiveAndIsAdmin: boolean
     *   }[]
     */
};

pool.connect().then(async client => {
    await selectUser(client);
    client.release();
    await pool.end();
});

INSERT Operations

INSERT Parameter: `rows`

Default and nullable columns are optional in rows. The priority is: the value specified in rows, then the default value, and then null. You can pass the rows directly or use a generator function to access the context.

INSERT Parameter: `returning`

See SELECT Parameter: returning.

Example of INSERT Operations

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const insertUser = async (client: PoolClient) => {
    const result = await User.insert(
        [
            {
                username: 'root',
                isActive: true,
                roles: ['reporter']
            }
        ],
        ['id']
    ).execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value); // { id: number }[]
};

pool.connect().then(async client => {
    await insertUser(client);
    client.release();
    await pool.end();
});

UPDATE Operations

UPDATE Parameter: `sets`

You can pass the sets object directly or use a generator function to access the context.

UPDATE Parameter: `where`

See SELECT Parameter: where.

UPDATE Parameter: `returning`

See SELECT Parameter: returning.

Example of UPDATE Operations

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';
import { U } from '@mrnafisia/type-query';

const updateUser = async (client: PoolClient) => {
    const result = await User.update(
        context => ({
            isActive: U.not(context.column('isActive')),
            isAdmin: false
        }),
        context => context.compare('roles', '?', 'reporter'),
        ['id']
    ).execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value); // { id: number }[]
};

pool.connect().then(async client => {
    await updateUser(client);
    client.release();
    await pool.end();
});

DELETE Operations

DELETE Parameter: `where`

See SELECT Parameter: where.

DELETE Parameter: `returning`

See SELECT Parameter: returning.

Example of DELETE Operations

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';

const deleteUser = async (client: PoolClient) => {
    const result = await User.delete(
        context => context.compare('name', '=', 'john'),
        ['id']
    ).execute(client, []);
    if (!result.ok) {
        throw new Error(`Query failed with error ${result.error}`);
    }
    console.log(result.value); // { id: number }[]
};

pool.connect().then(async client => {
    await deleteUser(client);
    client.release();
    await pool.end();
});

JOIN Operations

Using the join function alone will not result in a full query. The output is an object with select and join functions, allowing you to join another table or perform a select on the joined tables.

JOIN Parameter: `mainAlias`

This is the alias for the main table. It affects the returning column names and the context name whenever a context is provided. A prefix of ${mainAlias}_ is added, so with 'u' as the main alias, column names will be like 'u_id'.

JOIN Parameter: `joinType`

This specifies one of the four standard SQL join types.

type JoinType = 'inner' | 'left' | 'right' | 'full';

JOIN Parameter: `joinTable`

The table to join with.

JOIN Parameter: `joinAlias`

This behaves like mainAlias and affects the joinTable in the same way.

JOIN Parameter: `on`

on is a condition, similar to where, that determines which rows are included in the join. You can pass the on condition directly or use a generator function to access the contexts. When a join function is called, the joining table's context is added to the contexts object, prefixed with ${alias}Context. For example, a main table with alias u and a joined table with alias p will create { uContext, pContext } instead of a single context.

Example of JOIN Operations

import { pool } from './db';
import { User } from './User';
import { PoolClient } from 'pg';
import { Product } from './Product';
import { U } from '@mrnafisia/type-query';

const joinSelect = async (client: PoolClient) => {
    const result = await User.join(
        'u',
        'inner',
        Product.table,
        'p',
        ({ uContext, pContext }) =>
            uContext.compare('id', '=', pContext.column('userID'))
    )
        .select(['u_id', 'p_title'], ({ uContext, pContext }) =>
            U.and(
                uContext.compare('name', 'like', 'john%'),
                pContext.columnsOr({

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

Documentation

Table of Contents

Introduction

Installation

Getting Started

Connecting to the Database

Defining a Table

Defining an Entity

Performing a SELECT Query

Performing an INSERT Query

Performing an UPDATE Query

Performing a DELETE Query

Using JOINs

Defining a Schema

Defining a Model

Defining a Model Parser

API Reference

Advanced Database Connection

Advanced Table Definitions

Column Options Reference

type Options Reference

defaultValue Options Reference

createReference Reference

Advanced Table Definitions Example

Generating Tables and Sequences SQL

Setup and Teardown Database Sequences and Tables

execute() and getData()

Expression System

U.compare

U.arithmetic

U.json

U.fun

U.cons

U.switchCase

U.concat

U.not

U.and

U.or

U.subQuery

U.subQueryExist

U.raw

U.ignore

U.column

U.value

Context

Execution Mode

SELECT Operations

SELECT Parameter: returning

SELECT Parameter: where

SELECT Parameter: selectOptions

SelectOptions.distinct?

SelectOptions.groupBy?

SelectOptions.orders?

SelectOptions.start?

SelectOptions.step?

SelectOptions.customQueryBuilder?

Example of SELECT Operations

INSERT Operations

INSERT Parameter: rows

INSERT Parameter: returning

Example of INSERT Operations

UPDATE Operations

UPDATE Parameter: sets

UPDATE Parameter: where

UPDATE Parameter: returning

Example of UPDATE Operations

DELETE Operations

DELETE Parameter: where

DELETE Parameter: returning

Example of DELETE Operations

JOIN Operations

JOIN Parameter: mainAlias

JOIN Parameter: joinType

JOIN Parameter: joinTable

`type` Options Reference

`defaultValue` Options Reference

`createReference` Reference

`execute()` and `getData()`

SELECT Parameter: `returning`

SELECT Parameter: `where`

SELECT Parameter: `selectOptions`

INSERT Parameter: `rows`

INSERT Parameter: `returning`

UPDATE Parameter: `sets`

UPDATE Parameter: `where`

UPDATE Parameter: `returning`

DELETE Parameter: `where`

DELETE Parameter: `returning`

JOIN Parameter: `mainAlias`

JOIN Parameter: `joinType`

JOIN Parameter: `joinTable`

JOIN Parameter: `joinAlias`

JOIN Parameter: `on`