Database adapters (DB adapters)

This page is machine-translated from the Russian original. If something reads oddly, the Russian version is the source of truth — open an issue.

The umbot framework does not know whether you use SQL, NoSQL or the file system. It works with the abstract IQuery and IQueryData objects. Your job as an adapter developer is to write a "translator" that turns these abstractions into real queries to your DBMS.

The easiest way to start is the scaffold: npx umbot add db <Name> creates src/db/<Name>DbAdapter.ts — a working adapter that keeps data in process memory — and a test for it. You replace the methods one by one with calls to your database driver; the test checks the responses the framework expects (more in the CLI description).

Architecture: Template Method

The BaseDbAdapter base class (from umbot/plugins) takes care of the routine:

• Measuring query execution time (the EMetric.DB_SELECT, DB_INSERT, etc. metrics).
• Lifecycle management (calling connect at startup).
• Wrappers around your methods (the public select, insert call your _select, _insert).

Why do we override _select and not select?

The public methods (select, insert, update, remove) in BaseDbAdapter are already written. They wrap your internal methods (_select, _insert) to measure execution time and log metrics (lifecycle and reconnection management lives in connect()/init() — the public wrappers have nothing to do with it). If you override select(), you break metrics collection. You always implement only the underscore methods.

The required contract (what to implement)

Extend BaseDbAdapter and implement the abstract methods:

1. isConnected(): Promise | boolean — check whether the connection is alive (for example, ping the database).
2. _select(selectData: IQuery, where: IQueryData | null, isOne: boolean): IModelRes | Promise — search.
3. _insert(insertData: IQuery): boolean | Promise — insertion.
4. _update(updateData: IQuery): boolean | Promise — update.
5. _remove(removeData: IQuery): boolean | Promise — deletion.

Optional (the base class has default implementations):

• connect(): Promise | boolean — returns true by default. Override it to establish a real database connection.
• destroy(): void | Promise — an empty method by default. Override it to close the connection pool when the application stops.
• close(tableName: string): void | Promise — closes the connection to a specific table.
• _query(callback: TQueryCb) — returns null by default. Override it if you want to support arbitrary queries via model.query().
• ensureSchema(tables: readonly IDbTableSchema[]): boolean | Promise — does nothing by default. For databases with a schema (SQL) it is required: create the missing tables and indexes from the DB_TABLES_SCHEMA description. The framework calls the method after every successful connect(), before the first query to the database.

Who creates the tables

The framework stores data in three tables — UsersData, ImageTokens, SoundTokens:

• Schemaless storages (FileAdapter, MongoDB) create them themselves: the file or collection appears on the first write. MongoAdapter additionally creates indexes in ensureSchema.
• Databases with a schema (PostgreSQL, MySQL, SQLite, YDB) — the adapter creates the tables in ensureSchema. Without it the very first query fails with a "table does not exist" error. Ready-made implementations you can build on are the external packages umbot-knex-adapter (SQL via Knex.js) and umbot-ydb-adapter (YDB).

ensureSchema receives DB_TABLES_SCHEMA (exported from umbot): the table name, the primary key, uniqueKeys, typed fields (string with maxLength / text) and field sets for indexes. The method must be idempotent (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS); on failure return false or throw an exception — the framework logs the error and keeps working. A full PostgreSQL example is in the external adapter specification.

Data formats (cheat sheet):

Input (IQuery): what the framework passes to you.

This is the object the framework passes to your _select, _insert, etc. methods.

{
  tableName: 'UsersData',       // Table/collection name
  primaryKeyName: 'userId',     // Primary key
  query: { userId: '123', platform: 'telegram' }, // WHERE conditions (can be null)
  uniqueKeys: ['platform'],     // Composite key fields (optional)
  data: { name: 'John' },       // Data for SET (can be null)
  rules: [{ name: ['name'], type: 'string', max: 50 }] // Validation rules
}
Copy

uniqueKeys — a composite key. If the primary key value is unique only together with other fields, the model passes these fields in uniqueKeys and adds them to query for select/update/remove. This is how UsersData works: userId is unique within a platform (Telegram user 42 and VK user 42 are different people), so the query looks like { userId: '42', platform: 'telegram' } with uniqueKeys: ['platform']. An adapter that filters by all query fields (SQL WHERE, a MongoDB filter) supports this without changes. An adapter that looks up a record only by primaryKeyName (for example, by an object key, like FileAdapter) must also take the uniqueKeys fields into account — otherwise records of different users get merged. FileAdapter stores such rows under the <platform>:<userId> key and migrates rows of the old format (keyed by userId) on first access.

Conditions and data (IQueryData)

The format of query and data inside IQuery. Important: values can be not only primitives but also objects with operators. The framework does not impose a specific dialect (for example, $gt for Mongo or > for SQL). The adapter decides how to interpret these operators.

