PostgreSQL as alternate database provider

[kspearrin]

Adding another database provider should be fairly straight forward. Bitwarden's data access layer is abstracted away with repository interfaces which can be found under src/Core/Repositories. I've already done the work needed for making this possible. All that is left is re-writing all of the SQL tables, functions, stored procedures, etc over to PostgreSQL and then wiring them up repository implementations for PostgreSQL. I've started this work in the following commits, which can be followed as a model going forward:

84800da
acef40e

Choosing a database provider is as simple as either providing a connection string for MSSQL or PostgreSQL, whichever you prefer.

I do not have a lot of experience in working with PostgreSQL, so I will need some help to complete this task.

Questions:

• Are PostgreSQL functions equivalent to MSSQL stored procedures?

[jeremyVignelles]

I was just trying to setup bitwarden on my server and immediately saw ram usage getting high due to the use of SQL Server. I was wondering if there was a way to use another db provider, when I found this post.

I've never used PostgreSQL, but I'm curious to know if that would help my server support the extra load.

Why are you using stored procedures for? What other dbms-specfic feature are you using? Would SQLite be a candidate for small environments?

Hi there, long time lurker, first time poster.
As somebody who is highly interested in this feature I would like to help with at least my two cents.:

Are PostgreSQL functions equivalent to MSSQL stored procedures?

PosgreSQL stored procedures capabilities surpass MSSQL's, as this was one of the key aspects of its development. For a short info/comparison: https://stackoverflow.com/questions/339744/better-languages-than-sql-for-stored-procedures

I've never used PostgreSQL, but I'm curious to know if that would help my server support the extra load.

What would be your server's setup? Win or *nix? Usually I would say yes, it should reduce some of the load, but I don't want to give you some false hope.

I do not have a lot of experience in working with PostgreSQL, so I will need some help to complete this task.

I never helped I any open source project out of fear of doing something wrong, but if you guys need some help and are willing to help guide me on how to contribute I would gladly help.

[jeremyVignelles]

I never helped I any open source project out of fear of doing something wrong, but if you guys need some help and are willing to help guide me on how to contribute I would gladly help.

You can't mess up a project on your own by doing bad things. If you have the right development skills, go ahead, the worst that could happen is spending too much time, but you'll always learn something in the process 😉

[kspearrin]

@tamaralo All database interaction in Bitwarden with MSSQL is done with stored procedures. You can find them (all all other schema) listed here: https://github.com/bitwarden/server/tree/master/src/Sql/dbo

My assumption is that we'd do the same in PostgreSQL using stored procedures (seems to behave differently in PostgreSQL) or functions. Our SQL data mapper (Dapper) seems to only work with PostgreSQL functions.

The majority of the work here is just translating the MSSQL schema (tables, indexes, sprocs/functions) over to their equivalent on PostgreSQL. After that it's just a matter of wiring them up to a C# repository class, which is fairly easy to do. Do you have enough experience in MSSQL and PostgreSQL to translate between the two? If so, I'd say you're qualified enough to help with this.

@jeremyVignelles 😊 I give it a try! I have a lot of fear/respect for opensource projects, as they seem to be so well organized and fast paced. ^^

@kspearrin:
I worked with PostgreSQL, MySQL and some old versions from MSSQL ( in the 2000's ) so I think I could take a crack at it and try it at my server. The thing is, I study alongside my work and ATMI have some finals so, if its ok, I will try to take a look at it by the end of next week. 👍

[kspearrin]

@tamaralo If you have any questions when you get started, you can find me in our chatroom here: https://gitter.im/bitwarden/Lobby

@kspearrin Thx, I will come online by the end of next week (sorry, forgot the word next in the last post! Thanks for the prompt reply.

[Kovah]

The 2GB RAM requirement for SQLServer is the only thing holding me back from using Bitwarden. Would love to see an implementation for Postgres or any other database provider. 💙

[indy-singh]

Would love to help Bitwarden support PostgreSQL; though I tend to avoid stored procs/functions and prefer to bring that logic up into the code.

[mrahbar]

👍 For supporting PostgreSQL. I'm a big fan of Bitwarden and a happy paying customer. The only reason of not using a self-hosted Bitwarden is MSSQL.

On the topic tough I think that without any official endorsement of the Bitwarden team for PostgreSQL this endeavor is be doomed to failure. After the initial batch of SQL scripts is ported who is going to create new PostgreSQL scripts every time the equivalent MSSQL scripts are updated? Kudos for a defined data-abstraction-layer but I'm not optimistic when it gets to maintain two different database vendors.

So can this process be only an experiment or a real alternative solution for MSSQL?

[FingerlessGlov3s]

👍
This sounds like a good idea to me. Getting ram usage down and removing minimum 2 GB limit is good. Will make running it more resource efficient on virtual machines etc...

Although we do need support from Bitwarden themselves like @mrahbar says.

[Agraphie]

I would like to see this as well. Are there any experimental branches or any way I can help make this happen?

[keliansb]

Any news about this?