Add support for transactional job enqueuing

[SylvainMarty]

Hello there,

For my current project, I am looking for a solution like Sidequest.js for handling async jobs using my existing PostgreSQL database.

What is preventing me from using Sidequest.js is that I need the enqueued jobs to be committed in the same transaction as my business logic.

In my experience, this pattern provides multiple benefits:

1. It avoids race conditions that can happen when the job is being processed right after being enqueued, while your business entities might have not been committed yet
2. For bigger workflows with multiple jobs being enqueued in a single request, any error happening after enqueuing jobs will trigger a rollback, which avoids jobs being processed multiple times when the system retries the operation

I could write my own query to enqueue the job and be done with it, but I deeply think Sidequest should provide this feature to its users and help them make their system more robust. IMO, the main advantage of using your current DB as a message broker is to keep your data and workflows strongly consistent.

I'd love to implement this change if it fits Sidequest.js vision. 😊

[GiovaniGuizzo]

Hey @SylvainMarty.

That is a very niche usage of Sidequest. Perhaps you can write up some logic to enqueue a job for 1 hour into the future (availableAt() during job build) and then delete it if your transaction rolls back, or set as available now if your transaction was committed.

Perhaps we can implement this feature, but in our Pro version.

[SylvainMarty]

Hey @GiovaniGuizzo, thank you for your quick response.
I'm very surprise this is considered a niche usage: database transactions are a no brainer in most relational databases.
What kind of use cases Sidequest solves in production?

I am not convinced the solution you suggested provides the warranties I am looking for as this new logic would have to exist outside of the business transaction, making the whole workflow eventually consistent.
Also, this solution increases the complexity of the software to fix a problem transactional databases fixed decades ago.

I don't find any information on the official website about Sidequest pro version, but I'm not sure why anyone would pay for this feature.
As I said in my original message, it is not that difficult to re-write the code to enqueue the job in my own system that can run in the same db transaction as the business logic.
I think Sidequest would benefit from providing this feature out-of-the box to encourage developers to build better softwares.

[GiovaniGuizzo]

Indeed, transactions are very common in production environments. What I meant by "niche" is tying the business logic transactions lifecycle to the job processing lifecycle.

But I do agree with you here. Since Sidequest is meant to work on top of a DB, then there should be a way of creating said transactions.

The way the backend engine is implemented, this feature would be a bit difficult to incorporate, but I guess it would be possible.

[merencia]

Hey!

Just to clarify my thoughts here.

I’m not sure exposing Sidequest internals, such as a shared transaction, is the best approach for the project. There are a few concerns:

1. Sharing the same transaction means that if job insertion fails, the whole business transaction would be rolled back as well. That level of coupling can be problematic depending on the use case.
2. Sidequest manages its own database connection pool, and in many setups it may even run on a different database or schema. Your application does not share that connection, so supporting transactional enqueueing would require exposing internal connections or allowing external transaction injection, which adds significant complexity to the core design.
3. There’s also a trade-off between flexibility and the guarantees we want to provide. Since Sidequest relies heavily on its own connection and transaction handling, introducing this level of coupling could hurt long-term maintainability and reliability.

On top of that, this kind of feature represents a substantial increase in complexity and opens the door to many edge cases and misuse scenarios. That’s one of the reasons why I see this as something that would make more sense in a Pro or advanced tier rather than in the core offering.

That said, I do agree the use case itself is valid, and it’s something worth revisiting with a more robust design in the future.

[SylvainMarty]

Thank you both for your input on this matter!

I've looked at the Sidequest SQLBackend source code and I understand why the current design isn’t particularly compatible with this approach.

One possible solution would be to provide a lighter client library dedicated solely to managing jobs.
IMO, this would make sense, since the core purpose of Sidequest is to make it easier to implement asynchronous software in Node.js. At scale, managing jobs and processing jobs are typically distributed across different processes (or services/containers).
Providing separate client and server libraries makes sense in the long term.

PG Boss, an alternative solution to Sidequest (but specific to Postgre), addresses this problem by allowing users to pass a db object via the options for each job-management method.
The db object object exposes a single method, executeSql(query, params), which serves as a hook developers can use to execute queries using their existing database connection.

Since Sidequest uses Knex, a similar approach could be implemented by passing the result of knex(...).toSQL().toNative() to the hook, allowing developers to execute the native SQL using the driver of their choice (and not necessarily the Sidequest driver).

I’d love to hear your thoughts on this.

[merencia]

The idea is definitely a good one, and a fair request.

@GiovaniGuizzo and I need to discuss about how to approach that. We have some principles we are following at this project, we are very careful about how to introduce things for keeping a nice DX and keeping thinks simple. Also, since the initial idea about sidequest is "use the database you already have for background jobs" we have to keep compatibility with MongoDB and don't lock in a database (or relational databases only).