# 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/v4/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/v4/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/v4/docs/apis-all/system-apis/system-generated-create-indexes-api.html#from-code) |

## 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"`).

## From code

`g.sys.system.createIndexes` makes the same call from your code, whatever the settings of this API say about HTTP callers.

### Create indexes

The same request works on MongoDB, MySQL, MariaDB, SQL Server, PostgreSQL and Oracle ; an index which exists already is left alone (`EXISTS`), a field the database can not index is `SKIPPED` with the reason, a refusal of the database is `FAILED` with its message.

**TypeScript**

```typescript
const results = await g.sys.system.createIndexes({
    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,      // default : nothing is created twice
    continueOnError: true,  // default : one refused index does not stop the others
    skipUnindexable: true,  // default : TEXT, json, CLOB... are skipped before the database is asked
});
// results[0] : { status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', fields: ['customer_id', 'status'], ... }
// results[1] : { status: 'EXISTS', message: 'The index "ux_products_sku" exists already.', ... }
```

**Python**

```python
results = await g.sys.system.create_indexes(
    indexes=[
        IndexDefinition(
            'mysql8',
            'inventory',
            table='orders',
            fields=[IndexField('customer_id'), IndexField('status', order='DESC')],
        ),
        IndexDefinition(
            'mongodb',
            'inventory',
            'products',
            name='ux_products_sku',
            unique=True,
            fields=[IndexField('sku')],
        ),
    ],
    if_not_exists=True,  # default : nothing is created twice
    continue_on_error=True,  # default : one refused index does not stop the others
    skip_unindexable=True,  # default : TEXT, json, CLOB... are skipped before the database is asked
)
# results[0] : { status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', fields: ['customer_id', 'status'], ... }
# results[1] : { status: 'EXISTS', message: 'The index "ux_products_sku" exists already.', ... }
```

**PHP**

```php
$results = $g->sys->system->createIndexes(
    indexes: [
        new IndexDefinition(
            'mysql8',
            'inventory',
            table: 'orders',
            fields: [new IndexField('customer_id'), new IndexField('status', order: 'DESC')],
        ),
        new IndexDefinition(
            'mongodb',
            'inventory',
            'products',
            name: 'ux_products_sku',
            unique: true,
            fields: [new IndexField('sku')],
        ),
    ],
    ifNotExists: true, // default : nothing is created twice
    continueOnError: true, // default : one refused index does not stop the others
    skipUnindexable: true, // default : TEXT, json, CLOB... are skipped before the database is asked
);
// results[0] : { status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', fields: ['customer_id', 'status'], ... }
// results[1] : { status: 'EXISTS', message: 'The index "ux_products_sku" exists already.', ... }
```

**C#**

```csharp
var results = await g.Sys.System.CreateIndexes(new CreateIndexes
{
    Indexes = [
        new IndexDefinition
        {
            Instance = "mysql8",
            Database = "inventory",
            Table = "orders",
            Fields = [new IndexField("customer_id"), new IndexField("status") { Order = "DESC" }],
        },
        new IndexDefinition("mongodb", "inventory", "products")
        {
            Name = "ux_products_sku",
            Unique = true,
            Fields = [new IndexField("sku")],
        },
    ],
    IfNotExists = true, // default : nothing is created twice
    ContinueOnError = true, // default : one refused index does not stop the others
    SkipUnindexable = true, // default : TEXT, json, CLOB... are skipped before the database is asked
});
// results[0] : { status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', fields: ['customer_id', 'status'], ... }
// results[1] : { status: 'EXISTS', message: 'The index "ux_products_sku" exists already.', ... }
```

**Go**

```go
results, err := g.Sys.System.CreateIndexes(am.CreateIndexes{
	Indexes: []am.IndexDefinition{
		{
			Instance: "mysql8",
			Database: "inventory",
			Table:    "orders",
			Fields:   []am.IndexField{am.IndexField{Name: "customer_id"}, am.IndexField{Name: "status", Order: "DESC"}},
		},
		{
			Instance:   "mongodb",
			Database:   "inventory",
			Collection: "products",
			Name:       "ux_products_sku",
			Unique:     true,
			Fields:     []am.IndexField{am.IndexField{Name: "sku"}},
		},
	},
	IfNotExists:     am.Ptr(true), // default : nothing is created twice
	ContinueOnError: am.Ptr(true), // default : one refused index does not stop the others
	SkipUnindexable: am.Ptr(true), // default : TEXT, json, CLOB... are skipped before the database is asked
})
if err != nil {
	return nil, err
}
// results[0] : { status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', fields: ['customer_id', 'status'], ... }
// results[1] : { status: 'EXISTS', message: 'The index "ux_products_sku" exists already.', ... }
```

**Rust**

```rust
let results = g.sys.system.create_indexes(
    CreateIndexes::default()
        .indexes([
            IndexDefinition::default()
                .instance("mysql8")
                .database("inventory")
                .table("orders")
                .fields([IndexField::new("customer_id"), IndexField::new("status").order("DESC")]),
            IndexDefinition::new("mongodb", "inventory", "products")
                .name("ux_products_sku")
                .unique(true)
                .fields([IndexField::new("sku")]),
        ])
        .if_not_exists(true) // default : nothing is created twice
        .continue_on_error(true) // default : one refused index does not stop the others
        .skip_unindexable(true), // default : TEXT, json, CLOB... are skipped before the database is asked
)?;
// results[0] : { status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', fields: ['customer_id', 'status'], ... }
// results[1] : { status: 'EXISTS', message: 'The index "ux_products_sku" exists already.', ... }
```

