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

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.', ... }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.', ... }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$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.', ... }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.', ... }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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.', ... }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
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.', ... }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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.', ... }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
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 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 instance crm. In a custom API called for a tenant, instance: "crm" creates it in the database of that tenant.

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

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

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

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

1
2
3
4
5
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
}

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

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

1
2
3
4
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 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.