Skip to content

Home

Jump to bottom
do- edited this page Apr 29, 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 as Dates, to minimize 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 upgrade the actual (maybe outdated) one to that target state: 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 such 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.

Data Modification

insert and update 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'})

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

Reference

Clone this wiki locally