# Execute Query Example

> Learn how to run SQL queries with API Maker’s executeQuery function, including DDL, DML, DQL, and joins.

Source: https://docs.apimaker.dev/v1/examples/sys/system/executeQuery.html

`g.sys.system.executeQuery` calls the [execute plain query system API](https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) from your code, whatever the settings of that API say about HTTP callers.

### Execute query System API

Execute database query using global object 'g'.

```ts
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/

```ts
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.
```ts
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.
```ts
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
```ts
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
```ts
await g.sys.system.executeQuery({
    instance: "mysql",
    query: "SELECT * FROM inventory.employees;"
});
```

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

- Microsoft SQL Server
```ts
await g.sys.system.executeQuery({
    instance: "sqlserver",
    query: "SELECT * FROM inventory.dbo.employees;"
});
```

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

- PostgreSQL
```ts
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.

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

### DDL Operation in API Maker

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

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

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

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

- DROP TABLE
```ts
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
```ts
await g.sys.system.executeQuery({
    instance: "mysql",
    query: "INSERT into inventory.employees Values(101, 'Bob', 'M');"
})
```

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

- UPDATE
```ts
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
```ts
await g.sys.system.executeQuery({
    instance: "mysql",
    query: "SELECT * FROM inventory.employees;"
})
```

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

- ORDER BY
```ts
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
```ts
await g.sys.system.executeQuery({
    instance: "mysql",
    query: "SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;"
})
```

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

### How to create index in Mongodb

```ts
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](https://docs.apimaker.dev/v1/docs/features/multi-tenant.html) 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](https://docs.apimaker.dev/v1/docs/apis-all/header/requestHeader.html#x-am-tenant-username) header is passed on. Without a tenant, it runs in the structure database.

```ts
// 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"
});
```

## Related

- [Execute plain query system API](https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) · [All system APIs from code](https://docs.apimaker.dev/v1/examples/sys/system/system.html) · [The global object g](https://docs.apimaker.dev/v1/docs/pre-defined-terms/global-object-g.html)