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

Execute query

The code in every language

Each example is the inside of main, written in every language API Maker runs : pick yours in the tabs, and every page shows it from then on. The types a call takes (GetAll, DeepItem…) are those of am : from am import GetAll in Python, use am::GetAll; in Rust, import am.GetAll; in Java and Kotlin ; PHP and C# have them without an import, Go names them am.GetAll.

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

1
2
3
4
5
6
await g.sys.system.execute_query(
    'INSTANCE_NAME',
    'select * from DATABASE_NAME.COLLECTION_NAME',
    database='DATABASE_NAME',
    collection='COLLECTION_NAME',
)

1
2
3
4
5
6
$g->sys->system->executeQuery(
    'INSTANCE_NAME',
    'select * from DATABASE_NAME.COLLECTION_NAME',
    database: 'DATABASE_NAME',
    collection: 'COLLECTION_NAME',
);

1
2
3
4
5
await g.Sys.System.ExecuteQuery(new ExecuteQuery("INSTANCE_NAME", "select * from DATABASE_NAME.COLLECTION_NAME")
{
    Database = "DATABASE_NAME",
    Collection = "COLLECTION_NAME",
});

1
2
3
4
5
6
7
8
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance:   "INSTANCE_NAME",
    Database:   "DATABASE_NAME",
    Collection: "COLLECTION_NAME",
    Query:      "select * from DATABASE_NAME.COLLECTION_NAME",
}); err != nil {
    return nil, err
}

1
2
3
4
5
g.sys.system.execute_query(
    ExecuteQuery::new("INSTANCE_NAME", "select * from DATABASE_NAME.COLLECTION_NAME")
        .database("DATABASE_NAME")
        .collection("COLLECTION_NAME"),
)?;

1
2
3
g.sys.system.executeQuery(new ExecuteQuery("INSTANCE_NAME", "select * from DATABASE_NAME.COLLECTION_NAME")
    .database("DATABASE_NAME")
    .collection("COLLECTION_NAME"));

1
2
3
4
5
g.sys.system.executeQuery(
    ExecuteQuery("INSTANCE_NAME", "select * from DATABASE_NAME.COLLECTION_NAME")
        .database("DATABASE_NAME")
        .collection("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,
    }
});

1
2
3
4
5
6
7
await g.sys.system.execute_query(
    'INSTANCE_NAME',
    {
        'dbStats': 1,
    },
    database='DATABASE_NAME',
)

1
2
3
4
5
6
7
$g->sys->system->executeQuery(
    'INSTANCE_NAME',
    [
        'dbStats' => 1,
    ],
    database: 'DATABASE_NAME',
);

1
2
3
4
5
6
7
await g.Sys.System.ExecuteQuery(new ExecuteQuery("INSTANCE_NAME", new JsonObject
{
    ["dbStats"] = 1,
})
{
    Database = "DATABASE_NAME",
});

1
2
3
4
5
6
7
8
9
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "INSTANCE_NAME",
    Database: "DATABASE_NAME",
    Query: am.M{
        "dbStats": 1,
    },
}); err != nil {
    return nil, err
}

1
2
3
4
5
6
g.sys.system.execute_query(
    ExecuteQuery::new("INSTANCE_NAME", json!({
        "dbStats": 1,
    }))
        .database("DATABASE_NAME"),
)?;

1
2
3
4
g.sys.system.executeQuery(new ExecuteQuery("INSTANCE_NAME", Map.of(
    "dbStats", 1
))
    .database("DATABASE_NAME"));

1
2
3
4
5
6
g.sys.system.executeQuery(
    ExecuteQuery("INSTANCE_NAME", mapOf(
        "dbStats" to 1,
    ))
        .database("DATABASE_NAME"),
)

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: 'employees'
        }
    }
};
module.exports = Secret;

  • Read it from the secret, and create the table it names.

1
2
3
4
5
6
7
8
9
const dbConstrain = await g.sys.system.getSecret('common.dbConstrain');
const instance = dbConstrain?.instance ?? 'mysql';
const database = dbConstrain?.database ?? 'inventory';
const collection = dbConstrain?.collection ?? 'employees';

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

