How to manually add indexes in SQLite with knex?

Wappler General Need Help
1

Does anyone know how to set up indexes for mobile projects that are using SQLite?

I tried creating an additional knex js file under .wappler > migrations in hopes that it would be used during the build of the electron app, but it didn't appear to have any effect.

Here's the code I put in the file.

exports.up = function(knex) {
  return knex.schema.alterTable('Email', function(table) {
    // Add a single-column index for GmailThreadId
    table.index('GmailThreadId', 'idx_email_gmailthreadid');

    // Add a composite index for GmailThreadId and GmailInternalDate
    table.index(['GmailThreadId', knex.raw('GmailInternalDate DESC')], 'idx_email_threadid_date_desc');
  });
};

exports.down = function(knex) {
  return knex.schema.alterTable('Email', function(table) {
    // Drop the single-column index
    table.dropIndex('GmailThreadId', 'idx_email_gmailthreadid');

    // Drop the composite index
    table.dropIndex(['GmailThreadId', knex.raw('GmailInternalDate DESC')], 'idx_email_threadid_date_desc');
  });
};
2

Pinging @Apple, as it looks like you've got some experience with similar issues.

3

I'm afraid I don't know how migrations work in Electron, so I can't provide guidance troubleshooting why it isn't running

4

I created a custom knex migration file and for whatever reason it doesn't appear to get applied. I don't think there's a difference in Wappler's knex intergration between mobile and web projects.

5
  1. Have you restarted Wappler?
  2. Can you show the filename of the migration you created?
  3. Can you show the filename of a working migration?
6

I think the issue is the custom file I added in .wappler/migrations is not reflected in the wappler_migrations table. @JonL had the same issue Database Manager: create manual migration - #4 by kfawcett

Seems like I need to modify one of the Wappler created migration files or go the complex process that Jon outlined.

image
image540×697 8.87 KB

image
image954×605 11.7 KB

7

I think the issue is the custom file I added in .wappler/migrations is not reflected in the wappler_migrations table. @JonL had the same issue Database Manager: create manual migration - #4 by kfawcett

Seems like I need to modify one of the Wappler created migration files since there isn't a built in way to generate the record in the wappler_migrations table.

image
image540×697 8.87 KB

image
image954×605 11.7 KB

8

As far as I know, the row is created in wappler_migrations table the first time you run the migration - it's not the source of migration listing. I could be wrong though so I don't bet on it.

Do you confirm you've restarted Wappler and hit whatever refresh button you found?

9

I don't know exactly how Wappler processes, but Jon's steps were the only way I was able to get it to work.

  1. Make some database change in Database Manager to create a migration file and add a record in wappler_migrations table.
  2. Go to the Change section and "Undo this Change"
    image
  3. Open the migration file that was created and change the code to whatever custom code needed and save the file.
  4. Go back to the change and click "Appy this Change" (that option is not currently showing in the menu since I've already reapplied the change)
    image

It's only after following these steps that the custom code (e.g. creating indexes) gets applied to the database.

1 Like
10

Yes indeed this is currently needed because the changes are executed directly so you can’t edit them first.

I’ll see if we can add option for manual migration file creation or maybe add an option on save changes to execute them or not directly ( so you can edit them first.

Furthermore changes are just regular Knex migration files, so you can do all things in them as described on the Knex docs

1 Like