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

Create indexes

g.sys.system.createIndexes calls the create indexes system API from your code, whatever the settings of that API say about HTTP callers.

Create indexes System API

Create indexes using global object 'g'. 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.', ... }
  • 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.

Multi-tenant

  • 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 }],
});