1
2
3
4
5
6
db_constrain = await g.sys.system.get_secret('common.dbConstrain') or {}
instance = db_constrain.get('instance') or 'mysql'
database = db_constrain.get('database') or 'inventory'
collection = db_constrain.get('collection') or 'employees'

await g.sys.system.execute_query(instance, f'CREATE TABLE {database}.{collection}(emp_id int AUTO_INCREMENT PRIMARY KEY);')

1
2
3
4
5
6
$dbConstrain = $g->sys->system->getSecret('common.dbConstrain');
$instance = $dbConstrain->instance ?? 'mysql';
$database = $dbConstrain->database ?? 'inventory';
$collection = $dbConstrain->collection ?? 'employees';

$g->sys->system->executeQuery($instance, "CREATE TABLE {$database}.{$collection}(emp_id int AUTO_INCREMENT PRIMARY KEY);");

1
2
3
4
5
6
var dbConstrain = await g.Sys.System.GetSecret("common.dbConstrain");
var instance = dbConstrain.GetString("instance", "mysql");
var database = dbConstrain.GetString("database", "inventory");
var collection = dbConstrain.GetString("collection", "employees");

await g.Sys.System.ExecuteQuery(new ExecuteQuery(instance, $"CREATE TABLE {database}.{collection}(emp_id int AUTO_INCREMENT PRIMARY KEY);"));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
secret, err := g.Sys.System.GetSecret("common.dbConstrain")
if err != nil {
    return nil, err
}
dbConstrain := am.AsM(secret)
instance, database, collection := dbConstrain.String("instance"), dbConstrain.String("database"), dbConstrain.String("collection")
if instance == "" {
    instance = "mysql"
}
if database == "" {
    database = "inventory"
}
if collection == "" {
    collection = "employees"
}

if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: instance,
    Query:    "CREATE TABLE " + database + "." + collection + "(emp_id int AUTO_INCREMENT PRIMARY KEY);",
}); err != nil {
    return nil, err
}

1
2
3
4
5
6
let db_constrain = g.sys.system.get_secret("common.dbConstrain", None)?;
let instance = db_constrain["instance"].as_str().unwrap_or("mysql");
let database = db_constrain["database"].as_str().unwrap_or("inventory");
let collection = db_constrain["collection"].as_str().unwrap_or("employees");

g.sys.system.execute_query(ExecuteQuery::new(instance, format!("CREATE TABLE {database}.{collection}(emp_id int AUTO_INCREMENT PRIMARY KEY);")))?;

1
2
3
4
5
6
JsonObject dbConstrain = JsonObject.from(g.sys.system.getSecret("common.dbConstrain"));
String instance = dbConstrain.getString("instance", "mysql");
String database = dbConstrain.getString("database", "inventory");
String collection = dbConstrain.getString("collection", "employees");

g.sys.system.executeQuery(new ExecuteQuery(instance, "CREATE TABLE " + database + "." + collection + "(emp_id int AUTO_INCREMENT PRIMARY KEY);"));

1
2
3
4
5
6
val dbConstrain = JsonObject.from(g.sys.system.getSecret("common.dbConstrain"))
val instance = dbConstrain.getString("instance", "mysql")
val database = dbConstrain.getString("database", "inventory")
val collection = dbConstrain.getString("collection", "employees")

g.sys.system.executeQuery(ExecuteQuery(instance, "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;"
});

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'SELECT * FROM inventory.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'SELECT * FROM inventory.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "SELECT * FROM inventory.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "SELECT * FROM inventory.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees;"));

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

1
2
3
4
await g.sys.system.execute_query(
    'mariadb',
    'SELECT * FROM inventory.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mariadb',
    'SELECT * FROM inventory.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mariadb", "SELECT * FROM inventory.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mariadb",
    Query:    "SELECT * FROM inventory.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mariadb", "SELECT * FROM inventory.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mariadb", "SELECT * FROM inventory.employees;"));

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

1
2
3
4
await g.sys.system.execute_query(
    'sqlserver',
    'SELECT * FROM inventory.dbo.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'sqlserver',
    'SELECT * FROM inventory.dbo.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("sqlserver", "SELECT * FROM inventory.dbo.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "sqlserver",
    Query:    "SELECT * FROM inventory.dbo.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("sqlserver", "SELECT * FROM inventory.dbo.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("sqlserver", "SELECT * FROM inventory.dbo.employees;"));

