CODEWITHSUNDEEP
sequelize.js
user938363
user938363

Reputation: 10350

sequelize: Indexes not generated except for one table

In my nodejs app, this is how the db is generated with sequelizejs (4.42.0):

db.sync({force: true});

There are 5 tables in the db and the table is generated fine but not the indexes. There is only indexes generated for one table event and there is no indexes at all for 4 other tables. Here is the cmd output:

Executing (default): DROP TABLE IF EXISTS "events" CASCADE;
Executing (default): DROP TABLE IF EXISTS "socketlists" CASCADE;
Executing (default): DROP TABLE IF EXISTS "veriflogs" CASCADE;
Executing (default): DROP TABLE IF EXISTS "users" CASCADE;
Executing (default): DROP TABLE IF EXISTS "users" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER , "name" VARCHAR(255) NOT NULL, "email" VARCHAR(255), "cell" VARCHAR(255) NOT NULL, "cell_country_code" VARCHAR(255) NOT NULL, "comp_name" VARCHAR(255), "status" VARCHAR(255) NOT NULL, "role" VARCHAR(255) NOT NULL, "device_id" VARCHAR(255), "last_updated_by_id" INTEGER, "fort_token" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE, "updatedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "veriflogs" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "veriflogs" ("id" INTEGER , "cell" VARCHAR(255), "cell_country_code" VARCHAR(255), "vcode" VARCHAR(255), "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'veriflogs' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "socketlists" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "socketlists" ("id" INTEGER , "user_id" INTEGER, "socket_id" VARCHAR(255), "event_id" INTEGER, "server_id" VARCHAR(255), "active" BOOLEAN DEFAULT true, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'socketlists' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "events" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "events" ("id" INTEGER , "name" VARCHAR(255) NOT NULL, "created_by_id" INTEGER NOT NULL, "event_info" JSONB, "current_step" VARCHAR(255), "access_list" INTEGER[], "event_step" VARCHAR(255)[], "event_snapshot" JSON, "event_category_id" INTEGER, "status" VARCHAR(255), "last_updated_by_id" INTEGER, "fort_token" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE, "updatedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'events' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE INDEX "events_name" ON "events" ("name")
Executing (default): CREATE INDEX "events_status" ON "events" ("status")
Executing (default): CREATE INDEX "events_fort_token" ON "events" ("fort_token")
Executing (default): CREATE INDEX "events_event_category_id" ON "events" ("event_category_id")
Executing (default): CREATE INDEX "events_created_by_id" ON "events" ("created_by_id")
Executing (default): DROP TABLE IF EXISTS "messages" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "messages" ("id" INTEGER , "event_id" INTEGER, "sender_id" INTEGER, "data" JSONB NOT NULL, "fort_token" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'messages' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;

What could cause the missing of indexes for other 4 tables? Here is the definition of event and user:

const Event = db.define('event', {
    id: {type: Sql.INTEGER,
         primaryKey:true,
         min: 1},
    name: {type: Sql.STRING,
           min:2,
           allowNull: false,
        }, 
    created_by_id: {type: Sql.INTEGER,
              allowNull: false
            },
    event_info: {type: Sql.JSONB},
    current_step: {type: Sql.STRING},
    access_list: {type: Sql.ARRAY(Sql.INTEGER),
            },
    event_step: {type: Sql.ARRAY(Sql.STRING)},
    event_snapshot: {type: Sql.JSON},
    event_category_id: {type: Sql.INTEGER,
            },
    status: {type: Sql.STRING,
             isIn: ['active', 'cancelled', 'aborted', 'suspended', 'completed']},
    last_updated_by_id: {type: Sql.INTEGER},
    fort_token: {type: Sql.STRING,
                 allowNull: false,
                },
    createdAt: Sql.DATE,
    updatedAt: Sql.DATE
}, {
    indexes: [ {
        fields: ['name']
    },
    {
        fields: ['status']
    },
    {
        fields: ['fort_token']
    },{
        fields: ['event_category_id']
    }, {
        fields: ['created_by_id']
    } 
    ]
}); 

const User = db.define('user', {
    id: {type: Sql.INTEGER,
         primaryKey:true,
         min: 1},
    name: {type: Sql.STRING,
           allowNull: false,
           min: 2,
           max: 50,
    },
    email: {type: Sql.STRING,
            isEmail: true
    },      
    cell: {type: Sql.STRING,
            allowNull: false,
            min: 10,
            max: 20,
    },
    cell_country_code: {type: Sql.STRING,
                        allowNull: false,
    },
    comp_name: {type: Sql.STRING
    },
    status: {type: Sql.STRING,
             allowNull: false,
             isIn: ['active', 'blocked', 'inactive', 'pending']
    },
    role: {type: Sql.STRING,
           allowNull: false
    },
    device_id: {type: Sql.STRING,   //maybe empty when the user is initially created.
    },
    last_updated_by_id: {type: Sql.INTEGER},
    fort_token: {type: Sql.STRING,
                 allowNull: false,
                 min: 20  //64 for production
    },
    createdAt: Sql.DATE,
    updatedAt: Sql.DATE
  }, {
    validate: {async custom_validate() {
      let user = await User.findOne({where:{name :this.name, fort_token: this.fort_token}});
      if (user) throw new Error("用户重名！"); 
      let cell = await User.findOne({where: {cell: this.cell, cell_country_code: this.cell_country_code, status: 'active'}});
      if (cell) throw new Error("手机号已经注册使用！");
    }}
  }, { 
    indexes: [
      { 
        //For same fort_token, name to be unique
        unique: true,
        fields: ['name', 'fort_token']
      }, {
        //unique cell
        unique: true,
        fields: ['cell_country_code', 'cell', 'status']
      }, {
        fields: ['cell_country_code', 'cell']
      }, {
        //email
        fields: ['email']
      }, {
        fields: ['device_id']
      }, {
        fields: ['status']
      }, {
        fields: ['fort_token']
      }
    ]   

  });

Upvotes: 0

Views: 723

Answers (1)

5ar
5ar

Reputation: 2210

Put the indexes property in the options object, the same as you did in the Event model.

define has only three parameters define(modelName, attributes, options), but in the Users model you added a fourth one and extra parameters just get ignored in JavaScript if not handled. Just move the indexes in the same object as validate, i.e. do:

const User = db.define('user', {
   ...
}, {
   validate: {...}
   indexes: [...]
});

Upvotes: 1

Related Questions