# API Maker Documentation : Code examples, the code in Python

> The pages of https://docs.apimaker.dev in the order of the navigation, each with its title, its description and its URL. The code is in Python only, and the links lead to the Markdown of the pages in Python. The same with the code of another language : TypeScript https://docs.apimaker.dev/ai/typescript/llms-full.txt · PHP https://docs.apimaker.dev/ai/php/llms-full.txt · C# https://docs.apimaker.dev/ai/csharp/llms-full.txt · Go https://docs.apimaker.dev/ai/go/llms-full.txt · Rust https://docs.apimaker.dev/ai/rust/llms-full.txt · Java https://docs.apimaker.dev/ai/java/llms-full.txt · Kotlin https://docs.apimaker.dev/ai/kotlin/llms-full.txt.

---

# Code Examples

> A working example, in TypeScript, Python, PHP, C#, Go, Rust, Java and Kotlin, of every method your code can call in API Maker - g.sys.db and g.sys.db.gen for the database APIs, g.sys.system, g.sys.cache, the request and response objects, the shared space and the logger.

Source: https://docs.apimaker.dev/v4/examples/index.html

Custom APIs, hooks, events, schedulers, migrations, process initializers and test cases all receive the same [global object `g`](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md). Every page of this section is one method of it, with examples you can paste into the editor of the admin panel, in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) : pick yours in the tabs, and every page shows it from then on.