1
g.sys.system.executeQuery(ExecuteQuery("sqlserver", "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,
    }
});

1
2
3
4
5
6
7
await g.sys.system.execute_query(
    'mongodb',
    {
        'dbStats': 1,
    },
    database='inventory',
)

1
2
3
4
5
6
7
$g->sys->system->executeQuery(
    'mongodb',
    [
        'dbStats' => 1,
    ],
    database: 'inventory',
);

1
2
3
4
5
6
7
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mongodb", new JsonObject
{
    ["dbStats"] = 1,
})
{
    Database = "inventory",
});

1
2
3
4
5
6
7
8
9
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mongodb",
    Database: "inventory",
    Query: am.M{
        "dbStats": 1,
    },
}); err != nil {
    return nil, err
}

1
2
3
4
5
6
g.sys.system.execute_query(
    ExecuteQuery::new("mongodb", json!({
        "dbStats": 1,
    }))
        .database("inventory"),
)?;

1
2
3
4
g.sys.system.executeQuery(new ExecuteQuery("mongodb", Map.of(
    "dbStats", 1
))
    .database("inventory"));

1
2
3
4
5
6
g.sys.system.executeQuery(
    ExecuteQuery("mongodb", mapOf(
        "dbStats" to 1,
    ))
        .database("inventory"),
)
  • 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);
        `
});

 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.execute_query(
    'postgresql',
    'drop database if exists e_commerce;',
)

await g.sys.system.execute_query(
    'postgresql',
    'create database e_commerce;',
)

await g.sys.system.execute_query(
    'postgresql',
    """
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);
        """,
    database='e_commerce',  # 👈 Need to provide db name
)

 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
$g->sys->system->executeQuery(
    'postgresql',
    'drop database if exists e_commerce;',
);

$g->sys->system->executeQuery(
    'postgresql',
    'create database e_commerce;',
);

$g->sys->system->executeQuery(
    'postgresql',
    <<<'TEXT'
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);
TEXT,
    database: 'e_commerce', // 👈 Need to provide db name
);

 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
await g.Sys.System.ExecuteQuery(new ExecuteQuery("postgresql", "drop database if exists e_commerce;"));

await g.Sys.System.ExecuteQuery(new ExecuteQuery("postgresql", "create database e_commerce;"));

await g.Sys.System.ExecuteQuery(new ExecuteQuery("postgresql", """
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);
""")
{
    Database = "e_commerce", // 👈 Need to provide db name
});

 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
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "postgresql",
    Query:    "drop database if exists e_commerce;",
}); err != nil {
    return nil, err
}

if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "postgresql",
    Query:    "create database e_commerce;",
}); err != nil {
    return nil, err
}

if _, err := g.Sys.System.ExecuteQuery(am.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);
        `,
}); err != nil {
    return nil, err
}

 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
g.sys.system.execute_query(ExecuteQuery::new("postgresql", "drop database if exists e_commerce;"))?;

g.sys.system.execute_query(ExecuteQuery::new("postgresql", "create database e_commerce;"))?;

g.sys.system.execute_query(
    ExecuteQuery::new("postgresql", r#"
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);
        "#)
        .database("e_commerce"), // 👈 Need to provide db name
)?;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
g.sys.system.executeQuery(new ExecuteQuery("postgresql", "drop database if exists e_commerce;"));

g.sys.system.executeQuery(new ExecuteQuery("postgresql", "create database e_commerce;"));

g.sys.system.executeQuery(new ExecuteQuery("postgresql", """
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);
""")
    .database("e_commerce")); // 👈 Need to provide db name

 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
g.sys.system.executeQuery(ExecuteQuery("postgresql", "drop database if exists e_commerce;"))

g.sys.system.executeQuery(ExecuteQuery("postgresql", "create database e_commerce;"))

g.sys.system.executeQuery(
    ExecuteQuery("postgresql", """
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);
        """)
        .database("e_commerce"), // 👈 Need to provide db name
)
  • Oracle

