Examples and templates

In this section, we will look at some examples of simple queries to the users table in our ORM system.

1. Create a view that displays all directory items in code order.

const selectAllUsers = await databaseManager.queryBuilder<Users[]>()
    .createView('allUsersView')
    .select()
    .from('users')
    .execute();

1. Create a selection query with logical operations under record selection conditions.

const selectQueryExample = await databaseManager.queryBuilder<Tasks[]>()
    .select(['task_id', 'title', 'status'])
    .from('tasks')
    .where({
        conditions: {
            is_completed: { eq: false },
            price: { gt: 50 }
        },
        logicalOperator: 'and'
    })
    .orderBy('due_date', 'ASC')
    .execute();

1. Subqueries.

const subquery = databaseManager.queryBuilder()
    .select(['username'])
    .from('users')
    .where({ conditions: { is_active: { eq: true } } })
    .build(true, false, false);

const mainQuery = await databaseManager.queryBuilder()
    .select(['user_id', 'username'])
    .from('users')
    .where({ conditions: { username: { in: subquery } } })
    .parametrize([subquery])
    .execute()

1. Joining tables.

const queryWithLeftJoin = await databaseManager.queryBuilder()
    .select(['users.username', 'sections.name'])
    .from('users')
    .leftJoin('sections', {
        column: 'users.user_id', // основна колонка в таблиці Users
        operator: '=',
        value: 'sections.user_id' // колонка в таблиці Sections
    })
    .orderBy('users.username', 'ASC')
    .execute()

1. Connection.

const query1 = databaseManager.queryBuilder()
    .select(['user_id', 'username'])
    .from('users')
    .where({ conditions: { is_active: { eq: true } } });

const query2 = databaseManager.queryBuilder()
    .select(['user_id', 'username'])
    .from('users')
    .where({ conditions: { is_active: { eq: false } } });

const exampleOfUnionQuery = await query1.union(query2).parametrize([true, false]).execute();
const exampleOfUnionAllQuery = await query1.unionAll(query2).parametrize([true, false]).execute();

1. Create a simple grouping query using the Sum statistical function.

const sumQuery = await databaseManager.queryBuilder()
    .select(['SUM(price) AS total_price'])
    .from('tasks')
    .execute();

1. Creating a grouping query using the HAVING clause.

const havingQuery = await databaseManager.queryBuilder()
    .select(['user_id', 'COUNT(*) AS task_count'])
    .from('tasks')
    .groupBy(['user_id'])
    .having({ conditions: { 'COUNT(*)': { gt: 5 } } })
    .execute();

1. Create a group request.

const groupByQuery = await databaseManager.queryBuilder()
    .select(['user_id', 'COUNT(*) AS task_count'])
    .from('tasks')
    .groupBy(['user_id'])
    .execute();

1. Create an update request.

const updateQueryExample = await databaseManager.queryBuilder()
    .update({ is_completed: true }, 'tasks')
    .where({ conditions: { task_id: { eq: 1 } }, logicalOperator: 'and' })
    .execute();

1. Create a removal request.

const deleteQueryExample = await databaseManager.queryBuilder()
    .delete('tasks')
    .where({ conditions: { title: { eq: 'Завдання 2' } }})
    .execute();

1. Create an add request.

const insertQueryExample = await databaseManager.queryBuilder()
    .insert({
        task_id: 1,
        title: 'Новий таск',
        description: 'Опис нового таску',
        is_completed: false,
        due_date: '2024-12-31',
        price: 100.50
    }, 'tasks')
    .execute();

1. Creating triggers.

import {Trigger, TriggerEventsTypes, TriggerTimingsTypes, Table} from "@myroslavshymon/orm";

@Trigger({
    name: 'before_update_task',
    event: TriggerEventsTypes.UPDATE,
    timing: TriggerTimingsTypes.BEFORE,
    triggerFunction: `
        BEGIN
            IF NEW.is_completed THEN
                NEW.completed_at = CURRENT_TIMESTAMP;
            END IF;
            RETURN NEW;
        END;
    `,
    triggerFunctionName: 'set_completion_time'
})
@Table({name: 'tasks'})
export class Tasks {
    // поля таблиці tasks
}

1. Creation of indexes.

import {Index, Column, PrimaryGeneratedColumn, Table} from "@myroslavshymon/orm";

@Index({indexName: 'user_index', columns: ['username', 'email'], options: {isUnique: true}})
@Table({name: 'users'})
export class Users {
    @PrimaryGeneratedColumn({type: 'BIGINT'})
    user_id: number;

    @Column({options: {dataType: "VARCHAR", length: 255}})
    username: string;

    @Column({options: {dataType: "VARCHAR", length: 255}})
    email: string;

    @Column({options: {dataType: "VARCHAR", length: 255}})
    password: string;

    @Column({options: {dataType: 'BOOLEAN', defaultValue: false}})
    is_active: boolean;
}

1. Caching.

const selectUsersQueryCached = await databaseManager.queryBuilder<Users[]>()
    .select(['user_id', 'username', 'email'])
    .from('users')
    .orderBy('username', 'ASC')
    .cache({ ttl: 60000, key: 'users_list' });