Skip to content

db API

Jump to bottom
phoenixide edited this page Apr 2, 2023 · 31 revisions

createDataBase

It creates a database with the name provided as an argument

Parameters

  • databaseName string The name of the database to create.

Returns Promise<boolean> A promise which helps to know if createDataBase is successful

deleteDataBase

It deletes a database with the given name

Parameters

  • databaseName string The name of the database to be created.

Returns Promise<boolean> A promise which helps to know if database delete is successful

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.user string username of database
    • config.password string password of database username
    • config.connectionLimit Number Maximum MySql connection that can be open to the server default value is 10

Examples

Sample config

const config = {
   "host": "localhost",
   "port": "3306",
   "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_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. DocumentId is an alphanumeric string of length 128

deleteKey

It deletes a document from the database based on the document id. Conditional deletes are also supported with the optional condition parameter.

Parameters

  • tableName string The name of the table in which the key is to be deleted.
  • documentID string document id to be deleted
  • condition string? Optional coco query condition of the form "$.cost<35" that must be satisfied for delete to happen. See query API for more details on how to write coco query strings.

Examples

Sample code

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

Sample code with conditional option

const tableName = 'customers';
const documentID = '123456';
try {
   // Eg. delete the document only if the last modified is equals 21
   await deleteKey(tableName, documentID, "$.lastModified=21");
} 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.

deleteDocuments

Deletes a document from the database based the given query condition and returns the number of documents deleted.

Parameters

  • tableName string The name of the table in which the key is to be deleted.
  • queryString string The cocDB query string.
  • useIndexForFields (optional, default [])

Examples

Sample code

const tableName = 'dbName:customers';
try {
   // delete all documents with field 'location.city' set to paris
   let deletedDocumentCount = await deleteDocuments(tableName, "$.location.city  = 'paris'", ['location.city']);
} catch(e) {
   console.error(e);
}

Returns Promise A promise resolve with the number of deleted documents or 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. 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 NB: This query will return only 1000 entries.

Parameters

  • tableName string The name of the table you want to query.

  • queryObject Object This is the object that you want to query. (optional, default {})

  • options Object Optional parameter to add pagination. (optional, default {})

    • options.pageOffset number specify which row to start retrieving documents from. Eg: to get 10 documents from the 100'th document, you should specify pageOffset = 100 and pageLimit = 10
    • options.pageLimit number specify number of documents to retrieve. Eg: to get 10 documents from the 100'th document, you should specify pageOffset = 100 and pageLimit = 10

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. 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. The filed name should be a valid variable name of the form "x" or "x.y.z".
  • 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

getFromIndex

It takes a table name, a column name, and a query object, and returns a promise that resolves to an array of objects NB: This query will return only 1000 entries.

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.

  • options Object Optional parameter to add pagination. (optional, default {})

    • options.pageOffset number specify which row to start retrieving documents from. Eg: to get 10 documents from the 100'th document, you should specify pageOffset = 100 and pageLimit = 10
    • options.pageLimit number specify number of documents to retrieve. Eg: to get 10 documents from the 100'th document, you should specify pageOffset = 100 and pageLimit = 10

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

Updates the document in the database. Conditional updates are also supported with the optional condition parameter. 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.
  • condition string? Optional coco query condition of the form "$.cost<35" that must be satisfied for update to happen. See query API for more details on how to write coco query strings.

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));
}

Conditional update Sample code

const docId = 1234;
const document = {
            'lastName': 'Alice1',
            'Age': 140,
            'active': true
             };
try{
     // will update only if the existing document exists and the document has field `Age` with Value 100.
     await update(tableName, docId, document, "$.Age=100");
} catch(e){
    console.error(JSON.stringify(e));
}

Returns Promise<string> A promise resolves with documentId if success, or rejects if update failed as either document not found or the condition not satisfied.

mathAdd

It increments the value of a field in a JSON column in a MySQL table

Parameters

  • tableName string The name of the table in which the document is stored.
  • documentId string The primary key of the document you want to update.
  • jsonFieldsIncrements Object This is a JSON object that contains the fields to be incremented and the value by which they should be incremented.

Returns Promise<boolean> A promise

query

Execute a cocoDB query and return the documents matching the query. You can optionally specify a list of indexed fields to search on the index instead of scanning the whole table.

