How to sanitize Database Tables of my Custom App?

ERPNext
, ,
1

Hello,

Now that my Custom App has reached a stage where its database structure has become stable and probably will not change any more.

But when I checked the tables of the DocType that I have created I found that there are many fields which do not exist any more in my DocTypes.

I want to get rid of all these fields and also there are a few tables (that got created when I created a few Dummy DocTypes) that I need to get rid of.

How to sanitize Database Tables of my Custom App?

Is there any easy and sure shot way?

TIA

Yogi Yang

2

Hi @YogiYang

You can use the trim_tables method to clean up and sanitize your database structure.

Here’s a sample code block:

bench --site [site-name] console

from frappe.model.meta import trim_tables
trim_tables()
exit

bench reload-doc [module-name] doctype [doctype-name]

Please make sure that you use this method very carefully as it involves altering the database and any mistakes can lead to severe consequences like breaking the entire site.

It’s highly recommended that you dry run the command before executing it;

bench --site {site} trim-tables --dry-run
1 Like
3

Hello,

This command is not working for me. I am using ERPNext 13.

I checked the documentation of Bench command line but there I could not find any mention of trim-table.

https://frappeframework.com/docs/v13/user/en/bench/resources/bench-commands-cheatsheet

TIA

Yogi Yang

4

You can find official documentation here :point_down:

https://frappeframework.com/docs/v13/user/en/bench/reference/trim-tables

1 Like
5

Hello,

Thanks for the link but it is not working for me.

This are the errors that I am getting.

administrator@dellpc:~/frappe-bench$ bench --version
5.12.1
INFO: A newer version of bench is available: 5.12.1 → 5.14.3
administrator@dellpc:~/frappe-bench$ bench --site erpnext13 trim-tables --dry-run
Usage: bench [OPTIONS] COMMAND [ARGS]...
Try 'bench --help' for help.

Error: No such option: --site
administrator@dellpc:~/frappe-bench$ bench trim-tables
Usage: bench [OPTIONS] COMMAND [ARGS]...
Try 'bench --help' for help.

Error: No such command 'trim-tables'.

What must be the problem here?

If you notice in the output above there is a message about new version of Bench - INFO: A newer version of bench is available: 5.12.1 → 5.14.3.

Is this feature only available in latest version of Bench?

What command have to use to update Bench itself?

TIA

Yogi Yang

6

Hello,

Surprisingly after a few experimentations I observed that bench trim-tables command works in ERPNext 14 but does not work in case of ERPNext 13.

Here is the console output for ERPNext 13

administrator@dellpc:~/frappe-bench$ bench --version
5.14.3
administrator@dellpc:~/frappe-bench$ bench list-apps
frappe  13.39.0 version-13
mymfg   0.0.1   Version0.5
erpnext 13.36.5 version-13
administrator@dellpc:~/frappe-bench$ bench trim-tables
Usage: bench [OPTIONS] COMMAND [ARGS]...
Try 'bench --help' for help.

Error: No such command 'trim-tables'.

Here is the console output for ERPNext 14

administrator@dellpc:~/frappe-bench14$ bench --version
5.14.3
administrator@dellpc:~/frappe-bench14$ bench list-apps
frappe  14.4.2 version-14
erpnext 14.0.2 version-14
administrator@dellpc:~/frappe-bench14$ bench trim-tables
Taking backup for erp14
Backup Summary for erp14 at 2022-09-08 17:37:01.677219
Config  : ./erp14/private/backups/20220908_173659-erp14-site_config_backup.json 180.0B
Database: ./erp14/private/backups/20220908_173659-erp14-database.sql.gz         1.3MiB
Public  : ./erp14/private/backups/20220908_173659-erp14-files.tar               10.0KiB
Private : ./erp14/private/backups/20220908_173659-erp14-private-files.tar       10.0KiB
Ignoring missing table for DocType: test
Consider removing record in the DocType table for test
The following data have been removed from erp14
+---------+--------+
| DocType | Fields |
+---------+--------+

Am I missing something here?

TIA

Yogi Yang

7

You are not missing anything.
The command is available in version 14 only.
The docs for version 13 is wrong.

1 Like
8

@rahy,

Thanks for the clarification.

Is there any way by which we can add this functionality to version 13 by any chance?

Currently what I am doing is

bench uninstall-app <My App Name>
bench migrate
bench install-app <My App Name>
bench migrate

But this is an unprofessional work around at the moment. And doing this is resulting in loss of data so I have to enter all the master data again before I can use the Custom App.

TIA

Yogi Yang

9

I don’t know. Add the code to the bench or frappe v13? Because some of the bench commands are in frappe, and I think this command is one of them.

Before uninstalling the app, you can do bench export-fixture to all the doctypes with data. So when reinstalling, the data will come back.

10

@YogiYang I’m currently working on re-writing this for v13. When I’m finished, I will share the code in a public GitHub Gist. It won’t be a Bench/Click CLI command. But you’ll be able to run it with 'bench execute' .

2 Likes
11

@brian_pond, It would be greatly useful to developers like me!

TIA

Yogi Yang

12

@YogiYang
Here is a link to my GitHub Gist.

  • You’ll need to add this to a Python module somewhere (probably in your own App)
  • You cannot use ordinary "bench <command>" syntax. Achieving that is a bit harder, because you have to teach Bench where to find the new Click commands.
  • Instead, in each function’s docstring, I’ve tried to include a few examples of a 'bench execute’ command.

For example, to remove extra columns from the “Customer” DocType:

bench execute --args "['Customer', False]" some_app.some_module.sql_db_clean.trim_table

  • Note that “Dry Run” mode is enabled by default. You will have to explicitly disable. Please examine function arguments before using.

  • I have tested using my own, local v13 ERPNext installation. And it seemed to work great…

  • …however. Please test in your Test environment first, and verify the results, before trying in Production!

I offer no warranties, or guarantees this will work for anyone else.

But I hope it helps. :slight_smile:

sql_db_clean.py ¡ GitHub

4 Likes
13

After installing it in a new app, and running it, I get:

(env) erpnext@dos-dev:/opt/bench/frappe-bench/apps/util_app/util_app/util_app$ bench execute --args “[‘ThreeDModels’, False]” util_app.util_app.sql_db_clean.trim_table

Please specify --site sitename

Then I ran:

bench execute --args “ThreeDModels” util_app.util_app.sql_db_clean.trim_table --site dos-dev.sugar.design

or

bench execute --site dos-dev.sugar.design --args “ThreeDModels” util_app.util_app.sql_db_clean.trim_table

I get the usual:

Usage: bench execute [OPTIONS] METHOD
Try ‘bench execute --help’ for help.

Error: no such option: --site

If I run:

bench execute --args “[‘dos-dev.sugar.design’]” util_app.util_app.sql_db_clean.trim_tables_by_site

I get:

Please specify --site sitename

Can you share how to run your .py file?

14

I got it.

Using this article Bench execute and Bench console (frappe/ERPNext) | by Alain Berger | Medium, I use:

bench --site mysite.local console
1 Like