db API

init

This function helps to initialize MySql Client This function should be called before calling any other functions in this library

Best practice is to import @aicore/libcommonutils and call getMySqlConfigs() api to read values from of configs from environment variables.

Parameters

• config Object config to configure MySQL

  • config.host string mysql database hostname
  • config.port string port number of mysql db
  • config.database string name of database to connect
  • config.user string username of database
  • config.password string password of database username

Examples

Sample config

const config = {
   "host": "localhost",
   "port": "3306",
   "database": "testdb",
   "user" : "root",
   "password": "1234"
 };

Sample initialization code

// set  following  environment variables to access database securely
// set MY_SQL_SERVER for mysql server
// set MY_SQL_SERVER_PORT to set server port
// set MY_SQL_SERVER_DB to specify database where database operations are conducted
// set MY_SQL_USER to specify database user
// set MY_SQL_PASSWORD to set mysql password

import {getMySqlConfigs} from "@aicore/libcommonutils";

const configs = getMySqlConfigs();
init(configs)

Returns boolean true if connection is successful false otherwise*

close

This function helps to close the database connection

Returns void

_isValidTableAttributes

It checks if the table attribute name is a string, and if it is, it checks if the length of the string is less than or equal to the maximum length of a MySQL table name or column name, and if it is, it checks if the string matches the regular expression for a table attribute name

Parameters

• tableAttributeName string The name of the table attribute.

Returns boolean A boolean value.

_isValidPrimaryKey

Returns true if the given key is a string of length greater than zero and less than or equal to the maximum size of a primary key.

Parameters

• key string The primary key of the item to be retrieved.

Returns boolean A boolean value.

createTable

It creates a table in the database with the name provided as the parameter

we have simplified our database schema, for us, our database has only two columns

1. primary key column, which is a varchar(255)
2. JSON column, which stores values corresponding to the primary key as JSON using this approach will simplify our database design by delegating the handling of the semantics of data to the application.To speed up any query, we have provided an option to add a secondary index for JSON fields using createIndexForJsonField api.

Parameters

• tableName string name of table to create

Examples

How to use this function?

import {getMySqlConfigs} from "@aicore/libcommonutils";

const configs = getMySqlConfigs();
init(configs)
const tableName = 'customer';
try {
  await createTable(tableName);
} catch(e){
    console.error(JSON.stringify(e));
}

Returns Promise returns a Promise await on Promise to get status of createTable on success await will return true. on failure await will throw an exception.

put

It takes a table name and a document and then inserts the document into the database.

Parameters

• tableName string The name of the table in which you want to store the data.
• document Object The JSON string that you want to store in the database.

Examples

Sample code

try {
      const primaryKey = 'bob';
      const tableName = 'customers;
      const document = {
          'lastName': 'Alice',
          'Age': 100,
          'active': true
      };
      await put(tableName, document);
  } catch (e) {
      console.error(JSON.stringify(e));
 }

Returns Promise A promise on resolving the promise will give documentID throws an exception otherwise. DocumentId is an alphanumeric string of length 128

createDocumentId

It generates a random string of 16 hexadecimal characters When converting hexadecimal to string. The generated string will contain 32 characters

Returns string A random string of hexadecimal characters.

deleteKey

It deletes a document from the database based on the document id

Parameters

• tableName string The name of the table in which the key is to be deleted.
• documentID string document id to be deleted

Examples

Sample code

const tableName = 'customers';
const documentID = '123456';
try {
   await deleteKey(tableName, documentID);
} catch(e) {
   console.error(JSON.stringify(e));
}

Returns Promise A promise resolve promise to get status of delete. promise will resolve to true for success and throws an exception for failure.

get

It takes in a table name and documentId, and returns a promise that resolves to the document

Parameters

• tableName string The name of the table in which the data is stored.
• documentID string The primary key of the row you want to get.

Examples

sample code

const tableName = 'customers';
const documentID = '12345';
try {
    const results = await get(tableName, documentID);
    console.log(JSON.stringify(result));
} catch(e){
    console.error(JSON.stringify(e));
}

Returns Promise A promise on resolve promise to get the value stored for documentID

_queryScanBuilder

It takes a JSON object and returns a SQL query and an array of values to be used in a prepared statement

Parameters

• subQueryObject Object This is the object that you want to query.
• parentKey string? This is the parent key of the current object. (optional, default "")

Returns Object An object with two properties: getQuery and valArray.

_prepareQueryForScan

It takes a table name and a query object and returns a query string and an array of values to be used in a prepared statement

Parameters

• tableName string The name of the table to query.
• queryObject Object The query object that you want to run.

Returns Object An object with two properties: getQuery and valArray.

getFromNonIndex

It takes a table name and a query object, and returns a promise that resolves to the array of matched documents. NB: this api will not detect boolean fields while scanning This query is doing database scan. using this query frequently can degrade database performance. if this query is more frequent consider creating index and use getFromIndex API

Parameters

• tableName string The name of the table you want to query.
• queryObject Object This is the object that you want to query.

Examples

sample code

const tableName = 'customers';
const queryObject = {
            'lastName': 'Alice',
            'Age': 100
        };
try {
    const scanResults = await getFromNonIndex(tableName, queryObject);
    console.log(JSON.stringify(scanResults));
} catch (e){
    console.error(JSON.stringify(e));
}

Returns Promise A promise; on promise resolution returns array of matched documents. if there are no match returns empty array

deleteTable

It deletes a table from the database

Parameters

• tableName string The name of the table to be deleted.

Examples

Sample code

