will-db

Relational Database service for moleculer

Installation

npm install will-db

Examples

Configuration

This module require configuration (config) setting by config/default.json under project and will-sql, will-util

npm install config

{
    "MYSQL" : { "alias": "mysql", "dialect": "mysql", "url": "mysql://user:password@localhost:3306/testdb?charset=utf8&connectionLimit=10", "user": "user", "password": "password" },
    "ODBC" : { "alias": "odbc", "dialect": "mysql", "url": "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;DATABASE=testdb;HOST=localhost;PORT=3306;UID=user;PWD=password;", "user": "user", "password": "password" },
    "MSSQL": { "alias": "mssql", "dialect": "mssql", "url": "Server=localhost,1433;Database=testdb;User Id=user;Password=password;Encrypt=false;Trusted_Connection=Yes;", "user": "user", "password": "password" },
    "ORACLE": { "alias": "oracle", "dialect": "oracle", "url": "localhost:1521/ORCLCDB.localdomain", "user": "user", "password": "password" },
    "POSTGRES": { "alias": "postgres", "dialect": "postgres", "url": "postgresql://user:password@localhost:5432/testdb", "user": "user", "password": "password" },
    "INFORMIX": { "alias": "odbc", "dialect": "informix", "url": "DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)};SERVER=online_localhost;DATABASE=refdb;HOST=localhost;SERVICE=9088;UID=user;PWD=password;CLIENT_LOCALE=th_th.thai620;DB_LOCALE=th_th.thai620;", "user": "user", "password":"password" }
}

npm install will-sql
npm install will-util

KnService

KnService provide handler for CRUD in database table

Usage

import { ServiceBroker } from "moleculer";
import KnService from "will-db";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    model: {
        name: "testdbx",
        alias: { privateAlias: "MYSQL" },
    }
});
broker.start()
.then(() => broker.call("service.list").then((result) => { 
    console.log("service.list",result);
}))

CRUD Support

Create

import { ServiceBroker } from "moleculer";
import KnService from "will-db";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    model: {
        name: "testdbx",
        alias: { privateAlias: "MYSQL" },
    }
});
broker.start()
.then(() => broker.call("service.create",{share: "XXX", yield: 45, percent: 35, mktid: "TST", remark: "Testing"}).then((result) => { 
    console.log("service.create",result);
}))

Retrieve

import { ServiceBroker } from "moleculer";
import KnService from "will-db";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    model: {
        name: "testdbx",
        alias: { privateAlias: "MYSQL" },
    }
});
broker.start()
.then(() => broker.call("service.find",{share: "XXX"}).then((result) => { 
    console.log("service.find",result);
}))

Update

import { ServiceBroker } from "moleculer";
import KnService from "will-db";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    model: {
        name: "testdbx",
        alias: { privateAlias: "MYSQL" },
    }
});
broker.start()
.then(() => broker.call("service.update",{yield: 55, remark: "Update Testing"}).then((result) => { 
    console.log("service.update",result);
}))

Delete

import { ServiceBroker } from "moleculer";
import KnService from "will-db";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    model: {
        name: "testdbx",
        alias: { privateAlias: "MYSQL" },
    }
});
broker.start()
.then(() => broker.call("service.clear",{share: "XXX"}).then((result) => { 
    console.log("service.clear",result);
}))

Model Setting

import { ServiceBroker } from "moleculer";
import KnService from "will-db";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    model: {
        name: "testdbx",
        alias: { privateAlias: "MYSQL" },
        fields: {
            //this field make as key field
            share: { type: "STRING", key: true },
            mktid: { type: "STRING" },
            yield: { type: "DECIMAL" },
            percent: { type: "DECIMAL" },
            //this field does not in select clause
            remark: { type: "STRING", selected: false },
            //this field does not exist in table, it is user defined field  
            amount: { type: "DECIMAL", calculated: true }, 
        },
    },
});

broker.start()
.then(() => broker.call("service.find",{share: "XXX"}).then((result) => { 
    console.log("service.find",result);
}))

model

| Property | Type | Default | Description | | -------- | ---- | ------- | ----------- | | name | string | required | Name of table. | | alias | Object | required | The object must have key privateAlias. | | fields | FieldSetting | undefined | Schema for table name. |

Field Setting

| Property | Type | Default | Description | | -------- | ---- | ------- | ----------- | | name | string | required | Name of table column. | | type | string | required | Data type of table column ex. STRING, INTEGER, DECIMAL, BOOLEAN, BIGINT, TEXT, DATE, TIME, DATETIME, CLOB, BLOB. | | size | number | undefined | Length of table column. | | precision | number | undefined | Precision of table column. | | key | boolean | false | This column is key field. | | calculated | boolean | false | This column is calculated field. | | selected | boolean | true | This column should select or not. | | nullable | boolean | true | This column can be null. |

