Skip to content

Home

Jump to bottom
do- edited this page May 1, 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 Operation

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')

   const db        = new DbPoolPg (conf.db)
// const dbArchive = new DbPoolCh (conf.dbArchive)

///...
      pools: {
        db,       
//      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.

For bound parameters, contrarily, doix-db accepts nearly everything and do the best to map it properly in an intuitive way. It's recommended however to provide all values but safe integers in the string form.

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

Using a Database Model

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 a means to load the presumed database schemata from source files next to the Application's modules.

Setting Up, Exploring Content

Each database object, such as a table, sql view and so on, must be described by an eponymous module. Suppose you have all necessary modules in a directory called /path/to/the/model. To make use of this information, you have to associate it with the corresponding pool on application start (here, we suppose the db and dbArchive variables are the same as in the example above):

const {DbModel} = require ('doix-db')

// simple case:
new DbModel ({db, src: [{root: `/path/to/the/model`}]}).loadModules ()

// advanced usage:
{
  const dbModel = new DbModel ({dbArchive, src: [{root: `/path/to/the/model`}]})
//  dbModel.on ('objects-created', function () {/*...*/}) // after resolveReferences ()
//  dbModel.prependListener ('objects-created', function () {/*...*/}) // before
  dbModel.loadModules ()
}

Now both pools, and each DbClient acquired, will have corresponding .model properties injected therein. You can use it directly:

const {data: {length}} = this.db.model.find ('dim.gender')   // known table, mandatory fixed content
this.logger.log ({message: `We suppose there are ${length} genders`})

const tmpTables = []; for (const o of this.db.model.objects) // iterate over schemata
  if (o instanceof DbTable && o.isToPurge)                   // check for a custom tag
    tmpTables.push (o.qName)
if (tmpTables.length !== 0)
  await db.do (`TRUNCATE ${tmpTables}`)

More advanced API is covered below.

NOSQL: Not Only SQL

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 properties unknown to 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 fields. Commonly, most of filters are unset by default, and empty values must be ignored.

In this situation, the SQL query is better generated based on the actual set of search terms requested: at least the WHERE clause, but quite often the FROM part too, may be some others. And to display a page counter, a secondary SELECT COUNT(*) is needed, with a specific optimization (excluding ORDER BY, omitting some OUTER JOINs, etc.)

To facilitate the code generation is most such cases, doix-db features a dynamic query builder. Unlike some well known analogs (e. g. Knex.js), its API doesn't mock a SQL AST in form of multiple chained method calls.

From the application perspective, given a set of filters in form of a plain object, it takes a single this.db.model.createQuery () call to obtain a builder instance and immediately pass it as a parameter to this.db.getArray ().

const q = this.db.model.createQuery (
  [['notes', {filters: [['txt', 'ILIKE', '%' + v + '%']]}]],
  {order: 'created', limit: 50, offset: 0}
)
, range = await this.db.getArray (q)       // LIMIT / OFFSET applied
, count = selection [Symbol.for ('count')] // extra COUNT(*) result

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 advanced 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, those micro languages. No problem to translate from one to another, more versatile one (the doix-db one, that is). Two translators of this kind are available: for DevExtreme and for w2ui frameworks. More can be cloned easily.

Reference

Clone this wiki locally