@kitmi/data

Jacaranda Framework Data Access Layer

Built-in Jacaranda Features

Below features come with @kitmi/data can be configured in both @kitmi/jacaranda server config file and app module config file.

  • dataSource - Defines connector of a database

Note: For postgres, different postgres schema are considered different data source in this framework.

  • dataModel - To be used by @kitmi/xeml for code generation

  • db - Maps of schema name to data source defined by dataSource feature

Note: Schema in this framework is a collection of entities and it can be defined as a subset of a database. Different schemas can use the same dataSource connector.

Sample config:

dataSource:
    postgres:
        testConnector:
            connection: 'postgres://postgres:postgres@localhost:5432/jc-testdb'
            adminCredential:
                username: 'postgres'
                password: 'postgres'
            logStatement: true
            logConnection: false
            logTransaction: true

dataModel:
    schemaPath: './xeml'
    schemaSet:
        test:
            dataSource: postgres.testConnector
        test2:
            dataSource: postgres.testConnector
    dependencies:
        commons: '@xgent/xem-commons'

db:
    test:
        dataSource: postgres.testConnector        
    test2:
        dataSource: postgres.testConnector

Basic Usage

Supposed all the entity models are generated by @kitmi/xeml without errors and database has been migrated to the latest state.

The db feature will inject a db function into the app object.

The db function signature is as below.

app.db([schema-name]) -> DbModel;

The schema-name is optional and will use the value of app.settings.defaultDb as the default schema. The value of app.settings.defaultDb can be configured in the app's config as below:

settings:
    defaultDb: test

The db feature can also reference to a data source defined in a library module.

db:
    test:
        fromLib: '<name of the library defines the data source>'
        dataSource: postgres.testConnector

When an app with the db feature enabled, database operation can be done as below:

    const db = app.db('db name'); // or app.db() for default db configured in app's settings
    // ctx.db is a shorthand of ctx.module.db, ctx.module is the app instance serving the request

    // or db.entity('user') or db.User
    const User = db.entity('User'); 
    
    // or await User.findOne_({ id: 1011 }); 
    // or await User.findOne_(1011) if 1011 is the value of User's primary key; 
    const user = await User.findOne_({ $where: { id: 1011 } });      

    const user = await User.updateOne_({ name: 'New name' }, { $where: { id: 1011 } });
    // ...

CRUD Operations

  • Retrieval

    • async findOne_(findOptions)
    • async findMany_(findOptions)
    • async findManyByPage_(findOptions, page, rowsPerPage)

  • Creation

    • async create_(data, createOptions)
    • async createFrom_(findOptions, columnMapping)

  • Update

    • async updateOne_(data, updateOptions)
    • async updateMany_(data, updateOptions)

Note: regarding the data, please refers to Data to Update

  • Deletion

    • async deleteOne_(deleteOptions)
    • async deleteMany_(deleteOptions)
    • async deleteAll_()

  • Not implemented yet

    • async createMany_(data /* array of object */, createOptions)
    • async createMany_(fieldNames, data /* array of array */, createOptions)
    • async aggregate_(...)
    • async cached_(...)

Hooks

  • async beforeCreate_(context) -> boolean
  • async beforeUpdate_(context) -> boolean
  • async beforeUpdateMany_(context) -> boolean
  • async beforeDelete_(context) -> boolean
  • async beforeDeleteMany_(context) -> boolean
  • async afterCreate_(context) -> boolean
  • async afterUpdate_(context) -> boolean
  • async afterUpdateMany_(context) -> boolean
  • async afterDelete_(context) -> boolean
  • async afterDeleteMany_(context) -> boolean

Operation Options

Common Options

$ctx

The koa like ctx object passed in to interpolate into query condition, will also be passed on to associated operation.

  • session
  • request
  • header
  • state

$skipUniqueCheck

To skip unique check for $where object when performing xxxOne_ operation.

$key

The key field of the entity.

findOptions

$select

  • Select by dot-separated field name (syntax: [.])
$select: [
    '*',
    'user.* -password -passwordSalt', // with exclusions
    'offices.bookableResources.type'
]
// SELECT A.*, X.`type` ... JOIN `OfficeBookableResource` as X

Note: The output columns may have some automatically added fields especially keys of different layers for building the hierachy structure

  • Select by function
$select: [ { $xr: 'Function', name: 'MAX', alias: 'max', args: ['order'] } ]
// SELECT MAX(`order`) as max
  • Using the xrXXX helpers.
import { xrCall, xrCol } from '@kitmi/data';
//...
{
    $select: [ xrCall('COUNT', '*'),  xrCall('SUM', xrCol('field1')) ]
    // SELECT COUNT(*), SUM("field1")
}
Helper Functions
  • xrCall: call a SQL function
{
    $select: [ xrCall('SUM', xrCol('someField')) ] // SUM(someField)
}
  • xrCol: reference to a table column

Difference between using xrCol and string:

