CRUD-L via command arguments

I continue to describe my own immersion into the world of telegram bots, which began in the previous publication. Then I created a simple bot in Node.js with three standard commands (/start, /help, /settings) using the library grammYwhich could operate in modes long polling And webhook. This time I developed a bot that manipulates data in the database according to a template CRUD + List (CRUDL) using command arguments. Due to its simplicity, bordering on primitiveness, this solution is not suitable for commercial projects, but can be useful in personal projects.

Demo telegram bot @flancer64/tg-demo-crudl allows you to create a list of contacts and phone numbers in the database. Here is a list of relevant commands:

• /create <имя> <номер телефона>: adds a new contact to the list (/create John 123456789)

• /read <id>: Displays information about the contact by the specified ID (/read 1)

• /update <id> <новое имя> <новый номер телефона>: Updates an existing contact (/update 1 Jane 987654321)

• /delete <id>: removes a contact from the list (/delete 1)

• /list: Displays all saved contacts

Main technologies of the project

• grammY — npm package for interacting with the Telegram API from Node.js.

• Knex.js – npm package that allows you to work with various DBMSs (database abstraction level, DBAL).

• @teqfw/di – npm package for dependency injection, providing flexibility and extensibility of the application.

• @flancer32/teq-telegram-bot — npm package for performing typical telegram bot operations (launching in long polling and webhook mode, creating a list of commands, configuring the bot, etc.).

Typical command

I discussed in detail the creation of a bot framework, its registration in Telegram, its launch and stop in a previous publication. The architecture of the project is also described there. Here I will demonstrate the implementation of a typical command using an example /create (Demo_Crudl_Back_Bot_Cmd_Create).

Dependency Injection

ES6 module ./src/Back/Bot/Cmd/Create.js processed by the object container, which creates and injects the necessary dependencies:

export default class Demo_Crudl_Back_Bot_Cmd_Create {
    constructor(
        {
            TeqFw_Core_Shared_Api_Logger$$: logger,
            TeqFw_Db_Back_RDb_IConnect$: conn,
            TeqFw_Db_Back_Api_RDb_CrudEngine$: crud,
            Demo_Crudl_Back_Store_RDb_Schema_Phone$: rdbPhone,
        }
    ) {}
}

• TeqFw_Core_Shared_Api_Logger$$: logger — a logger for tracing command execution.

• TeqFw_Db_Back_RDb_IConnect$: conn — connection to the database.

• TeqFw_Db_Back_Api_RDb_CrudEngine$: crud — an object for performing basic operations with the database.

• Demo_Crudl_Back_Store_RDb_Schema_Phone$: rdbPhone — DTO to describe the structure of the data stored in the database.

The first three dependencies are standard tools from my TeqFW set, and the last one is domain specific.

DTO for database

DTO Demo_Crudl_Back_Store_RDb_Schema_Phone describes a simple structure of four fields:

class Dto {
    date_created; 
    id; 
    name;
    phone;
}

which corresponds to the following table in the SQLite database:

CREATE TABLE main.phone
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    date_created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
);

This is enough to have an idea of ​​the data structure in the database. Demo bot works with SQLite, but DBAL Knex.js allows also use PostgreSQL, MariaDB/MySQL, MS SQL and Oracle. You just need to install the appropriate npm package to work with the selected DBMS.

Parameters for connecting Knex.js to the corresponding DBMS are specified in the configuration file ./cfg/local.json (settings template – in ./cfg/init.json). The demo version uses SQLite, the database file is located in ./var/data.db. The following commands are used to work with the database:

$ ./bin/tequila.mjs db-init
$ ./bin/tequila.mjs db-export -f ./var/data.json
$ ./bin/tequila.mjs db-import -f ./var/data.json

The purpose of the commands is clear from the name: creating data structures (tables), exporting and importing data into JSON format.

Telegram command processing template

Since our bot uses the grammY platform to work, a typical command processor works with code objects of this platform (ctx). Here is the skeleton of the handler:

const handler = async (ctx) => {
    let msg = 'The command has failed.';
    const from = ctx.message.from;
    logger.info(`Command has been received from user '${from.username}' (id:${from.id})`);
    const trx = await conn.startTransaction();
    try {
        // const parts = ctx.message.text.split(' ');
        // ... 
        await trx.commit();
    } catch (e) {
        await trx.rollback();
        msg = e.toString();
        logger.error(msg);
    }
    // https://core.telegram.org/bots/api#sendmessage
    await ctx.reply(msg, {
        parse_mode: 'HTML',
    });
};

The algorithm of actions in the handler is as follows:

• register accepted command

• initiate a transaction to work with the database

• perform actions with the database within the transaction and generate a response to the received command

• commit changes to the database

• generate a message for the user about the successful completion of the command

• in case of errors, send an error message to the user

Executing a command

This code fragment directly demonstrates the processing of the command itself create – extracting data from the command and saving it to the database:

try {
    const parts = ctx.message.text.split(' ');
    const dto = rdbPhone.createDto();
    dto.name = parts[1];
    dto.phone = parts[2];
    const {[A_PHONE.ID]: id} = await crud.create(trx, rdbPhone, dto);
    await trx.commit();
    msg = `New record #${id} has been created.`;
    logger.info(msg);
} catch (e) {...}

I use my own DBAL wrapper Knex.js which allows me to do basic operations (CRUD) with DTOs. If you have another library for working with data in the database (for example, sequelize or prisma), then you will have different code here. But all other bindings can be left (except for transactions).

Example of how the bot works

After creating a bot in BotFather, obtaining an API key and configuring the bot (./cfg/local.json) you need to create tables in the database:

$ ./bin/tequila.mjs db-init

Launching a bot in long polling mode:

$ ./bin/tequila.mjs tg-bot-start

or

$ npm start

Calling help

Create

Read

Update

List

Delete

Conclusion

In this article I described the creation process simple telegram bot in Node.js using the grammY library, which supports CRUD-L (create, read, update, delete and list) operations. We looked at how commands with arguments can be used to manipulate data in a database. Despite its simplicity, this approach opens up opportunities for personal projects where complex business logic is not required, but simplicity in implementation is important.

If you are looking for a simple but powerful solution for working with databases through telegram bots, then this project can be an excellent starting point for realizing your fantasies.