> **Tip : New to your own code ?**
>
> Start with [Your own code](https://docs.apimaker.dev/v4/docs/getting-started/first-custom-api.md) : one custom API, step by step, on the real screens.

Every piece of code is the function `main` of its language ; the examples are the inside of it :

```python
from am import G


async def main(g: G):
    # your code, with await on every call of g.sys (a coroutine ; `def main` works too, without await)
    return {'hello': 'world'}  # what the caller receives in data
```

Every language gets the same `g`, written the way the language writes things (`g.sys.db.get_all` in Python, `g.Sys.Db.GetAll` in C# and Go) : see [Languages and runtimes](https://docs.apimaker.dev/v4/docs/languages/index.python.md).

- [g.sys.db](https://docs.apimaker.dev/v4/examples/sys/db/getAll.python.md) : The 17 schema APIs of a table, from get all to distinct with query.
- [g.sys.db.gen](https://docs.apimaker.dev/v4/examples/sys/db/getAll.python.md#without-a-schema) : The same operations without the schema.
- [g.sys.system](https://docs.apimaker.dev/v4/examples/sys/system/system.md) : Encrypt, hash, tokens, secrets, queries, indexes, events, validation, external calls.
- [g.sys.cache](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-set-redis-key-api.python.md#from-code) : Redis keys and cache resets.
- [g.req](https://docs.apimaker.dev/v4/examples/req/req.python.md#body) : Body, headers, params, query params, event data and the tokens of the caller.
- [g.res](https://docs.apimaker.dev/v4/examples/res/res.python.md#output) : Output, status code, errors, warnings, content type.
- [Custom API](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md) : A complete custom API, from the settings to the code.
- [The samples of a new account](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#samples) : Login, data isolation, deep populate, find join, caching, a captcha, WebSocket, streams, S3.
- [g.logger and g.shared](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md#glogger-and-gshared) : Logs which reach the caller and the log table, and values shared between hooks.

> **Tip : The whole envelope**
>
> A `g.sys` call returns `data` and throws the errors, so the caller of your API gets them. The whole [response envelope](https://docs.apimaker.dev/v4/docs/apis-all/response-format.python.md), which never throws, comes with `true` as the second argument in TypeScript (`const res = await g.sys.db.count({ ... }, true); if (!res.success) ...`), and through `full` in the other languages (`res = await g.sys.db.full.count(...)` in Python, `g.Sys.Db.Full.Count(...)` in C# and Go).

---

# Get All

> Retrieve all records from a specified collection in your database using API Maker's `g.sys.db.getAll` method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/getAll.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.getAll` calls the [get all schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Get all data

**Get all data from table 'customers'**

```python
get_all = await g.sys.db.get_all(
    'mysql_8', 'inventory', 'customers',
)
```

## With header

**Get all with header**

```python
get_all = await g.sys.db.get_all(
    'mysql_8', 'inventory', 'customers',
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## With queryParams

**Get all with queryParams**

```python
get_all = await g.sys.db.full.get_all(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'James',
    },
)
```

## Every header and query param

**Structure of get all schema API**

```python
get_all = await g.sys.db.full.get_all(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
    find={
        'COLUMN_NAME': 'COLUMN_VALUE',
    },
    skip=1,
    limit=5,
    sort='COLUMN_NAME',
    select='COMMA_SEPARATED_COLUMN_NAMES',
    get_total_count=True,
    deep=[
        DeepItem(
            'SOURCE_COLLECTION_COLUMN_NAME',
            t_instance='TARGET_INSTANCE_NAME',
            t_db='TARGET_DATABASE_NAME',
            t_col='TARGET_COLLECTION_NAME',
            t_key='TARGET_COLLECTION_COLUMN_NAME',
            find={
                'COLUMN_NAME': 'COLUMN_VALUE',
            },
            is_multiple=True,
            limit=10,
            select='COMMA_SEPARATED_COLUMN_NAMES',
            skip=2,
            sort='COLUMN_NAME',
            deep=[
                DeepItem(
                    'SOURCE_COLLECTION_COLUMN_NAME',
                    t_instance='TARGET_INSTANCE_NAME',
                    t_db='TARGET_DATABASE_NAME',
                    t_col='TARGET_COLLECTION_NAME',
                    t_key='TARGET_COLLECTION_COLUMN_NAME',
                    find={
                        'COLUMN_NAME': 'COLUMN_VALUE',
                    },
                    is_multiple=True,
                    limit=10,
                    select='COMMA_SEPARATED_COLUMN_NAMES',
                    skip=2,
                    sort='COLUMN_NAME',
                ),
            ],
        ),
    ],
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Get all customers of the tenant acme**

```python
get_all = await g.sys.db.get_all(
    'crm::acme', 'crm', 'customers',
    sort='customer_id',
)
```

**Get all customers of the tenant globex, named with the header of the call**

```python
get_all = await g.sys.db.get_all(
    'crm', 'crm', 'customers',
    headers={
        'x-am-tenant-username': 'globex',
    },
)
```

## Without a schema

`g.sys.db.gen.getAllGen` takes the same arguments and calls the [get all generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.getAllGen(…)` |
| Python | `g.sys.db.gen.get_all_gen(…)` |
| PHP | `$g->sys->db->gen->getAllGen(…)` |
| C#, Go | `g.Sys.Db.Gen.GetAllGen(…)` |
| Rust | `g.sys.db.get_all_gen(GetAll::new(…))` |
| Java, Kotlin | `g.sys.db.gen.getAllGen(…)` |

## Related

- [Get all API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/getAll.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Get All by Stream

> Fetch all records from a collection in a streaming manner, useful for handling large datasets efficiently.

Source: https://docs.apimaker.dev/v4/examples/sys/db/getAllByStream.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.getAllByStream` calls the [get all by stream schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-by-stream-api.python.md) of any table from your code, with the same headers and query parameters as over HTTP. Each row goes to your function as the database sends it, so a large table never sits in memory at once.

## Get all using stream API

**Get all from table**

```python
response = []
await g.sys.db.get_all_by_stream(
    GetAll(
        'mysql_8', 'inventory', 'customers',
    ),
    response.append,
)
```

## With header

**Get all with header**

```python
response = []
await g.sys.db.get_all_by_stream(
    GetAll(
        'mysql_8', 'inventory', 'customers',
        headers={
            'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        },
    ),
    response.append,
)
```

## With queryParams

**Get all With queryParams**

```python
response = []
await g.sys.db.get_all_by_stream(
    GetAll(
        'mysql_8', 'inventory', 'customers',
        select='first_name,last_name',
        limit=10,
        sort='customer_id',
        find={
            'first_name': 'John',
        },
        skip=1,
        get_total_count=True,
        deep=[
            DeepItem(
                'customer_id',
                t_key='owner_id',
                t_col='products',
                select='name, description, price',
            ),
        ],
    ),
    response.append,
)
```

## Structure

**Get all by stream with all params**

```python
response = []
await g.sys.db.get_all_by_stream(
    GetAll(
        'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
        headers={
            'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
            'x-am-response-object-type': 'make_flat',  # no_action | make_flat
            'x-am-internationalization': 'USER_I18N_ID',
            'x-am-run-in-sandbox': '2',
            'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
            'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
            'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
            'x-am-sandbox-timeout': '13000',
            'x-no-compression': 'true',  # true | false
            'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
            # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
            'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
            # Authorization headers
            'x-am-authorization': 'AUTHORIZATION_TOKEN',
            'x-am-user-authorization': 'API_USER_TOKEN',
            'x-aws-authorization': 'AWS_TOKEN',
            'x-google-authorization': 'GOOGLE_TOKEN',
            'x-azure-authorization': 'AZURE_TOKEN',
        },
        find={
            'COLUMN_NAME': 'COLUMN_VALUE',
        },
        skip=1,
        limit=5,
        sort='COLUMN_NAME',
        select='COMMA_SEPARATED_COLUMN_NAMES',
        get_total_count=True,
        deep=[
            DeepItem(
                'SOURCE_COLLECTION_COLUMN_NAME',
                t_instance='TARGET_INSTANCE_NAME',
                t_db='TARGET_DATABASE_NAME',
                t_col='TARGET_COLLECTION_NAME',
                t_key='TARGET_COLLECTION_COLUMN_NAME',
                find={
                    'COLUMN_NAME': 'COLUMN_VALUE',
                },
                is_multiple=True,
                limit=10,
                select='COMMA_SEPARATED_COLUMN_NAMES',
                skip=2,
                sort='COLUMN_NAME',
                deep=[
                    DeepItem(
                        'SOURCE_COLLECTION_COLUMN_NAME',
                        t_instance='TARGET_INSTANCE_NAME',
                        t_db='TARGET_DATABASE_NAME',
                        t_col='TARGET_COLLECTION_NAME',
                        t_key='TARGET_COLLECTION_COLUMN_NAME',
                        find={
                            'COLUMN_NAME': 'COLUMN_VALUE',
                        },
                        is_multiple=True,
                        limit=10,
                        select='COMMA_SEPARATED_COLUMN_NAMES',
                        skip=2,
                        sort='COLUMN_NAME',
                    ),
                ],
            ),
        ],
    ),
    response.append,
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Get all customers of the tenant acme by stream**

```python
response = []
await g.sys.db.get_all_by_stream(
    GetAll(
        'crm::acme', 'crm', 'customers',
    ),
    response.append,
)
```

## Without a schema

`g.sys.db.gen.getAllByStreamGen` takes the same arguments and calls the [get all by stream generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-by-stream-api.python.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.getAllByStreamGen(…)` |
| Python | `g.sys.db.gen.get_all_by_stream_gen(…)` |
| PHP | `$g->sys->db->gen->getAllByStreamGen(…)` |
| C#, Go | `g.Sys.Db.Gen.GetAllByStreamGen(…)` |
| Rust | `g.sys.db.get_all_by_stream_gen(GetAll::new(…))` |
| Java, Kotlin | `g.sys.db.gen.getAllByStreamGen(…)` |

## Related

- [Get all by stream API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-by-stream-api.python.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/getAllByStream.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Get by ID

> Retrieve a single record from a collection based on its unique identifier using API Maker's g.sys.db.getById method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/getById.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.getById` calls the [get by id schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-by-id-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Get by id

**Get by id from table**

```python
get_by_id = await g.sys.db.get_by_id(
    'mysql_8', 'inventory', 'customers',
    2,
)
```

## With [primaryKey] override

**Get by id with primary key override**

```python
get_by_id = await g.sys.db.get_by_id(
    'mysql_8', 'inventory', 'customers',
    'NICK',
    primary_key='first_name',
)
```

## With header

**Get by id with header**

```python
get_by_id = await g.sys.db.get_by_id(
    'mysql_8', 'inventory', 'customers',
    2,
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## Structure

**Get by id with all params**

```python
get_by_id = await g.sys.db.get_by_id(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    100050,
    primary_key='COLUMN_NAME',
    select='COMMA_SEPARATED_COLUMN_NAMES',
    deep=[
        DeepItem(
            # string, string[], array of objects are supported
            'customer_id',
            t_key='owner_id',
            t_col='products',
            select='proName,proDescription,proPrice',
        ),
    ],
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Get the customer 1 of the tenant acme**

```python
get_by_id = await g.sys.db.get_by_id(
    'crm::acme', 'crm', 'customers',
    1,
)
```

## Without a schema

`g.sys.db.gen.getByIdGen` takes the same arguments and calls the [get by id generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-by-id-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.getByIdGen(…)` |
| Python | `g.sys.db.gen.get_by_id_gen(…)` |
| PHP | `$g->sys->db->gen->getByIdGen(…)` |
| C#, Go | `g.Sys.Db.Gen.GetByIdGen(…)` |
| Rust | `g.sys.db.get_by_id_gen(GetById::new(…))` |
| Java, Kotlin | `g.sys.db.gen.getByIdGen(…)` |

## Related

- [Get by id API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-by-id-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/getById.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Save Single/Multiple

> Insert one or more records into a collection using API Maker's g.sys.db.save method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/saveSingleOrMultiple.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.saveSingleOrMultiple` calls the [save single or multiple schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-save-single-or-multiple-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Save single or multiple

**Save single/multiple**

```python
save_single_or_multiple = await g.sys.db.save_single_or_multiple(
    'mysql_8', 'inventory', 'customers',
    {
        'first_name': 'Bob',
        'last_name': 'Lin',
    },
)
```

## Only how many rows were saved : returnData false

A save returns the rows it saved. With `returnData: false` it returns `{ savedRowsCount }` only, and the rows are not read back from the database : a query and the memory of the rows are saved. Use it for imports, and for every save whose answer you do not read.

**Save, the count only**

```python
saved = await g.sys.db.save_single_or_multiple(
    'mysql_8', 'inventory', 'customers',
    [
        {'first_name': 'Bob', 'last_name': 'Lin'},
        {'first_name': 'Alice', 'last_name': 'Ray'},
    ],
    return_data=False,
)
g.logger.log(f"{saved.saved_rows_count} rows saved")
```

## Save single or multiple with multiple data

**Save single/multiple**

```python
save_single_or_multiple = await g.sys.db.save_single_or_multiple(
    'mysql_8', 'inventory', 'customers',
    [
        {
            'first_name': 'Bob',
            'last_name': 'Lin',
        },
        {
            'first_name': 'Alice',
            'last_name': 'Page',
        },
    ],
)
```

## with header

**Save single/multiple with header**

```python
save_single_or_multiple = await g.sys.db.save_single_or_multiple(
    'mysql_8', 'inventory', 'customers',
    {
        'first_name': 'Bob',
        'last_name': 'Lin',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## Structure

**Save single/multiple structure**

```python
save_single_or_multiple = await g.sys.db.save_single_or_multiple(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    {
        'COLUMN_NAME': 'VALUE',
        'COLUMN_NAME2': 'VALUE',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Save a customer of the tenant acme**

```python
save_single_or_multiple = await g.sys.db.save_single_or_multiple(
    'crm::acme', 'crm', 'customers',
    {
        'first_name': 'Bob',
        'last_name': 'Lin',
    },
)
```

## Save vectors, JSON and bytes

A vector is a list of numbers, a JSON column takes any object or list, and bytes go as a `Buffer` object : a plain map in every language. They are read back in the same forms. [Data types](https://docs.apimaker.dev/v4/docs/schema/data-types.md) lists every type of every database.

**Save a vector, JSON and bytes**

```python
saved = await g.sys.db.save_single_or_multiple(
    'mysql_8', 'inventory', 'products',
    {
        'name': 'Mug',
        'embedding': [0.12, 0.5, 0.33],
        'details': {'color': 'blue', 'sizes': [250, 350]},
        'photo': {'type': 'Buffer', 'data': [137, 80, 78, 71]},
    },
)
```

## Without a schema

`g.sys.db.gen.saveSingleOrMultipleGen` takes the same arguments and calls the [save single or multiple generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-save-single-or-multiple-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.saveSingleOrMultipleGen(…)` |
| Python | `g.sys.db.gen.save_single_or_multiple_gen(…)` |
| PHP | `$g->sys->db->gen->saveSingleOrMultipleGen(…)` |
| C#, Go | `g.Sys.Db.Gen.SaveSingleOrMultipleGen(…)` |
| Rust | `g.sys.db.save_single_or_multiple_gen(Save::new(…))` |
| Java, Kotlin | `g.sys.db.gen.saveSingleOrMultipleGen(…)` |

## Related

- [Save single or multiple API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-save-single-or-multiple-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/saveSingleOrMultiple.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Master Save/Update

> Perform save or update operations on single or multiple records with master-level control using API Maker.

Source: https://docs.apimaker.dev/v4/examples/sys/db/masterSave.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.masterSave` calls the [master save schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-master-save-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## master save single data

**Master save**

```python
master_save = await g.sys.db.master_save(
    'mysql_8', 'inventory', 'customers',
    {
        'first_name': 'Bob',
        'last_name': 'Lin',
    },
)
```

## Only how many rows were saved : returnData false

A master save returns the rows it saved. With `returnData: false` it returns `{ savedRowsCount }` only : how many rows of the call were saved.

**Master save, the count only**

```python
saved = await g.sys.db.master_save(
    'mysql_8', 'inventory', 'customers',
    [
        {'first_name': 'Bob', 'last_name': 'Lin'},
        {'first_name': 'Alice', 'last_name': 'Ray'},
    ],
    return_data=False,
)
g.logger.log(f"{saved.saved_rows_count} rows saved")
```

## master save multiple data

**Master save**

```python
master_save = await g.sys.db.master_save(
    'mysql_8', 'inventory', 'customers',
    [
        {
            'first_name': 'Bob',
            'last_name': 'Lin',
        },
        {
            'first_name': 'Bob',
            'last_name': 'Lin',
        },
    ],
)
```

## with header

**Master save with header**

```python
master_save = await g.sys.db.master_save(
    'mysql_8', 'inventory', 'customers',
    {
        'first_name': 'Bob',
        'last_name': 'Lin',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## multiple database entry

**Master save with multy data[with help of schema configuration]**

```python
master_save = await g.sys.db.master_save(
    'mysql_8', 'inventory', 'customers',
    {
        'city_name': 'Wembley',  # city_name save in mysql database
        'state_id': {
            'state_name': 'London',  # state_name save in maria database
            'country_id': {
                'country_name': 'UK',  # country_name save in postGreSql database
            },
        },
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## multiple database multiple entry

**Master save with multy data[with help of schema configuration]**

```python
master_save = await g.sys.db.master_save(
    'mysql_8', 'inventory', 'customers',
    [
        {
            'city_name': 'Wembley',
            'state_id': {
                'state_name': 'London',
                'country_id': 302,
            },
        },
        {
            'city_name': 'Buffalo',
            'state_id': {
                'state_name': 'Chicago',
                'country_id': 303,
            },
        },
    ],
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## Structure

**Master save**

```python
master_save = await g.sys.db.master_save(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    {
        'COLUMN_NAME': 'VALUE',
        'COLUMN_NAME2': 'VALUE',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Master save a customer of the tenant acme**

```python
master_save = await g.sys.db.master_save(
    'crm::acme', 'crm', 'customers',
    {
        'first_name': 'Bob',
        'last_name': 'Lin',
    },
)
```

## Without a schema

`g.sys.db.gen.masterSaveGen` takes the same arguments and calls the [master save generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-master-save-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.masterSaveGen(…)` |
| Python | `g.sys.db.gen.master_save_gen(…)` |
| PHP | `$g->sys->db->gen->masterSaveGen(…)` |
| C#, Go | `g.Sys.Db.Gen.MasterSaveGen(…)` |
| Rust | `g.sys.db.master_save_gen(Save::new(…))` |
| Java, Kotlin | `g.sys.db.gen.masterSaveGen(…)` |

## Related

- [Master save API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-master-save-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/masterSave.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Array Operations

> Manipulate array fields within documents using operations like push, pull, and pop in API Maker.

Source: https://docs.apimaker.dev/v4/examples/sys/db/arrayOperations.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.arrayOperations` calls the [array operations schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-array-operations-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Array operation [push]

**Array operation push**

```python
await g.sys.db.array_operations(
    'mongodb', 'inventory', 'customers',
    find={
        'customer_id': 1,
    },
    operations=[
        ArrayOperation(
            'push',
            'attributes.color',
            data_to_push=[
                'YELLOW',
                'SKY_BLUE',
            ],
        ),
    ],
)
```

## Array operation [pull]

**Array operation pull**

```python
await g.sys.db.array_operations(
    'mongodb', 'inventory', 'customers',
    find={
        'customer_id': 1,
    },
    operations=[
        ArrayOperation(
            'pull',
            'ages',
            query_to_remove={
                'age': 2,
                'birth_year': 1989,
                'country_id': 1,
            },
        ),
    ],
)
```

## Array operation [pullAll]

**Array operation pullAll**

```python
await g.sys.db.array_operations(
    'mongodb', 'inventory', 'customers',
    find={
        'customer_id': 1,
    },
    operations=[
        ArrayOperation(
            'pullAll',
            'attributes.size',
            data_to_pull=[
                3.5,
            ],
        ),
    ],
)
```

## Array operation [pop]

**Array operation pop**

```python
await g.sys.db.array_operations(
    'mongodb', 'inventory', 'customers',
    find={
        'customer_id': 1,
    },
    operations=[
        ArrayOperation(
            'pop',
            'attributes.color',
            direction=1,
        ),
    ],
)
```

## Array operation [set]

**Array operation set**

```python
await g.sys.db.array_operations(
    'mongodb', 'inventory', 'customers',
    find={
        'customer_id': 1,
    },
    operations=[
        ArrayOperation(
            'set',
            data_to_set={
                'ages.birth_year': 1890,
            },
            array_filters=[
                {
                    'item.age': 4,
                },
            ],
        ),
    ],
)
```

## Array operation [addToSet]

**Array operation addToSet**

```python
await g.sys.db.array_operations(
    'mongodb', 'inventory', 'customers',
    find={
        'customer_id': 1,
    },
    operations=[
        ArrayOperation(
            'addToSet',
            data_to_set={
                'ages.birth_year': 1890,
            },
            array_filters=[
                {
                    'item.age': 4,
                },
            ],
        ),
    ],
)
```

## Structure

**Array operations**

```python
await g.sys.db.array_operations(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    find={
        'customer_id': 1,
    },
    operations=[
        ArrayOperation(
            'push',  # push | addToSet | pull | pullAll | pop | set
            'attributes.color',
            data_to_push=[
                'YELLOW',
                'SKY_BLUE',
            ],
        ),
        ArrayOperation(
            'pull',  # push | addToSet | pull | pullAll | pop | set
            'attributes.color',
            data_to_pull=[
                'SKY_BLUE',
            ],
        ),
    ],
    select='COMMA_SEPARATED_COLUMN_NAMES',
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Array operations on a customer of the tenant acme**

```python
array_operations = await g.sys.db.array_operations(
    'crm::acme', 'crm', 'customers',
    find={
        'customer_id': 1,
    },
    operations=[
        ArrayOperation(
            'push',
            'tags',
            data_to_push=['vip'],
        ),
    ],
)
```

## Without a schema

`g.sys.db.gen.arrayOperationsGen` takes the same arguments and calls the [array operations generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-array-operations-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.arrayOperationsGen(…)` |
| Python | `g.sys.db.gen.array_operations_gen(…)` |
| PHP | `$g->sys->db->gen->arrayOperationsGen(…)` |
| C#, Go | `g.Sys.Db.Gen.ArrayOperationsGen(…)` |
| Rust | `g.sys.db.array_operations_gen(ArrayOperations::new(…))` |
| Java, Kotlin | `g.sys.db.gen.arrayOperationsGen(…)` |

## Related

- [Array operations API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-array-operations-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/arrayOperations.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Update by ID

> Update a single record in a collection based on its unique identifier using API Maker's g.sys.db.updateById method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/updateById.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.updateById` calls the [update by id schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-update-by-id-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Update by id

**Update By Id**

```python
update_by_id = await g.sys.db.update_by_id(
    'mysql_8', 'inventory', 'customers',
    1,
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## Update by id primaryKey override

**Update By Id primarykey override**

```python
update_by_id = await g.sys.db.update_by_id(
    'mysql_8', 'inventory', 'customers',
    'Alice',
    primary_key='first_name',
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## Update by id [upsert]

**Update By Id with upsert**

```python
update_by_id = await g.sys.db.update_by_id(
    'mysql_8', 'inventory', 'customers',
    '202',
    update_data={
        'first_name': 'MALLORY',
        'last_name': 'ANI',
    },
    upsert=True,
)
```

## Update by id [returnDocument]  

**Update By Id with returnDocument**

```python
update_by_id = await g.sys.db.update_by_id(
    'mysql_8', 'inventory', 'customers',
    '202',
    update_data={
        'first_name': 'MALLORY',
    },
    return_document='before',  # return old doc if value 'before'. Supported values are 'before' | 'after'.
)
```

## Structure

**Update by id with all params**

```python
update_by_id = await g.sys.db.update_by_id(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    1008,
    primary_key='id',
    update_data={
        'first_name': 'Tanmay',
    },
    return_document='before',  # return old doc if value 'before'. Supported values are 'before' | 'after'.
    select='COLUMN_NAMES',
    upsert=False,
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Update the customer 1 of the tenant acme**

```python
update_by_id = await g.sys.db.update_by_id(
    'crm::acme', 'crm', 'customers',
    1,
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## Without a schema

`g.sys.db.gen.updateByIdGen` takes the same arguments and calls the [update by id generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-update-by-id-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.updateByIdGen(…)` |
| Python | `g.sys.db.gen.update_by_id_gen(…)` |
| PHP | `$g->sys->db->gen->updateByIdGen(…)` |
| C#, Go | `g.Sys.Db.Gen.UpdateByIdGen(…)` |
| Rust | `g.sys.db.update_by_id_gen(UpdateById::new(…))` |
| Java, Kotlin | `g.sys.db.gen.updateByIdGen(…)` |

## Related

- [Update by id API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-update-by-id-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/updateById.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Update Many

> Update multiple records in a collection based on specified criteria using API Maker's g.sys.db.updateMany method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/updateMany.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.updateMany` calls the [update many schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-update-many-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Simple update-many

**Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Alice',
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## The changed rows : returnData true

`updateMany` returns `{ updatedRowsCount }` : how many rows changed. With `returnData: true` it returns `updatedRows` too : the rows `find` matched, as they are after the update, also when the update changed the columns they were found by. They are read after the update : ask for them only when you use them.

**Update many, with the rows**

```python
updated = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    update_data={
        'last_name': 'Lin',
    },
    return_data=True,
)
g.logger.log(f"{updated.updated_rows_count} rows changed : {len(updated.updated_rows)} read")
```

## update-many with header

**Update many with header**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Alice',
    },
    update_data={
        'first_name': 'MALLORY',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## $in update-many

**$in Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$in': [2, 3],
        },
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## $nin update-many

**$nin Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$nin': [2, 3],
        },
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## $and update-many

**$and Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        '$and': [
            {
                'first_name': 'Alice',
            },
            {
                'isActive': 1,
            },
        ],
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## $or update-many

**$or Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        '$or': [
            {
                'first_name': 'Alice',
            },
            {
                'isActive': 1,
            },
        ],
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## $eq update-many

**$eq Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$eq': 2,
        },
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## $gt update-many

**$gt Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gt': 2,
        },
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## $gte update-many

**$gte Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gte': 2,
        },
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## $lt update-many

**$lt Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$lt': 2,
        },
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## $lte update-many

**$lte Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$lte': 2,
        },
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## [mongoDB] update-many

**$mongoDB Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': 2,
        'ages.birth_year': '1889',
    },
    update_data={
        'ages.$.age': '35',
    },
)
```

## [MongoDB] $elemMatch, $gte, $lte update-many

**$elemMatch, $gte, $lte Update many**

```python
update_many = await g.sys.db.update_many(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': 2,
        'ages': {
            '$elemMatch': {
                'age': {
                    '$gte': 30,
                },
                'birth_year': {
                    '$lte': 1900,
                },
            },
        },
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## Structure

**Update many**

```python
update_many = await g.sys.db.update_many(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    find={
        'COLUMN_NAME': 'VALUE',
    },
    update_data={
        'COLUMN_NAME': 'VALUE',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Update many customers of the tenant acme**

```python
update_many = await g.sys.db.update_many(
    'crm::acme', 'crm', 'customers',
    find={
        'first_name': 'Alice',
    },
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## Without a schema

`g.sys.db.gen.updateManyGen` takes the same arguments and calls the [update many generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-update-many-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.updateManyGen(…)` |
| Python | `g.sys.db.gen.update_many_gen(…)` |
| PHP | `$g->sys->db->gen->updateManyGen(…)` |
| C#, Go | `g.Sys.Db.Gen.UpdateManyGen(…)` |
| Rust | `g.sys.db.update_many_gen(UpdateMany::new(…))` |
| Java, Kotlin | `g.sys.db.gen.updateManyGen(…)` |

## Related

- [Update many API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-update-many-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/updateMany.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Replace by ID

> Replace a record in a collection with a new document based on its unique identifier using API Maker's g.sys.db.replaceById method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/replaceById.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.replaceById` calls the [replace by id schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-replace-by-id-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Replace By Id

**Replace By Id**

```python
replace_by_id = await g.sys.db.replace_by_id(
    'mysql_8', 'inventory', 'customers',
    1,
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## Replace By Id [primaryKey] override

**Replace By Id primary key override**

```python
replace_by_id = await g.sys.db.replace_by_id(
    'mysql_8', 'inventory', 'customers',
    'Bob',
    primary_key='first_name',
    update_data={
        'first_name': 'MALLORY',
    },
)
```

## Replace By Id [header]

**Replace By Id header**

```python
replace_by_id = await g.sys.db.replace_by_id(
    'mysql_8', 'inventory', 'customers',
    606,
    update_data={
        'first_name': 'MALLORY',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## Replace By Id [upsert]

**Replace By Id upsert**

```python
replace_by_id = await g.sys.db.replace_by_id(
    'mysql_8', 'inventory', 'customers',
    505,
    update_data={
        'first_name': 'MALLORY',
    },
    upsert=True,
)
```

## Replace By Id [returnDocument]

**Replace By Id returnDocument**

```python
replace_by_id = await g.sys.db.replace_by_id(
    'mysql_8', 'inventory', 'customers',
    80,
    update_data={
        'first_name': 'MALLORY',
    },
    return_document='before',  # return old doc if value 'before'. Supported values are 'before' | 'after'.
)
```

## Structure

**Replace by id with all params**

```python
replace_by_id = await g.sys.db.replace_by_id(
    'mysql_8', 'inventory', 'customers',
    45855,
    primary_key='pincode',
    update_data={
        'first_name': 'Elonn',
        'last_name': 'Mask',
    },
    return_document='before',  # return old doc if value 'before'. Supported values are 'before' | 'after'.
    select='COMMA_SEPARATED_COLUMN_NAMES',
    upsert=False,
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Replace the customer 1 of the tenant acme**

```python
replace_by_id = await g.sys.db.replace_by_id(
    'crm::acme', 'crm', 'customers',
    1,
    update_data={
        'first_name': 'MALLORY',
        'last_name': 'Lin',
    },
)
```

## Without a schema

`g.sys.db.gen.replaceByIdGen` takes the same arguments and calls the [replace by id generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-replace-by-id-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.replaceByIdGen(…)` |
| Python | `g.sys.db.gen.replace_by_id_gen(…)` |
| PHP | `$g->sys->db->gen->replaceByIdGen(…)` |
| C#, Go | `g.Sys.Db.Gen.ReplaceByIdGen(…)` |
| Rust | `g.sys.db.replace_by_id_gen(UpdateById::new(…))` |
| Java, Kotlin | `g.sys.db.gen.replaceByIdGen(…)` |

## Related

- [Replace by id API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-replace-by-id-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/replaceById.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Remove by ID

> Delete a single record from a collection based on its unique identifier using API Maker's g.sys.db.removeById method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/removeById.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.removeById` calls the [remove by id schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-remove-by-id-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Remove By Id

**Remove By Id**

```python
remove_by_id = await g.sys.db.remove_by_id(
    'mysql_8', 'inventory', 'customers',
    101,
)
```

## Remove By Id [primaryKey] override

**Remove By Id primary key override**

```python
remove_by_id = await g.sys.db.remove_by_id(
    'mysql_8', 'inventory', 'customers',
    'Bob',
    primary_key='first_name',
)
```

## removeById [header]

**Remove By Id header**

```python
remove_by_id = await g.sys.db.remove_by_id(
    'mysql_8', 'inventory', 'customers',
    'Bob',
    primary_key='first_name',
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## Structure

**Remove by id with all params**

```python
remove_by_id = await g.sys.db.remove_by_id(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    7878,
    primary_key='COLUMN_NAME',
    select='COMMA_SEPARATED_COLUMN_NAMES',
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Remove the customer 101 of the tenant acme**

```python
remove_by_id = await g.sys.db.remove_by_id(
    'crm::acme', 'crm', 'customers',
    101,
)
```

## Without a schema

`g.sys.db.gen.removeByIdGen` takes the same arguments and calls the [remove by id generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-remove-by-id-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.removeByIdGen(…)` |
| Python | `g.sys.db.gen.remove_by_id_gen(…)` |
| PHP | `$g->sys->db->gen->removeByIdGen(…)` |
| C#, Go | `g.Sys.Db.Gen.RemoveByIdGen(…)` |
| Rust | `g.sys.db.remove_by_id_gen(RemoveById::new(…))` |
| Java, Kotlin | `g.sys.db.gen.removeByIdGen(…)` |

## Related

- [Remove by id API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-remove-by-id-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/removeById.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Query for Get Data

> Perform complex queries to retrieve data from a collection with filtering, sorting, and pagination using API Maker.

Source: https://docs.apimaker.dev/v4/examples/sys/db/query.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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`.

## Simple query

**Simple query to get data**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
)
```

## query [headers]

**query headers**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## query [select]

**query select**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    select='first_name',
)
```

## query [limit]

**query limit**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    limit=2,
)
```

## query [skip]

**query skip**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    skip=1,
)
```

## query [sort]

**query sort**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    sort='first_name',
)
```

## query [deep]

**query deep**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    deep=[
        DeepItem(
            'customer_id',
            t_instance='mysql',
            t_db='inventory',
            t_col='products',
            t_key='owner_id',
        ),
    ],
)
```

## $lt [less than] query

**less than query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$lt': 2,
        },
    },
)
```

## $lte [less than equal] query

**less than equal query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$lte': 2,
        },
    },
)
```

## $gt [greater than] query

**greater than query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gt': 2,
        },
    },
)
```

## $gte [greater than equal] query

**greater than equal query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gte': 2,
        },
    },
)
```

## $eq [equal to] query

**$eq query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$eq': 2,
        },
    },
)
```

## $ne [not equal to] query

**$ne query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$ne': 2,
        },
    },
)
```

## $not [not] query

**$not query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$not': {
                '$in': [1, 2, 3, 4],
            },
        },
    },
)
```

## $and [and] query

**$and query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$and': [
            {
                'customer_id': 2,
            },
            {
                'pincode': 7171,
            },
        ],
    },
)
```

Find-join example

**find join $and query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$and': [
            {
                'first_name.customer_id.shipping_id': 2,
            },
            {
                'first_name.customer_id.order_id': 58,
            },
        ],
    },
)
```

## $or [or] query

**$or query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$or': [
            {
                'customer_id': 2,
            },
            {
                'pincode': 7171,
            },
        ],
    },
)
```

Find-join example

**find join $or query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$or': [
            {
                'first_name.customer_id.shipping_id': 2,
            },
            {
                'first_name.customer_id.order_id': 58,
            },
        ],
    },
)
```

## $in [in] query

**$in query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$in': [1, 2],
        },
    },
)
```

## $nin [not in] query

**$nin query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$nin': [1, 2],
        },
    },
)
```

## $like [like] query

**$like query**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': {
            '$like': 'Bob%',
        },
    },
)
```

## $isNull [null check] with sql databases

**$isNull use**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': {
            '$isNull': True,
        },
    },
)
```

## MongoDB $in operator in nested find

**$in operator in nested find**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'eventListeners.versions.name': {
            '$in': [
                'Original',
                '1',
            ],
        },
    },
)
```

## MongoDB $nin operator in nested find

**$nin operator in nested find**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'eventListeners.versions.name': {
            '$nin': [
                'Original',
                '1',
            ],
        },
    },
)
```

## MongoDB $and operator in nested find

**$and operator in nested find**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$and': [
            {
                'eventListeners.versions.name': 'Original',
            },
            {
                'eventListeners.versions.version': '-1',
            },
        ],
    },
)
```

## MongoDB $or operator in nested find

**$or operator in nested find**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$or': [
            {
                'eventListeners.versions.name': 'Original',
            },
            {
                'eventListeners.versions.version': '-1',
            },
        ],
    },
)
```

## Structure

**query with all params **

```python
query = await g.sys.db.full.query(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    find={
        'COLUMN_NAME': 'VALUE',
    },
    limit=10,
    select='COMMA_SEPARATED_COLUMN_NAMES',
    deep=[
        DeepItem(
            'SOURCE_COLLECTION_COLUMN_NAME',
            t_instance='TARGET_INSTANCE_NAME',
            t_db='TARGET_DATABASE_NAME',
            t_col='TARGET_COLLECTION_NAME',
            t_key='TARGET_COLLECTION_COLUMN_NAME',
            find={
                'COLUMN_NAME': 'COLUMN_VALUE',
            },
            is_multiple=True,
            limit=10,
            select='COMMA_SEPARATED_COLUMN_NAMES',
            skip=2,
            sort='COLUMN_NAME',
        ),
    ],
    skip=1,
    sort='COLUMN_NAME',
    group_by='COLUMN_NAME',
    get_total_count=True,
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Query the customers of the tenant acme**

```python
query = await g.sys.db.query(
    'crm::acme', 'crm', 'customers',
    find={
        'first_name': 'Bob',
    },
    sort='customer_id',
    limit=10,
)
```

## Without a schema

`g.sys.db.gen.queryGen` takes the same arguments and calls the [query for get data generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-query-for-get-data-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.queryGen(…)` |
| Python | `g.sys.db.gen.query_gen(…)` |
| PHP | `$g->sys->db->gen->queryGen(…)` |
| C#, Go | `g.Sys.Db.Gen.QueryGen(…)` |
| Rust | `g.sys.db.query_gen(Query::new(…))` |
| Java, Kotlin | `g.sys.db.gen.queryGen(…)` |

---

# Query for Get Data by Stream

> Fetch data from a collection in a streaming manner with complex queries for efficient data retrieval.

Source: https://docs.apimaker.dev/v4/examples/sys/db/queryByStream.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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`.

## Simple query by stream

**Simple query by stream to get data**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'first_name': 'Bob',
        },
    ),
    response.append,
)
```

## query by stream [headers]

**query by stream headers**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'first_name': 'Bob',
        },
        headers={
            'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        },
    ),
    response.append,
)
```

