How to add GIN index using Knex.js

Question

I have been trying to add a GIN index using Knex.js on one of the column in my table, but I can not achieve this. I am trying to do something like this-

exports.up = function(knex, Promise) {
    return knex.schema.createTable('prediction_models', function(table){
            table.increments('id');
            table.string('model_name').index();
            table.jsonb('model_params');
            table.timestamp('createdAt').defaultTo(knex.fn.now());
        })
        .then(createGinIndex());

    function createGinIndex() {
        return knex.raw('CREATE INDEX modelgin ON public.prediction_models USING GIN (model_params);');
    }
};

exports.down = function(knex, Promise) {
    return knex.schema.dropTable('prediction_models');
};

I am using PostgreSQL database. Can anyone please tell me if this is the correct way to implement this? If yes what's wrong with my code and if not how to implement it? Thanks!

Answer 1

Those looking for a more "knexy" solution to this problem: it's not necessary to use .raw, as in Mikael Lepistö's solution. The .index chainable accepts a 2nd parameter called indexType. It can be used like so:

knex.schema.createTable('table_name', table => {
  table.jsonb('column_name').index(null, 'GIN')
})

(the null on the first parameter is me saying "name this index for me")

This piece of code will produce the following queries:

create table "table_name" ("column_name" jsonb);
create index "table_name_column_name_index" on "table_name" using GIN ("column_name")

Currently I'm creating such an index but, for performance reasons, I want it to only support the jsonb_path_ops operator class (as per section 8.14.4 of the documentation). This requires a create index statement ending in using GIN ("column_name" jsonb_path_ops). As such, I'll need to use a raw but there are plenty of use-cases where .index is sufficient.

Answer 2

You should be able to create full GIN index like this:

CREATE INDEX on prediction_models USING GIN (model_params)

More info about creating indexes for jsonb columns can be found from end of https://www.vincit.fi/en/blog/objection-js-postgresql-power-json-queries/

You can print out the queries ran by migration like this (http://runkit.com/embed/8fm3z9xzjz9b):

var knex = require("knex")({ client: 'pg' });
const queries = knex.schema.createTable('prediction_models', function (table){
    table.increments('id');
    table.string('model_name').index();
    table.jsonb('model_params');
    table.timestamp('createdAt').defaultTo(knex.fn.now());
}).raw('CREATE INDEX on prediction_models USING GIN (model_params)')
.toSQL();

queries.forEach(toSql => console.log(toSql.sql));

And copy-paste them to psql:

mikaelle=# begin;
BEGIN
mikaelle=# create table "prediction_models" ("id" serial primary key, "model_name" varchar(255), "model_params" jsonb, "createdAt" timestamptz default CURRENT_TIMESTAMP);
CREATE TABLE
mikaelle=# create index "prediction_models_model_name_index" on "prediction_models" ("model_name");
CREATE INDEX
mikaelle=# CREATE INDEX on prediction_models USING GIN (model_params);
CREATE INDEX
mikaelle=# commit;
COMMIT
mikaelle=#
mikaelle=# \d prediction_models
                                     Table "public.prediction_models"
    Column    |           Type           |                           Modifiers                            
--------------+--------------------------+----------------------------------------------------------------
 id           | integer                  | not null default nextval('prediction_models_id_seq'::regclass)
 model_name   | character varying(255)   | 
 model_params | jsonb                    | 
 createdAt    | timestamp with time zone | default now()
Indexes:
    "prediction_models_pkey" PRIMARY KEY, btree (id)
    "prediction_models_model_name_index" btree (model_name)
    "prediction_models_model_params_idx" gin (model_params)

mikaelle=#