Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Execute query

g.sys.system.executeQuery calls the execute plain query system API from your code, whatever the settings of that API say about HTTP callers.

Execute query System API

Execute database query using global object 'g'.

1
2
3
4
5
6
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/
1
2
3
4
5
6
7
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. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    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. 

    1
2
3
4
    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 

    1
2
3
4
    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 

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

  • MariaDB 

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

  • Microsoft SQL Server 

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

  • MongoDB 

    1
2
3
4
5
6
7
    await g.sys.system.executeQuery({
    instance: 'mongodb',
    database: 'inventory',
    query: {
        dbStats: 1,
    }
});

  • PostgreSQL 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
    await g.sys.system.executeQuery({
    instance: "postgresql",
    query: `drop database if exists e_commerce;`
});

await g.sys.system.executeQuery({
    instance: "postgresql",
    query: `create database e_commerce;`
});

await g.sys.system.executeQuery({
    instance: "postgresql",
    database: "e_commerce", // 👈 Need to provide db name
    query: `
DROP TABLE IF EXISTS "public"."customers";
CREATE TABLE "public"."customers"
(
    "customer_id" int2                                       NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 32767 START 1 ),
    "first_name"  varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
    "last_name"   varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
    "phone"       int8                                       NOT NULL,
    "last_update" timestamp(6)                               NOT NULL,
    "pincode" int NOT NULL,
    "shipping_id" int NULL,
    CONSTRAINT "customers_pkey" PRIMARY KEY ("customer_id")
);
CREATE INDEX "idx_actor_last_name" ON "public"."customers" USING btree ("last_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST);

INSERT INTO "public"."customers" ("first_name", "last_name", "phone", "last_update", "pincode", "shipping_id") VALUES ('PENELOPE', 'GUINESS', 123456789, '2006-02-15 04:34:33', 100050, 1);
INSERT INTO "public"."customers" ("first_name", "last_name", "phone", "last_update", "pincode", "shipping_id") VALUES ('NICK', 'WAHLBERG', 1123456789, '2006-02-15 04:34:33', 111111, 2);
INSERT INTO "public"."customers" ("first_name", "last_name", "phone", "last_update", "pincode", "shipping_id") VALUES ('ED', 'CHASE', 2123456789, '2006-02-15 04:34:33', 222222, 3);
        `
});

  • 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.
1
2
3
4
await g.sys.system.executeQuery({
    instance: "oracle",
    query: `SELECT * FROM "INVENTORY"."employees"`
});

DDL Operation in API Maker

  • CREATE TABLE 

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

  • ALTER TABLE 

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

  • RENAME TABLE 

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

  • TRUNCATE TABLE 

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

  • DROP TABLE 

    1
2
3
4
    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 

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

  • DELETE 

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

  • UPDATE 

    1
2
3
4
    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 

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

  • DISTINCT 

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

  • ORDER BY 

    1
2
3
4
    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 

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

  • AVG 

    1
2
3
4
    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 

    1
2
3
4
    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 

    1
2
3
4
    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". 
    1
2
3
4
    await g.sys.system.executeQuery({
    instance: "sql_server",
    query: "USE inventory; exec dbo.procedureName;"
});

How to create index in Mongodb

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
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;
}

Multi-tenant

  • instance: "crm::acme" runs the query in the database of the tenant acme of the multi-tenant instance crm.
  • In a custom API called for a tenant, instance: "crm" runs it in the database of that tenant: the x-am-tenant-username header is passed on. Without a tenant, it runs in the structure database.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// The database of acme.
const ordersOfAcme = await g.sys.system.executeQuery({
    instance: 'crm::acme',
    database: 'crm',
    query: "select status, count(*) as orders from orders group by status"
});

// The database of the tenant of the request.
const customers = await g.sys.system.executeQuery({
    instance: 'crm',
    database: 'crm',
    query: "select count(*) as customers from customers"
});