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: https://github.com/frappe/frappe/pull/14963 ERPNext: https://github.com/frappe/erpnext/pull/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.

[stale[bot]]

This issue has been automatically marked as inactive because it has not had recent activity and it wasn't validated by maintainer team. It will be closed within a week if no further activity occurs.

[iashaik]

Is this still active? It's been months.

[skygunner]

This issue might be related https://github.com/frappe/frappe/issues/19996

[sidharthramesh]

Hey @ankush, it seems like we are focusing on long-term maintainability as mentioned here https://github.com/frappe/erpnext/pull/28359#issuecomment-1277105369, which is great!

I know it's hard to estimate timelines for an open-source project, however, is there any information or idea you have on how long it would be before ERPNext can run on Postgres?

We need to make a few business decisions around this since the rest of our stack already uses Postgres and running an instance of MariaDB might increase our cloud costs significantly.

CC: @pythonpen @DanBrown47

[rmehta]

@sidharthramesh if someone out there is willing to contribute (there is a semi-finished PR out there), then we would be happy to accept as long as it meets quality and contribution guidelines

[ankush]

I know it's hard to estimate timelines for an open-source project, however, is there any information or idea you have on how long it would be before ERPNext can run on Postgres?

If someone works continuously, my guess would be approximately 3-9 months. That will be enough to reach a level where it can be used in production.

I get the cost of running a separate server but Frappe on MariaDB is battle tested for years and optimized over the years, all of that will take at least a year of running postgres in prod to reach a similar level of maturity. So don't forget that intangible cost.

---

I'll at least start putting all the work that is required in issue so whoever ends up working on it (frappe or outside) can have some structure way of addressing the problem.

[kisg]

One of the issues with adding support for PostgreSQL is the continued maintenance of the sql() and multi_sql() queries. What is your position on moving away from these in favor of the new PyPika-based Query Builder interface? I would even go as far as deprecating the old sql() and multi_sql() APIs now, before ERPNext 15 is released, and maybe remove it in the ERPNext 16 cycle. (I know that this is a drastic move, and would require agreement from both the core developers and the community.)

What do you think @ankush?

[sidharthramesh]

I know it's hard to estimate timelines for an open-source project, however, is there any information or idea you have on how long it would be before ERPNext can run on Postgres?

If someone works continuously, my guess would be approximately 3-9 months. That will be enough to reach a level where it can be used in production.

I get the cost of running a separate server but Frappe on MariaDB is battle tested for years and optimized over the years, all of that will take at least a year of running postgres in prod to reach a similar level of maturity. So forget that that intangible cost.

I'll at least start putting all the work that is required in issue so whoever ends up working on it (frappe or outside) can have some structure way of addressing the problem.

Thank you @ankush for the detailed reply. I understand the implications and the fact that Postgres support will never be on par with MariaDB.

However, assuming that we (and other interested parties) have commercial interest in getting production ready Postgres support, would it be useful to discuss methods to fund a work group / developer / ERPNext team for this in the long term? Are there any governance structure already in place for ways to fund feature requests like this?

[ankush]

I've started listing down everything required for stable support in framework here: https://github.com/frappe/frappe/issues/21613 (keep in mind this will keep expanding as we find more issues)

---

However, assuming that we (and other interested parties) have commercial interest in getting production ready Postgres support, would it be useful to discuss methods to fund a work group / developer / ERPNext team for this in the long term? Are there any governance structure already in place for ways to fund feature requests like this?

As of now I need an internal owner first to push postgres support. I'll get back to you after that.

I can push for stable support for Framework, ERPNext support is mostly rewriting queries in QB or ORM OR using multisql. Community developers can help by sending small PRs that move raw queries to QB/ORM. Make sure it's tested and old/new generated queries are equivalent. We don't want to introduce new bugs in process of rewriting queries.

If you're interested we can build a small working group on telegram and collaborate on it. Ping me on telegram: @ankushmnt or email ankush (at) frappe.io

---

One of the issues with adding support for PostgreSQL is the continued maintenance of the sql() and multi_sql() queries. What is your position on moving away from these in favor of the new PyPika-based Query Builder interface? I would even go as far as deprecating the old sql() and multi_sql() APIs now, before ERPNext 15 is released, and maybe remove it in the ERPNext 16 cycle. (I know that this is a drastic move, and would require agreement from both the core developers and the community.)

We discourage use of sql already. Removing sql would be a bit too much. Apps who want to support postgres can chose to not use it by using https://github.com/frappe/semgrep-rules

[ankush]

Link to telegram group if anyone wants to participate t.me/ +cPr4H4qhiOJlNjc1 (remove spaces)

[pramod-shanmugam]

What are the steps that you followed to setup postgres instead on mariadb?

[SalahAdDin]

Is there any ETA for this feature?

[WilsonG08]

Alguien me puede ayudar, estoy utilizando ERPNext, frappe v=14 y postgress pero me da este error:

2024-05-02 12:51:29 2024-05-02 17:51:29.147 UTC [372] ERROR: syntax error at or near "index" at character 92 2024-05-02 12:51:29 2024-05-02 17:51:29.147 UTC [372] STATEMENT: select item_code, warehouse, count(name) as total_row 2024-05-02 12:51:29 from "tabStock Ledger Entry" force index (item_warehouse) 2024-05-02 12:51:29 where 2024-05-02 12:51:29 (warehouse = 'Stores - W' 2024-05-02 12:51:29 and item_code in ('ITM-0001')) 2024-05-02 12:51:29 and timestamp(posting_date, posting_time) 2024-05-02 12:51:29 >= timestamp('2024-05-02'::date, '12:51:24.913741') 2024-05-02 12:51:29 and voucher_no != 'MAT-STE-2024-00001' 2024-05-02 12:51:29 and is_cancelled = '0' 2024-05-02 12:51:29 GROUP BY 2024-05-02 12:51:29 item_code, warehouse 2024-05-02 12:54:07 2024-05-02 17:54:07.177 UTC [389] ERROR: syntax error at or near "index" at character 92 2024-05-02 12:54:07 2024-05-02 17:54:07.177 UTC [389] STATEMENT: select item_code, warehouse, count(name) as total_row 2024-05-02 12:54:07 from "tabStock Ledger Entry" force index (item_warehouse) 2024-05-02 12:54:07 where 2024-05-02 12:54:07 (warehouse = 'Stores - W' 2024-05-02 12:54:07 and item_code in ('ITM-0001')) 2024-05-02 12:54:07 and timestamp(posting_date, posting_time) 2024-05-02 12:54:07 >= timestamp('2024-05-02'::date, '12:54:06.508791') 2024-05-02 12:54:07 and voucher_no != 'MAT-STE-2024-00001' 2024-05-02 12:54:07 and is_cancelled = '0' 2024-05-02 12:54:07 GROUP BY 2024-05-02 12:54:07 item_code, warehouse