Postgres support for ERPNext

[rmehta]

Frappe Framework has support for both MariaDB and Postgres. ERPNext does not have support for Postgres. While queries are mostly similar, there are 1300 SQL queries that need to be verified and checked. Thankfully we have unit-tests.

Steps:

1. Setup tests for postgres (similar to Frappe Framework)
2. Makes sure the tests pass for all queries

If there are problems in certain SQL queries:

1. Use the ORM
2. Rewrite the query so that it will work for both databases
3. Use frappe.db.multi_sql to write separate queries for each platform (for complex joins)

[kisg]

Is someone at Frappe already working on this? We would be interested in contributing code to this issue.

I submitted a proposal some time ago on the forum, that also included more proposed data layer refactoring at the same time: https://discuss.erpnext.com/t/erpnext-postgres-support/67069/2

Would there be interest in such changes?

It does not have to be an "all or nothing" acceptance, e.g. I can understand if the numeric id part is kept as a completely separate project, because it would require creating prototypes first and do extensive benchmarking to make sure that the transition is worth it.

But the following changes certainly would bring a lot of maintainability and performance benefits:

• Move to a DAO model where the SQL queries are kept in separate files in an app.
• Introduce a Fluent SQL query API that can hide most of the syntax differences across SQL engines. For example Pypika (https://github.com/kayak/pypika) could be used as a starting point.
• Use prepared statements in the ORM and in custom SQL queries as well

If there is interest, I would be happy to discuss the technical details and provide a more detailed design for review either here or on the forum.

[rmehta]

@kisg no we have not yes assigned this. If you are working on the first milestone of setting up the test and making sure they pass with postgres, do open a PR - will get someone from the Frappe team to help you.

[pratikbin]

waiting for this one

[Verequies]

How is the progress on this? Any sort of timeline? Would love to see this integrated.

[elbanna-h]

Yes, Postgres is more stable and powerful

[Verequies]

I'm actually working on fixing the queries at the moment. Currently the installation works and am surprised that a lot of it actually works as is.

[wodka]

@Verequies is there any PR / branch others can help you to get it running?

[neilcamilleri]

Also waiting for this, this is a blocker to adoption for us.

[Verequies]

Sorry for the lack of updates, I plan to get back to this however as I just graduated from university I am stuck on finishing a couple of personal projects first - at least to the point where I can release an initial version. I am almost finished however.

When I get back to it, I am happy for others to help :) @wodka

[JeffKnorr]

@Verequies I'd be interested in helping too. Please share your progress so far.

Sorry for the lack of updates, I plan to get back to this however as I just graduated from university I am stuck on finishing a couple of personal projects first - at least to the point where I can release an initial version. I am almost finished however.

When I get back to it, I am happy for others to help :) @wodka

[c-p-b]

Frappe: frappe/frappe#14963
ERPNext: #28359

[Verequies]

@cpdeethree Thanks for taking over, glad to see that some of my commits helped. I wasn't sure when I was going to be able to get back to due to running my own businesses and doing contract jobs so glad to see it is being worked on.