System information
- Node.js version: 18.16.0
- NPM version: 9.5.1
- Strapi version: 4.10.1
- Database: MySQL
- Operating system: linux/mac os
- Is your project Javascript or Typescript: Javascript
Bug Description
I am creating custom indexes in my db in my src/index.js like this:
const knex = strapi.db.connection;
await knex.schema.alterTable(my_table, async (t) => {
t.index(my_columns, my_index);
});
I’ve to comment out this code after creating indexes as on server restart it will give duplicate index error.
But, if the server is stopped for few minutes, after starting all my indexes from the db are deleted. So, every time while pushing new feature to production I’ve to uncomment the code make the indexes, then start the server and again comment the code then start.
One temporary solution for this problem is checking if index already exists, every time server is restarted:
const knex = strapi.db.connection;
const hasIndex = async (tableName, indexName) => {
const [rows] = await knex.raw(
`SHOW INDEX FROM \`${tableName}\` WHERE Key_name = '${indexName}'`
);
return rows.length > 0;
};
if (!(await hasIndex(my_table, my_index))) {
await knex.schema.alterTable(my_table, async (t) => {
t.index(my_columns, my_index);
});
}
I’ve to create a separate config file which exports all the table names with their indexes configuration.
But, this approach is not suitable as I have more than a million records in each table and checking & creating index this way may take hours, which is not suitable for production environment.
Does this mean strapi is not suitable for production? Or is there a workaround?