// A simple condition (equality)
{ userId: '123', platform: 'alisa' }

// A condition with an operator (the adapter must parse it into SQL `age > 18` or Mongo `$gt` itself)
{ age: { $gt: 18 }, status: 'active' }
Copy

Output (IModelRes)

What you must return from _select.

// Success: records found (isOne — the record itself, otherwise an array)
{ status: true, data: { userId: '123', name: 'John' } }
{ status: true, data: [{ userId: '123' }, { userId: '456' }] }

// Record not found (empty result) — also status: false
{ status: false }

// Error (connection failure, syntax error, etc.)
{ status: false, error: 'Connection timeout' }
Copy

Critical: return status: true only when there really is data. If the record is not found, return { status: false }. Both built-in adapters (FileAdapter, MongoAdapter) work exactly like this, and Model.save() chooses insert vs update by selectOne().status: a false status: true on an empty result breaks saving (update instead of insert).

Do not confuse "not found" with an error: on failure always fill in error. This is how the core knows that the user's data was not loaded: the request is handled with empty userData, but it is not saved. Without error, a failure looks like "a new user", and the core inserts a new record on top of the existing data.

For _insert, _update, _remove you simply return a boolean (true on success, false on error).

Critical details (hidden contracts)

1. Data validation

The BaseDbAdapter base class has no built-in validate() method. MongoAdapter, however, implements it to validate data against the model rules (IModelRules).

Recommendation: if you need additional validation (for example, trimming strings to max, type casting), implement a validate() method in your class:

public validate(query: IQuery, element: IQueryData | null): IQueryData {
    if (!element) return {};

    const rules = query.rules;
    if (rules) {
        rules.forEach((rule) => {
            rule.name.forEach((fieldName) => {
                if (rule.type === 'string' || rule.type === 'text') {
                    if (rule.max !== undefined) {
                        element[fieldName] = Text.resize(element[fieldName] as string, rule.max);
                    }
                    element[fieldName] = this.escapeString(element[fieldName] as string);
                } else if (rule.type === 'integer' || rule.type === 'int') {
                    element[fieldName] = +(element[fieldName] as number);
                }
            });
        });
    }
    return element;
}
Copy

Then call it in _insert() and _update():

public async _insert(insertData: IQuery): Promise<boolean> {
    const validData = this.validate(insertData, insertData.data);
    // ... run the query with validData
}
Copy

Note: validation in the model (Model.validate()) and in the adapter (validate()) are different things. The model validates its data before saving, while the adapter validates data against the IModelRules before running the database query.

Security: before passing query (where) and data to the driver, check them for dangerous keys — __proto__, constructor, prototype (including nested objects and compound paths like a.__proto__.b) and operator objects in the record data. Without this check, prototype pollution and driver injections are possible. A reference implementation is the private #isSafeMongoQuery method in MongoAdapter (src/plugins/db/Mongo/Adapter.ts).

Why are rules passed in IQuery then? You need them for type mapping specific to your DBMS. For example, if you write an SQL adapter, you can use rules to learn that a field with type: 'object' must be serialized into a JSON string before insertion, and use max: 150 to create VARCHAR(150) dynamically.

Note on FileAdapter: FileAdapter does not implement validate() against IModelRules, because it works only with exact value matches and does not support operators. Its operations are still protected against reserved keys (__proto__, constructor, prototype) by the private #isForbiddenKey check in select/insert/update/remove.

2. Storing the connection (connection pool)

To avoid creating a new database connection on every request, the pool is stored in the application context — one per Bot instance. By the time connect() is called, the base class has already bound appContext and created an empty databaseInfo (this is done by init() in Base/Base.ts), so the convention is simple: store your pool/client in this._appContext.database.databaseInfo — _select/_insert and external model.query(callback) read it from there.

async connect(): Promise<boolean> {
    const pool = await createMyDbPool(this._dbOptions);
    // Save the pool to use it in _select/_insert
    this._appContext.database.databaseInfo = { myDbPool: pool };
    return true;
}
Copy

3. Arbitrary queries (_query)

If an application developer needs to run a "raw" SQL query or an aggregation, they use model.query(callback). In BaseDbAdapter the public query simply calls _query. By default _query returns null. If you want to support custom queries, override _query.

The callback contract (TQueryCb): (client, db) => Promise<IModelRes>. The first parameter is the connection client, the second is the database object (db in Mongo is client.db(...); for SQL databases you can pass the same pool). The callback returns IModelRes; the adapter gives the user data.data on status: true and null on error (reference — MongoAdapter._query).

4. Error handling

