DEV Community

Dimitrios Desyllas
Dimitrios Desyllas

Posted on

Postgresql Proxy for analyzing complex and legacy db schema on spaghetti code

Whist working this Idea came across to my mind a proxy server that both forwards postgresql queries to actual database but to an analyzer as well.

The idea is as follows:

Image description

What I want top achieve is to somehow document any impact that will have any schema change to my existing apps.
I want to obtain the knowledge so I can use it in situations for example if I alter a table I want to know:

  1. What files and app sections will be affected
  2. What views will be affected.

For any PHP app I either will modify the PDO or the frameworks DB connection in order to prepend SQL comment that will contain the file and optionally the URL of my query.

An alternative approach will be:

Image description

That the proxy will create for each query a checksum and provide it to the analyzer. Afterwards the PHP App for each query will also provide a checksum the file and the Url.

For each case I will generate a map of table - url - file affection. So I can know what table affects what.

How do you lime my idea? Are there ready-to-use tools instead?

Firthermore, in my case the app is in PHP so In order for the analyzer to work I want somehow to "ruffian" the url and file into the analyzer therefore I thought these ideas:

  1. Extend the PDO class
  2. Implement my own driver extending the existing one from PHP

Is this a good idea though? Could I do with a custom extention for example something like xdebug but analyzes only the nessesary pieces of code.

Top comments (0)