Skip to content

Create indexes

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