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

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

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

getFromNonIndex

It takes a table name and a query object, and returns a promise that resolves to the array of matched documents 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.

createIndexForJsonField

It creates a new column in the table for the JSON field and then creates an index on that column.

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.

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

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 any A promise on resolving promise will get documentId