## query by stream [select]

**query by stream select**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'first_name': 'Bob',
        },
        select='first_name',
    ),
    response.append,
)
```

## query by stream [limit]

**query by stream limit**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'first_name': 'Bob',
        },
        limit=2,
    ),
    response.append,
)
```

## query by stream[skip]

**query by stream skip**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'first_name': 'Bob',
        },
        skip=1,
    ),
    response.append,
)
```

## query by stream [sort]

**query by stream sort**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'first_name': 'Bob',
        },
        sort='first_name',
    ),
    response.append,
)
```

## query by stream [deep]

**query by stream deep**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'first_name': 'Bob',
        },
        deep=[
            DeepItem(
                'customer_id',
                t_instance='mysql',
                t_db='inventory',
                t_col='products',
                t_key='owner_id',
            ),
        ],
    ),
    response.append,
)
```

## $lt [less than] query

**less than query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$lt': 2,
            },
        },
    ),
    response.append,
)
```

## $lte [less than equal] query

**less than equal query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$lte': 2,
            },
        },
    ),
    response.append,
)
```

## $gt [greater than] query

**greater than query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$gt': 2,
            },
        },
    ),
    response.append,
)
```

## $gte [greater than equal] query

**greater than equal query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$gte': 2,
            },
        },
    ),
    response.append,
)
```

## $eq [equal to] query

**$eq query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$eq': 2,
            },
        },
    ),
    response.append,
)
```

## $ne [not equal to] query

**$ne query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$ne': 2,
            },
        },
    ),
    response.append,
)
```

## $not [not] query

**$not query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$not': {
                    '$in': [1, 2, 3, 4],
                },
            },
        },
    ),
    response.append,
)
```

## $and [and] query

**$and query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            '$and': [
                {
                    'customer_id': 2,
                },
                {
                    'zipcode': 7171,
                },
            ],
        },
    ),
    response.append,
)
```

## $or [or] query

**$or query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            '$or': [
                {
                    'customer_id': 2,
                },
                {
                    'zipcode': 7171,
                },
            ],
        },
    ),
    response.append,
)
```

## $in [in] query

**$in query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$in': [1, 2],
            },
        },
    ),
    response.append,
)
```

## $nin [not in] query

**$nin query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'customer_id': {
                '$nin': [1, 2],
            },
        },
    ),
    response.append,
)
```

## $like [like] query

**$like query**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'first_name': {
                '$like': 'Bob%',
            },
        },
    ),
    response.append,
)
```

## $isNull [null check] with sql databases

**$isNull use**

```python
query = await g.sys.db.query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': {
            '$isNull': True,
        },
    },
)
```

## MongoDB $in operator in nested find

**$in operator in nested find**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'eventListeners.versions.name': {
                '$in': [
                    'Original',
                    '1',
                ],
            },
        },
    ),
    response.append,
)
```

## MongoDB $nin operator in nested find

**$nin operator in nested find**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            'eventListeners.versions.name': {
                '$nin': [
                    'Original',
                    '1',
                ],
            },
        },
    ),
    response.append,
)
```

## MongoDB $and operator in nested find

**$and operator in nested find**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            '$and': [
                {
                    'eventListeners.versions.name': 'Original',
                },
                {
                    'eventListeners.versions.version': '-1',
                },
            ],
        },
    ),
    response.append,
)
```

## MongoDB $or operator in nested find