postgres: 
    xrCol('abc') -> quote_ident('abc') -> "abc"
    direct string "abc" or 'abc' -> 'abc'
  • xrExpr: binary expression
{
    $select: [ xrExpr(xrCol('someField'), '+', 1) ] // someField + 1
}
  • xrRaw: raw expression

Note: Directly inject into the SQL statement, should use it carefully.

xrRaw(<raw-statement>, [params])

In the raw statement, you can put a db.paramToken as the placeholders of params and separately pass the params array as the second argument.

e.g.

xrRaw(
    `("testJson"->>'duration')::INTEGER > ${Book.db.paramToken} AND ("testJson"->>'duration')::INTEGER < ${Book.db.paramToken}`,
    quizCount
)
  • xrGet: get array element or json field
xrGet(<field>, (<1-based index> | <"." separated key path>)[, <alias>])
  • Others

More usage samples can be found in the Data to Update section below.

$relation

Indicate to include what associations in the query.

Note: No trailing (s).

  • Use anchor name as the relationship
// use an anchor 
$relation: [ 'profile', 'roles' ];
// can use multi-levels
$relation: [ 'profile.address', 'roles.role' ];
  • Use custom relation that not defined in xeml
$relation: [{ alias: 'anyNode', entity: 'tagCategoryTree', joinType: 'CROSS JOIN', on: null }], // 

$relation: [
    ...,
    {
        alias: 'course',
        entity: 'course',
        joinType: 'INNER JOIN',
        on: {
            'course.rootDoc': xrCol('rootDoc.knowledges.knowledge.documents.document.id'),
        },
    },
    'course.branches.branch.subject',
    ...
]

$where

The where clause object.

  • Condition with AND
{
    $where: {
        key1: value1,
        key2: value2
    } 
    // key1 == value1 AND key2 == value2
}

{
    $where: {
        $and: [ { key1: value1 }, { key2: value2 } ]
    } 
    // key1 == value1 AND key2 == value2
}
  • Condition with OR
{
    $where: {
        $or: [ { key1: value1 }, { key2: value2 } ],
        $or_2: [ { key3: value3, key4: value4 }, { key5: value5 } ],
    }
    // (key1 == value1 OR key2 == value2) AND ((key3 == value3 AND key4 == value4) OR (key5 == value5))
}
  • Condition with NOT
{
    $where: {
        $not: {
            key1: value1,
            key2: value2
        }
    }
    // NOT (key1 == value1 AND key2 == value2)
}
  • Condition with Expressions
{
    $where: {
        $expr: xrExpr(xrCol('someField'), '==', xrCol('someField2')),
        $expr_2: xrExpr(xrCol('metadata'), '@>', obj)
    }
    // someField == someField2 AND metadata @> $1
    // $1: obj
}
  • Condition with Raw Statement

Had better put a db.paramToken as the placeholders of params and separately pass the params array as the second argument.

const duration = [10, 20];
await Book.findMany_({
    $where: {
        $expr: xrRaw(
            `("testJson"->>'duration')::INTEGER > ${Book.db.paramToken} AND ("testJson"->>'duration')::INTEGER < ${Book.db.paramToken}`,
            duration
        ),
    },
});
Condition Operators
  • $gt: >
  • $lt: <
  • $gte: >=
  • $lte: <=
  • $exist
    • { $exist: true } = IS NOT NULL
    • { $exist: false } = IS NULL
  • $eq: ==
  • $neq: <>
  • $in, $notIn
  • $between, $notBetween
  • $startsWith: LIKE %S
  • $endsWith: LIKE S%
  • $like: LIKE %S%
  • $fitler: jsonb_field @> filter

$orderBy

  • Order by ascending
{
    ...,
    $orderBy: 'field1' // ORDER BY field1 
}

{
    ...,
    $orderBy: [ 'field1', 'field2' ] // ORDER BY field1, field2
}

{
    ...,
    $orderBy: { 'field1': true } // ORDER BY field1 
}

{
    ...,
    $orderBy: { 'field1': 1 } // ORDER BY field1 
}
  • Order by descending
{
    ...,
    $orderBy: { 'field1': false } // ORDER BY field1 DESC
}

{
    ...,
    $orderBy: { 'field1': -1 } // ORDER BY field1 DESC 
}
  • Mix
{
    ...,
    $orderBy: { 'field1': -1, 'field2': 1 } // ORDER BY field1 DESC, field2 
}
  • Order by alias

When a query has joining tables, all column reference will be padding the table alias automatically, e.g. { $orderBy: 'field1' } will be converted into ORDER BY A."field1".

If you want to order by an alias which is not a column of any table, the above rule will fail the final SQL execution. A "::" prefix can be used to force the conversion to not add the table alias, i.e. { $orderBy: '::field1' } to ORDER BY "field1".

$groupBy

{
    ...,
    $groupBy: 'field1' // GROUP BY field1
}