Do not add a semicolon (;) at the end of a query or a statement of Oracle.

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

1
2
3
4
await g.sys.system.execute_query(
    'oracle',
    'SELECT * FROM "INVENTORY"."employees"',
)

1
2
3
4
$g->sys->system->executeQuery(
    'oracle',
    'SELECT * FROM "INVENTORY"."employees"',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("oracle", "SELECT * FROM \"INVENTORY\".\"employees\""));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "oracle",
    Query:    "SELECT * FROM \"INVENTORY\".\"employees\"",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("oracle", "SELECT * FROM \"INVENTORY\".\"employees\""))?;

1
g.sys.system.executeQuery(new ExecuteQuery("oracle", "SELECT * FROM \"INVENTORY\".\"employees\""));

1
g.sys.system.executeQuery(ExecuteQuery("oracle", "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);"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'TRUNCATE TABLE inventory.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'TRUNCATE TABLE inventory.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "TRUNCATE TABLE inventory.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "TRUNCATE TABLE inventory.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "TRUNCATE TABLE inventory.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "TRUNCATE TABLE inventory.employees;"));

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

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'DROP TABLE IF EXISTS inventory.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'DROP TABLE IF EXISTS inventory.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "DROP TABLE IF EXISTS inventory.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "DROP TABLE IF EXISTS inventory.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "DROP TABLE IF EXISTS inventory.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "DROP TABLE IF EXISTS inventory.employees;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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');"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    "INSERT into inventory.employees Values(101, 'Bob', 'M');",
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'INSERT into inventory.employees Values(101, \'Bob\', \'M\');',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "INSERT into inventory.employees Values(101, 'Bob', 'M');"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "INSERT into inventory.employees Values(101, 'Bob', 'M');",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "INSERT into inventory.employees Values(101, 'Bob', 'M');"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "INSERT into inventory.employees Values(101, 'Bob', 'M');"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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';"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    "DELETE FROM inventory.employees WHERE emp_Id = '101';",
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'DELETE FROM inventory.employees WHERE emp_Id = \'101\';',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "DELETE FROM inventory.employees WHERE emp_Id = '101';"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "DELETE FROM inventory.employees WHERE emp_Id = '101';",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "DELETE FROM inventory.employees WHERE emp_Id = '101';"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "DELETE FROM inventory.employees WHERE emp_Id = '101';"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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';"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    "UPDATE inventory.employees SET emp_Name = 'Alice' WHERE emp_Id = '101';",
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'UPDATE inventory.employees SET emp_Name = \'Alice\' WHERE emp_Id = \'101\';',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "UPDATE inventory.employees SET emp_Name = 'Alice' WHERE emp_Id = '101';"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "UPDATE inventory.employees SET emp_Name = 'Alice' WHERE emp_Id = '101';",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "UPDATE inventory.employees SET emp_Name = 'Alice' WHERE emp_Id = '101';"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "UPDATE inventory.employees SET emp_Name = 'Alice' WHERE emp_Id = '101';"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'SELECT * FROM inventory.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'SELECT * FROM inventory.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "SELECT * FROM inventory.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "SELECT * FROM inventory.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees;"));

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

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'SELECT DISTINCT emp_Name FROM inventory.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'SELECT DISTINCT emp_Name FROM inventory.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "SELECT DISTINCT emp_Name FROM inventory.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "SELECT DISTINCT emp_Name FROM inventory.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "SELECT DISTINCT emp_Name FROM inventory.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "SELECT DISTINCT emp_Name FROM inventory.employees;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'SELECT * FROM inventory.employees order by emp_Gen,emp_Name;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'SELECT * FROM inventory.employees order by emp_Gen,emp_Name;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees order by emp_Gen,emp_Name;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "SELECT * FROM inventory.employees order by emp_Gen,emp_Name;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "SELECT * FROM inventory.employees order by emp_Gen,emp_Name;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees order by emp_Gen,emp_Name;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'SELECT AVG(emp_Sal) as Average_Salary FROM inventory.employees;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'SELECT AVG(emp_Sal) as Average_Salary FROM inventory.employees;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "SELECT AVG(emp_Sal) as Average_Salary FROM inventory.employees;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "SELECT AVG(emp_Sal) as Average_Salary FROM inventory.employees;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "SELECT AVG(emp_Sal) as Average_Salary FROM inventory.employees;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "SELECT AVG(emp_Sal) as Average_Salary FROM inventory.employees;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'SELECT * FROM inventory.employees INNER JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'SELECT * FROM inventory.employees INNER JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees INNER JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "SELECT * FROM inventory.employees INNER JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "SELECT * FROM inventory.employees INNER JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees INNER JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
})

