Edit ERPNext database outside of ERPNext: Microsoft PowerApps?

1

Will editing ERPNext data in the SQL database in something like Microsoft PowerApps/Zoho Creator break anything?

For example, if a business wants a mobile application to edit some sales/products fields for its retail workers, can they use another platform to edit the data in database, directly?

2

ERPNext provides a powerful API that can be achieved from any external development. You don’t need connect to the database.

Hope this helps.

3

Short, but truthful answer:

Maybe yes, maybe no: depends on the context and person doing the updates.

Medium, but truthful answer:

  • -If- you are very familiar and experienced with the DocType data you’re modifying…
  • …-and- you are both careful and methodical, and know the data relations…
  • …then yes, modifying the MySQL database directly can be done safely…
  • …but those are some very big “if’s”…
  • …and this is rarely the recommended solution.

Longer explanation:

The MySQL database, created and managed by the Frappe framework:

  • does not have SQL foreign keys.
  • does not make use of SQL triggers.
  • does not make use of SQL functions, stored procedures, or views.
  • has a few, limited constraints. mostly limited to unique indexes.

But this is okay. Because in place of the above, the software itself is responsible for maintaining referential integrity, cascading actions (e.g. deletes), processing data via controllers, and a lot more.1

As a hypothetical example: assume we want to change a Customer’s first name. Accomplishing this with pure SQL is a simple task:

UPDATE `tabCustomer`
SET first_name = 'Brian'
WHERE `name` = 'CUST-00001';

This SQL statement is valid and correct. However…there is a lot of related activity that did not happen. Yet possibly should have:

  1. The customer’s Full Name (first + last) was not updated, to reflect the change in 'first_name'

  2. The 'modified' and 'modified_by' fields on tabCustomer were not updated, to reflect who last-edited the record, and the date+time this happened.

  3. The Contact documents related to this Customer were not updated.

  4. The Address documents related to this Customer were not updated.

  5. The open Sales Orders, Delivery Notes, and Sales Invoices related to this Customer were not updated.

  6. The auditing table (tabVersion) will not contain the Before vs. After information for this Customer’s first name.

There are many more things we could think of. Yet Customer is a relatively easy DocType in ERPNext. Some of the others (Items, Pricing, GL Entries, much more), are far more complicated. What are their relationships with other documents? What other data must be changed at the same time? What constraints must be followed?

As @avc noted, the solution is manipulating Documents (SQL tables) via their API’s. Every Document has APIs for Create, Read, Update, and Delete (CRUD).

If that is insufficient, then writing your own APIs or Python code is the next-best thing. You can call controller methods like validate(), save(), and delete() that will ensure the proper actions are taken.

1SQL DBAs may feel this strange, or a bad practice. Like most things in software, there are advantages and disadvantages. While I cannot speak for -every- platform, in my experience, it is normal for ERP software to manage the (very) complex data relationships and integrity. Instead of SQL itself.)

4 Likes
4

Now or later you will have a broken db. You MUST use an API.