**Java**

```java
var results = g.sys.system.createIndexes(new CreateIndexes()
    .indexes(
        new IndexDefinition()
            .instance("mysql8")
            .database("inventory")
            .table("orders")
            .fields(new IndexField("customer_id"), new IndexField("status").order("DESC")),
        new IndexDefinition("mongodb", "inventory", "products")
            .name("ux_products_sku")
            .unique(true)
            .fields(new IndexField("sku"))
    )
    .ifNotExists(true) // default : nothing is created twice
    .continueOnError(true) // default : one refused index does not stop the others
    .skipUnindexable(true)); // default : TEXT, json, CLOB... are skipped before the database is asked
// results[0] : { status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', fields: ['customer_id', 'status'], ... }
// results[1] : { status: 'EXISTS', message: 'The index "ux_products_sku" exists already.', ... }
```

**Kotlin**

```kotlin
val results = g.sys.system.createIndexes(
    CreateIndexes()
        .indexes(
            IndexDefinition()
                .instance("mysql8")
                .database("inventory")
                .table("orders")
                .fields(IndexField("customer_id"), IndexField("status").order("DESC")),
            IndexDefinition("mongodb", "inventory", "products")
                .name("ux_products_sku")
                .unique(true)
                .fields(IndexField("sku")),
        )
        .ifNotExists(true) // default : nothing is created twice
        .continueOnError(true) // default : one refused index does not stop the others
        .skipUnindexable(true), // default : TEXT, json, CLOB... are skipped before the database is asked
)
// results[0] : { status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', fields: ['customer_id', 'status'], ... }
// results[1] : { status: 'EXISTS', message: 'The index "ux_products_sku" exists already.', ... }
```

### An index of a tenant

- Without a `name`, the index is called `am_ix_<table>_<fields>_<hash>` (`am_ixu_` when unique), the same in every environment, cut to the limit of the database (30 characters on Oracle).
- `order` : `ASC`, `DESC`, `HASHED` and `TEXT` (MongoDB) ; `nullsFirst: true` on PostgreSQL. `type` : `FULLTEXT` or `SPATIAL` (MySQL), `CLUSTERED`, `XML`, `SPATIAL` (SQL Server), `hash`, `gin`, `gist`, `spgist`, `brin` (PostgreSQL). `method` : `BTREE`, `HASH`, `RTREE` (MySQL).
- This is the call the migration script `am_index_maker` of [Index Maker](https://docs.apimaker.dev/v4/extensions/index_maker/automatic_indexes.html) makes on deploy : a migration script of your own can do the same.

- `instance: "crm::acme"` creates the index in the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.html) instance `crm`. In a custom API called for a tenant, `instance: "crm"` creates it in the database of that tenant.

**TypeScript**

```typescript
await g.sys.system.createIndexes({
    indexes: [{ instance: 'crm::acme', database: 'crm', collection: 'customers', fields: [{ name: 'email' }], unique: true }],
});
```

**Python**

```python
await g.sys.system.create_indexes(
    indexes=[IndexDefinition('crm::acme', 'crm', 'customers', fields=[IndexField('email')], unique=True)],
)
```

**PHP**

```php
$g->sys->system->createIndexes(
    indexes: [new IndexDefinition('crm::acme', 'crm', 'customers', fields: [new IndexField('email')], unique: true)],
);
```

**C#**

```csharp
await g.Sys.System.CreateIndexes(new CreateIndexes
{
    Indexes = [new IndexDefinition("crm::acme", "crm", "customers") { Fields = [new IndexField("email")], Unique = true }],
});
```

**Go**

```go
if _, err := g.Sys.System.CreateIndexes(am.CreateIndexes{
	Indexes: []am.IndexDefinition{am.IndexDefinition{Instance: "crm::acme", Database: "crm", Collection: "customers", Fields: []am.IndexField{am.IndexField{Name: "email"}}, Unique: true}},
}); err != nil {
	return nil, err
}
```

**Rust**

```rust
g.sys.system.create_indexes(
    CreateIndexes::default()
        .indexes([IndexDefinition::new("crm::acme", "crm", "customers").fields([IndexField::new("email")]).unique(true)]),
)?;
```

**Java**

```java
g.sys.system.createIndexes(new CreateIndexes()
    .indexes(new IndexDefinition("crm::acme", "crm", "customers").fields(new IndexField("email")).unique(true)));
```

**Kotlin**

```kotlin
g.sys.system.createIndexes(
    CreateIndexes()
        .indexes(IndexDefinition("crm::acme", "crm", "customers").fields(IndexField("email")).unique(true)),
)
```

## Access and settings

- Over HTTP, a system API answers once its [settings](https://docs.apimaker.dev/v4/docs/settings/systemApiSettings.html) give it `apiAccessType: TOKEN_ACCESS` (the token of an API user whose [group](https://docs.apimaker.dev/v4/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/v4/docs/apis-all/hooks/preHook-api.html) run around it like around any API.
- The [request headers](https://docs.apimaker.dev/v4/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/v4/docs/apis-all/overview.html) · [Response format](https://docs.apimaker.dev/v4/docs/apis-all/response-format.html) · [System API settings](https://docs.apimaker.dev/v4/docs/settings/systemApiSettings.html) · [System APIs from code](https://docs.apimaker.dev/v4/examples/sys/system/system.html)