1
2
3
4
await g.sys.system.execute_query(
    'mysql',
    'SELECT * FROM inventory.employees LEFT JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'mysql',
    'SELECT * FROM inventory.employees LEFT JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees LEFT JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mysql",
    Query:    "SELECT * FROM inventory.employees LEFT JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("mysql", "SELECT * FROM inventory.employees LEFT JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("mysql", "SELECT * FROM inventory.employees LEFT JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"));

1
g.sys.system.executeQuery(ExecuteQuery("mysql", "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;"
});

1
2
3
4
await g.sys.system.execute_query(
    'sql_server',
    'USE inventory; exec dbo.procedureName;',
)

1
2
3
4
$g->sys->system->executeQuery(
    'sql_server',
    'USE inventory; exec dbo.procedureName;',
);

1
await g.Sys.System.ExecuteQuery(new ExecuteQuery("sql_server", "USE inventory; exec dbo.procedureName;"));

1
2
3
4
5
6
if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "sql_server",
    Query:    "USE inventory; exec dbo.procedureName;",
}); err != nil {
    return nil, err
}

1
g.sys.system.execute_query(ExecuteQuery::new("sql_server", "USE inventory; exec dbo.procedureName;"))?;

1
g.sys.system.executeQuery(new ExecuteQuery("sql_server", "USE inventory; exec dbo.procedureName;"));

1
g.sys.system.executeQuery(ExecuteQuery("sql_server", "USE inventory; exec dbo.procedureName;"))

Commands of MongoDB : indexes

A command of MongoDB is an object : listIndexes, createIndexes, dropIndexes… createIndexes and getIndexes do the same on every database, in the same words.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
const listed = await g.sys.system.executeQuery({
    instance: 'mongodb', database: 'shop',
    query: { listIndexes: 'products' },
});

await g.sys.system.executeQuery({
    instance: 'mongodb', database: 'shop',
    query: {
        createIndexes: 'products',
        indexes: [{ key: { category: 1, productName: 1 }, name: 'category_productName', unique: true }],
    },
});

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
listed = await g.sys.system.execute_query(
    'mongodb',
    {'listIndexes': 'products'},
    database='shop',
)

await g.sys.system.execute_query(
    'mongodb',
    {
        'createIndexes': 'products',
        'indexes': [{'key': {'category': 1, 'productName': 1}, 'name': 'category_productName', 'unique': True}],
    },
    database='shop',
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
$listed = $g->sys->system->executeQuery(
    'mongodb',
    ['listIndexes' => 'products'],
    database: 'shop',
);

$g->sys->system->executeQuery(
    'mongodb',
    [
        'createIndexes' => 'products',
        'indexes' => [['key' => ['category' => 1, 'productName' => 1], 'name' => 'category_productName', 'unique' => true]],
    ],
    database: 'shop',
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
var listed = await g.Sys.System.ExecuteQuery(new ExecuteQuery("mongodb", new JsonObject { ["listIndexes"] = "products" })
{
    Database = "shop",
});

await g.Sys.System.ExecuteQuery(new ExecuteQuery("mongodb", new JsonObject
{
    ["createIndexes"] = "products",
    ["indexes"] = new JsonArray { new JsonObject { ["key"] = new JsonObject { ["category"] = 1, ["productName"] = 1 }, ["name"] = "category_productName", ["unique"] = true } },
})
{
    Database = "shop",
});

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
listed, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mongodb", Database: "shop",
    Query: am.M{"listIndexes": "products"},
})
if err != nil {
    return nil, err
}

