Specification: external database adapters for umbot

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

Status: a specification for the community / contributors. Goal: extend the set of supported DBMSs beyond the umbot core so that any developer can publish their own adapter to npm and connect it with one line — without changing the framework sources.

1. Motivation

Out of the box, umbot ships two DB adapters: FileAdapter (a JSON file, for development) and MongoAdapter (production). For production loads the community often needs Redis, PostgreSQL, SQLite, MySQL, DynamoDB and so on.

Instead of bloating the core and pulling the drivers of every DBMS into dependencies, the right model is external adapter packages. The framework is already designed for this: the database layer is fully abstracted by interfaces, and connecting happens through bot.use().

This specification describes how to write such an external package so that it works correctly with umbot and is compatible with its metrics system, lifecycle and types.

2. What the framework already provides (what to build on)

2.1 The `BaseDbAdapter` base class

Exported from umbot/plugins:

import { BaseDbAdapter } from 'umbot/plugins';

This is the abstract class Base<TDbInfo extends IDatabaseInfo> (src/plugins/db/Base/Base.ts) implementing IDatabaseAdapter. It already contains:

integration with AppContext (storing the connection in appContext.database);
query timing metrics (EMetric.DB_SELECT/INSERT/UPDATE/REMOVE) — the public select/insert/update/remove methods wrap your _select/_insert/_update/_remove;
ready-made save() (insert-or-update) and selectOne() logic;
lifecycle management (init, connect, destroy, close).

Rule: extend BaseDbAdapter and override only the underscore methods (_select, _insert, ...). Do not override the public select/insert/update/remove — otherwise you break metrics and reconnection.

2.2 The contract to implement

Method	Signature	What it does
`connect`	`(): Promise<boolean> \| boolean`	Establish the connection. Return `true` on success.
`isConnected`	`(): Promise<boolean> \| boolean`	Check whether the connection is alive (ping).
`_select`	`(selectData: IQuery, where: IQueryData \| null, isOne: boolean) => IModelRes \| Promise<IModelRes>`	Find records.
`_insert`	`(insertData: IQuery) => boolean \| Promise<boolean>`	Insert. `true`/`false`.
`_update`	`(updateData: IQuery) => boolean \| Promise<boolean>`	Update. `true`/`false`.
`_remove`	`(removeData: IQuery) => boolean \| Promise<boolean>`	Delete. `true`/`false`.
`destroy`	`(): void \| Promise<void>`	Close the pool/connection on shutdown.
`close`	`(tableName: string) => void \| Promise<void>`	Release the resources of a specific table.

Optional:

Method	When to override
`_query`	If you want to support `model.query(callback)` — an arbitrary query. Returns `null` by default.
`escapeString`	Must be overridden for SQL databases: the base implementation just converts the value to a string.
`ensureSchema`	Required for databases with a schema (SQL): create tables and indexes. See the section below.

Preparing the schema: `ensureSchema`

The framework stores data in three tables — UsersData, ImageTokens, SoundTokens. Who creates them:

Schemaless storages (FileAdapter, MongoDB) — themselves: the file or collection appears on the first write.
Databases with a schema (PostgreSQL, MySQL, SQLite, etc.) — the adapter. Without it the very first query fails with a "table does not exist" error.

For this the adapter has an ensureSchema(tables) method. The framework calls it after every successful connect() and before the first query to the database (concurrent queries wait for it to finish) and passes the table description — DB_TABLES_SCHEMA (exported from umbot): the table name, the primary key, uniqueKeys, typed fields (string with maxLength / text) and the field sets that need indexes. The base implementation does nothing and returns true.

Requirements:

the method is idempotent — tables and indexes that already exist are not recreated (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS);
on failure (no DDL permissions) return false or throw an exception — the framework logs the error and keeps working;
new columns in future umbot versions will appear in DB_TABLES_SCHEMA — add the missing ones (ALTER TABLE ... ADD COLUMN), not just create the table.

import { BaseDbAdapter } from 'umbot/plugins';
import type { IDbTableSchema } from 'umbot';