Do not throw exceptions (throw new Error) from _select, _insert, _update, _remove. The framework expects you to handle errors inside the method and return false or { status: false, error: ... }.

A universal implementation example (pseudocode)

import { BaseDbAdapter } from 'umbot/plugins';
import { IQuery, IQueryData, IModelRes, TQueryCb } from 'umbot';

export class MyCustomDbAdapter extends BaseDbAdapter {
    async connect(): Promise<boolean> {
        try {
            // 1. Create a connection pool
            const pool = await myDbDriver.connect(this._dbOptions);
            // 2. Save it in the context
            this._appContext.database.databaseInfo = { pool };
            return true;
        } catch (err) {
            return false;
        }
    }

    async isConnected(): Promise<boolean> {
        const pool = this._appContext.database.databaseInfo?.pool;
        return pool ? await pool.ping() : false;
    }

    async _select(
        selectData: IQuery,
        where: IQueryData | null,
        isOne: boolean,
    ): Promise<IModelRes> {
        const pool = this._appContext.database.databaseInfo?.pool;
        if (!pool) return { status: false, error: 'No DB connection' };

        try {
            // 1. Parse the abstract where conditions into an SQL/NoSQL query
            const sqlQuery = this.buildSelectQuery(selectData.tableName, where, isOne);

            // 2. Run the query
            const rows = await pool.execute(sqlQuery);

            // 3. Return it in the IModelRes format: an empty result is status: false without error
            if (!rows.length) return { status: false };
            return { status: true, data: isOne ? rows[0] : rows };
        } catch (err) {
            // Do not throw, return status false
            return { status: false, error: (err as Error).message };
        }
    }

    async _insert(insertData: IQuery): Promise<boolean> {
        const pool = this._appContext.database.databaseInfo?.pool;
        if (!pool) return false;

        try {
            // Validation (BaseDbAdapter has none, so we use our own)
            const validData = this.validate(insertData, insertData.data);
            const sqlQuery = this.buildInsertQuery(insertData.tableName, validData);
            await pool.execute(sqlQuery);
            return true;
        } catch (err) {
            return false;
        }
    }

    async _update(updateData: IQuery): Promise<boolean> {
        const pool = this._appContext.database.databaseInfo?.pool;
        if (!pool) return false;

        try {
            const validData = this.validate(updateData, updateData.data);
            const sqlQuery = this.buildUpdateQuery(
                updateData.tableName,
                validData,
                updateData.query,
            );
            await pool.execute(sqlQuery);
            return true;
        } catch (err) {
            return false;
        }
    }

    async _remove(removeData: IQuery): Promise<boolean> {
        const pool = this._appContext.database.databaseInfo?.pool;
        if (!pool) return false;

        try {
            const sqlQuery = this.buildDeleteQuery(removeData.tableName, removeData.query);
            await pool.execute(sqlQuery);
            return true;
        } catch (err) {
            return false;
        }
    }

    // Override _query to support raw queries from the developer
    public async _query(callback: TQueryCb): Promise<unknown> {
        const pool = this._appContext.database.databaseInfo?.pool;
        if (pool) {
            // Pass the client and the database to the developer's callback.
            // The callback returns IModelRes; on status: true we return data.data
            const data = await callback(pool, pool);
            if (data && data.status) {
                return data.data;
            }
            return null;
        }
        return null;
    }

    async destroy(): Promise<void> {
        const pool = this._appContext.database.databaseInfo?.pool;
        if (pool) await pool.close();
    }

    // --- Helper methods ---

    // Custom validation
    private validate(query: IQuery, data: IQueryData | null): IQueryData {
        if (!data) return {};
        // Here you can walk over query.rules and trim strings to max
        return data;
    }

    // IQueryData-to-SQL translator (simplified)
    // ⚠️ WARNING: this is demo pseudocode. In real code use
    // parameterized queries (prepared statements) to protect against SQL injection!
    private buildSelectQuery(table: string, where: IQueryData | null, isOne: boolean): string {
        let sql = `SELECT * FROM ${table}`;
        if (where) {
            const conditions = Object.keys(where).map((key) => {
                const val = where[key];
                // Operator support
                if (typeof val === 'object' && val !== null && val.$gt !== undefined) {
                    return `${key} > ?`; // parameterized query
                }
                return `${key} = ?`; // parameterized query
            });
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }
        if (isOne) sql += ' LIMIT 1';
        return sql;
    }

    private buildInsertQuery(table: string, data: IQueryData): string {
        // ... INSERT building logic
        return '';
    }

    private buildUpdateQuery(table: string, data: IQueryData, where: IQueryData | null): string {
        // ... UPDATE building logic
        return '';
    }

    private buildDeleteQuery(table: string, where: IQueryData | null): string {
        // ... DELETE building logic
        return '';
    }
}
Copy