Execute query System API

Execute database query using global object 'g'.

await g.sys.system.executeQuery({
    instance: 'INSTANCE_NAME',
    database: 'DATABASE_NAME',
    collection: 'COLLECTION_NAME',
    query: "select * from DATABASE_NAME.COLLECTION_NAME"
});

• If the database is Mongodb API Maker supports all the commands listed in below.
• https://www.mongodb.com/docs/drivers/node/current/usage-examples/command/
• https://www.mongodb.com/docs/v6.0/reference/command/

await g.sys.system.executeQuery({
    instance: 'INSTANCE_NAME',
    database: 'DATABASE_NAME',
    query: {
        dbStats: 1,
    }
});

How we use Native Query in API Maker.

Native query using secret management

• Set Database constrain in Secret management.

  import * as T from 'types';
  
  let Secret: T.ISecretType | any = {
  // Place your keys here in json format.
      common: <T.ISecretTypeCommon>{
          dbConstrain: {
              instance: 'mysql',
              database: 'inventory',
              collection: ''
          }
      }
  };
  module.exports = Secret;
  

• Get Database constrain from Secret management.

  let dbConstrain = await g.sys.system.getSecret('common.dbConstrain');
  let instance = dbConstrain.instance || 'mysql';
  let database = dbConstrain.database || 'inventory';
  let collection = dbConstrain.collection || 'employees';
  

• Create table

  await g.sys.system.executeQuery({
      instance: `${instance}`,
      query: `CREATE TABLE ${database}.${collection}(emp_id int AUTO_INCREMENT PRIMARY KEY);`
  });
  

Various types of Database & It`s Native Query In API Maker

• MySQL | TiDB | Percona XtraDB

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "SELECT * FROM inventory.employees;"
  });
  

• MariaDB

  await g.sys.system.executeQuery({
      instance: "mariadb",
      query: "SELECT * FROM inventory.employees;"
  });
  

• Microsoft SQL Server

  await g.sys.system.executeQuery({
      instance: "sqlserver",
      query: "SELECT * FROM inventory.dbo.employees;"
  });
  

• MongoDB

  await g.sys.system.executeQuery({
      instance: 'mongodb',
      database: 'inventory',
      query: {
          dbStats: 1,
      }
  });
  

• Oracle

Please follow these rules when writing a query or statement:

• Use backticks (`) to enclose your query or statement.
• Do not add a semicolon (;) at the end of your query.

await g.sys.system.executeQuery({
    instance: "oracle",
    query: `SELECT * FROM "INVENTORY"."employees"`
});

DDL Operation in API Maker

• CREATE TABLE

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);"
  })
  

• ALTER TABLE

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;"
  })
  

• RENAME TABLE

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;"
  })
  

• TRUNCATE TABLE

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "TRUNCATE TABLE inventory.employees;"
  })
  

• DROP TABLE

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "DROP TABLE IF EXISTS inventory.employees;"
  })
  

DML Operation in API Maker

API Maker allows us to perform various DML operations, such as the following examples.

• INSERT

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "INSERT into inventory.employees Values(101, 'Bob', 'M');"
  })
  

• DELETE

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "DELETE FROM inventory.employees WHERE emp_Id = '101';"
  })
  

• UPDATE

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "UPDATE inventory.employees SET emp_Name = 'Alice' WHERE emp_Id = '101';"
  })
  

DQL Operation in API Maker

API Maker allows us to perform various DQL operations, such as the following examples.

• SELECT

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "SELECT * FROM inventory.employees;"
  })
  

• DISTINCT

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "SELECT DISTINCT emp_Name FROM inventory.employees;"
  })
  

• ORDER BY

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "SELECT * FROM inventory.employees order by emp_Gen,emp_Name;"
  })
  

Aggregated Function in API Maker

API Maker allows us to perform various Aggregated Function, such as the following examples.

• SUM

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;"
  })
  

• AVG

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "SELECT AVG(emp_Sal) as Average_Salary FROM inventory.employees;"
  })
  

Joins in API Maker

API Maker allows us to perform Joins, such as the following examples.

• INNER JOIN

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "SELECT * FROM inventory.employees INNER JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"
  })
  

• LEFT JOIN

  await g.sys.system.executeQuery({
      instance: "mysql",
      query: "SELECT * FROM inventory.employees LEFT JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"
  })
  

How to call SQL Server Store Procedure

• Just replace your procedure name with below "procedureName".

  await g.sys.system.executeQuery({
      instance: "sql_server",
      query: "USE inventory; exec dbo.procedureName;"
  });
  

How to create index in Mongodb

import * as T from 'types';
import * as db from 'db-interfaces';
import * as C from 'iot/Constants';

const createIndexList: IIndexObj[] = [{
    collectionName: "collectionName",
    indexName: 'indexName',
    isUnique: true,
    fields: {
        "category": 1,
        "productName": 1,
    },
}];

async function main(g: T.IAMGlobal) {
    const colMap = {};
    for (const iObj of createIndexList) {
        await dropIndexListOfCollection(g, iObj.collectionName, iObj.indexName);
        await createIndexOnCollection(g, iObj.collectionName, iObj.fields, iObj.indexName, iObj.isUnique);
        colMap[iObj.collectionName] = 1;
    }

    for (const colName in colMap) {
        const indexList = await getIndexListOfCollection(g, C.col.configuration_transactions);
        colMap[colName] = indexList;
    }

    return { hello: 'world', colMap };
};
module.exports = main;


async function getIndexListOfCollection(
    g: T.IAMGlobal,
    collectionName: string,
) {
    const listIndexResp = await g.sys.system.executeQuery({
        instance: C.insDB.instance,
        database: C.insDB.database,
        query: {
            listIndexes: collectionName,
        }
    });

    const indexList = [];
    for (const item of listIndexResp?.cursor?.firstBatch || []) {
        indexList.push(item.name);
    }

    return indexList;
}

async function createIndexOnCollection(
    g: T.IAMGlobal,
    collectionName: string,
    fields: any,
    indexName: string,
    isUnique: boolean,
) {
    const createIndexResp = await g.sys.system.executeQuery({
        instance: C.insDB.instance,
        database: C.insDB.database,
        query: {
            createIndexes: collectionName,
            indexes: [
                {
                    key: fields,
                    name: indexName,
                    unique: isUnique,
                },
            ],
        }
    });

    const indexList = [];
    for (const item of createIndexResp?.cursor?.firstBatch || []) {
        indexList.push(item.name);
    }

    return indexList;
}

async function dropIndexListOfCollection(
    g: T.IAMGlobal,
    collectionName: string,
    indexName: string,
) {
    try {
        const dropResp = await g.sys.system.executeQuery({
            instance: C.insDB.instance,
            database: C.insDB.database,
            query: {
                dropIndexes: collectionName,
                index: indexName,
            }
        });
        return dropResp;
    } catch (e) {
    }
}


interface IIndexObj {
    indexName: string;
    collectionName: string;
    fields: any;
    isUnique: boolean;
}