# Get All API (generated)

> Read the rows of a table with the generated get all API of API Maker - filter with find or any column, page with skip and limit, sort, select fields, populate related rows with deep, and get the total count.

Source: https://docs.apimaker.dev/v1/docs/apis-all/generated-apis/auto-generated-get-all-api.html

Reads rows of a table. Without a query param it returns every row ; with `find`, `skip`, `limit`, `sort`, `select`, `deep` and `getTotalCount` it becomes the list API of your screens.

| | |
|---|---|
| Method | GET |
| URL | `/api/gen/admin/mysql8/inventory/customers` |
| Body | none |
| Query params | [find](https://docs.apimaker.dev/v1/docs/apis-all/query-params/find.html), [skip](https://docs.apimaker.dev/v1/docs/apis-all/query-params/skip.html), [limit](https://docs.apimaker.dev/v1/docs/apis-all/query-params/limit.html), [sort](https://docs.apimaker.dev/v1/docs/apis-all/query-params/sort.html), [select](https://docs.apimaker.dev/v1/docs/apis-all/query-params/select.html), [deep](https://docs.apimaker.dev/v1/docs/apis-all/query-params/deep.html), [getTotalCount](https://docs.apimaker.dev/v1/docs/apis-all/query-params/getTotalCount.html), any column |
| Answer | `data` : an array of rows, `totalCount` with `getTotalCount=true` |
| Databases | MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona |
| Cached | yes, with `enableCaching` on the table |
| From code | [`g.sys.db.gen.getAllGen`](https://docs.apimaker.dev/v1/examples/sys/db/gen/getAllGen.html) |
| API id | `GEN_GET_ALL` (groups, settings, hooks, WebSocket subscriptions) |
| The other family | [Get all as a schema API](https://docs.apimaker.dev/v1/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-api.html) |

> **Schemaless : the data passes as it is**
>
> The generated APIs never read the [schema](https://docs.apimaker.dev/v1/docs/schema/schema.html) of the table. Nothing is converted, validated, defaulted, encrypted or numbered : what you send is what the database gets, and a filter matches the type you send (`"22"` does not match the number `22`). On MongoDB, strings which look like ObjectIds are converted. Every table has these APIs, with or without a schema ; the same operations under `/api/schema` apply the schema.

## The URL

`/api/gen/admin/mysql8/inventory/customers` : replace `admin` with the user path of your account, `mysql8` with your instance, `inventory` with the database and `customers` with the table. The examples use this table :

| customer_id | first_name | last_name | last_update           | pincode | isActive |
|-------------|------------|-----------|-----------------------|---------|----------|
| 1           | Bob        | lin       | 2022-11-14 04: 34: 58 | 382345  | 1        |
| 2           | Alice      | Page      | 2022-10-15 02: 10: 40 | 382346  | 1        |
| 3           | Mallory    | Brown     | 2022-09-13 03: 44: 05 | 382347  | 1        |
| 4           | Eve        | Mathly    | 2022-11-12 01: 59: 33 | 382348  | 1        |
| 5           | Eve        | Page      | 2022-11-12 01: 59: 33 | 382349  | 1        |

## Every row

```text
GET /api/gen/admin/mysql8/inventory/customers
```

```json
{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 1, "first_name": "Bob", "last_name": "lin", "last_update": "2022-11-14T04:34:58.000Z", "pincode": 382345, "isActive": 1 },
        { "customer_id": 2, "first_name": "Alice", "last_name": "Page", "last_update": "2022-10-15T02:10:40.000Z", "pincode": 382346, "isActive": 1 }
    ]
}
```

- Without `limit`, every row comes back. Add `limit` as soon as the table can grow.

## Filter with a column name

Any column is a query param. The operators of [api-query-params](https://github.com/loris/api-query-params) work in the key : `>`, `>=`, `<`, `<=`, `!=`, a comma for a list, a regex between slashes.

```text
GET /api/gen/admin/mysql8/inventory/customers?customer_id=1
GET /api/gen/admin/mysql8/inventory/customers?first_name=Bob&last_name=lin          # both must match
GET /api/gen/admin/mysql8/inventory/customers?first_name=Bob,Alice                  # one of them
GET /api/gen/admin/mysql8/inventory/customers?pincode>=382346
GET /api/gen/admin/mysql8/inventory/customers?first_name!=Bob
GET /api/gen/admin/mysql8/inventory/customers?first_name=/^ma/i                     # regular expression
```

- Numbers, booleans and ISO dates in a query param are cast to their type before the query.

## Filter with find

`find` takes a JSON5 object with the MongoDB operators, on every database : `$eq`, `$ne`, `$gt`, `$gte`, `$lt`, `$lte`, `$in`, `$nin`, `$and`, `$or`, `$not`, `$like`, `$regex`, `$isNull`. See [find](https://docs.apimaker.dev/v1/docs/apis-all/query-params/find.html).

```text
GET /api/gen/admin/mysql8/inventory/customers?find={first_name:'Bob'}
GET /api/gen/admin/mysql8/inventory/customers?find={customer_id:{$in:[1,2,3]}}
GET /api/gen/admin/mysql8/inventory/customers?find={$or:[{pincode:382345},{first_name:'Alice'}]}
GET /api/gen/admin/mysql8/inventory/customers?find={first_name:{$like:'Ma%'}}
```

## Page and sort

```text
GET /api/gen/admin/mysql8/inventory/customers?skip=20&limit=10&sort=-last_update,first_name
```

- `sort` takes column names separated by commas, `-` for descending. The JSON form `sort={last_update:-1}` works too.
- `skip` and `limit` are numbers. `limit` caps the rows of the answer ; `skip` leaves out the first rows.

## Select fields

```text
GET /api/gen/admin/mysql8/inventory/customers?select=first_name,last_name       # only these (the primary key stays)
GET /api/gen/admin/mysql8/inventory/customers?select=-last_update,-isActive     # everything but these
```

## Related rows with deep

`deep` replaces an id with the row it points to, from any table of any instance, as many levels as you want. See [deep](https://docs.apimaker.dev/v1/docs/apis-all/query-params/deep.html).

```text
GET /api/gen/admin/mysql8/inventory/customers?deep=[{s_key:'customer_id',t_col:'orders',t_key:'customer_id',isMultiple:true,select:'order_no,total'}]
```

```json
{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 1, "first_name": "Bob", "last_name": "lin", "orders": [ { "order_no": 1001, "total": 12900 } ] }
    ]
}
```

## Total count

```text
GET /api/gen/admin/mysql8/inventory/customers?find={isActive:1}&skip=0&limit=10&getTotalCount=true
```

```json
{ "success": true, "statusCode": 200, "data": [ "…10 rows…" ], "totalCount": 5 }
```

- `totalCount` counts every row matching the filter, before `skip` and `limit` : what a pager needs.

## Everything together

```text
GET /api/gen/admin/mysql8/inventory/customers?find={first_name:'Bob'}&skip=1&limit=4&sort=-customer_id&select=first_name&deep=[{s_key:'customer_id',t_col:'orders',t_key:'customer_id'}]&getTotalCount=true
```

## Caching, hooks, notifications

- With [caching](https://docs.apimaker.dev/v1/docs/features/automatic-caching.html) on for the table, the answer comes from Redis until a write through API Maker changes the table ; the response header `x-am-data-source` says `cache` or `api`, and `x-am-cache-control: reset_cache` forces the database.
- [Pre hooks](https://docs.apimaker.dev/v1/docs/apis-all/hooks/preHook-api.html) can add conditions to `g.req.query.find` (for example the rows of the signed-in person) and post hooks can reshape `g.res.output`. When the answer comes from the cache, the hooks do not run.
- Clients subscribed to this table with [WebSocket events](https://docs.apimaker.dev/v1/docs/pages/web-socket-event-page.html) are notified after a successful call.

## From your code

[`g.sys.db.gen.getAllGen`](https://docs.apimaker.dev/v1/examples/sys/db/gen/getAllGen.html) makes the same call from custom APIs, hooks, events, schedulers and test cases, with the same headers and params. Pass `true` as the second argument to get the whole [response envelope](https://docs.apimaker.dev/v1/docs/apis-all/response-format.html) instead of `data`.

## Headers

Every call takes the [request headers](https://docs.apimaker.dev/v1/docs/apis-all/header/requestHeader.html) : the tokens, `x-am-response-case`, `x-am-content-type-response` (JSON, XML, YAML…), `x-am-response-object-type: make_flat`, `x-am-cache-control`, `x-am-get-encrypted-data`, `x-am-internationalization`, `x-am-tenant-username`, `x-am-meta`.

## Errors

| Code | When |
|---|---|
| `401` | The token in `x-am-authorization` is missing or invalid. |
| `403` | No group of the API user grants this API of this table, or a field of the request. |
| `404` | The instance, database or table of the URL does not exist. |
| `400` | The query or the body is wrong : the [error messages](https://docs.apimaker.dev/v1/docs/apis-all/error-codes.html) say which key and why. |

## Related

- [All APIs at a glance](https://docs.apimaker.dev/v1/docs/apis-all/overview.html) · [Query params](https://docs.apimaker.dev/v1/docs/apis-all/query-params/query-params.html) · [Response format](https://docs.apimaker.dev/v1/docs/apis-all/response-format.html) · [Pre hooks](https://docs.apimaker.dev/v1/docs/apis-all/hooks/preHook-api.html) and [post hooks](https://docs.apimaker.dev/v1/docs/apis-all/hooks/postHook-api.html) · [Automatic caching](https://docs.apimaker.dev/v1/docs/features/automatic-caching.html)