Issue with reference fields in db manager - knex referencing wrong db settings

[Hyperbytes]

Not posting as a bug yet as i would prefer to understand what is happening here first.

When doing tutorials i often re-use existing projects but sometimes make some small changes to add a bit of variety

I set up a project using sqlite as a local database (used in orangehost video).
I experienced no problems with the Database Manager

In a later project i switched that to using a cloud (docker) local database (done through interface, no json hacking!) for another video

Again, no problems

Later I switched back to sqlite (via interface) and all seemed to be working as expected, i could add/ remove tables etc without errors

Then I tried adding a reference field and got this error message

it appears Knex is trying to connect to the old (removed) mysqlDBlocal connection rather than the defined sqlite connection.

I have scoured every .json file in the .wappler and app folder and can not find any reference to mysqlDBlocal.

Anyone any idea where knex gets it's connection name from so i can investigate further to ascertain if a bug or me doing something wrong.

Add/ remove tables acts on sqlite table (docker is not actually running at this time), it only appears that the reference fields are effected.

The knex file

exports.up = function(knex) {
  return knex.schema
    .table('mysqlDBlocal.people', async function (table) {
      table.integer('add_ref').unsigned();
      table.foreign('add_ref').references('person_id').inTable('mysqlDBlocal.address');
    })

};

exports.down = function(knex) {
  return knex.schema
    .table('mysqlDBlocal.people', async function (table) {
      table.dropForeign('add_ref');
      table.dropColumn('add_ref');
    })
};

it appears this code should not be there (it is not in any other knex files, for example delete/add tables etc)

 .table('mysqlDBlocal.people'

[George]

Could you just check if the generated Server Connect database connection is correct? You can open it in code editor from the file manager context menu.

And the Wappler direct connection for your Development target is stored in .wappler/targets/Development/databases/_con_name_.json

The .wappler folder is hidden so choose to show hidden files first from the context menu.

So check if this one is also fine.

[Hyperbytes]

Sorry, been out
Looks like that is wrong, somehow not been updated on switch of db targets and schema is incorrect, still shows the old database schema

        "db": {
          "engine": "InnoDB",
          "schema": "mysqlDBlocal",
          "collation": "utf8mb4_general_ci",
          "system_versioned": false
        }

even though head has been updated correctly

  "direct": true,
  "type": "sqlite3",
  "connection": {
    "filename": "/db/mydb.sqlite3"
  },

[George]

well the schema doesn't automatically refresh when you change the database connection settings, you have to refresh it manually from the context menu

[Hyperbytes]

tried that, the schema still remains at the incorrect settings

[George]

oh forgot to mention one important thing:

The Database settings in project targets work only on the default database called "db"

so any additional database connections that you define, you have to manage on your own in the Database manager and in the server connect global database connections

[Hyperbytes]

Only have one connection called db

[George]

then it should be all fine - I was confused because you first show a different connection name "mysqlDBlocal"

so if it is all db - then check the connection settings in the database manager and server connect for the target, should be the same as in the project settings target.

[Hyperbytes]

This is the only place "mysqlDBlocal" appears in any of the .json files

It is the schema associated with the cloud database i was previously using before i changed to sqlite.
Looks like every setting has changed correctly except this one

From memory, sqlite does not have a schema name associated with it?
could it be that if changing from cloud to sqlite, as SQLite has no schema, that setting is not updated resulting in the old setting being persistent?

I will go through the process again of sqlite -> cloudDb -> sqlite again and see if i get the same result

[George]

yes indeed SQLite doesn't have a schema but also MySQL/MariaDB doesn't have ones.

Only Postgres and MS SQL have schemas but usually they are using the default public schema.

So I have no idea how you got this schema prefix.

[Hyperbytes]

OK, just gone through that again

1. created project
2. set db to sqlite
3. changed to cloud db
4. refreshed schema
   All good to there
5. changed back to sqlite
6. refreshed schema

old schema persists

 "db": {
          "engine": "InnoDB",
          "schema": "mysqlDBlocal",
          "collation": "utf8mb4_general_ci",
          "system_versioned": false
        }

it is added when changing the db from sqlite to cloud db and refreshing schema

[George]

aha I see, will see if we can clean this up on schema refresh

[Hyperbytes]

Phew! thought i was going mad

As i changed to cloud db I had the connection file open and as i refreshed the schema I actually saw the file updated and that schema entry added

Problem now is that if i manually remove it i.e.

"schema": ""

it comes back when i refresh!

[famousmag]

This reminds me small issues when removing, renming columns etc... on DB Manager.
It looks like it doesn't clear the data and refresh as wappler is still open (something like that)

If you excuse me a small test..
after the change you made (and save if needed):

1. close the project (!important)
2. quit wappler from the tray icon
3. restart and open your project
   check the changes

*of course this is not a solution but maybe team can understand how to resolve it

[George]

@Hyperbytes

old schema persists

 "db": {
          "engine": "InnoDB",
          "schema": "mysqlDBlocal",
          "collation": "utf8mb4_general_ci",
          "system_versioned": false
        }

it is added when changing the db from sqlite to cloud db and refreshing schema

well I just checked and I actually never get the schema prefix in my changes file....

Just redid your steps and changed an existing mysql database to sqlite and it returned initially empty tables that I could fill in or reapply my previous changes.

But do note that my changes actually never include the schema like in your case:

@Hyperbytes

The knex file

exports.up = function(knex) {
  return knex.schema
    .table('mysqlDBlocal.people', async function (table) {
      table.integer('add_ref').unsigned();
      table.foreign('add_ref').references('person_id').inTable('mysqlDBlocal.address');
    })

};

exports.down = function(knex) {
  return knex.schema
    .table('mysqlDBlocal.people', async function (table) {
      table.dropForeign('add_ref');
      table.dropColumn('add_ref');
    })
};

it appears this code should not be there (it is not in any other knex files, for example delete/add tables etc)

But they are just regular table names:

So maybe you just had an old changes files from some weird postgres database test?

[Hyperbytes]

found if i set the entire {db} setting to null fixes this

 "db": {}

[Hyperbytes]

@George

So maybe you just had an old changes files from some weird postgres database test?

Never use postgres (or mssql)

[Hyperbytes]

@George

well I just checked and I actually never get the schema prefix in my changes file....

i only see this in knex files when setting reference fields

[George]

@Hyperbytes

"db": {}

where is this db setting? Which file and target?