[NV-1251] Add support for PostgreSQL DB

[cananda]

What?

Most applications would use some sort of RDBMS and most new age BaaS also use RDBMS. Just for using an notification engine supporting self-hosted NoSQL like MongoDB is an overhead. If Novu supports RDBMS, a managed service or a developer would be familiar and easy to work with. PostgreSQL for example has great JSON support too.

Why?

Managing multiple services/technologies in production becomes a challenge and supporting a variety of datastores will help simplifying deployment, operations and maintenance

How?

Maybe use a ORM that supports multiple Databases and most of them support JSON anyway,

Other information

Since notifications contain PII data, more use cases maybe to self host to comply with local data laws. Supporting multiple datastores will increase adoption and contribution.

_NV-1251

[scopsy]

Thanks for reporting this @cananda, I will take a look at some more generic ORMS, but from experience working with some of them, they are either very lacking in a NO-SQL use case or the other way around :(

Novu goal is to be a microservice dedicated to managing the communication stack of the application, this is the reason we provide the service in the enclosed docker-compose environment working with it's own Redis and MongoDB instances.

I will be investigating this suggestion a bit down the road, since now we are mainly focusing on providing the core functionality of the API, once we are there we will definitely evaluate supporting more data stores.

The current architecture uses a DAL which encapsulates MongoDB usage, but even with this separation, it might be a hard task to add support for a relational database such as Postgres.

Hope my answer makes sense 🙏

[cananda]

@scopsy Thank you for your comments and I understand the decisions made. As a micro service, the service should be reliable, performant, scalable and secure. Running a DB on a docker, will be challenging to secure, backup and scale unless there is expertise available. Most micro services will rely on some RDBMS and hence easy to use novu with RDBMS as a datastore.

At this stage of the project, it may be easier to make this change than later on as complexity and features grow.

Prisma / TypeORM has native MongoDB support along with multiple RDBMS. For a few projects of mine, Novu for notification as a microservice is an excellent fit just that managing MongoDB is an overhead (data has to be local to the region else 3rd party hosted MongoDB would work) as it is not part of the current tech stack

[L-U-C-K-Y]

I support this enhancement as we can reuse our Postgres database provisioning process and existing knowledge!

[derekperkins]

👍 for using Prisma if possible to enable the most data stores with the least effort - we'd prefer to wire in MySQL (specifically Vitess)

[oba2311]

Thank you @cananda @derekperkins @L-U-C-K-Y @fredsossa1 and others involved in this conversation - moving this to a discussion so we can finalize together our approach and understand what's the best way forward.

[MichalNemec]

This would surely open up more possibilities for us. For future it would be nice to have novu with option to be as an enclosure for "pluggable" services to fit more tech stacks.

[tsssdev]

With Prisma we can have SQLite support and that can be used for local setup, so devsetup can be simplified.

[SebastianStehle]

I understand the reason to postpone it. One real big reasons is testing. With the hosted novu solution you can easily test MongoDB in production, which would not be possible on the same extend with other data providers.

But I recommend to have a closer look to the repositories. There are a lot of leaky abstractions where mongodb details are used everywhere in the code. For example the update method.

[paulwer]

any updates on this? we would also be glad to have something like this supported.