const tableName = 'customer';
try{
  await deleteTable(tableName);
} catch(e){
    console.error(JSON.stringify(e));
}

Returns Promise A promise that will resolve to true if the table is deleted, or reject with an error if the table is not deleted.

_buildCreateJsonColumQuery

_buildCreateJsonColumQuery(tableName, nameOfJsonColumn, jsonField, dataTypeOfNewColumn, isNotNull, isUnique)

The function takes the following parameters:

• tableName - the name of the table to add the column to
• nameOfJsonColumn - the name of the new column
• jsonField - the name of the field in the JSON column to extract
• dataTypeOfNewColumn - the data type of the new column
• isNotNull - a boolean value indicating whether the new column should be nullable
• isUnique - a boolean value indicating whether the new column should be unique

Parameters

• tableName string The name of the table you want to add the column to.
• nameOfJsonColumn string The name of the new column that will be created.
• jsonField string The field in the JSON object that you want to extract.
• dataTypeOfNewColumn string This is the data type of the new column.
• isNotNull boolean If the new column should be NOT NULL
• isUnique boolean If true, the new column will be a unique key.

Returns string A string that is a query to add a column to a table.

_buildCreateIndexQuery

It takes a table name, a json field name, and a boolean value indicating whether the index should be unique or not, and returns a string containing the SQL query to create the index

Parameters

• tableName string The name of the table to create the index on.
• jsonField string The name of the JSON field that you want to index.
• isUnique boolean If true, the index will be unique.

Returns string A string that is a query to create an index on a table.

_createIndex

It creates an index on the JSON field in the table

Parameters

• resolve function A function that is called when the promise is resolved.
• reject function A function that will be called if the promise is rejected.
• tableName string The name of the table to create the index on
• jsonField string The JSON field that you want to create an index on.
• isUnique boolean true if the index is unique, false otherwise

Returns void NB private function exporting this for testing

_isJsonField

It checks if the jsonField is a valid json field.

Parameters

• jsonField string The JSON field to be queried.

Returns boolean if its valid json field false otherwise

_getColumNameForJsonField

It takes a string and returns a hash of that string

Parameters

• jsonField string The JSON field you want to query.

Returns string A string of hexadecimal characters.

createIndexForJsonField

It creates a new column in the table for the JSON field and then creates an index on that column. NB: this will not work with boolean fields

Parameters

• tableName string The name of the table in which you want to create the index.
• jsonField string The name of the field in the JSON object that you want to index.
• dataTypeOfNewColumn string This is the data type of the new column that will be created. visit https://dev.mysql.com/doc/refman/8.0/en/data-types.html to know all supported data types
• isUnique boolean If true, the json filed has to be unique for creating index. (optional, default false)
• isNotNull boolean If true, the column will be created with NOT NULL constraint. (optional, default false)

Examples

Sample code

const tableName = 'customers';
let jsonfield = 'lastName';
// supported data types can be found on https://dev.mysql.com/doc/refman/8.0/en/data-types.html
let dataTypeOfNewColumn = 'VARCHAR(50)';
let isUnique = false;
try{
     await createIndexForJsonField(tableName jsonfield, dataTypeOfNewColumn, isUnique);
     jsonfield = 'Age';
     dataTypeOfNewColumn = 'INT';
     isUnique = false;

     await createIndexForJsonField(tableName, nameOfJsonColumn, jsonfield, dataTypeOfNewColumn, isUnique);
} catch (e){
     console.error(JSON.stringify(e));
}

Returns Promise A promise

_prepareQueryForNestedObject

It takes a nested object and returns a query string and an array of values

Parameters

• subQueryObject Object This is the object that you want to convert to a query.
• parentKey string? This is the key of the parent object. (optional, default "")

Returns Object An object with two properties, getQuery and valArray.

_prepareQueryOfIndexSearch

It takes a table name and a query object and returns a query string and an array of values

Parameters

• tableName string The name of the table in which the data is stored.
• queryObject Object The object that you want to search for.

_queryIndex

_queryIndex() is a function that takes a queryParams object, a resolve function, and a reject function as parameters. It then executes the query in the queryParams object, and if the query is successful, it returns the results of the query to the resolve function. If the query is unsuccessful, it returns the error to the reject function

Parameters

• queryParams Object This is an object that contains the query and the values to be used in the query.
• resolve Function a function that takes a single argument, which is the result of the query.
• reject Function a function that will be called if the query fails.

Returns Array An array of objects

getFromIndex

It takes a table name, a column name, and a query object, and returns a promise that resolves to an array of objects

Parameters

• tableName string The name of the table in which the data is stored.
• queryObject Object This is the object that you want to search for.

Examples

Sample code

const tableName = 'customer';
const queryObject = {
            'lastName': 'Alice',
            'Age': 100
            };
try {
     const queryResults = await getFromIndex(tableName, queryObject);
     console.log(JSON.stringify(queryResults));
} catch (e) {
     console.error(JSON.stringify(e));
}

Returns Promise A promise; on promise resolution returns array of matched values in json column. if there are no matches returns empty array. if there are any errors will throw an exception

update

It updates the document in the database This api will overwrite current document with new document

Parameters

• tableName The name of the table to update.
• documentId The primary key of the document to be updated.
• document The document to be inserted.

Examples

Sample code

const docId = 1234;
const document = {
            'lastName': 'Alice1',
            'Age': 140,
            'active': true
             };
try{
     await update(tableName, docId, document);
} catch(e){
    console.error(JSON.stringify(e));
}

Returns Promise A promise on resolving promise will get documentId