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

Create indexes

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 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

The call

Body
{
    "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

{
    "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 give it apiAccessType: TOKEN_ACCESS (the token of an API user whose group 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 run around it like around any API.
  • The request headers 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.