class PgAdapter extends BaseDbAdapter {
    async ensureSchema(tables: readonly IDbTableSchema[]): Promise<boolean> {
        for (const table of tables) {
            const columns = Object.entries(table.fields).map(
                ([name, field]) =>
                    `"${name}" ${field.type === 'text' ? 'TEXT' : `VARCHAR(${field.maxLength ?? 255})`}`,
            );
            await this.#pool.query(
                `CREATE TABLE IF NOT EXISTS "${table.tableName}" (${columns.join(', ')})`,
            );
            for (const fields of table.indexes) {
                const name = `umbot_${table.tableName}_${fields.join('_')}`;
                const list = fields.map((field) => `"${field}"`).join(', ');
                await this.#pool.query(
                    `CREATE INDEX IF NOT EXISTS "${name}" ON "${table.tableName}" (${list})`,
                );
            }
        }
        return true;
    }
}

MongoAdapter creates the indexes from indexes in ensureSchema (MongoDB creates the collections itself).

2.3 Data formats

Input — IQuery (what the framework passes to your methods):

{
  tableName: 'UsersData',       // table/collection name
  primaryKeyName: 'userId',     // primary key (string | number | null)
  query: { userId: '123', platform: 'telegram' }, // WHERE conditions (can be null)
  uniqueKeys: ['platform'],     // composite key fields (optional, since 3.1.4)
  data: { name: 'John' },       // data for SET/INSERT (can be null)
  rules: [{ name: ['name'], type: 'string', max: 50 }] // validation rules
}

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 — IQueryData. Values can be primitives or objects with operators. The framework does not impose a dialect — the adapter decides how to interpret the operators ($gt, $in, etc.):

{ userId: '123', platform: 'alisa' }              // equality
{ age: { $gt: 18 }, status: 'active' }            // operators

The recommended minimum set of operators: $gt, $gte, $lt, $lte, $ne, $in.

_select output — IModelRes:

// success: records found
{ status: true, data: [{ id: 1, name: 'Alice' }] }
// record not found (empty result)
{ status: false }
// error
{ status: false, error: 'Connection timeout' }

Important: status: true only when there really is data; when there is no record, return { status: false }. This is how the built-in FileAdapter and MongoAdapter work, and Model.save() chooses insert vs update by selectOne().status — a false status: true on an empty result breaks saving (update instead of insert).

On failure always fill in error: this is how the core tells "not found" from "the database did not respond", and in the second case it does not save the request's userData. Without error, a failure looks like a new user, and the core inserts a new record.

Important: do not throw exceptions from _select/_insert/_update/_remove. Handle errors inside and return status: false / false.

2.4 Public types to import

All the types you need are available from the umbot root and umbot/plugins:

import {
    BaseDbAdapter, // base class
} from 'umbot/plugins';

import type {
    IQuery, // query structure
    IQueryData, // conditions/data
    IModelRes, // _select result
    IDbResult, // result data
    IAppDB, // connection options (host/user/pass/database/options)
    IDatabaseInfo, // what to store in appContext.database.databaseInfo
    AppContext, // application context
} from 'umbot';

3. Requirements for an external package

3.1 Package structure

umbot-<db>-adapter/
├── src/
│   └── index.ts          # adapter class export
├── tests/
│   └── adapter.test.ts   # unit tests (jest)
├── package.json
├── tsconfig.json
└── README.md

3.2 package.json

{
    "name": "umbot-<db>-adapter",
    "version": "1.0.0",
    "main": "./dist/index.js",
    "types": "./dist/index.d.ts",
    "files": ["dist"],
    "peerDependencies": {
        "umbot": ">=3.1.0"
    },
    "dependencies": {
        "<db-driver>": "^x.y.z"
    }
}

Key points:

umbot is a peerDependency, not a dependency. The user already has umbot in the project; the adapter must not install its own copy.
The DBMS driver (ioredis, pg, better-sqlite3, ...) is a regular dependency of this package. This way the driver is installed only by those who actually need the adapter, and the umbot core stays lightweight.
Publish only dist (files: ["dist"]).

