Reuse database connections

Feature Requests
, , , ,
1

Hello, n8n community,
Let me start off by saying that n8n is an amazing tool. It just simplifies development to a great extent.
However, it has a lot way to go and requires a lot of polishing.

So something from my side :slight_smile:

I have noticed that if in an n8n workflow, if you are using 5 database nodes, 5 separate/new database connections are established with the database.

This is resource-intensive and will result in slower execution.

Is there any way we can re-use previously established database connection?

Maybe we can create an internal array map where we map connections with credentials, and if the connection for that credential is already established, we can reuse it.
Else we can establish a new one.

I believe this will speed up n8n workflows drastically.

2

I like this idea and it could apply to more than just the database nodes it would also be useful for the FTP node. The way I have seen it implemeted before is you can output a “session id” and if you are using that the connection will be kept open for the life of the workflow and you can then pass that id instead of using credentials.

In theory we could automatically handle that but I suspect it wouldn’t be an easy or quick feature to add.

3

I went through the source code of a couple of nodes.
It won’t be a straightforward implementation for sure.

Coz you will need something on the lines of a singleton object or a global object which is available everywhere and you can store "session-id"s there.

Once you have the global object ready, it will be very easy from there.
Something as simple as an if condition.

1 Like
4

Hi Everyone,

Can we use generic-pool library for this case? why not?

I also think that Nodejs is Event-driven programming, we just need a single connection to connect to a single point for all nodes.

5

@Jon I am pretty sure it will also solve the other postgress issue which we are faicng.

6

I was going through the source code, and looks like this will have to be handled for each node separately.

For Postgres:

Line: 110

const db = pgp(config);

If we store this const db for the lifecycle of the workflow with some basic hash of “IDataObject”, the next time, we will just have to check if this object is defined or not

eg:

const db = (typeof dbConnectionStore[WorkflowID][hashIDataObject] != 'undefined') ? dbConnectionStore[WorkflowID][hashIDataObject] : pgp(config);

Reason for hash is that it will create a new connection if you are trying to connect to a different host or db or using credentials with different access level.

A similar thing can be done for redis as well.

7

Or we can even take it one step further with

const db = (typeof globalConnectionStore[NodeType][hashIDataObject] != 'undefined') ? globalConnectionStore[NodeType][hashIDataObject] : pgp(config);

This way, the connection string will be shared across all workflows & all executions.

@Jon, @Roney_Dsilva and I can create a community pull-request to implement this feature. But we need to know where we can define the globalConnectionStore object to its available globally.

8

Yes, this will be very much helpful.

In my case, I want to connect to Redis. Using a Redis node and creating a new Redis connection every time is expensive, especially if the workflow has very high usage.

Instead, I would prefer to create a redis connection object after the machine starts and use that connection object it in any of the workflow.

Is it possible to do this now? @Jon

Or if there is any other alternative, please let me know.

Thanks.

9

Hey @shriram.balakrishnan,

Not yet as it wouldn’t be a quick feature to add, Currently there is no alternative.

10

I am new in the n8n community,
but for my experience in other domains, I can already tell that @MayurVirkar globalConnectionStore can only be seen as a workaround and will lead to other issues in regards to connection management

A new managed n8n section, new types and API to define resources
and the lifecycle need to be managed by n8n itself.

This resources would need to be defined like node types and credential types.

n8n host can then warmup, tear down and share this instances with the workflows
and of course implement a backoff strategy.

Resource types:

  • singleton connections
  • session tokens
  • pooled connections
  • file handles
  • … many others
11

Hi, I’m new here and kinda new to nodejs also, but I’ve implemented Singleton in nodejs as a module that create the object on first import, so every other import would get the same object.

I’m thinking in using this approach, the only downside is that the variables of the database would be fixed, as the path, database name, etc.

I use RavenDB, which have a super heavy DocumentStore to manage the connection and a light weight session object that is used just for transaction puposes kindof.

In their documentation they strongly suggest to have just 1 store for the application, and I don’t see any way to do so in n8n other than creating an external module.

Any thoughts?