Support for PostgreSQL?

carto_aca · July 23, 2019, 4:52pm

Hello,

I have seen a few posts mentioning PostgreSQL but I am not quite sure yet if ERPNext has full support for PostgreSQL backend or if it’s still experimental. I have seen 5 years old posts about future Postgres implementation or recent bug/error reports.

I am considering implementing ERPNext in our organization but we also deal heavily with all sorts of geospatial data. In that matter, PostgreSQL/PostGIS is MUCH more powerful than MySQL, hence the need for it.

clarkej · July 23, 2019, 5:31pm

Hi welcome to ERPNext!

FWIW with each code commit, the checkin tests run with PostgreSQL on the backend for example Travis CI - Test and Deploy Your Code with Confidence

EDIT: Note the tests exercise the frappe app code with Postgresql (and not erpnext)

carto_aca · July 23, 2019, 5:59pm

Sorry but since English is not my primary language, I am not 100% sure what you mean. My understanding is that Postgres is fully supported. Am I right?

clarkej · July 23, 2019, 6:14pm

Good question! I can’t comment from experience. Postgres users here may want to comment.

As for geospatial data, ERPNext supports this https://erpnext.com/docs/user/manual/en/customize-erpnext/articles/geolocation-field

Whatever due diligence tests you perform the community would welcome your feedback.

As well this forum is active for support too…

A search like this will help inform you Search results for 'Postgres after:2018-12-01' - Frappe Forum

hrwx · July 24, 2019, 3:28am

Frappe Framework support PostgreSQL, ERPNext is still under process. You can try running ERPNext on PostgreSQL but some querries might not run as intended.

Obinna_Ukwueze · July 24, 2019, 6:43am

What kind of queries and is anyone using it in production?

hrwx · July 24, 2019, 6:55am

SQL queries used to make ERPNext work the way it does.

rmehta · July 24, 2019, 7:34am

Frappe Framework is ready for Postgres but ERPNext is not. There are 1300 queries that might need refactoring as of July 2019.

If there is anyone willing to sponsor and use this in production, this is a project we can definitely invest time and effort.

kaysersoze · September 19, 2019, 9:36pm

My organization is actually in the process of installing ERPNext to use internally, as well as exploring the possibility to create an integration with our product (and offer it to customers that might not have an ERP currently).

For our next major release we are moving to PostgreSQL as our main DB, and as such would love to have ERPNext support this.

How can I help to implement better PostgreSQL support in ERPNext?

felix · September 20, 2019, 12:35pm

see Support for PostgreSQL? - #8 by rmehta

kaysersoze · September 21, 2019, 5:37pm

Hi @felix, I think perhaps I wasn’t too clear in my post.

I was interested in @rmehta’s comment, specifically “If there is anyone willing to sponsor and use this in production, this is a project we can definitely invest time and effort.”

We are certainly willing to use this in production, and I’m willing to make any changes and create PRs for increasing PostgreSQL integration.

What would “sponsorship” entail? Also, is there anywhere that is tracking what is still remaining for the integration, as per the comment “here are 1300 queries that might need refactoring as of July 2019”? (I didn’t see anything in the issues or in Milestones - frappe/erpnext · GitHub)

Thanks in advance.

ahmadRagheb · September 21, 2019, 7:25pm

@kaysersoze am following and willing to help too

rmehta · September 24, 2019, 5:21pm

This is a solid 2 month project, will cost roughly $15k - $20k (excluding support). Drop us a mail at hello@erpnext.com if you are interested.

The project will be a contribution to the product and community, and released in a new major release.

tejpochiraju · November 22, 2019, 4:02pm

Can’t sponsor the 20k ourselves but happy to contribute towards a crowdfunded pool. Especially because we could also then get a graphql layer automatically - thanks to Hasura.

raveslave · November 23, 2019, 12:45pm

would actually be a great feature if we could have a way to cooperatively sponsor things like this. I could offer 10%

benedictjohannes · December 27, 2019, 6:40am

Any branch that focus on implementing postgreSQL? I think beginning by marking where the query is constructed inside the source code would help in starting postgreSQL database support effort.

clarkej · December 27, 2019, 2:53pm

Welcome to ERPNext Benedict!

I can find no Postgres branch nor Postgres-specific test cases.

But in any case here’s the latest Postgres test run https://next.travis-ci.org/frappe/frappe/jobs/629971723

and the travis ansible build config for that
https://github.com/frappe/frappe/blob/develop/.travis.yml#L25

Here are example PRs for Postgres
https://github.com/frappe/frappe/pull/8909/files

This PR uses the database target frappe.db.multisql query construct
https://github.com/frappe/frappe/pull/7418/

Here’s some (pandas library!) database target test code

github.com

pandas-dev/pandas/blob/main/pandas/tests/io/test_sql.py#L89


      
          SQL_STRINGS = {
              "read_parameters": {
                  "sqlite": "SELECT * FROM iris WHERE Name=? AND SepalLength=?",
                  "mysql": "SELECT * FROM iris WHERE `Name`=%s AND `SepalLength`=%s",
                  "postgresql": 'SELECT * FROM iris WHERE "Name"=%s AND "SepalLength"=%s',
              },
              "read_named_parameters": {
                  "sqlite": """
                          SELECT * FROM iris WHERE Name=:name AND SepalLength=:length
                          """,
                  "mysql": """
                          SELECT * FROM iris WHERE
                          `Name`=%(name)s AND `SepalLength`=%(length)s
                          """,
                  "postgresql": """
                          SELECT * FROM iris WHERE
                          "Name"=%(name)s AND "SepalLength"=%(length)s
                          """,
              },
              "read_no_parameters_with_percent": {
                  "sqlite": "SELECT * FROM iris WHERE Name LIKE '%'",

tcioc · September 10, 2021, 3:29pm

@carto_aca just saw your post from 2019, were you able to build your solution that suit geospatial data?

Ivan_Guevara_Mondrag · November 16, 2022, 5:56pm

Wherever you are Clark. Thank you.
Mis mejores deseos.

Hela_Guesmi · January 11, 2023, 11:46am

Hello,

I want to modify my current site from using mariadb to use postgres OR create new site with db type postgres , I do :

bench new-site sitename --db-type postgres
but it return error :
ERROR: permission denied for schema public LINE 1: CREATE TABLE “tabDocField” ().

the database and user automatically created . how can I grant privileges to user before creation tables , any help please