Wappler does not migrate SQLite database

[franse]

Wappler 7.3.6
Electron
SQLite

I'm having some issues and trying to understand what's going on when change some values on db:

Here are the steps that I make:

1. Create a db and set some int value
   

   

2. You can check the table with this code:

(async () => {
  const { CapacitorSQLite } = Capacitor.Plugins;
  const dbName = 'maindb'; // Change db name here
  await CapacitorSQLite.createConnection({
    database: dbName, version: 999, encrypted: false, mode: 'no-encryption', readonly: false
  });
  await CapacitorSQLite.open({ database: dbName, readonly: false });

  const ver = await CapacitorSQLite.query({ database: dbName, statement: 'PRAGMA user_version;' });
  console.log('DB user_version:', ver.values[0]);

  const schema = await CapacitorSQLite.query({ database: dbName, statement: 'SELECT name, sql FROM sqlite_master WHERE type="table";' });
  console.table(schema.values);

  await CapacitorSQLite.closeConnection({ database: dbName });
})();

3. The schema is defined as:
   

4. On change, save and update:
   

   
   
   

This is what I have on \www\js\connections\testing.js:

dmx.databases = dmx.databases || {};
dmx.databases['testing'] = {
  "version": 2,
  "upgrade": [
    {
      "toVersion": 1,
      "statements": [
        "create table `values` (`id` integer not null primary key autoincrement, `value` integer)"
      ]
    },
    {
      "toVersion": 2,
      "statements": [
        "PRAGMA table_info(`values`)"
      ]
    }
  ]
}

This on migrations:
.wappler\migrations\testing\20251009185638_a.js:

exports.up = function(knex) {
  return knex.schema
    .createTable('values', async function (table) {
      table.increments('id');
      table.integer('value');
    })

};

exports.down = function(knex) {
  return knex.schema
    .dropTable('values')
};

.wappler\migrations\testing\20251009200743_var1.js:

exports.up = function(knex) {
  return knex.schema
    .table('values', async function (table) {
      table.text('value').alter();
    })

};

exports.down = function(knex) {
  return knex.schema
    .table('values', async function (table) {
      table.integer('value').alter();
    })
};

Maybe an alter table is missing on testingdb.json?

PS, also opening database outside Wappler (generated one on capacitor user\capacitor databases)
I can see:

While the original on root folder:

So I'm confused here, I'm doing something wrong?
Thanks

[George]

@franse

Maybe an alter table is missing on testingdb.json?

What kind of alter are you missing because it all looks good to me.

Here is what happens in the background:

The SQLite database file you create in Wappler with its Knex migration is a dev only database.

In the runtime in electron or a mobile app based on capacitor, when the app is started for first time, a local user SQLite database file is created specially for this user.

To create it, and later on update its schema runtime, special sql migration statements are used that are stored in the connection.js sql statements list with a version number to upgrade to.

So those upgrades are done automatically runtime.
The sql statements are derived from the Wappler migration Knex files that are generated and used in Wappler by its own database manager to create and update the dev database file. So they should have the same functionality.

So just note the big difference that the SQLite database file and migrations you are checking in Wappler are dev only and the runtime of your app a whole new database is created for the running user only locally - usually in they documents folder

[franse]

Thanks George

@George

So they should have the same functionality.

This is the part I think it's not working:

@George

and later on update

1. I create a db with text value
2. Create the app
3. Make a modification: value is int now
4. Delete the generated db by electron (stored in c:/users/username/capacitordatabases/databasename.db)
5. New build
6. Open app (I see the table empty)
7. Make an insert
8. Make a query
9. Value is still stored as text

Will record a video, maybe I'm missing a step here

[franse]

Here is the video:

Also changed the type in the flow, no effect:

    sql: {
      type: "insert",
      values: [
        {table: "test", column: "test", type: "number", value: "1"}

Still

[franse]

@George sorry, don't want to be a pain here, but have you seen the video? This looks like a bug, testingdb.json doesn't change at all

[George]

After a deep investigation, we found that this is actually a bug/limitation in the KnexJS library we use.

Because altering the data type of a column in SQLite is not supported by SQLite itself,
Knex does a lot of extra SQL statement to make this possible

However when we ask about those SQL statements it gives us only the first one.

Filed a bug about this:

`toSQL()` method returns incomplete SQL for SQLite3 DDL operations

Will see if we can fix it ourselves and also contribute the fix to Knexjs

[franse]

Oh I got it now..
Yes I'm aware of the extra steps since alter table it's not directly possible..
I thought Wappler doesn't "migrate" the steps into the \js\connections\database.js (and that is used to build the database)
Will try to do some research but not an expert here
Thanks @George once again