**$or operator in nested find**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'mysql_8', 'inventory', 'customers',
        find={
            '$or': [
                {
                    'eventListeners.versions.name': 'Original',
                },
                {
                    'eventListeners.versions.version': '-1',
                },
            ],
        },
    ),
    response.append,
)
```

## Structure

**query by stream with all params **

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
        find={
            'COLUMN_NAME': 'VALUE',
        },
        limit=10,
        select='COMMA_SEPARATED_COLUMN_NAMES',
        deep=[
            DeepItem(
                'SOURCE_COLLECTION_COLUMN_NAME',
                t_instance='TARGET_INSTANCE_NAME',
                t_db='TARGET_DATABASE_NAME',
                t_col='TARGET_COLLECTION_NAME',
                t_key='TARGET_COLLECTION_COLUMN_NAME',
                find={
                    'COLUMN_NAME': 'COLUMN_VALUE',
                },
                is_multiple=True,
                limit=10,
                select='COMMA_SEPARATED_COLUMN_NAMES',
                skip=2,
                sort='COLUMN_NAME',
            ),
        ],
        skip=1,
        sort='COLUMN_NAME',
        group_by='COLUMN_NAME',
        headers={
            'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
            'x-am-response-object-type': 'make_flat',  # no_action | make_flat
            'x-am-internationalization': 'USER_I18N_ID',
            'x-am-run-in-sandbox': '2',
            'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
            'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
            'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
            'x-am-sandbox-timeout': '13000',
            'x-no-compression': 'true',  # true | false
            'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
            # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
            'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
            # Authorization headers
            'x-am-authorization': 'AUTHORIZATION_TOKEN',
            'x-am-user-authorization': 'API_USER_TOKEN',
            'x-aws-authorization': 'AWS_TOKEN',
            'x-google-authorization': 'GOOGLE_TOKEN',
            'x-azure-authorization': 'AZURE_TOKEN',
        },
    ),
    response.append,
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Query the customers of the tenant acme by stream**

```python
response = []
await g.sys.db.query_by_stream(
    Query(
        'crm::acme', 'crm', 'customers',
        find={
            'first_name': 'Bob',
        },
    ),
    response.append,
)
```

## Without a schema

`g.sys.db.gen.queryByStreamGen` takes the same arguments and calls the [query for get data by stream generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-query-for-get-data-by-stream-api.python.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.queryByStreamGen(…)` |
| Python | `g.sys.db.gen.query_by_stream_gen(…)` |
| PHP | `$g->sys->db->gen->queryByStreamGen(…)` |
| C#, Go | `g.Sys.Db.Gen.QueryByStreamGen(…)` |
| Rust | `g.sys.db.query_by_stream_gen(Query::new(…))` |
| Java, Kotlin | `g.sys.db.gen.queryByStreamGen(…)` |

---

# Remove by Query

> Delete records from a collection based on specified criteria using API Maker's g.sys.db.removeByQuery method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/removeByQuery.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.removeByQuery` calls the [remove by query schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-remove-by-query-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Simple removeByQuery

**Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
)
```

## The removed rows : returnData true

`removeByQuery` returns `{ deletedRowsCount }` : how many rows went. The rows are not read. With `returnData: true` they are read before they go, and it returns them too, with their keys : `{ deletedRows, deletedRowsCount, ids }`. Ask for them only when you use them : a removal of many rows reads every one of them, in the memory of the process which runs your code.

**Remove by query, with the rows**

```python
removed = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    return_data=True,
)
g.logger.log(f"{removed.deleted_rows_count} rows removed : {len(removed.deleted_rows)} read")
```

## removeByQuery with headers

**Remove by query with headers**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## $lt removeByQuery

**$lt Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$lt': 2,
        },
    },
)
```

## $lte removeByQuery

**$lte Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$lte': 2,
        },
    },
)
```

## $gt removeByQuery

**$gt Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gt': 2,
        },
    },
)
```

## $gte removeByQuery

**$gte Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gte': 2,
        },
    },
)
```

## $eq removeByQuery

**$eq Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$eq': 2,
        },
    },
)
```

## $ne removeByQuery

**$ne Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$ne': 2,
        },
    },
)
```

## $not removeByQuery

**$not Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$not': {
                '$in': [1, 2, 3, 4],
            },
        },
    },
)
```

## $and removeByQuery

**$and Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$and': [
            {
                'customer_id': 2,
            },
            {
                'pincode': 7171,
            },
        ],
    },
)
```

## $or removeByQuery

**$or Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$or': [
            {
                'customer_id': 2,
            },
            {
                'pincode': 7171,
            },
        ],
    },
)
```

## $in removeByQuery

**$in Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$in': [1, 2],
        },
    },
)
```

## $nin removeByQuery

**$nin Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$nin': [1, 2],
        },
    },
)
```

## $like removeByQuery

**$nin Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': {
            '$like': 'Bob%',
        },
    },
)
```

## MongoDB $in operator in nested find

**$in operator in nested find**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'eventListeners.versions.name': {
            '$in': [
                'Original',
                '1',
            ],
        },
    },
)
```

## MongoDB $nin operator in nested find

**$nin operator in nested find**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        'eventListeners.versions.name': {
            '$nin': [
                'Original',
                '1',
            ],
        },
    },
)
```

## MongoDB $and operator in nested find

**$and operator in nested find**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$and': [
            {
                'eventListeners.versions.name': 'Original',
            },
            {
                'eventListeners.versions.version': '-1',
            },
        ],
    },
)
```

## MongoDB $or operator in nested find

**$or operator in nested find**

```python
update_by_id = await g.sys.db.remove_by_query(
    'mysql_8', 'inventory', 'customers',
    find={
        '$or': [
            {
                'eventListeners.versions.name': 'Original',
            },
            {
                'eventListeners.versions.version': '-1',
            },
        ],
    },
)
```

## Structure

**Remove by query**

```python
update_by_id = await g.sys.db.remove_by_query(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    find={
        'COLUMN_NAME': 'VALUE',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Remove customers of the tenant acme by query**

```python
remove_by_query = await g.sys.db.remove_by_query(
    'crm::acme', 'crm', 'customers',
    find={
        'first_name': 'Bob',
    },
)
```

## Without a schema

`g.sys.db.gen.removeByQueryGen` takes the same arguments and calls the [remove by query generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-remove-by-query-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.removeByQueryGen(…)` |
| Python | `g.sys.db.gen.remove_by_query_gen(…)` |
| PHP | `$g->sys->db->gen->removeByQueryGen(…)` |
| C#, Go | `g.Sys.Db.Gen.RemoveByQueryGen(…)` |
| Rust | `g.sys.db.remove_by_query_gen(RemoveByQuery::new(…))` |
| Java, Kotlin | `g.sys.db.gen.removeByQueryGen(…)` |

## Related

- [Remove by query API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-remove-by-query-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/removeByQuery.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Aggregate

> Perform aggregation operations like count, sum, average, etc., on data within a collection using API Maker.

Source: https://docs.apimaker.dev/v4/examples/sys/db/aggregate.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.aggregate` calls the [aggregate schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-aggregate-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

Aggregation runs on MongoDB. `aggregateQuery` is a pipeline of MongoDB : every stage and operator of the [MongoDB manual](https://www.mongodb.com/docs/manual/reference/operator/aggregation-pipeline/) works, so this page shows three common shapes.

## Group, filter and count : $group, $match

**MongoDB Basic aggregation**

```python
aggregate = await g.sys.db.aggregate(
    'mongodb', 'inventory', 'customers',
    aggregate_query=[
        {
            '$group': {
                '_id': '$_id',
                'total': {
                    '$sum': '$customer_id',
                },
            },
        },
        {
            '$match': {
                'total': {
                    '$gt': 1,
                },
            },
        },
        {
            '$sort': {
                'total': -1,
            },
        },
        {
            '$count': 'CountValue',
        },
    ],
)
```

## Several groupings at once : $facet and $bucket

**MongoDB $facet with multiple $bucket**

```python
aggregate = await g.sys.db.aggregate(
    'mongodb', 'inventory', 'customers',
    aggregate_query=[
        {
            '$facet': {
                'price': [
                    {
                        '$bucket': {
                            'groupBy': '$price',
                            'boundaries': [10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000],
                            'default': 'Other',
                            'output': {
                                'count': {
                                    '$sum': 1,
                                },
                                'product_details': {
                                    '$push': {
                                        'name': '$name',
                                        'price': '$price',
                                    },
                                },
                            },
                        },
                    },
                ],
                'categories': [
                    {
                        '$bucket': {
                            'groupBy': '$category_id',
                            'boundaries': [10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000],
                            'default': 'Other',
                            'output': {
                                'count': {
                                    '$sum': 1,
                                },
                                'product_details': {
                                    '$push': {
                                        'name': '$name',
                                        'id': '$id',
                                        'category_id': '$category_id',
                                    },
                                },
                            },
                        },
                    },
                ],
            },
        },
    ],
)
```

## Join another collection : $lookup

**MongoDB $lookup**

```python
aggregate = await g.sys.db.aggregate(
    'mongodb', 'inventory', 'customers',
    aggregate_query=[
        {
            '$lookup': {
                'from': 'products',
                'localField': 'customer_id',
                'foreignField': 'owner_id',
                'as': 'customer_data',
            },
        },
    ],
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Aggregate the customers of the tenant acme (MongoDB)**

```python
aggregate = await g.sys.db.aggregate(
    'crm::acme', 'crm', 'customers',
    aggregate_query=[
        {
            '$group': {
                '_id': '$city',
                'customers': {'$sum': 1},
            },
        },
    ],
)
```

## Without a schema

`g.sys.db.gen.aggregateGen` takes the same arguments and calls the [aggregate generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-aggregate-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.aggregateGen(…)` |
| Python | `g.sys.db.gen.aggregate_gen(…)` |
| PHP | `$g->sys->db->gen->aggregateGen(…)` |
| C#, Go | `g.Sys.Db.Gen.AggregateGen(…)` |
| Rust | `g.sys.db.aggregate_gen(Aggregate::new(…))` |
| Java, Kotlin | `g.sys.db.gen.aggregateGen(…)` |

## Related

- [Aggregate API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-aggregate-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/aggregate.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Count

> Get the total number of records in a collection or matching a specific query using API Maker's g.sys.db.count method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/count.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.count` calls the [count schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-count-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Simple get count

**Simple get count**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
)
```

## get count [headers]

**get count with headers**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## get count [find]

**Simple find**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': 'Bob',
    },
)
```

## $gt

**$gt**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gt': 4,
        },
    },
)
```

## $gte

**$gte**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gte': 4,
        },
    },
)
```

## $lt

**$lt**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$lt': 4,
        },
    },
)
```

## $lte

**$lte**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$lte': 4,
        },
    },
)
```

## $eq

**$eq**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': {
            '$eq': 'Bob',
        },
    },
)
```

## $ne

**$ne**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': {
            '$ne': 'Bob',
        },
    },
)
```

## $in

**$in**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$in': [1, 2],
        },
    },
)
```

## $nin

**$nin**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$nin': [1, 2],
        },
    },
)
```

## $and

**$and**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        '$and': [
            {
                'employee_Details.emp_Id': 1,
            },
            {
                'city': 'Baroda',
            },
        ],
    },
)
```

## $or

**$or**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        '$or': [
            {
                'state': 'Gujarat',
            },
            {
                'city': 'Baroda',
            },
        ],
    },
)
```

## $like

**$like**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'first_name': {
            '$like': '%Mall%',
        },
    },
)
```

## floating value support

**floating value support**

```python
count = await g.sys.db.count(
    'mysql_8', 'inventory', 'customers',
    find={
        'customer_id': {
            '$gt': 2.2,
        },
    },
)
```

## Structure

**Count method**

```python
count = await g.sys.db.count(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    find={
        'COLUMN_NAME': 'VALUE',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Count the customers of the tenant acme**

```python
count = await g.sys.db.count(
    'crm::acme', 'crm', 'customers',
    find={
        'first_name': 'Bob',
    },
)
```

## Without a schema

`g.sys.db.gen.countGen` takes the same arguments and calls the [count generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-count-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.countGen(…)` |
| Python | `g.sys.db.gen.count_gen(…)` |
| PHP | `$g->sys->db->gen->countGen(…)` |
| C#, Go | `g.Sys.Db.Gen.CountGen(…)` |
| Rust | `g.sys.db.count_gen(Count::new(…))` |
| Java, Kotlin | `g.sys.db.gen.countGen(…)` |

## Related

- [Count API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-count-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/count.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Distinct

> Retrieve distinct values for a specified field in a collection using API Maker's g.sys.db.distinct method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/distinct.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.distinct` calls the [distinct schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-distinct-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Simple distinct

**Simple distinct**

```python
distinct = await g.sys.db.distinct(
    'mysql_8', 'inventory', 'customers',
    'first_name',
)
```

## distinct with header

**distinct with header**

```python
distinct = await g.sys.db.distinct(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## distinct with order
### ascending

**distinct with order**

```python
distinct = await g.sys.db.distinct(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    order='asc',
)
```

### descending

**distinct with header**

```python
distinct = await g.sys.db.distinct(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    order='desc',
)
```

## Structure

**Distinct method**

```python
distinct = await g.sys.db.distinct(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    'COLUMN_NAME',
    order='asc',  # asc | desc
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Distinct first names of the customers of the tenant acme**

```python
distinct = await g.sys.db.distinct(
    'crm::acme', 'crm', 'customers',
    'first_name',
)
```

## Without a schema

`g.sys.db.gen.distinctGen` takes the same arguments and calls the [distinct generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-distinct-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.distinctGen(…)` |
| Python | `g.sys.db.gen.distinct_gen(…)` |
| PHP | `$g->sys->db->gen->distinctGen(…)` |
| C#, Go | `g.Sys.Db.Gen.DistinctGen(…)` |
| Rust | `g.sys.db.distinct_gen(Distinct::new(…))` |
| Java, Kotlin | `g.sys.db.gen.distinctGen(…)` |

## Related

- [Distinct API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-distinct-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/distinct.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Distinct with Query

> Fetch distinct values for a field based on a query filter using API Maker's g.sys.db.distinctWithQuery method.

Source: https://docs.apimaker.dev/v4/examples/sys/db/distinctQuery.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.db.distinctQuery` calls the [distinct with query schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-distinct-with-query-api.md) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

## Simple get distinctWithQuery

**Simple distinctWithQuery**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
)
```

## distinctWithQuery with multiple distinctField

**distinctWithQuery with multiple distinctField**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name,last_name',
)
```

## distinctWithQuery with headers

**distinctWithQuery with headers**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
)
```

## distinctWithQuery with order
### ascending

**distinctWithQuery with ascending order**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    order='asc',
)
```

### descending

**distinctWithQuery with descending order**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    order='desc',
)
```

## distinctWithQuery with find

**distinctWithQuery with find**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'first_name': 'Bob',
    },
)
```

## $lt distinctWithQuery

**distinctWithQuery with $lt**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$lt': 2,
        },
    },
)
```

## $lte distinctWithQuery

**distinctWithQuery with $lte**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$lte': 2,
        },
    },
)
```

