algaeh-mysql

This package is developed as per our company need. it may help others. algaeh-mysql is completely promise base api and developer has the facility to release connection where ever it required.

Importing

import algaehMysql from "algaeh-mysql";

Creating instance

const _mysql = new algaehMysql();

Configure MYSQL Connection

By default configuration took from the path root of current working directory. path.join(process.cwd(), "../keys/keys.js") even you can pass the connection configuration to the 'algaehMysql' instance

const _mysql = new algaehMysql({
  path: {
    mysqlDb: {
      host: "localhost",
      port: 3306,
      user: "root",
      password: "****",
      database: "Optional",
      multipleStatements: true
    }
  }
});

Or by passing configuration file path

const _mysql = new algaehMysql({
  path: "file path"
});

Code Syntax

Simple select

_mysql
  .executeQuery({
    query: "select * from table_name where table_id=?",
    values: [10],
    printQuery: true
  })
  .then(result => {
    _mysql.releaseConnection();
    //result ...........
  })
  .catch(error => {
    _mysql.releaseConnection();
    //error .........
  });

the above basic example execute a simple query.

| Field | Description | | ----------------- | ------------------------------------------------- | | executeQuery | method execute without transaction | | query | query string | | values | array of values | | printQuery | it print the query on console in development mode | | releaseConnection | method to release the perticular connection |

[========]

** Bulk insert ,replace keys and exclude values and extravalues**

_mysql
  .executeQuery({
    values: [
      { algaeh_d_formulas_id: 3, formula_for: "Testing", status: "A" },
      { algaeh_d_formulas_id: 4, formula_for: "Testing1", status: "I" }
    ],
    extraValues: {
      formula: "A+B"
    },
    excludeValues: ["formula_for"],
    replcaeKeys: { status: "record_status" },
    query: "insert into  algaeh_d_formulas (??) values ?",
    printQuery: true,
    bulkInsertOrUpdate: true
  })
  .then(result => {
    _mysql.releaseConnection();
    //result -----------
  })
  .catch(error => {
    _mysql.releaseConnection();
    //error -----------
  });

| Field | Description | | ------------------ | ------------------------------------ | | extraValues | insert extra values | | replcaeKeys | replace key name with other key name | | bulkInsertOrUpdate | is true for array of objects |

[========]

**Bulk update ,replace keys and exclude values and extravalues **

_mysql
  .executeQuery({
    values: [
      {
        algaeh_d_formulas_id: 4,
        formula_for: "Testing1",
        status: "I"
      }
    ],
    extraValues: {
      formula: "A+B"
    },
    excludeValues: ["formula_for"],
    replcaeKeys: { status: "record_status" },
    where: ["algaeh_d_formulas_id"],
    query: "update  algaeh_d_formulas set ? where algaeh_d_formulas_id=?",
    printQuery: true,
    bulkInsertOrUpdate: true
  })
  .then(result => {
    _mysql.releaseConnection();
    // result ---------
  })
  .catch(error => {
    _mysql.releaseConnection();
    //error
  });

[========]

** Execute with transaction **

_mysql
  .executeQueryWithTransaction({
    query: "insert into tableName(`a`,`b`) values(?,?)",
    values: ["aaa", "bbb"],
    printQuery: true
  })
  .then(result => {
    const id = result.insertId;
    _mysql
      .executeQuery({
        query: "insert into tableNameX(`id`,'x') values(?,?)",
        values: [id, "xyz"],
        printQuery: true
      })
      .then(finalresult => {
        _mysql.commitTransaction((error, resu) => {
          if (error) {
            _mysql.rollBackTransaction();
            //error-------
          } else {
            //resu =Success;
            _mysql.releaseConnection();
            //finalresult --------------
          }
        });
      })
      .catch(error => {
        _mysql.rollBackTransaction(() => {
          //error --------
        });
        //or _mysql.rollBackTransaction();
      });
  })
  .catch(error => {
    _mysql.rollBackTransaction(() => {
      //error --------
    });
    //or _mysql.rollBackTransaction();
  });

[========]

to get connection at anypoint of time by calling the funtion

_mysql.getMysqlConnection((error, connection) => {
  if (error) {
    //error---------
  } else {
    //connection
  }
});

to format our as sql string

let query = "";
for (let i = 0; i < 10; i++) {
  query = _mysql.mysqlQueryFormat(
    "insert into tableName(`a`,`i`) values(?,?)",
    ["a" + i, i]
  );
}