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.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more