if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "mongodb", Database: "shop",
    Query: am.M{
        "createIndexes": "products",
        "indexes":       []any{am.M{"key": am.M{"category": 1, "productName": 1}, "name": "category_productName", "unique": true}},
    },
}); err != nil {
    return nil, err
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
let listed = g.sys.system.execute_query(
    ExecuteQuery::new("mongodb", json!({ "listIndexes": "products" }))
        .database("shop"),
)?;

g.sys.system.execute_query(
    ExecuteQuery::new("mongodb", json!({
        "createIndexes": "products",
        "indexes": [{ "key": { "category": 1, "productName": 1 }, "name": "category_productName", "unique": true }],
    }))
        .database("shop"),
)?;

1
2
3
4
5
6
7
8
var listed = g.sys.system.executeQuery(new ExecuteQuery("mongodb", Map.of("listIndexes", "products"))
    .database("shop"));

g.sys.system.executeQuery(new ExecuteQuery("mongodb", Map.of(
    "createIndexes", "products",
    "indexes", List.of(Map.of("key", Map.of("category", 1, "productName", 1), "name", "category_productName", "unique", true))
))
    .database("shop"));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
val listed = g.sys.system.executeQuery(
    ExecuteQuery("mongodb", mapOf("listIndexes" to "products"))
        .database("shop"),
)

g.sys.system.executeQuery(
    ExecuteQuery("mongodb", mapOf(
        "createIndexes" to "products",
        "indexes" to listOf(mapOf("key" to mapOf("category" to 1, "productName" to 1), "name" to "category_productName", "unique" to true)),
    ))
        .database("shop"),
)

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# The database of acme.
orders_of_acme = await g.sys.system.execute_query(
    'crm::acme',
    'select status, count(*) as orders from orders group by status',
    database='crm',
)

# The database of the tenant of the request.
customers = await g.sys.system.execute_query(
    'crm',
    'select count(*) as customers from customers',
    database='crm',
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// The database of acme.
$ordersOfAcme = $g->sys->system->executeQuery(
    'crm::acme',
    'select status, count(*) as orders from orders group by status',
    database: 'crm',
);

// The database of the tenant of the request.
$customers = $g->sys->system->executeQuery(
    'crm',
    'select count(*) as customers from customers',
    database: 'crm',
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// The database of acme.
var ordersOfAcme = await g.Sys.System.ExecuteQuery(new ExecuteQuery("crm::acme", "select status, count(*) as orders from orders group by status")
{
    Database = "crm",
});

// The database of the tenant of the request.
var customers = await g.Sys.System.ExecuteQuery(new ExecuteQuery("crm", "select count(*) as customers from customers")
{
    Database = "crm",
});

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
// The database of acme.
ordersOfAcme, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "crm::acme",
    Database: "crm",
    Query:    "select status, count(*) as orders from orders group by status",
})
if err != nil {
    return nil, err
}

// The database of the tenant of the request.
customers, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
    Instance: "crm",
    Database: "crm",
    Query:    "select count(*) as customers from customers",
})
if err != nil {
    return nil, err
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// The database of acme.
let orders_of_acme = g.sys.system.execute_query(
    ExecuteQuery::new("crm::acme", "select status, count(*) as orders from orders group by status")
        .database("crm"),
)?;

// The database of the tenant of the request.
let customers = g.sys.system.execute_query(
    ExecuteQuery::new("crm", "select count(*) as customers from customers")
        .database("crm"),
)?;

1
2
3
4
5
6
7
// The database of acme.
var ordersOfAcme = g.sys.system.executeQuery(new ExecuteQuery("crm::acme", "select status, count(*) as orders from orders group by status")
    .database("crm"));

// The database of the tenant of the request.
var customers = g.sys.system.executeQuery(new ExecuteQuery("crm", "select count(*) as customers from customers")
    .database("crm"));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// The database of acme.
val ordersOfAcme = g.sys.system.executeQuery(
    ExecuteQuery("crm::acme", "select status, count(*) as orders from orders group by status")
        .database("crm"),
)

// The database of the tenant of the request.
val customers = g.sys.system.executeQuery(
    ExecuteQuery("crm", "select count(*) as customers from customers")
        .database("crm"),
)