3.3 Naming

Package: umbot-<db>-adapter (for example, umbot-redis-adapter, umbot-postgres-adapter).
Class: <Db>Adapter (for example, RedisAdapter, PostgresAdapter).
The dbFormat field: a unique format identifier, for example 'redis', 'postgres'.

3.4 Implementation template

import { BaseDbAdapter } from 'umbot/plugins';
import type { IQuery, IQueryData, IModelRes, IAppDB, IDatabaseInfo } from 'umbot';
// import your database driver

interface IRedisDbInfo extends IDatabaseInfo {
    client: unknown | null; // keep the live connection here
}

export class RedisAdapter extends BaseDbAdapter<IRedisDbInfo> {
    dbFormat = 'redis';

    constructor(options?: IAppDB) {
        super(options);
    }

    connect(): Promise<boolean> {
        // 1. create a client from this._dbOptions (host/user/pass/database/options)
        // 2. save it to this._appContext.database.databaseInfo
        //    (the base class has already created an empty object in init(); you fill it
        //    with your client/pool — this is a convention, not an integration requirement)
        // 3. return true on success, false on error (do not throw)
        return Promise.resolve(true);
    }

    isConnected(): boolean {
        // ping / check the client status
        return Boolean(this._appContext.database.databaseInfo?.client);
    }

    async _select(
        selectData: IQuery,
        where: IQueryData | null,
        isOne: boolean,
    ): Promise<IModelRes> {
        try {
            // translate where (with operators) into a query for your database
            const rows: Record<string, unknown>[] = [];
            if (!rows.length) {
                return { status: false }; // not found — without error
            }
            return { status: true, data: isOne ? rows[0] : rows };
        } catch (e) {
            return { status: false, error: (e as Error).message };
        }
    }

    async _insert(insertData: IQuery): Promise<boolean> {
        try {
            return true;
        } catch {
            return false;
        }
    }

    async _update(updateData: IQuery): Promise<boolean> {
        try {
            return true;
        } catch {
            return false;
        }
    }

    async _remove(removeData: IQuery): Promise<boolean> {
        try {
            return true;
        } catch {
            return false;
        }
    }

    async destroy(): Promise<void> {
        // close the client/pool, reset databaseInfo
    }

    close(_tableName: string): void {
        // release the table's resources (if applicable)
    }
}

3.5 How users connect it

import { Bot } from 'umbot';
import { TelegramAdapter } from 'umbot/plugins';
import { RedisAdapter } from 'umbot-redis-adapter';

const bot = new Bot()
    .use(new TelegramAdapter())
    .use(new RedisAdapter({ host: 'localhost', database: 'bot_db' }))
    .setAppConfig({/* ... */});

⚠️ Only one DB adapter can be active in an application. When a second one is connected, BaseDbAdapter.init() automatically calls destroy() on the previous one.

4. Quality requirements

Tests. Cover _select/_insert/_update/_remove, connect, isConnected, destroy. Mock external connections (jest.fn() / jest.mock()); tests must make no real queries. Use tests/DbModel/ in the umbot repository as a reference.
Error handling. No unhandled exceptions from contract methods — only status: false / false.
Timeouts. All database calls must have bounded timeouts. Do not create never-ending queries in handlers.
Escaping. Override escapeString for SQL databases. Do not concatenate user values into a query — use parameterized queries.
Secrets. Do not log passwords/tokens. Use this._appContext.logError/logWarn (they mask secrets).
TypeScript. strict: true, no any (use unknown + narrowing). JSDoc in Russian for public methods.
README. A quick start, a connection example, a table of supported operators, limitations.

5. Adapter readiness checklist