## $gt distinctWithQuery

**distinctWithQuery with $gt**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$gt': 2,
        },
    },
)
```

## $gte distinctWithQuery

**distinctWithQuery with $gte**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$gte': 2,
        },
    },
)
```

## $eq distinctWithQuery

**distinctWithQuery with $eq**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$eq': 2,
        },
    },
)
```

## $ne distinctWithQuery

**distinctWithQuery with $ne**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$ne': 2,
        },
    },
)
```

## $not distinctWithQuery

**distinctWithQuery with $not**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$not': {
                '$in': [1, 2, 3],
            },
        },
    },
)
```

## $and distinctWithQuery

**distinctWithQuery with $and**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        '$and': [
            {
                'customer_id': 1,
            },
            {
                'zipcode': 382345,
            },
        ],
    },
)
```

## $or distinctWithQuery

**distinctWithQuery with $or**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        '$or': [
            {
                'customer_id': 1,
            },
            {
                'zipcode': 382345,
            },
        ],
    },
)
```

## $in distinctWithQuery

**distinctWithQuery with $in**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$in': [1, 2, 3],
        },
    },
)
```

## $nin distinctWithQuery

**distinctWithQuery with $nin**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'customer_id': {
            '$nin': [1, 2, 3],
        },
    },
)
```

## $like distinctWithQuery

**distinctWithQuery with $like**

```python
distinct_query = await g.sys.db.distinct_query(
    'mysql_8', 'inventory', 'customers',
    'first_name',
    find={
        'first_name': {
            '$like': 'Bob%',
        },
    },
)
```

## Structure

**Distinct Query**

```python
distinct_query = await g.sys.db.distinct_query(
    'INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME',
    'COLUMN_NAME',
    order='asc',  # asc | desc
    find={
        'COLUMN_NAME': 'VALUE',
    },
    headers={
        'x-am-response-case': 'capitalCase',  # noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        'x-am-response-object-type': 'make_flat',  # no_action | make_flat
        'x-am-internationalization': 'USER_I18N_ID',
        'x-am-run-in-sandbox': '2',
        'x-am-content-type-response': 'text/xml',  # application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        'x-am-cache-control': 'reset_cache',  # no_action | reset_cache
        'x-am-get-encrypted-data': 'get_only_encryption',  # no_encryption | get_only_encryption | get_data_and_encryption
        'x-am-sandbox-timeout': '13000',
        'x-no-compression': 'true',  # true | false
        'x-am-encrypted-payload': 'true',  # user will send 'true', when payload is encrypted for transfer
        # Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        'x-am-tenant-username': 'TENANT_USERNAME',  # ex: acme
        # Authorization headers
        'x-am-authorization': 'AUTHORIZATION_TOKEN',
        'x-am-user-authorization': 'API_USER_TOKEN',
        'x-aws-authorization': 'AWS_TOKEN',
        'x-google-authorization': 'GOOGLE_TOKEN',
        'x-azure-authorization': 'AZURE_TOKEN',
    },
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**Distinct first names of some customers of the tenant acme**

```python
distinct_query = await g.sys.db.distinct_query(
    'crm::acme', 'crm', 'customers',
    'first_name',
    find={
        'last_name': 'Lin',
    },
)
```

## Without a schema

`g.sys.db.gen.distinctQueryGen` takes the same arguments and calls the [distinct with query generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-distinct-with-query-api.md#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.distinctQueryGen(…)` |
| Python | `g.sys.db.gen.distinct_query_gen(…)` |
| PHP | `$g->sys->db->gen->distinctQueryGen(…)` |
| C#, Go | `g.Sys.Db.Gen.DistinctQueryGen(…)` |
| Rust | `g.sys.db.distinct_query_gen(Distinct::new(…))` |
| Java, Kotlin | `g.sys.db.gen.distinctQueryGen(…)` |

## Related

- [Distinct with query API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-distinct-with-query-api.md) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/distinctQuery.python.md#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# System APIs from Code

> Every system API of API Maker called from your code with g.sys.system and g.sys.cache - encryption, hashing, tokens, secrets, plain queries, indexes, events, validations, external APIs and Redis - each with its page and its code in eight languages.

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

Your code calls every [system API](https://docs.apimaker.dev/v4/docs/apis-all/overview.python.md#system-apis) through `g.sys.system` and `g.sys.cache`, whatever the settings of the API say about HTTP callers. Each API has one page : the HTTP call first, then **From code** with the code in eight languages.

- Video: [System APIs: Encryption, Hashing, Tokens and Redis, Ready to Call](https://apimaker.dev/videos/system-apis) (5 min)

## g.sys.system

| Method | What it does | Page |
|---|---|---|
| `encrypt`, `decrypt` | Encrypt and decrypt a value with the transfer key of the secret, or your own | [Encrypt](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-encrypt-data-api.python.md#from-code) · [Decrypt](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-decrypt-data-api.python.md#from-code) |
| `hash` | The hash a hashed column stores, to look a row up | [Hash data](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-hash-data-api.python.md#look-up-a-hashed-column) |
| `getToken` | The token of an API user, or of a person | [Get token](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-token-api.python.md#from-code) |
| `getSecret` | A key of a secret, or several | [Get secret](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-get-secret-by-name-api.python.md#from-code) |
| `executeQuery` | A plain query of the database : SQL, or a command of MongoDB | [Examples](https://docs.apimaker.dev/v4/examples/sys/system/executeQuery.python.md) · [Execute plain query](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-execute-plain-query-api.md) |
| `callExternalApi` | Call another API over HTTP : one call, several in parallel or in sequence, files | [Examples](https://docs.apimaker.dev/v4/examples/sys/system/callExternalApi.python.md) · [Call external API](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-call-external-api.python.md#from-code) |
| `getTableMeta` | The columns of a table, as its database has them | [Get table meta](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-get-table-meta-api.python.md#from-code) |
| `createIndexes`, `dropIndexes`, `getIndexes` | Indexes in the same words on every database | [Create](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-create-indexes-api.python.md#from-code) · [Drop](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-drop-indexes-api.python.md#from-code) · [Get](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-get-indexes-api.python.md#from-code) |
| `emitEvent`, `emitEventWS` | Run the listeners of an event, or tell WebSocket clients | [Emit event](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-emit-event-api.python.md#from-code) · [Emit WebSocket event](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-emit-event-ws-api.python.md#from-code) |
| `isValidDataForTable`, `isValidDataForCustomAPI` | Check data against a schema, or the body of a custom API, before you use it | [For a table](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-is-valid-data-for-table-api.python.md#from-code) · [For a custom API](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-is-valid-data-for-custom-api.python.md#from-code) |
| `isValidConnectionString` | Try a connection string before you save it | [Is valid connection string](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-is-valid-connection-string-api.python.md#from-code) |
| `multiTenantInstanceUpdated` | Tell every server that the connection of a tenant changed | [Multi tenant instance updated](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-multi-tenant-instance-updated-api.python.md#from-code) |

## g.sys.cache

| Method | What it does | Page |
|---|---|---|
| `getKey`, `setKey`, `removeKey` | Keys of your own in Redis, with a time to live | [Get](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-get-redis-key-api.python.md#from-code) · [Set](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-set-redis-key-api.python.md#from-code) · [Remove](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-remove-redis-key-api.python.md#from-code) |
| `resetCacheDB`, `resetCacheCustomApis`, `resetCacheSystemApis` | Drop the cached answers of a table, a custom API or a system API | [Database](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-reset-redis-cache-db-api.python.md#from-code) · [Custom API](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-reset-redis-cache-custom-api.python.md#from-code) · [System API](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-reset-redis-cache-system-api.python.md#from-code) |

## Good to know

- A call answers `data`, and throws the errors : the caller of your API gets them. In the languages other than TypeScript, `full` gives the whole response envelope (`g.sys.system.full` in Python…) : see the page of your [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md).
- Every call takes the [request headers](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md) of its HTTP form, such as `x-am-tenant-username`.

---

# 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/v4/examples/sys/system/executeQuery.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-execute-plain-query-api.md) from your code, whatever the settings of that API say about HTTP callers.

### Execute query System API

Execute database query using global object 'g'.

```python
await g.sys.system.execute_query(
    '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/

```python
await g.sys.system.execute_query(
    'INSTANCE_NAME',
    {
        'dbStats': 1,
    },
    database='DATABASE_NAME',
)
```

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

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

```python
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);')
```

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

- MySQL | TiDB | Percona XtraDB

```python
await g.sys.system.execute_query(
    'mysql',
    'SELECT * FROM inventory.employees;',
)
```

- MariaDB

```python
await g.sys.system.execute_query(
    'mariadb',
    'SELECT * FROM inventory.employees;',
)
```

- Microsoft SQL Server

```python
await g.sys.system.execute_query(
    'sqlserver',
    'SELECT * FROM inventory.dbo.employees;',
)
```

- MongoDB

```python
await g.sys.system.execute_query(
    'mongodb',
    {
        'dbStats': 1,
    },
    database='inventory',
)
```

- PostgreSQL

```python
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
)
```

- Oracle

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

```python
await g.sys.system.execute_query(
    'oracle',
    'SELECT * FROM "INVENTORY"."employees"',
)
```

### DDL Operation in API Maker

- CREATE TABLE

```python
await g.sys.system.execute_query(
    'mysql',
    'CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);',
)
```

- ALTER TABLE

```python
await g.sys.system.execute_query(
    'mysql',
    'ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;',
)
```

- RENAME TABLE

```python
await g.sys.system.execute_query(
    'mysql',
    'ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;',
)
```

- TRUNCATE TABLE

```python
await g.sys.system.execute_query(
    'mysql',
    'TRUNCATE TABLE inventory.employees;',
)
```

- DROP TABLE

```python
await g.sys.system.execute_query(
    '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

```python
await g.sys.system.execute_query(
    'mysql',
    "INSERT into inventory.employees Values(101, 'Bob', 'M');",
)
```

- DELETE

```python
await g.sys.system.execute_query(
    'mysql',
    "DELETE FROM inventory.employees WHERE emp_Id = '101';",
)
```

- UPDATE

```python
await g.sys.system.execute_query(
    '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

```python
await g.sys.system.execute_query(
    'mysql',
    'SELECT * FROM inventory.employees;',
)
```

- DISTINCT

```python
await g.sys.system.execute_query(
    'mysql',
    'SELECT DISTINCT emp_Name FROM inventory.employees;',
)
```

- ORDER BY

```python
await g.sys.system.execute_query(
    '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

```python
await g.sys.system.execute_query(
    'mysql',
    'SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;',
)
```

- AVG

```python
await g.sys.system.execute_query(
    '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

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

- LEFT JOIN

```python
await g.sys.system.execute_query(
    '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".

```python
await g.sys.system.execute_query(
    'sql_server',
    'USE inventory; exec dbo.procedureName;',
)
```

### Commands of MongoDB : indexes

A command of MongoDB is an object : `listIndexes`, `createIndexes`, `dropIndexes`… [createIndexes](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-create-indexes-api.python.md#from-code) and [getIndexes](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-get-indexes-api.python.md#from-code) do the same on every database, in the same words.

```python
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',
)
```

## Multi-tenant

- `instance: "crm::acme"` runs the query in the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) 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/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header is passed on. Without a tenant, it runs in the structure database.

```python
# 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',
)
```

## Related

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

---

# Call External API Example

> Examples of calling other APIs from API Maker code - JSON, forms, file uploads, SOAP and XML, OAuth 2, retries, redirects, file downloads, and lists of calls in parallel or in sequence, in every language.

Source: https://docs.apimaker.dev/v4/examples/sys/system/callExternalApi.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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.callExternalApi` calls the [call external API system API](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-call-external-api.python.md) from your code, whatever the settings of that API say about HTTP callers.

Call external API using global object 'g'.

### Simple call external apis

```python
await g.sys.system.call_external_apis([
    {
        'url': 'your_url',
        'method': 'GET',
        'timeout': 5000,
        'queryParams': {'select': 'first_name'},
        'headers': {'x-am-authorization': 'TOKEN'},
    },
])
```

### Parallel type

- If the type is 'parallel' the external APIs are executed in parallel.

```python
await g.sys.system.call_external_apis([
    {
        'url': 'FULL_URL',
        'method': 'POST',
        'body': [
            {
                'first_name': 'James',
                'last_name': 'Bond',
            },
        ],
        'id': 'saveApi',
        'postProcess': [
            {
                'from': 'saveApi.output.body.data.first_name',
                'to': 'setApi.body.first_name',
            },
        ],
        'queryParams': {
            # the query params of a URL are texts : deep is its JSON
            'deep': '[{"s_key":"SOURCE_COLLECTION_COLUMN_NAME","t_key":"TARGET_COLLECTION_COLUMN_NAME","t_col":"TARGET_COLLECTION_NAME","select":"COLUMN_NAMES"}]',
            'select': 'COLUMN_NAMES',
            'limit': 2,
            'sort': 'COLUMN_NAMES',
        },
        'timeout': 1000,
        'headers': {'x-am-authorization': 'TOKEN'},
    },
    {
        'type': 'parallel',
        'data': [
            {
                'url': 'FULL_URL',
                'method': 'PUT',
                'body': {},
                'id': 'setApi',
                'headers': {'x-am-authorization': 'TOKEN'},
            },
            {
                'url': 'FULL_URL',
                'method': 'GET',
                'headers': {'x-am-authorization': 'TOKEN'},
            },
        ],
    },
])
```

### Sequential type

- If the type is 'sequential' the API calls execute one by one.

```python
await g.sys.system.call_external_apis([
    {
        'url': 'your_url',
        'method': 'GET',
        'timeout': 5000,
        'queryParams': {'select': 'first_name'},
        'headers': {'accept': 'application/json'},
        'id': 'getData',
        'output': 'any',
    },
    {
        'type': 'sequential',
        'data': [
            {
                'url': 'your_url',
                'method': 'POST',
                'body': {},
                'id': 'addData',
                'preProcess': [
                    {
                        'from': 'getData.output.body.data',
                        'to': 'addData.body',
                    },
                ],
                'headers': {'x-am-authorization': 'TOKEN', 'x-am-response-case': 'capitalCase'},
            },
            {
                'url': 'your_url',
                'method': 'GET',
                'headers': {'x-am-response-case': 'capitalCase'},
            },
        ],
    },
])
```

### Path and query parameters, an API key, a form

- `pathParams` fills `:customerId` of the URL, `queryArrayFormat: 'repeat'` writes the list as `tags=web&tags=priority`, and `bodyType: 'form'` sends the body as `product=Pen&quantity=3`.
- `throwOnError` throws when the API answers with a status of 400 or above.

```python
order = await g.sys.system.call_external_api(
    'https://api.example.com/customers/:customerId/orders',
    method='POST',
    path_params={'customerId': 42},
    query_params={'tags': ['web', 'priority']},
    query_array_format='repeat',
    auth={'type': 'apiKey', 'name': 'x-api-key', 'value': 'API_KEY'},
    body_type='form',
    body={'product': 'Pen', 'quantity': 3},
    throw_on_error=True,
)
g.logger.log(order.body.id)
```

### Upload files

- `bodyType: 'multipart'` sends a form of several parts : every key of the body is a field, and `{ file }` a file of the uploads folder.
- A file a form sent to your custom API is sent on as it is : `document: g.req.files.files[0]`.

```python
await g.sys.system.call_external_api(
    'https://api.example.com/documents',
    method='POST',
    auth={'type': 'bearer', 'token': 'TOKEN'},
    body_type='multipart',
    body={
        'title': 'Contract',
        # a file of the uploads folder
        'document': {'file': 'contracts/42.pdf'},
        # a text sent as a file
        'note': {'content': 'Signed on site', 'fileName': 'note.txt'},
    },
)
```

### XML and SOAP

- `bodyType: 'xml'` sends the text of the body as it is, `responseType: 'text'` gives the answer as its text, whatever it holds.
- `retry` sends the request again when it got no answer, or one of the statuses 408, 429, 500, 502, 503 and 504.

```python
rate = await g.sys.system.call_external_api(
    'https://soap.example.com/service',
    method='POST',
    body_type='xml',
    headers={'content-type': 'text/xml; charset=utf-8', 'SOAPAction': 'urn:GetRate'},
    body="<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><GetRate/></soap:Body></soap:Envelope>",
    response_type='text',
    retry={'count': 3, 'delay': 500},
)
g.logger.log(rate.status_code)
```

### OAuth 2, redirects and retries

- The token is asked of `tokenUrl` with the id and the secret of the client, kept until it expires, and asked for again when the API refuses it.
- `ok` is `true` when the status of the answer is 200 to 299.

```python
reports = await g.sys.system.call_external_api(
    'https://api.example.com/reports',
    auth={
        'type': 'oauth2',
        'tokenUrl': 'https://login.example.com/oauth/token',
        'clientId': 'CLIENT_ID',
        'clientSecret': 'CLIENT_SECRET',
        'scope': 'reports.read',
    },
    follow_redirects=True,
    retry=3,
    timeout=10000,
)
if not reports.ok:
    g.logger.log(reports.status_code)
```

### Download a file

- `download` saves the body of the answer as a file of the uploads folder, and the answer has `file` : its `path` in the uploads folder, its `name`, its `size` and its `contentType`.
- Your code reads the file at `uploads/` + `file.path`. A custom API sends it to its caller by returning `{ __am__downloadFilePath: saved.file.path }`.
- Redirects are followed. When the status of the answer is not 200 to 299 there is no `file` : the answer is given as any other.

```python
saved = await g.sys.system.call_external_api(
    'https://api.example.com/invoices/42/pdf',
    download={'path': 'invoices/42.pdf', 'fileName': 'Invoice 42.pdf'},
)
g.logger.log(saved.file.size)
```

### Bytes

- `responseType: 'base64'` gives the bytes of the answer as Base64 text, in every language. `'buffer'` gives a `Buffer` in TypeScript.
- `bodyType: 'binary'` sends bytes : their Base64 text, a `Buffer`, or `{ file }` for a file of the uploads folder.

```python
picture = await g.sys.system.call_external_api('https://api.example.com/logo.png', response_type='base64')
await g.sys.system.call_external_api(
    'https://storage.example.com/bucket/logo.png',
    method='PUT',
    body_type='binary',
    headers={'content-type': 'image/png'},
    body=picture.body,
)
```

### A call which may fail

- `allowFailure` : when the call fails, its answer is `{ statusCode: 0, ok: false, error: { message } }`, and the calls after it run.
- `throwOnError` : a status of 400 or above ends the list with an error. `retry: 2` sends the request two more times first.

```python
await g.sys.system.call_external_apis([
    {
        'id': 'audit',
        'url': 'https://audit.example.com/events',
        'method': 'POST',
        'body': {'event': 'order'},
        'timeout': 2000,
        'allowFailure': True,
    },
    {
        'id': 'order',
        'url': 'https://api.example.com/orders',
        'method': 'POST',
        'body': {'product': 'Pen'},
        'retry': 2,
        'throwOnError': True,
    },
])
```

## Related

- [Call external api system API](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-call-external-api.python.md) · [All system APIs from code](https://docs.apimaker.dev/v4/examples/sys/system/system.md) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Custom API Example

> Learn how to create and implement custom APIs in API Maker with this comprehensive example, including code snippets and best practices.

Source: https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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`.

The code of a [custom API](https://docs.apimaker.dev/v4/docs/apis-all/custom-apis/user-created-custom-api.python.md) : what to return, how to call the APIs of API Maker, how to send files.

## Basic code

- A custom API is the function `main` of its language : what it returns is the `data` of the answer. The pieces below are the inside of `main`.

```python
return {'hello': 'world'}
```

## Calls of `g.sys`

- In TypeScript every `g.sys` call returns a promise : `await` it. A call without `await` runs, but its result and its errors are lost. The same in Python (in `async def main`) and C#. PHP, Go, Rust, Java and Kotlin wait for the answer of each call.

```python
return await g.sys.db.get_all('INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME')
```

## The whole envelope : `true`, or `full`

- A `g.sys` call returns the data and **throws** the errors of the API.
- With `true` as the second argument in TypeScript, or through `full` in the other languages (`g.sys.db.full`, `g.Sys.Db.Full`), it returns the whole envelope `{ success, statusCode, data, errors, warnings }` and never throws : read `success` yourself.

```python
save_data = await g.sys.db.full.save_single_or_multiple('INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME', {}, headers={})
count_of_all_data = await g.sys.db.full.count('INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME')
return {'saveData': save_data, 'countOfAllData': count_of_all_data}
```

## Throw an error

- An error raised ends the call with `success: false`, the message in `errors` and its code as the status. A message listed in the `errorList` of the API can be [translated](https://docs.apimaker.dev/v4/docs/i18/i18.python.md).

```python
save_data = await g.sys.db.full.save_single_or_multiple('INSTANCE_NAME', 'DB_NAME', 'COLLECTION_NAME', {}, headers={})
if not save_data.success:
    raise Errors('Your data is not saved!', code=400)
return save_data
```

## Return an image or any binary

- Set `g.res.contentType` and return the base64 of the content.

```python
g.res.content_type = 'image/gif'
return 'data:image/gif;base64,R0lGODlhAQABAIAAAP///////yH5BAEKAAEALAAAAAABAAEAAAICTAEAOwA='
```

## Download file

```python
import os

from am import G


async def main(g: G):
    # Write the files to send in the folder uploads ; the other files anywhere else.
    os.makedirs('uploads', exist_ok=True)
    with open(os.path.join('uploads', 'myfile.txt'), 'w', encoding='utf-8') as file:
        file.write('file 1 content')
    return {
        '__am__downloadFilePath': 'myfile.txt',          # its name in the folder uploads
        '__am__downloadFolderFileName': 'newName.txt',   # the name the file is saved under
    }
```

## Download a folder as a zip

- A folder of `uploads` goes into the zip under the path `archiveDestination`.

```python
from am import G


async def main(g: G):
    return {
        '__am__downloadFileOrFolderPaths': [
            {
                'fsSource': 'folder1/folder2/folder3/data_folder',   # a path in the folder uploads
                'archiveDestination': 'data_folder',                  # its path in the zip file
            },
        ],
        '__am__downloadFolderFileName': 'output.zip',                 # the name the file is saved under
    }
```

## Download files & folders

```python
import os

from am import G


async def main(g: G):
    # Write the files to send in the folder uploads ; the other files anywhere else.
    os.makedirs('uploads', exist_ok=True)
    with open(os.path.join('uploads', 'myfile.txt'), 'w', encoding='utf-8') as file:
        file.write('file 1 content')
    return {
        # Files and folders of the folder uploads : they go out as one zip file.
        '__am__downloadFileOrFolderPaths': ['myfile.txt', 'someFolder'],
        '__am__downloadFolderFileName': 'zipFile.zip',   # the name of the zip file
    }
```

## Download file from Angular sent by custom API

```ts
async function downloadFileFromBrowser() {
    const requestPayload = {};
    const requestHeaders = {};
    const resp = <any>await this.http.post(`API Maker custom API endpoint`, requestPayload, {
        headers: requestHeaders,
        responseType: <any>'text',
        observe: 'response',
    }).toPromise();
    const fileName = resp.headers.get('content-filename');
    await this.downloadFileFromBlob('application/zip', resp.body, fileName);
    return resp;
}

async function downloadFileFromBlob(contentType, base64Data: any, fileName) {
    const a = document.createElement('a');
    document.body.appendChild(a);
    a.style.display = 'none';

    let blob;
    if (base64Data instanceof Blob) {
        blob = base64Data;
    } else {
        const base64Response = await fetch(`data:${contentType};base64,` + base64Data);
        blob = await base64Response.blob();
    }

    const url = window.URL.createObjectURL(blob);
    a.href = url;
    a.download = fileName;
    a.click();
    window.URL.revokeObjectURL(url);
}
```

## Multi-tenant

- Call a custom API with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header to run it for one tenant of a [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance.
- It reads the tenant in `g.req.params`, and the APIs it calls with `g.sys.db` and `g.sys.system` work on the database of that tenant: the header is passed on, and each instance finds the tenant in its own tenants table.
- `instance: 'crm::globex'` names another tenant for one call, and an empty tenant header names the structure database. See [Custom APIs of a multi-tenant instance](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md#custom-apis).

```text
GET /api/custom-api/admin/customers-report
x-am-tenant-username: acme
```

```python
from am import G


async def main(g: G):
    tenant = g.req.params.tenantUsername  # 'acme'
    customers = await g.sys.db.get_all('crm', 'crm', 'customers')  # from the database of acme
    customers_of_globex = await g.sys.db.count('crm::globex', 'crm', 'customers')
    return {'tenant': tenant, 'customers': customers, 'customersOfGlobex': customers_of_globex}
```

## The samples of a new account

A new account comes with custom APIs under `/default/…` which show the features at work. Each one is written below in every language : the settings (always TypeScript) and the code of the language of your tab.

| Sample | Path | Shows |
|---|---|---|
| Login | `/default/login` | [the token of the app and of the person](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#sign-in), one message for every failure |
| Data Isolation | `/default/data-isolation` | [only the rows of the person who calls](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#rows-of-the-caller) |
| Deep Populate Orders, Deep Populate Order Items | `/default/deep-populate-orders`, `/default/deep-populate-order-items` | [rows with the rows they point to](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#deep-populate), on one and two levels |
| Find Join Orders, Find Join Products | `/default/find-join-orders`, `/default/find-join-products` | [a filter on a column of another table](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#find-join) |
| Caching Example | `/default/caching-example` | [an answer kept in the cache until its tables change](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#cache-the-answer) |
| Get Captcha, Login With Captcha | `/default/get-captcha`, `/default/login-with-captcha` | [a value kept for a while in the cache, used once](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#captcha) |
| WebSocket Notify | `/default/ws-notify` | [a message to one person over WebSocket](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#websocket-notify) |
| Collection Copy Stream, Database Copy Stream | `/default/collection-copy`, `/default/database-copy` | [rows read one by one](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#stream-to-a-file) : the memory stays the same whatever the size of the table |
| S3 Upload, S3 Download, S3 List, Image Upload | `/default/s3-upload`… | [files in S3](https://docs.apimaker.dev/v4/examples/custom-apis/custom-api.python.md#s3), with a package of the language |

Most of them are `NO_ACCESS` in the account : open one with `apiAccessType` in its settings before an application calls it.

## Sign in : the token of the app and of the person

The login of an application : it checks the email and the password with the [token provider](https://docs.apimaker.dev/v4/docs/authorization/AMDB.md) `default_db_login`, and answers the two tokens every later call sends, `x-am-authorization` (the app, an API user) and `x-am-user-authorization` (the person).

**Its settings**

```typescript
import * as T from 'types';

const customApi: T.ICustomApiSettingsTypes = {
    name: 'Login',
    path: '/default/login',
    requestMethod: T.ERequestMethod.POST,
    apiAccessType: T.EAPIAccessType.IS_PUBLIC,   // the caller has no token yet
    errorList: ['Invalid email or password'],    // one message for every failure : it never tells which emails exist
    reqBodySchema: {                             // checked before the code runs
        email: { __type: T.EType.string, validations: { required: true } },
        password: { __type: T.EType.string, validations: { required: true } },
    },
};
module.exports = customApi;
```

```python
from am import Errors, G


async def main(g: G):
    email = str(g.req.body.email or '').strip().lower()   # as the schema of customers stores it
    password = str(g.req.body.password or '')
    if not email or not password:
        raise Errors('Invalid email or password', code=401)

    # The person, without its password : the token provider checks the password, not this read.
    customers = await g.sys.db.query('mongodb', 'shop', 'customers', find={'email': email}, select='-password', limit=1)
    if not customers:
        raise Errors('Invalid email or password', code=401)

    # The token of the app : the password of the API user is read from the secret, never written in the code.
    app_password = await g.sys.system.get_secret('common.apiUserPasswords.default')
    app = await g.sys.system.get_token('default', app_password)
    # The token of the person : default_db_login compares the password with the stored one.
    try:
        person = await g.sys.system.get_token(email, password, name='default_db_login')
    except Errors:
        raise Errors('Invalid email or password', code=401)
    return {'amToken': app.token, 'userToken': person.token, 'refreshToken': person.refresh_token, 'customer': customers[0]}
```

- The API user `default` and its password path `common.apiUserPasswords.default` are those of a new account. Use the API user of the application, with only the [groups](https://docs.apimaker.dev/v4/docs/apis-security/api-group-permission.md) it needs.
- The two tokens in one call : [Who calls](https://docs.apimaker.dev/v4/examples/req/auth/auth.python.md#database-user).

## Only the rows of the person who calls

The person comes from its token (`g.req.auth.authAMDB`, the row the token provider read), never from the request : nobody can ask for the orders of somebody else.

```python
from am import Errors, G


async def main(g: G):
    person = g.req.auth.authAMDB   # its row of customers, as its token says
    if not person or person.customer_id is None:
        raise Errors('Please sign in.', code=401)
    return await g.sys.db.get_all('mongodb', 'shop', 'orders', find={'customer_id': person.customer_id}, sort='-created_at', limit=50)
```

- The APIs of the tables need the same rule : a [row scoping pre hook](https://docs.apimaker.dev/v4/docs/apis-all/hooks/preHook-api.python.md#narrow-a-request-to-the-rows-of-the-caller) puts the person into the filter of every API of the table. The samples `restrictToOwnerInstance`, `restrictToOwnerDatabase` and `restrictToOwnerOrders` of a new account are such hooks, inactive.

## Rows with the rows they point to : deep

`deep` replaces the id a column holds with the row it names : each order comes with its customer. See [deep](https://docs.apimaker.dev/v4/docs/apis-all/query-params/deep.md).

**Orders with their customer, and the total count**

```python
return await g.sys.db.full.get_all(   # full : the whole answer, with totalCount
    'mongodb', 'shop', 'orders',
    find={'status': 'PAID'},
    select='order_no,total_cents,status,customer_id',
    sort='-created_at', limit=50, get_total_count=True,
    deep=[DeepItem('customer_id', t_db='shop', t_col='customers', t_key='customer_id', select='customer_id,email,country')],
)
```

A `deep` inside a `deep` goes one level further : each order item comes with its product, and each product with its category.

**Two levels**

```python
return await g.sys.db.get_all(
    'mongodb', 'shop', 'orderItems',
    select='order_id,product_id,qty,unit_price_cents',
    limit=200,
    deep=[DeepItem(
        'product_id', t_db='shop', t_col='products', t_key='_id', select='name,slug,price_cents,category',
        deep=[DeepItem('category', t_db='shop', t_col='categories', t_key='_id', select='name,slug')],
    )],
)
```

- `t_col`, `t_key` and `t_db` can be left out when the [schema](https://docs.apimaker.dev/v4/docs/schema/schema.md) of the column says where it points (`database`, `collection`, `column` of the property).

## A filter on a column of another table : find join

A column which points to another table (a relation of the schema) is followed in `find` with a dot : `'customer_id.country'` is the country of the customer of the order. It narrows the rows ; `deep` adds the related row. See [find](https://docs.apimaker.dev/v4/docs/apis-all/query-params/find.python.md#find-and-join-fields-of-related-tables).

**The paid orders of the customers of India**

```python
return await g.sys.db.query(
    'mongodb', 'shop', 'orders',
    find={'status': 'PAID', 'customer_id.country': 'IN'},   # a column of orders, then one of customers
    select='order_no,total_cents,status,customer_id',
    limit=50,
)
```

The same column can filter and populate : the active products of the category `audio`, each with its category.

**Filter and populate**

```python
return await g.sys.db.query(
    'mongodb', 'shop', 'products',
    find={'is_active': True, 'category.slug': 'audio'},
    deep=[DeepItem('category', t_db='shop', t_col='categories', t_key='_id', select='name,slug')],
    limit=50,
)
```

- The path follows a relation forwards (a column which names `database`, `collection` and `column` in the schema). A virtual field is not read in `find`.

## Cache the answer

The settings keep the answer in Redis until one of the tables it reads is written through API Maker : the code has nothing to do. See [Automatic caching](https://docs.apimaker.dev/v4/docs/features/automatic-caching.md).

**Its settings**

```typescript
import * as T from 'types';

const customApi: T.ICustomApiSettingsTypes = {
    name: 'Caching Example',
    path: '/default/caching-example',
    requestMethod: T.ERequestMethod.GET,
    apiAccessType: T.EAPIAccessType.TOKEN_ACCESS,
    errorList: [],
    enableCaching: true,                     // one answer per URL, body and headers which matter
    resetCacheOnModificationOf: [
        'DB: mongodb:shop:orders',           // forgotten when the orders change
        'DB: mongodb:shop:orderItems',       // or their items
    ],
};
module.exports = customApi;
```

**Its code**

```python
orders = await g.sys.db.get_all(
    'mongodb', 'shop', 'orders',
    find={'status': 'PAID'}, select='order_no,total_cents', sort='-created_at', limit=20,
)
return {'orders': orders}   # the next calls get this answer from the cache, until the orders change
```

- An answer which must differ for every call (a captcha, a time) needs `enableCaching: false`.

## A value kept for a while, used once : a captcha

The sample **Get Captcha** draws an image with the npm package `svg-captcha` (add it under [Sandbox settings › packages](https://docs.apimaker.dev/v4/docs/settings/sandboxSettings.md#packages)) and keeps only its answer, in the cache, for three minutes :

**Get Captcha, in TypeScript**

```typescript
import * as T from 'types';
const svgCaptcha = require('svg-captcha');

async function main(g: T.IAMGlobal) {
    const captcha = svgCaptcha.create({ size: 6, noise: 2 });
    // The key names the captcha, nothing in it gives the answer.
    const key = Date.now().toString(36) + '-' + Math.random().toString(36).substring(2, 12);
    await g.sys.cache.setKey('captcha:' + key, captcha.text, 180);   // only the answer, only on the server, 180 seconds
    return { key, svg: captcha.data, expiresInSeconds: 180 };
}
module.exports = main;
```

In another language, draw the image with a package of its registry and keep the answer the same way : `g.sys.cache.set_key(key, text, 180)` in Python, `$g->sys->cache->setKey` in PHP, `g.Sys.Cache.SetKey` in C# and Go, `g.sys.cache.set_key(…, Some(180))` in Rust, `g.sys.cache.setKey` in Java and Kotlin. The login which gets the answer reads it, removes it at once (one try per image : a solved image can not be sent twice), and compares :

**Check the captcha before the login**

```python
key = 'captcha:' + str(g.req.body.captchaKey or '')
expected = await g.sys.cache.get_key(key)
if not expected:
    raise Errors('Captcha expired, please refresh it', code=400)
await g.sys.cache.remove_key(key)   # one try per image
if str(expected).lower() != str(g.req.body.captcha or '').strip().lower():
    raise Errors('Invalid captcha', code=400)
return {'solved': True}   # then the login itself : see Sign in
```

- Both APIs are `IS_PUBLIC` (the login screen calls them before anybody has a token) and `enableCaching: false`.
- The messages of the captcha say what failed, so that the screen fetches a new image ; the message of the credentials stays one for every failure.

## A message to one person over WebSocket

`g.sys.system.emitEventWS` sends the data to the clients subscribed to the [WebSocket event](https://docs.apimaker.dev/v4/docs/pages/web-socket-event-page.python.md) whose criteria equal it : one message per person, no broadcast room. The event `default/orders-updated` is a WebSocket event of a new account.

**Tell one customer**

```python
email = str(g.req.body.email or '')
if not email:
    raise Errors('Please provide email.', code=400)
# The clients which subscribed with the criteria { email: '<this email>' } get it.
await g.sys.system.emit_event_ws('default/orders-updated', {'email': email, 'type': 'order-updated', 'message': 'Your order was shipped.'})
return {'emitted': True, 'email': email}
```

The page of the customer subscribes once it is connected :

**In the browser**

```js
const ws = new WebSocket(`${wsUrl}?authTokenInfo=default_db_login&x-am-authorization=${appToken}&x-am-user-authorization=${userToken}`);
ws.onmessage = (event) => {
    const message = JSON.parse(event.data);
    if (message.type === 'CONNECTED' && message.response.connected) {
        ws.send(JSON.stringify({
            objType: 'REGISTER',
            onEvents: [{
                eventType: 'CUSTOM_WS_EVENTS', apiName: 'default/orders-updated', getEventData: true,
                condition: { conditionType: 'RESPONSE', criteria: { email: myEmail } },
            }],
        }));
    }
    if (message.type === 'NOTIFICATION') showMessage(message.response.eventData);
};
```

## Rows read one by one : a table to a file

`queryByStream` hands the rows to a function one by one : the memory of the API stays the same whatever the size of the table. This API writes the paid orders into a CSV file, then sends it.

```python
import csv
import os

from am import G, Query


async def main(g: G):
    os.makedirs('uploads', exist_ok=True)
    with open(os.path.join('uploads', 'orders.csv'), 'w', newline='', encoding='utf-8') as file:
        out = csv.writer(file)
        out.writerow(['order_no', 'status', 'total_cents'])

        def write(order):
            out.writerow([order.get('order_no'), order.get('status'), order.get('total_cents')])

        await g.sys.db.query_by_stream(
            Query('mongodb', 'shop', 'orders', find={'status': 'PAID'}, select='order_no,status,total_cents'),
            write,
        )
    return {
        '__am__downloadFilePath': 'orders.csv',             # its name in the folder uploads
        '__am__downloadFolderFileName': 'paid-orders.csv',  # the name the file is saved under
        '__am__cleanupFileOrFolderPaths': 'orders.csv',     # removed once it is sent
    }
```

- The samples **Collection Copy Stream** and **Database Copy Stream** of a new account copy a table, or every table of a database, the same way : `g.sys.db.gen.queryByStreamGen` reads the rows without a schema, and the function saves them by batches of 1,000 with `g.sys.db.gen.saveSingleOrMultipleGen`, waiting for each batch before the next rows come.
- `getAllByStream` does the same with the `queryParams` of a get all. See [Query for get data by stream](https://docs.apimaker.dev/v4/examples/sys/db/queryByStream.python.md).

## Files in S3

The samples **S3 Upload**, **S3 Download**, **S3 List** and **Image Upload** use the npm package `@aws-sdk/client-s3` (and `sharp` to make the images smaller), with the keys of the bucket in the secret (`s3.bucket`, `s3.region`, `s3.accessKeyId`, `s3.secretAccessKey`, and `s3.endpoint` for MinIO, R2 or Spaces). The parts of API Maker are the same in every language : the keys from `g.sys.system.getSecret('s3')`, the uploaded file in `g.req.body.files` (see [Files](https://docs.apimaker.dev/v4/docs/apis-all/custom-apis/user-created-custom-api.python.md#files)), and a download through the folder `uploads` :

**S3 Download, in TypeScript**

```typescript
import * as T from 'types';
import * as fs from 'fs';
import * as path from 'path';
import { S3Client, GetObjectCommand } from '@aws-sdk/client-s3';

async function main(g: T.IAMGlobal): Promise<T.IDownloadResponse> {
    const s3 = await g.sys.system.getSecret('s3');
    if (!s3 || !s3.bucket) throw new Error('S3 secret is not configured.');
    const client = new S3Client({
        region: s3.region,
        endpoint: s3.endpoint || undefined,
        forcePathStyle: !!s3.endpoint,
        credentials: { accessKeyId: s3.accessKeyId, secretAccessKey: s3.secretAccessKey },
    });
    const key = String(g.req.query?.key || '');
    if (!key) throw new Error('Query param "key" is required.');

    // The object is written into the folder uploads, the one folder files are sent from.
    const obj = await client.send(new GetObjectCommand({ Bucket: s3.bucket, Key: key }));
    const name = path.basename(key);
    await new Promise<void>((resolve, reject) => {
        const out = fs.createWriteStream(path.join(__dirname, 'uploads', name));
        (obj.Body as NodeJS.ReadableStream).pipe(out);
        out.on('finish', () => resolve());
        out.on('error', reject);
    });
    return {
        __am__downloadFilePath: name,         // in the folder uploads
        __am__downloadFolderFileName: name,   // the name the file is saved under
        __am__cleanupFileOrFolderPaths: name, // removed once it is sent
    };
}
module.exports = main;
```

- In another language : the SDK of AWS for it (`boto3` for Python, `aws/aws-sdk-php`, `AWSSDK.S3` for C#, `github.com/aws/aws-sdk-go-v2/service/s3`, `aws-sdk-s3` for Rust, `software.amazon.awssdk:s3` for Java and Kotlin), added to the packages of its language in [Sandbox settings](https://docs.apimaker.dev/v4/docs/settings/sandboxSettings.md#packages).

---

# The Request in Code (g.req)

> Read the request in your code with g.req of API Maker - its body, headers, params of the path, query params and the data of an event - with the same code in eight languages.

Source: https://docs.apimaker.dev/v4/examples/req/req.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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`.

Custom APIs, hooks, events and schedulers read the request through `g.req`. Who calls is in `g.req.auth` : see [Who calls : g.req.auth](https://docs.apimaker.dev/v4/examples/req/auth/auth.python.md).

## Body

To get the value of request body, use `g.req.body`.

```python
body = g.req.body
```

## Headers

To get the value of requested headers, use `g.req.headers`.

```python
headers = g.req.headers
```

The [tenant header](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) of a request of a tenant of a [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance. `g.req.params.tenantUsername` holds the tenant too, also when the request named it in the path (`crm::acme`).

```python
tenant_username = g.req.headers.get('x-am-tenant-username')  # 'acme'
```

## Params

To get the value of requested params, use `g.req.params`.

```python
params = g.req.params
```

The tenant of the request, for a [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.python.md) instance: named with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.python.md#x-am-tenant-username) header, or in the path of an API of the instance (`crm::acme`).

```python
tenant_username = g.req.params.tenantUsername  # 'acme', None without a tenant
```

## Query params

To get the value of requested query params, use `g.req.query`.

```python
query = g.req.query
```

## Event data

To get the value of requested body data of the event, use `g.req.eventData`.

```python
event_data = g.req.event_data
```

## Related

- [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md) · [Custom APIs](https://docs.apimaker.dev/v4/docs/apis-all/custom-apis/user-created-custom-api.python.md) · [The answer : g.res](https://docs.apimaker.dev/v4/examples/res/res.python.md)

---

# Who Calls (g.req.auth)

> Read in your code who calls an API of API Maker - the API user, a person of your users table, a person of Google, Azure AD or AWS Cognito - with g.req.auth, in eight languages, and a login API which gets both tokens.

Source: https://docs.apimaker.dev/v4/examples/req/auth/auth.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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`.

Every provider which checked a token of the call leaves what it found in `g.req.auth` : the application in `authAMUser`, the person in `authAMDB`, `authGoogle`, `authAzure`, `authAWS` or `authCustom`. The [sign-in providers](https://docs.apimaker.dev/v4/docs/authorization/sign-in-providers.md) page says how each one is set up.

## The API user

The application behind `x-am-authorization`, from the [token API](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-token-api.python.md).

```python
api_user_auth = g.req.auth.authAMUser  # the API user of the token of x-am-authorization
```

## A person of your users table

The person behind `x-am-user-authorization`, a row of the table of a [DB token generator](https://docs.apimaker.dev/v4/docs/authorization/AMDB.md).

```python
database_user_auth = g.req.auth.authAMDB  # the row of the person, without its password
```

### A login API which gets both tokens

```python
from am import G, GetToken


# A login API (apiAccessType : IS_PUBLIC) : the token of the API user 'default' for the app, and the token of the
# person whose email and password the body brings, checked by the DB token generator 'users_tg'.
async def main(g: G):
    password = await g.sys.system.get_secret('common.apiUserPasswords.default')
    app, person = await g.sys.system.get_tokens([
        GetToken('default', password),
        GetToken(g.req.body.email, g.req.body.password, name='users_tg'),
    ])
    return {'amToken': app.token, 'amDbToken': person.token, 'refreshToken': person.refresh_token}
```

- The token of a person, a row of the table of the token generator `users_tg`, from the [token API](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-token-api.python.md#the-token-of-a-person) :

```json
{
    "name": "users_tg",
    "u": "USER_NAME",
    "p": "PASSWORD"
}
```

## Google, Azure AD and AWS Cognito

The opened token of the provider, with the columns its `groupsDataSource` selected from your table.

### Google

```python
google_auth = g.req.auth.authGoogle  # the opened token of x-google-authorization
```

### Azure AD

```python
azure_auth = g.req.auth.authAzure  # the opened token of x-azure-authorization
```

### AWS Cognito

```python
aws_auth = g.req.auth.authAWS  # the opened token of x-aws-authorization
```

## A provider of your own

The result of your token validator, in `g.req.auth.authCustom` : see [the custom provider example](https://docs.apimaker.dev/v4/examples/req/auth/authCustom.python.md).

## Related

- [Sign-in providers](https://docs.apimaker.dev/v4/docs/authorization/sign-in-providers.md) · [The request : g.req](https://docs.apimaker.dev/v4/examples/req/req.python.md) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md)

---

# Custom Auth Provider Example

> Build your own token provider in API Maker with jose — sign it, validate it, and read the payload from g.req.auth.authCustom.

Source: https://docs.apimaker.dev/v4/examples/req/auth/authCustom.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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`.

### Custom authorization

To get the value of request header `x-custom-authorization`, use `g.req.auth.authCustom`.

```python
custom_auth = g.req.auth.authCustom  # what the token validator returned
```

Whatever your **Token Validator** function returns is what lands in `g.req.auth.authCustom`. Return the decoded JWT payload and you get the decoded token there — return your own object and you get that instead.

## Why a custom provider?

AM, database, AWS, Azure and Google cover most cases. But sometimes the token is yours — your own claims, your own signing key, your own expiry rules. That is what a **Custom Token Generator** is for: you write the code that mints the token and the code that checks it, and API Maker handles the rest of the request pipeline.

Below is a complete working example: a JWT signed with HS256, with the usual JWT package of each language.

---

### 1. Add a JWT package, and a signing key

Go to **Operations → Sandbox Settings**, select the language of the provider and add its package (see [Packages](https://docs.apimaker.dev/v4/docs/settings/sandboxSettings.md#packages)):

| Language | Registry | Package |
|---|---|---|
| TypeScript | npm | `jose` |
| Python | PyPI | `pyjwt` |
| PHP | Packagist | `firebase/php-jwt` |
| C# | NuGet | `Microsoft.IdentityModel.JsonWebTokens` |
| Go | Go modules | `github.com/golang-jwt/jwt/v5` |
| Rust | crates.io | `jsonwebtoken[rust_crypto]` and `uuid[v4]` |
| Java, Kotlin | Maven Central | `com.auth0:java-jwt` |

Once added, it is available in every piece of code of that language, including the two functions below.

Then add a key of your own to the [secret](https://docs.apimaker.dev/v4/docs/secrets/secrets.python.md#your-own-keys), `jwtSigningKey`, with at least 32 random characters: the packages of PHP and C# refuse a shorter key for HS256. Keep it for the tokens alone. `common.secretFETransfer` is no signing key: your apps hold it, so anyone with a copy of an app could sign tokens with it.

---

### 2. Create the auth provider

Go to **Security → Auth Providers**, click **+** and pick **Custom Token Generator**.

You will get three tabs: **Basic Info**, **Token Generator** and **Token Validator**. The Basic Info is always TypeScript, which API Maker [runs itself](https://docs.apimaker.dev/v4/docs/languages/index.python.md#settings-run) when you save ; the generator and the validator are written in the [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md#basic-info) you pick for the provider.

#### Basic Info

```ts
import * as T from 'types';

let customTokenBasicInfo: Partial<T.IAuthTokenAMDB> = {
    name: 'jose_example',
    runOnNativeProcess: false,
};
module.exports = customTokenBasicInfo;
```

- `name` is how you will refer to this provider everywhere else — in the get-token request and in the `authProviders` array of your settings files.
- Keep `runOnNativeProcess` as `false` so the code runs inside the sandbox, where the packages of its language live.

#### Token Generator

Runs when someone asks for a token. Whatever you return is sent back to the caller as-is.

**Token Generator**

```python
import time
import uuid

import jwt  # the package pyjwt

from am import G


# Produce and return a token string from the request context.
# Runs at token-generation time.
async def main(g: G):
    body = g.req.body
    signing_key = await g.sys.system.get_secret('jwtSigningKey')

    now = int(time.time())
    access_token = jwt.encode(
        {
            # set your required data
            'role': body.role,
            'email': 'ada@example.com',
            'companyId': 'cmp_123',
            'sub': body.userId,
            'iat': now,
            'exp': now + 15 * 60,
            'jti': str(uuid.uuid4()),
        },
        signing_key,
        algorithm='HS256',
    )

    return {
        'access_token': access_token,
        'refresh_token': 'refresh_token_test',
        'validity': 19200,
    }
```

The whole request body reaches this function as `g.req.body`, so you decide what your login payload looks like.

#### Token Validator

Runs on every request that carries the `x-custom-authorization` header. The token is in `g.req.body.token`.

**Token Validator**

```python
import jwt  # the package pyjwt

from am import G


# Validate an incoming token. Return a truthy object (usually the decoded payload) to mark valid.
# Raise or return None to mark invalid.
async def main(g: G):
    access_token = g.req.body.token
    signing_key = await g.sys.system.get_secret('jwtSigningKey')

    # Raises when the signature or the expiry is wrong.
    payload = jwt.decode(access_token, signing_key, algorithms=['HS256'])

    return payload
```

The rule is simple — **truthy means valid**. Anything falsy, or a thrown error, means the request is rejected.

---

### 3. Generate a token

Request Method: POST

URL
```text
/api/system-api/user-path/token
```

```json
{
    "name": "jose_example",
    "userId": "user_123",
    "role": "admin"
}
```

- `name` tells API Maker which auth provider to run. Everything else in the body is yours and is passed straight to your Token Generator as `g.req.body`.
- For more information about this API [click here](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-token-api.python.md).

Response — exactly what your generator returned:

```json
{
    "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
    "refresh_token": "refresh_token_test",
    "validity": 19200
}
```

---

### 4. Send the token

Put the `access_token` in the `x-custom-authorization` header on every call:

```text
x-custom-authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
```

To let an API accept this provider, add its name to `authProviders` in your settings file:

```ts
authProviders: <string[]>["jose_example"],
```

---

### 5. Read it in your custom code

```python
def main(g):
    auth = g.req.auth.authCustom

    g.logger.log(auth.sub)        # user_123
    g.logger.log(auth.role)       # admin
    g.logger.log(auth.companyId)  # cmp_123

    return {'ok': True}
```

By the time your code runs, the token is already verified — API Maker rejects bad ones before they reach you. So `g.req.auth.authCustom` is safe to trust.

## Related

- [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md) · [Custom APIs](https://docs.apimaker.dev/v4/docs/apis-all/custom-apis/user-created-custom-api.python.md)

---

# The Answer in Code (g.res)

> Shape the answer of an API in your code with g.res of API Maker - its output, status code, errors, warnings, data shared between hooks, and the content type (JSON, HTML, text, XML, YAML, bytes) - in eight languages.

Source: https://docs.apimaker.dev/v4/examples/res/res.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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`.

Custom APIs and hooks shape the answer of the request through `g.res`.

## Output

```python
g.res.output = {'hello': 'world'}
```

Response
```json
{
    "success": true,
    "statusCode": 200,
    "data": {
        "hello": "world"
    }
}
```

## Status code

`statusCode` use to set/get status code of the response.

```python
g.res.status_code = 400  # BAD_REQUEST
```

Response
```json
{
    "success": false,
    "statusCode": 400,
    "data": 400
}
```

All supported status code.
```text
OK = 200,
NO_CONTENT = 204,
CREATED = 201,
BAD_REQUEST = 400,
UNAUTHORIZED = 401,
FORBIDDEN = 403,
RESOURCE_NOT_FOUND = 404,
INTERNAL_SERVER_ERROR = 500
```

## Errors

```python
g.res.errors = [{'code': 400, 'message': 'Error message here.'}]
```

Response
```json
{
    "success": false,
    "statusCode": 400,
    "errors": [
        {
            "code": 400,
            "message": "Error message here."
        }
    ],
    "warnings": []
}
```

## Warnings

```python
g.res.warnings = [{'code': 401, 'message': 'This is warning!'}]
return {'hello': 'v2'}
```

Response
```json
{
    "success": true,
    "statusCode": 200,
    "data": {
        "hello": "v2"
    },
    "warnings": [
        {
            "code": 401,
            "message": "This is warning!"
        }
    ]
}
```

## Shared between hooks and code

`g.res.shared` is used to share data between post-hooks, pre-hooks, and code within that API.

```python
g.res.shared.name = 'Joe'
return g.res.shared.name
```

Response
```json
{
    "success": true,
    "statusCode": 200,
    "data": "Joe"
}
```

## Content types

- The user does not need to set the response content type to send JSON as a response.
- If the response content type is not set that means it is application/JSON.

If ContentType is set to below system will return data accordingly.
```text
JSON = 'application/json'
XML = 'text/xml'
YAML = 'text/yaml'
TEXT = 'text/plain'
HTML = 'text/html'
```

> Note: If content type is set and not from above list, then API Maker will consider a text the code returns as base64 encoded. Bytes the code returns (a `Buffer`, `bytes`, `byte[]`, `[]byte`…) are sent as they are, whatever the content type : see OCTET_STREAM below.

### HTML

**Content type HTML**

```python
g.res.content_type = 'text/html'
return '<!DOCTYPE html><html><body><h1>My First Heading </h1><p> My first paragraph.</p></body></html>'
```

Response
```html
<!DOCTYPE html><html><body><h1>My First Heading </h1><p> My first paragraph.</p></body></html>
```

### JSON

**Content type JSON**

```python
g.res.content_type = 'application/json'
g.res.errors = [{'code': 401, 'message': 'This is error'}]
g.res.status_code = 555
return 'World'
```

Response
```json
{
    "success": false,
    "statusCode": 555,
    "data": "World",
    "errors": [
        {
            "code": 401,
            "message": "This is error"
        }
    ],
    "warnings": []
}
```

### Bytes : application/octet-stream

**Content type OCTET_STREAM**

```python
g.res.content_type = 'application/octet-stream'
```

- It will download the image.

**base64**

```python
g.res.content_type = 'image/gif'
return 'data:image/gif;base64,R0lGODlhAQABAIAAAP///////yH5BAEKAAEALAAAAAABAAEAAAICTAEAOwA='
```

- It will send the bytes as they are : a file the code read or downloaded, a PDF it made. Return the bytes (a `Buffer`, `bytes`, `byte[]`, `[]byte`), or leave them in `g.res.output` : the answer is those bytes, under the content type the code names, whatever it is (`image/png`, `application/pdf`…). Headers of the code go with them (`Content-Disposition`, `Cache-Control`).

**bytes**

```python
import base64

picture = base64.b64decode('R0lGODlhAQABAIAAAP///////yH5BAEKAAEALAAAAAABAAEAAAICTAEAOwA=')
g.res.content_type = 'image/gif'
g.res.headers = {'Content-Disposition': 'inline; filename="pixel.gif"'}
return picture
```

### Text

**Content type TEXT**

```python
g.res.content_type = 'text/plain'
return 'Hello world from the API Maker.'
```

Response
```text
Hello world from the API Maker.
```

### XML

**Content type XML**

```python
g.res.content_type = 'text/xml'
return 'Hello world from the API Maker.'
```

Response
```xml
<?xml version='1.0'?>
<root>
    <success>true</success>
    <statusCode>200</statusCode>
    <data>Hello world from the API Maker.</data>
</root>
```

### YAML

**Content type YAML**

```python
g.res.content_type = 'text/yaml'
return 'Hello world from the API Maker.'
```

Response
```yaml
success: true
statusCode: 200
data: Hello world from the API Maker.
```

## Related

- [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md) · [Response format](https://docs.apimaker.dev/v4/docs/apis-all/response-format.python.md) · [The request : g.req](https://docs.apimaker.dev/v4/examples/req/req.python.md)

---

# Global Shared Example

> See how to use g.shared to share data across APIs, pre/post-hooks, and custom code in API Maker—enable seamless data flow.

Source: https://docs.apimaker.dev/v4/examples/shared/globalShared.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.python.md) 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`.

### Shared data globally

`g.shared` is used to share data between post-hooks, pre-hooks, code, and APIs.

- If the user set a shared variable from the one API response.

```python
g.shared['lastOutput'] = g.res.output
```

- User can get that shared variables value in another API.

```python
last_output = g.shared.get('lastOutput')
```

## Related

- [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.python.md) · [Custom APIs](https://docs.apimaker.dev/v4/docs/apis-all/custom-apis/user-created-custom-api.python.md)