{
    ...,
    $groupBy: [ 'field1', 'field2' ] // GROUP BY field1, field2
}

$offset

{
    $offset: 10
}

$limit

{
    $limit: 10
}

$countBy

Returns total record count

$includeDeleted -

To include logically deleted records

$skipOrm -

Internal use, to skip mapping the result into a nested object.

$asArray -

Return result as array, i.e. array mode.

$sqlOnly -

Only return the sql.

createOptions

$ignore -

If already exist (unique key conclicts), just ignore the operation.

$upsert -

If already exist (unique key conclicts), just update the record.

$getCreated -

Return created records.

updateOptions

$where

See $where in findOptions.

$getUpdated -

Return updated records.

createOptions & updateOptions

$bypassReadOnly -

Internal use, to bypass some read-only fields

$skipModifiers -

Skip auto generated modifiers.

$skipValidators -

Skip the specified validators

$dryRun -

Just do the entity pre-process and skip the actual db creation call.

$migration -

For migration only.

deleteOptions

$getDeleted -

Return the deleted records

$deleteAll -

To delete all records with deleteMany_

$physical -

To physically delete a record.

Data to Update

  • Plain Object
const data = {
    field1: 'value1',
    field2: 'value2'
};

await Entity.updateOne_(data, { $where, ... });
  • Special Values
    • xrCol - Set the value to another column
    • xrCall - Set the value to the result of a function call
    • xrExpr - Set the value to the result of an expression
    • xrRaw - Set the value to a raw SQL statement
    • doInc(field, n): shorthand for field + n using xrExpr and xrCol
    • doDec(field, n): shorthand for field - n using xrExpr and xrCol
entity.updateOne_({
    version: doInc('version', 1)  // version = version + 1
}, { $where: { id: xxx } })
  • Special Operators
    • $set: { key: value [, key2: value2, ...] } for jsonb field
    • $setAt: { at, value } for array field
    • $setSlice: { begin, end, value } for update partial array field, value is an array

Transaction

When executing transactions, DbModel (i.e. entity.db or app.db()) will fork a new instance containing a dedicated connection for a transaction.

The business logic of the whole transaction should be wrapped in an async function and should use the newly given db object as _db in below example.

  // Transaction
  const ret = await this.db.transaction_(async (_db) => {

      const User = _db.entity('user'); // should use _db to get entity
      ...

      return ret;
  });

Create with Associations

  • 1:many as array and reference as object
const { op, data, affectedRows, insertId } = await Product.create_({
    'type': 'good',
    'name': 'Demo Product 2',
    'desc': 'Demo Product Description 2',
    'image': 'https://example.com/demo.jpg',
    'thumbnail': 'https://example.com/demo-thumb.jpg',
    'free': true,
    'openToGuest': true,
    'isPackage': false,
    'hasVariants': true,
    'category': 2,
    ':assets': [
        {
            'tag': 'snapshots',
            ':resource': {
                mediaType: 'image',
                url: 'https://example.com/demo-asset.jpg',
            },
        },
        {
            'tag': 'snapshots',
            ':resource': {
                mediaType: 'image',
                url: 'https://example.com/demo-asset2.jpg',
            },
        },
        {
            'tag': 'poster',
            ':resource': {
                mediaType: 'image',
                url: 'https://example.com/demo-poster.jpg',
            },
        },
    ],
    ':attributes': [
        {
            type: 'dimension',
            value: '10x10x10',
        },
        {
            type: 'origin',
            value: 'China',
        },
    ],
    ':variants': [
        {
            type: 'color',
            value: 'red',
        },
        {
            type: 'color',
            value: 'blue',
        },
        {
            type: 'color',
            value: 'green',
        },
        {
            type: 'size',
            value: 'L',
        },
        {
            type: 'size',
            value: 'M',
        },
        {
            type: 'size',
            value: 'S',
        },
    ],
});

Find with Associations

const result = await Product.findOne_({
    id: insertId,   // all k-v pairs without starting with $ will be pushed into $where
    $select: ['*'],
    $relation: ['category', 'assets.resource', 'attributes', 'variants'],
});

Update with associations

  • Update 1:1 or m:1 association
  • Update 1:m assoication with sub-operations
    • $delete : delete existing
    • $update : update existing
    • $create : create more
await Product.updateOne_(
    {
        'name': 'Demo Product 200',
        // a product has many assets
        ':assets': { 
            $delete: [existing[':assets'][0], existing[':assets'][1]],
            $update: [
                {
                    ...Product.getRelatedEntity('assets').omitReadOnly(existing[':assets'][2]),
                    tag: 'poster2',
                },
            ],
            $create: [
                {
                    'tag': 'poster',
                    ':resource': {
                        mediaType: 'image',
                        url: 'https://example.com/demo-poster2.jpg',
                    },
                },
            ],
        },
    },
    { $where: { id: insertId }, $getUpdated: true }
);

License

  • MIT
  • Copyright (c) 2023 KITMI PTY LTD