# Create Indexes API

> Create indexes on any database of the account with one system API of API Maker - the same request for MongoDB, MySQL, MariaDB, SQL Server, PostgreSQL and Oracle, safe to run again and again.

Source: https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-create-indexes-api.html

Creates one or more indexes with the same request on every database. An index which exists already is left alone, a field the database can not index is skipped with the reason, and the call can run again and again : it is what [Index Maker](https://docs.apimaker.dev/v1/extensions/index_maker/introduction.html) runs from its migration script `am_index_maker` on deploy.

| | |
|---|---|
| Method | POST |
| URL | `/api/system-api/admin/create-indexes` : `admin` is the user path of your account |
| Body | `{ indexes: [ … ], ifNotExists?, continueOnError?, skipUnindexable? }` |
| Answer | `data` : one result per definition, in order |
| From code | [`g.sys.system.createIndexes`](https://docs.apimaker.dev/v1/examples/sys/system/createIndexes.html) |

## The call

**Body**

```json
{
    "indexes": [
        { "instance": "mysql8", "database": "inventory", "table": "orders", "fields": [ { "name": "customer_id" }, { "name": "status", "order": "DESC" } ] },
        { "instance": "mongodb", "database": "inventory", "collection": "products", "name": "ux_products_sku", "unique": true, "fields": [ { "name": "sku" } ] }
    ],
    "ifNotExists": true,
    "continueOnError": true,
    "skipUnindexable": true
}
```

| Property | Required | Meaning |
|---|---|---|
| `indexes` | yes | The definitions, one per index. |
| `indexes[].instance` | yes | Instance name. `crm::acme` names the tenant `acme` of the multi-tenant instance `crm` ; in a call made for a tenant, the plain name is enough. |
| `indexes[].database` | yes | Database name, as the account sees it. |
| `indexes[].collection` or `table` | yes | The table. PostgreSQL : `public.orders`, SQL Server : `dbo.orders`. |
| `indexes[].fields` | yes | `[{ name, order?, nullsFirst? }]`. `order` : `ASC` (default), `DESC`, `HASHED` (MongoDB), `TEXT` (MongoDB). `nullsFirst` : PostgreSQL. |
| `indexes[].name` | no | Index name. Missing : `am_ix_<table>_<fields>_<hash>` (`am_ixu_` when unique), cut to the limit of the database. |
| `indexes[].unique` | no | A unique index. |
| `indexes[].type` | no | MySQL : `FULLTEXT`, `SPATIAL` ; SQL Server : `CLUSTERED`, `XML`, `SPATIAL` ; PostgreSQL : `hash`, `gin`, `gist`, `spgist`, `brin`. |
| `indexes[].method` | no | MySQL : `BTREE`, `HASH`, `RTREE`. |
| `ifNotExists` | no, `true` | An index with the same name, or with the very same fields under another name, is `EXISTS` : nothing is created twice. |
| `continueOnError` | no, `true` | An index the database refuses is `FAILED` in the results and the next ones go on. `false` : the call fails at the first refusal. |
| `skipUnindexable` | no, `true` | A field of a type no plain index can hold (TEXT on MySQL, json on PostgreSQL, CLOB on Oracle, `varchar(max)` on SQL Server, a primary key…) is `SKIPPED` with the reason, before the database is asked. `false` : the database decides. |

## The answer

```json
{
    "success": true,
    "statusCode": 200,
    "data": [
        { "instance": "mysql8", "database": "inventory", "collection": "orders", "name": "am_ix_orders_customer_id_status_3f9a1c", "fields": [ "customer_id", "status" ], "status": "CREATED" },
        { "instance": "mongodb", "database": "inventory", "collection": "products", "name": "ux_products_sku", "fields": [ "sku" ], "status": "EXISTS", "message": "The index \"ux_products_sku\" exists already." }
    ]
}
```

| Status | Meaning |
|---|---|
| `CREATED` | The index was created, and logged in the index logs of Index Maker. |
| `EXISTS` | An index with this name, or with the same fields, is there already. |
| `SKIPPED` | A field can not go in a plain index of this database ; `message` says which and why. |
| `FAILED` | The database refused ; `message` is its error. |

## Good to know

- Every database is supported : MongoDB, MySQL, MariaDB, SQL Server, PostgreSQL and Oracle. The statements are the plain `CREATE INDEX` of each one, so they run on every version.
- The API changes the database : the APIs Security Report lists it with the sensitive system APIs. Give it only to the groups that need it.
- Every index created is logged with its source (`SYSTEM_API`, or `MIGRATION_SCRIPT` when the body says `"source": "MIGRATION_SCRIPT"`).

## Access and settings

- Over HTTP, a system API answers once its [settings](https://docs.apimaker.dev/v1/docs/settings/systemApiSettings.html) give it `apiAccessType: TOKEN_ACCESS` (the token of an API user whose [group](https://docs.apimaker.dev/v1/docs/apis-security/api-group-permission.html) grants this system API, in `x-am-authorization`) or `IS_PUBLIC`. Without settings it is `NO_ACCESS` : your code calls it through `g.sys`, the admin panel tests it, and an HTTP call is refused.
- The settings can also cache the answer or require person tokens (`authProviders`) ; [pre and post hooks](https://docs.apimaker.dev/v1/docs/apis-all/hooks/preHook-api.html) run around it like around any API.
- The [request headers](https://docs.apimaker.dev/v1/docs/apis-all/header/requestHeader.html) apply : `x-am-response-case`, `x-am-content-type-response`, `x-am-internationalization`, `x-am-tenant-username`…

## Errors

| Code | When |
|---|---|
| `400` | The body is wrong : the message names the missing or invalid key. |
| `401` | No valid API user token, or the API is `NO_ACCESS` : `You are not authorized to access this API.` |
| `403` | No group grants this system API. |

## Related

- [All APIs at a glance](https://docs.apimaker.dev/v1/docs/apis-all/overview.html) · [Response format](https://docs.apimaker.dev/v1/docs/apis-all/response-format.html) · [System API settings](https://docs.apimaker.dev/v1/docs/settings/systemApiSettings.html) · [System APIs from code](https://docs.apimaker.dev/v1/examples/sys/system/system.html)