Truly Dynamic DB connection parameters

Wappler General
1

For quite a long time I have used a db query to set the DB connection - this could be, for example, to connect to a user's company data which is in a separate DB to other companies. It has worked for PHP and Node but on a new project, using the latest modules it seems to have a different behaviour.

The user logs in and a query is run in Globals to find the company info - the dynamic DB connection is set to point to that user's company connection settings from that point on. Because it is in Globals it applies to all dynamic connections they run. Users can also be a user on more than one company so can switch and load different DB connection settings which then should be used by dynamic connections from that point.

Using 6.7.1 on NodeJS (stable) the dynamic connection is made but it seems that the setting used by the first connection are used for the entire service and not changed until the server is restarted. This means that they aren't really dynamic but allow data binding to some fixed value

Could the connection properties be evaluated on use and if different than previous, that connection is closed and a new one opened?

This is a sample DB connection file:

image
image571×756 29.5 KB

Which equates to this in code view:

{
  "name": "dyn_db",
  "module": "dbconnector",
  "action": "connect",
  "options": {
    "client": "mysql2",
    "connection": {
      "host": "{{get_dbcreds.dba}}",
      "port": 3306,
      "user": "{{get_dbcreds.dbu}}",
      "password": "{{get_dbcreds.dbpw}}",
      "database": "{{get_dbcreds.dbn}}",
      "charset": "utf8mb4",
      "ssl": {
        "rejectUnauthorized": false
      }
    }
  },
  "serverType": "node",
  "fileName": "dyn_db.json"
}
2

looks like this values are "cashed" and changes are ignored on 6.7.1?

3

What a fascinating read!

The practice of opening and closing DB connections per HTTP request is usually only present in PHP. And even then, the dynamic configuration you make might only work if the framework (the Wappler PHP code) is programmed in a certain way, which by chance happens to be.

Patrick could've optimized it in NodeJS so the DB connection is only made when starting the server, as industry practices dictate.

You found a creative use-case, probably it didn't even cross the mind of the Wappler team when making updates

4

It was working for Ben on PHP and NodeJS also in previous versions..

5

I know it was working on PHP but I'm starting to wonder if I just didn't notice on Node until now

It seems the 'issue' is that the connections, when created in lib/core/app.js, are being added to global.db[name], if it exists then it just retrieves the existing connection

What would be good is that the DB connection options can include a dynamic binding for the name parameter so it can be set to something unique for each company as retrieved from the DB. That way the efficiency of saving and retrieving connections is maintained and parameters can be set for each.

Commenting out this seems to work but does slow things down

if (global.db[name]) {
      return global.db[name];
    }
1 Like
6

@patrick (assuming you look into this)

I have had a play with some of the files and this seems to work but I'm sure you might find a more elegant method to achieve it. It adds a little bit of reading json file or parsing options upfront to check for a unique connection name, otherwise it should preserve the existing flow without extra overhead.

Add an option in the Wappler UI to set a unique name for connections (for cases where they are set dynamically) this would add a value to the db JSON like below (uqname) - this, in my case is a UUID found in the DB for each company:

{
  "name": "dyn_db",
  "module": "dbconnector",
  "action": "connect",
  "options": {
    "uqname": "{{get_dbcreds.dbuq}}",
    "client": "mysql2",
    "connection": {
      "host": "{{get_dbcreds.dba}}",
      "port": 3306,
      "user": "{{get_dbcreds.dbu}}",
      "password": "{{get_dbcreds.dbpw}}",
      "database": "{{get_dbcreds.dbn}}",
      "charset": "utf8mb4",
      "ssl": {
        "rejectUnauthorized": false
      }
    }
  },
  "serverType": "node",
  "fileName": "dyn_db.json"
}

In app.js:
setDbConnection has the following at the start before the switch block for client option to get the options and if a uqname is set, use that otherwise use the name of the connection component:

setDbConnection: function (name, options) {
    options = this.parse(options);
    name = options.uqname ?? name;
    if (global.db[name]) {
      return global.db[name];
    }
    switch (options.client) {
   // code continues unchanged

getDbConnection changed to this to look in the json for the uqname or use the name of the connection component:

getDbConnection: function (name) {
    let action = false;
    if (fs.existsSync(`app/modules/connections/${name}.json`)) {
      action = fs.readJSONSync(`app/modules/connections/${name}.json`);
      connname = this.parse(action.options.uqname) ?? name;
    }
    if (this.trx[connname]) return this.trx[connname];

    if (global.db[connname]) {
      return global.db[connname];
    }

    if (config.db[connname]) {
      return this.setDbConnection(connname, config.db[connname]);
    }

    if (action) {
      return this.setDbConnection(name, action.options);
    }

    throw new Error(`Couldn't find database connection "${name}".`);
  }
7

Hi @patrick
Could this be added? - I currently have a bit of a dance whenever any new Server Connect files are released to properly support dynamic connections. Should be a pretty easy job as I think most of the work is done for you above :wink:

There should be no backwards compatibility issues as unless the unique field is used, it continues to use the connection name as before

8

Adding the code will be no problem, the problem is with implementing it with Wappler. We currently have no way of having separate sets of options per server model, so adding a new option on NodeJS will mean it will also be added to the PHP model.

NodeJS is the only server model that reuses the connections and currently we use the name to identify them. We could perhaps better store it using a hash based on the connection settings like host, port and database. Using a hash wouldn't need an extra option to be added to the UI. The hash could be something very simple as JSON.stringify(options) (after they are parsed).

Perhaps @George can make it possible to have options per server model in future versions of Wappler. For now I think using a hash would be a useful solution.

1 Like
9

Thanks Patrick,
The hash may even be better than a dynamic name as if any of the connections change, the hash will follow. Also if any of the companies share a DB it won't necessarily create a whole new connection for each as they will generate the same hash.

I appreciate you looking into it :+1:

11

I don’t think that is feasible as we store and use all database schema and connection based on the name.

Also having so many different databases, how do you ensure they have all the same schema?

How do you handle schema updates?

Why not just use a single database where you just have the companies as keys and segment the data.

12

Also related discussion is:

13

Thanks George,
I work with schools in the UK and, unfortunately, many have a historic predisposition to having their data on their own tin. As a result I offer a multi-tenant model like you have suggested above but the option of using their own DB. In terms of updating schema, this has successfully been handled for some time as part of my upgrade process that I share with admins (mainly through schema update SQL scripts - or in some cases manage on their behalf)

I appreciate that the name is used for the connection and the suggested code (or using hash as Patrick suggested) doesn't change that aspect of the UI and usage within Wappler, it only affects how the connection is stored for re-use, allowing for dynamic switching to occur.

I see the process as:

  1. Dynamic connection called (using e.g. dyn_db as the connection name)
  2. The DB connection options are retrieved from data bindings
  3. Hash created of connection options
  4. Does hash exist as stored connection name?
  • Yes - retrieve existing connection
  • No - create new stored connection using hash as stored connection name

In the above dyn_db (as a connection name) is still used, as it always has in the Wapler UI and Server Connect APIs. The only difference is the storage of connections when called.

There is also another situation where this might have benefit - if a Wappler user is using dynamic bindings (even if they don't often change) should a connection password or hostname change, with the current implementation on NodeJS the server would need to be restarted for the new credentials to be applied. I understand that you want everyone to use DB and Resource managers which would involve a server restart on deploy, but not everyone does and this would close a potential loophole before it bites someone.

1 Like
14

Can you tell me where does get_dbcreds come from in the expressions, are the credentials for each connection stored in an other database and how do you assure that data is available before the dynamic connection is being created.

I'm currently trying some changes in the code but facing some minor issues. With the updated code we now have to read the json file, parse the json and then parse expressions to generate a hash and get the correct connection pool. This will happen for every query which is not ideal.

We can probably cache the connection file since that is static and won't change. We then inspect the config if it has dynamic data in it, if it is static we can store it in the global cache by name (the current way). If it has dynamic data in it we will have to parse it on each request, but only once and cache that for the duration of the request. I can assume that the data won't change in a request.

15

The get_dbcreds is run in the global Server Connect straight after security restrict

I have static DB that manages accounts/login and contains connection details - this is what the Security Provider uses

Once logged in, a single query is run tor retrieve the relevant company details which is the get_dbcreds query

16

What do you consider a request? I presume a single run of the full API file?

I have some cases where it would be useful to be able for it to change during a run (such as processing email comms queues where data comes from each item's company database). The queue could contain multiple company's comms. I can sort by company to make it more efficient of find a workaround to process one company at a time. If it were stored against a hash, this would be taken care of as it would just pick the connection with the corresponding hash wouldn't it?

17

Yes, it is a single http request from the client to your server.

I'm not following you here, are these email queue related to the user databases and how would the hash help them?

18

10 emails queued from companies A, B and C. Each has it's own data. To process the queue (chron or schedule) it needs to retrieve the list of 10 and repeat through, sometimes gettin info from each of the DBs. Therefore in the one call it might need to connect to DB for A, the DB for B and the DB for C (each have settings that feed the dynamic connection)

If the connection name is hash based, if the company has the same hostname, username, password, port, the hash will be the same and it can retrieve that connection (if it exists - or create new) - great for multi-tenant tha my share the same DB as well as those who want to connect to their own DB. It also means if the hostname is changed (or other option) the hash will change and therefore the correct connection will be made rather than a potentially outdated one.

19

So I also should not cache the dynamic connections per request since they can change inside them. The dynamic connections should always reparse and generate a hash to determine which connection it requires.

I wonder if this will not have any impact on database transactions, the transactions are also tracked using the database name and are per request.

I was hoping it would be a simple change, but I first have to investigate in how far this will infect other functionality.

2 Likes
20

Just want to add that as a multi-tenant app builder, I'd love to have this option natively. Thanks Ben for clearly articulating and Patrick for investigating!

1 Like