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 other query fields like, 'find', 'sort', 'skip', 'limit', and 'select'.

await g.sys.system.executeQuery({
    instance: 'INSTANCE_NAME',
    database: 'DATABASE_NAME',
    collection: 'COLLECTION_NAME',
    query: {
        find: {
            "column_name": "value"
        },
        sort: "column_name",
        skip: 3,
        limit: 5,
        select: "column_name with comma sparator",
    }
});

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: 'am_cloud',
      collection: 'orders',
      query: {
          find: {
              "pro_name": "Oil"
          },
          sort: "created_at",
          skip: 3,
          limit: 5,
          select: "order_id,order_price,pro_name,created_at",
      }
  })
  

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