Hello,
I have an old Semi ERP application with back end powered by PostgreSQL. I am planning to try to build the whole app in Frappe Framework from scratch.
There are 300+ tables and around 1k stored procedures and 750+ views.
To build this application we had employed 12 developers for 24 months.
It would prove to be a nightmare to build DocTypes for each table.
Are there any tools which will reverse engineer an existing database and generate DocTypes?
TIA
Yogi Yang
Not sure if anyone has done this by reverse engineering from total another schema type, But i guess it will not take much time to create 300 doctypes, since this would be a one-time process, even if you write some script like the below attached u would have to define fields mapping so instead you can create itself manually (By dividing among 12 
)
The script would be prone to error and manually you can configure correctly since those would be handled through f/w itself
Hello @khushal_t,
Your suggestion is valid from your stand point because you are just looking at 300+ tables.
But if you look from my point of view it would be an impossible task as I will have to update various triggers, heavily edit all SPs and what not.
It would truly be a nightmare.
While I am at it I also want to ask whether we can force Frappe Framework to use pure table name instead of prefixing all table names with tab.
TIA
Yogi Yang
There is no option to remove the ‘tab’ prefix. This is hardcoded in 'database.py’ and a few other places in the framework. You’d have to alter that code yourself, and maintain your edits.
Here are a few examples of code:
There are no tools for reverse engineering PostgreSQL tables + stored procedures + views, and transforming into DocTypes.
That sounds extremely cool, though. 
Can you say more about this?
As others have said, translating table schemas to frappe doctypes should be doable, with a few caveats. If you’re hoping to automatically convert sql triggers to frappe controller methods, however, that’s probably going to be very challenging.
Hello,
I just want to convert Tables to DocTypes and nothing else.
The rest will work automatically at Database server level. Provided table names are maintained as they are.
I am not looking at converting Triggers, SPs, etc.
TIA
Yogi Yang
You might look at the Virtual Doctype feature that was introduced in v13 but much improved in v14. It allows you to define arbitrary backend storage.
This is an interesting suggestion.
I am interested in trying to use this feature. Is there any working and well commented sample of Virtual DocType that I can refer to, to learn about this sleek feature?
Regards,
Take a look here …
https://frappeframework.com/docs/v14/user/en/basics/doctypes/virtual-doctype
And this PR uses virtual doctype approach
Hope this helps.
Hello,
Thank you for the link. I have already studied this. But I want to understand, what happens if the SQL query returns fields which are not declared in the DocType?
TIA
Yogi Yang
Well, you have to write your own controller (on .py file) that connects to your db, launch the quere, read and transform the data, adapting to the doctype requirements.
Sample:
When you receive the data you will consider only the first 3 fields, and must adapt this to the virtual doctype structure (meta fields too: creation date, ownler, assign …). It is not easy, but Virtual Doctypes is a amazing feature.
Hope this helps.