[ ] Extends BaseDbAdapter, only the _ methods are overridden.
[ ] connect/isConnected/destroy/close are implemented and safe to call repeatedly.
[ ] For databases with a schema: ensureSchema creates the missing tables and indexes and is safe to call repeatedly.
[ ] _select returns IModelRes (status: true only when records are found; no record — status: false; error — status: false with error filled in).
[ ] No exceptions from contract methods.
[ ] The $gt/$gte/$lt/$lte/$ne/$in operators are supported (minimum).
[ ] umbot in peerDependencies, the database driver in dependencies.
[ ] Tests with mocks pass, key branches are covered.
[ ] npm run build and npm run lint are clean.
[ ] A README with a connection example.
[ ] The pitfalls from section 6 are taken into account (ensureSchema in serverless, the key in UPDATE, condition types).

6. Pitfalls

Found while developing umbot-knex-adapter and umbot-ydb-adapter.

ensureSchema on every cold start. The framework calls the method after every connect(), and in serverless (Cloud Functions) that means every new function instance. Check the schema first with one cheap query (for example, SELECT <all columns> FROM <table> LIMIT 0) and run DDL only if it fails.
Key columns also arrive in the UPDATE data. Model.update() and save() remove only primaryKeyName from data; the uniqueKeys fields (platform in UsersData) stay in both data and query. If the database does not allow changing primary key columns (YDB), remove them from SET.
Numbers in string conditions. getQueryData turns numeric strings into numbers (`userId`=123 → 123), while userId is stored as a string. A strictly typed database has to cast the value to the column type, otherwise the query fails on a type mismatch.
ESM-only driver. A CommonJS package can load such a driver with require() on Node.js ≥ 20.19 (the minimum umbot version): build it with "module": "Node20" (TypeScript ≥ 5.9). Jest in CommonJS mode cannot load such modules — replace the driver with jest.mock() factories.
host and database in IAppDB are required. If the database can connect using environment variables, accept your own parameter type with optional fields in the constructor and pass empty strings to super().
Secondary indexes. Not every database picks an index by itself (YDB uses one only with an explicit VIEW). The lookup of ImageTokens/SoundTokens goes by (platform, path), not by the key — without an index it reads the whole table.

7. Ready-made and wanted adapters

Adapter	Package	Status
PostgreSQL, MySQL, SQLite	umbot-knex-adapter	ready (via Knex.js)
YDB (Yandex Database)	umbot-ydb-adapter	ready
Redis	`umbot-redis-adapter` (`ioredis`)	wanted (cache/sessions)
DynamoDB	`umbot-dynamodb-adapter` (`@aws-sdk/client-dynamodb`)	low priority

Key API classes

Bot — the entry point: registering platforms, commands, lifecycle
BotController — the base controller class with the business logic
IAppConfig — the application configuration interface
IPlatformAdapter — the platform adapter contract
Preload — preloading platform media files
Card — cards and galleries for all platforms
Buttons — buttons: plain, inline, with a payload

Full reference — API v-3.1 · all versions.

Specification: external database adapters for umbot

1. Motivation

2. What the framework already provides (what to build on)

2.1 The `BaseDbAdapter` base class

2.2 The contract to implement

Preparing the schema: `ensureSchema`

2.3 Data formats

2.4 Public types to import

3. Requirements for an external package

3.1 Package structure

3.2 package.json

3.3 Naming

3.4 Implementation template

3.5 How users connect it

4. Quality requirements

5. Adapter readiness checklist

6. Pitfalls

7. Ready-made and wanted adapters

Key API classes

Settings

On This Page

Specification: external database adapters for umbot

1. Motivation

2. What the framework already provides (what to build on)

2.1 The BaseDbAdapter base class

2.2 The contract to implement

Preparing the schema: ensureSchema

2.3 Data formats

2.4 Public types to import

3. Requirements for an external package

3.1 Package structure

3.2 package.json

3.3 Naming

3.4 Implementation template

3.5 How users connect it

4. Quality requirements

5. Adapter readiness checklist

6. Pitfalls

7. Ready-made and wanted adapters

Key API classes

Settings

On This Page

2.1 The `BaseDbAdapter` base class

Preparing the schema: `ensureSchema`