Parameters

  • tableName string The name of the table in which the data is stored.

  • queryString string The query as string.

  • useIndexForFields Array<String> A string array of field names for which the index should be used. Note that an index should first be created using createIndexForJsonField API. Eg. ['customerID', 'price.tax'] (optional, default [])

  • options Object Optional parameter to add pagination. (optional, default {})

    • options.pageOffset number specify which row to start retrieving documents from. Eg: to get 10 documents from the 100'th document, you should specify pageOffset = 100 and pageLimit = 10
    • options.pageLimit number specify number of documents to retrieve. Eg: to get 10 documents from the 100'th document, you should specify pageOffset = 100 and pageLimit = 10

Examples

A Sample coco query

const tableName = 'customer';
const queryString = `NOT($.customerID = 35 && ($.price.tax < 18 OR ROUND($.price.amount) != 69))`;
try {
     const queryResults = await query(tableName, queryString, ["customerID"]); // customerID is indexed field
     console.log(JSON.stringify(queryResults));
} catch (e) {
     console.error(JSON.stringify(e));
}

## cocodb query syntax
cocodb query syntax closely resembles mysql query syntax. The following functions are supported as is:

## `$` is a special character that denotes the JSON document itself.
All json field names should be prefixed with a `$.` symbol. For Eg. field `x.y` should be given
in query as `$.x.y`.
It can be used for json compare as. `JSON_CONTAINS($,'{"name": "v"}')`.
** WARNING: JSON_CONTAINS this will not use the index. We may add support in future, but not presently. **

### Supported functions
#### MATH functions defined in https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html
    'ABS', 'ACOS', 'ASIN', 'ATAN', 'ATAN2', 'ATAN', 'CEIL', 'CEILING', 'CONV', 'COS', 'COT',
    'CRC32', 'DEGREES', 'EXP', 'FLOOR', 'LN', 'LOG', 'LOG10', 'LOG2', 'MOD', 'PI', 'POW', 'POWER', 'RADIANS', 'RAND',
    'ROUND', 'SIGN', 'SIN', 'SQRT', 'TAN', 'TRUNCATE',
#### String functions defined in https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
    "ASCII", "BIN", "BIT_LENGTH", "CHAR", "CHAR_LENGTH", "CHARACTER_LENGTH", "CONCAT", "CONCAT_WS", "ELT", "EXPORT_SET",
    "FIELD", "FORMAT", "FROM_BASE64", "HEX", "INSERT", "INSTR", "LCASE", "LEFT", "LENGTH", "LOAD_FILE", "LOCATE",
    "LOWER", "LPAD", "LTRIM", "MAKE_SET", "MATCH", "MID", "OCT", "OCTET_LENGTH", "ORD", "POSITION", "QUOTE",
    "REGEXP_INSTR", "REGEXP_LIKE", "REGEXP_REPLACE", "REGEXP_SUBSTR", "REPEAT", "REPLACE", "REVERSE", "RIGHT",
    "RPAD", "RTRIM", "SOUNDEX", "SPACE", "SUBSTR", "SUBSTRING", "SUBSTRING_INDEX", "TO_BASE64", "TRIM",
    "UCASE", "UNHEX", "UPPER", "WEIGHT_STRING",
#### comparison
    "SOUNDS", "STRCMP",
#### Selected APIs defined in https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html
    "IF", "IFNULL", "NULLIF", "IN",
#### Selected JSON Functions in https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html
    "JSON_ARRAY", "JSON_ARRAY_APPEND", "JSON_ARRAY_INSERT", "JSON_CONTAINS", "JSON_CONTAINS_PATH", "JSON_DEPTH",
    "JSON_INSERT", "JSON_KEYS", "JSON_LENGTH", "JSON_MERGE_PATCH", "JSON_MERGE_PRESERVE", "JSON_OBJECT",
    "JSON_OVERLAPS", "JSON_QUOTE", "JSON_REMOVE", "JSON_REPLACE", "JSON_SEARCH", "JSON_SET", "JSON_TYPE",
    "JSON_UNQUOTE", "JSON_VALID", "JSON_VALUE", "MEMBER OF"
#### Other Keywords
    "LIKE", "NOT", "REGEXP", "RLIKE", "NULL", "AND", "OR", "IS", "BETWEEN", "XOR"

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