Skip to content

db API

Jump to bottom
phoenixide edited this page Aug 19, 2022 · 31 revisions

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

This function helps to create a table in database 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';
const nameOfPrimaryKey = 'name';
const nameOfJsonColumn = 'details';
try {
  await createTable(tableName, nameOfPrimaryKey, nameOfJsonColumn);
} 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, a primary key, a json column name, and a json value, and inserts the json value into the json column. If the primary key already exists, it updates the json column with the new value

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 valueOfJson = {
          'lastName': 'Alice',
          'Age': 100,
          'active': true
      };
      await put('hello', nameOfPrimaryKey, primaryKey, nameOfJsonColumn, valueOfJson);
  } 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 row from the database based on the primary key

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 = 'customer';
const nameOfPrimaryKey = 'name';
const primaryKey = 'bob';
try {
   await deleteKey(tableName, nameOfPrimaryKey, primaryKey);
} 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, a primary key name, a primary key value, and a json column name, and returns a promise that resolves to the json column value

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 = 'customer';
const nameOfPrimaryKey = 'name';
const nameOfJsonColumn = 'details';
const primaryKey = 'bob';
try {
    const results = await get(tableName, nameOfPrimaryKey, primaryKey, nameOfJsonColumn);
    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 primary key

getFromNonIndex

It takes a table name, a column name, and a query object, and returns a promise that resolves to the result of a scan of the table

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 = 'customer';
const nameOfJsonColumn = 'details';
const queryObject = {
            'lastName': 'Alice',
            'Age': 100
        };
try {
    const scanResults = await getFromNonIndex(tableName, nameOfJsonColumn, queryObject);
    console.log(JSON.stringify(scanResults));
} catch (e){
    console.error(JSON.stringify(e));
}

Returns Promise A promise; on promise resolution returns array of matched object from json column. 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 = 'customer';
const nameOfJsonColumn = 'customerDetails';
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, nameOfJsonColumn, 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 nameOfJsonColumn = 'customerDetails';
const queryObject = {
            'lastName': 'Alice',
            'Age': 100
            };
try {
     const queryResults = await getFromIndex(tableName, nameOfJsonColumn, 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

Clone this wiki locally