Skip to content

Home

Jump to bottom
do- edited this page Apr 30, 2025 · 161 revisions

doix-db is an extension to the doix framework for working with relational databases.

tl;dr

Start a trivial Web service project with doix-http, add a connection to PostgreSQL or to ClickHouse and hack on.

Description

Basically, this is the common RDB interface for doix, the same as ODBC for Windows, JDBC for Java and so on.

Aside from processing given statements, it features some SQL generation capabilities.

Connection & Basic Usage

For an Application to operate on a database, you have to register therein the properly configured vendor specific DbPool:

const {DbPoolPg} = require ('doix-db-postgresql')
// const {DbPoolCh} = require ('doix-db-clickhouse')

///...
      pools: {
        db        : new DbPoolPg (conf.db),       
//      dbArchive : new DbPoolCh (conf.dbArchive),
      },
///...

Then, corresponding DbClient instances will be automatically injected in execution contexts:

const dt = await this.db.getScalar ('SELECT CURRENT_DATE')
//         await this.dbArchive.do ('ALTER TABLE facts DROP PARTITION ?', [dt])

Asynchronous methods can be called right away; initialization and cleanup are up to doix internals.

Just in case, the hosting application is always in hand, with all its internals, including the pools Map, so developers may operate on it directly, at their own risk:

this.app.pools.get ('db').pool.end () // see https://node-postgres.com/apis/pool#poolend

Executing Arbitrary SQL

DbClient's most common methods are:

The API is designed to be versatile yet concise. Each request is invoked by a single call; scalars, arrays and streams are represented uniformly.

In result sets, the primitive types mapping depends on the specific driver, but, in general, when ambiguous, strings are used. In particular:

  • fixed precision numbers (DECIMAL etc.) are returned as Strings, never by Numbers to avoid rounding errors;
  • dates are represented by ISO strings, never by Dates, because of time zone related issues.

Like every process in doix, each SQL statement execution is transparently logged, with references to the containing job.

Model Based SQL Generation

Far from embracing the MDA approach in its totality, doix-db is developed keeping in mind that an application must be aware of, and effectively use meta information about data structures in operates on. Even more, a well designed application must keep the image of the required structure of its database and should be able to compare it to the actual one, maybe outdated, and to upgrade it according to requirements: primarily, with automatic migrations during deployments.

To this end, doix-db offers DbModel: the class implementing a metainformation store loadable from modules, along with Application's ones. Each of these modules describes a table, sql view or another database object.

A DbModel instance can be passed to a DbPool constructor. In this case, each related DbClient, say this.db, receives the corresponding this.db.model, which makes metadata available to use in API calls described further.

ORM Style Data Modification

insert, update and upsert represent simplest use cases for implicit use of DbModel:

await this.db.insert ('log', {id: 1, message: 'Test', level: 1})
await this.db.update ('log', {id: 1, message: 'The test'})
await this.db.upsert (
  'user_options', 
   {
     id_user: 1, 
     id_option: 10, 
     value: true,
   }, 
   {key: [
     'id_user', 
     'id_option',
   ]}
)

You just put a plain object in the needed table. Naturally, field names must match. Extra fields missing from the data model are silently ignored.

Although not recommended for mission critical high load operations, this technique can save a lot of time while prototyping simple CRUD functionality.

Dynamic Search Queries

Most user interfaces (especially, Web ones) contain scrollable roasters with multiple search filters. Commonly, most of such filters are unset by default, and empty values must be ignored.

In this situation, the SQL query is better generated based on the totality of search terms: at least the WHERE clause, but quite often the FROM part too. Moreover, to display page counters, a secondary SELECT COUNT(*) is needed, and it allows specific optimization (like omitting some LEFT JOINs).

To facilitate the code generation is most such cases, you can use this.db.model.createQuery () method transforming a set of filters into a SQL generator object ready to be used with this.db.getArray () directly, but still open for additional tweaking.

But what is the right structure for that set of filters mentioned above? Surprisingly, no common standard for this is in sight. Every DHTML AJAX library featuring some data grid seems to invent its own one: the same thing once expressed as filter: ['label', 'startswith', 'admin'], looks like search: [{field: 'label', operator: 'begins', value: 'admin'}] elsewhere and so on.

They are all pretty similar though, all those micro languages. No problem to translate from one to another, more versatile one (the doix-db one, that is). Two such translators are available: for DevExtreme and for w2ui frameworks.

Reference

Clone this wiki locally