Actions

| Name | Description | | -------- | ----------- | | create | To create new record. | | find | To find out data records. | | list | To list out data records (as same as find). | | update | To update data record. | | clear | To delete data record. | | insert | For override. | | retrieve | For override. | | collect | For override. | | remove | For override. |

Results

Type: ResultSet {rows: Object, columns: Object}

Raw Query

The handler provide two methods (executeQuery & executeUpdate) to execute query statement by this.handler

import { ServiceBroker } from "moleculer";
import KnService from "will-db";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    model: {
        name: "testdbx",
        alias: { privateAlias: "MYSQL" },
    },
    actions: {
        getShare() {
            let sql = "select * from testdbx where share = 'XXX'";
            return this.handler.executeQuery(sql);    
        },
        updatePercent() {
            let sql = "update testdbx set percent = 55 where share = 'XXX'";
            return this.handler.executeUpdate(sql);
        }
    }
});

broker.start()
.then(() => broker.call("service.getShare").then((result) => { 
    console.log("service.getShare",result);
}))
.then(() => broker.call("service.updatePercent").then((result) => { 
    console.log("service.getupdatePercentYield",result);
}))

Handler

In order to make your own handler it can be extend from KnHandler.

MyHandler

import { KnHandler, KnModel } from "will-db";
import { KnSQL, ResultSet } from "will-sql";

class MyHandler extends KnHandler {
    //this is an addon action
    public findby(context: any) : Promise<string> {
        console.debug("findby",context);
        return Promise.resolve("MyHandler.findby");
    }
    //must declared handlers in order to merge addon action to service 
    //ex. name: "findby" is the handler naming function from public findby(context) method
    public handlers = [ {name: "findby"} ];

    //have to defined model to connect db
    public model : KnModel = { name: "testdbx", alias: { privateAlias: "MYSQL" } };
    public override async collect(context: any) : Promise<ResultSet> {
        let db = this.getPrivateConnector(this.model);
        try {
            let knsql = new KnSQL("select * from testdbx");
            if(context.params.sharecode) {
                knsql.append(" where share = ?sharecode ");
                knsql.set("sharecode",context.params.sharecode);
            }
            let rs = await knsql.executeQuery(db);
            return Promise.resolve(rs);
        } finally {
            db.close();
        }
    }
}

MyHandler Usage

import { ServiceBroker } from "moleculer";
import KnService from "will-db";
import MyHandler from "./MyHandler";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    handler: new MyHandler(),    
});

broker.start()
.then(() => broker.call("service.collect",{sharecode: "XXX"}).then((result) => { 
    console.log("service.collect",result);
}))

Pagination

In order to reduce large result set from query. Action find and list support paging as default.

import { ServiceBroker } from "moleculer";
import KnService from "will-db";

const broker = new ServiceBroker({
    logLevel: "debug"
});
broker.createService({
    name: "service",
    mixins: [KnService],
    model: {
        name: "test1",
        alias: { privateAlias: "MYSQL" },
    },
    //as default rowsPerPage = 20 and maxRowsPerPage = 100
    //this setting will override default value
    settings: {
        //this is minimum number of records return
        rowsPerPage: 10,
        //this is maximum number of records return, 
        //so it can reach when defined rowsPerPage over as parameter
        maxRowsPerPage: 200,
        //to disable column schema output
        disableColumnSchema: true,
        //to disable page offset output
        disablePageOffset: true,
    }
});

broker.start()
//default page number = 1
.then(() => broker.call("service.list").then((result) => { 
    console.log("service.list",result);
}))
//specified order by field and order direction
.then(() => broker.call("service.list",{page: 2, orderBy: "field1", orderDir: "ASC"}).then((result) => { 
    console.log("service.list",result);
}))

.then(() => broker.call("service.list",{page: 3, orderBy: "field1", orderDir: "DESC"}).then((result) => { 
    console.log("service.list",result);
}))

//defined paging: rowsPerPage = 10 as parameter
.then(() => broker.call("service.list",{page: 1, rowsPerPage: 10 }).then((result) => { 
    console.log("service.list",result);
}))

Paging Result

Result set of paging include attribute

offsets: {
    //current page
    page: 1,
    //number of rows per page
    rowsPerPage: 10,
    //total rows from query
    totalRows: 27,
    //total pages
    totalPages: 3,
    //limit of result set
    limit: 10,
    //offset query depending on current page
    offset: 0,
    //order by field name
    orderBy: 'field1',
    //order direction (ASC/DESC)
    orderDir: 'ASC'
}