Ever wondered how to gain deeper insights into your PostgreSQL database's structure and relationships? Picture this: you're handed a basket of seemingly random ingredients, and your challenge is to concoct a gourmet dish out of them. That's the essence of building a schema viewer for your database. From optimizing performance when querying and rendering database structures to visualizing a directional graph within the browser, we'll take you behind the scenes of Nile's schema visualization, revealing the intricacies of querying Postgres for essential data and serving up a delectable UI experience.
The task begins with understanding what is available in Postgres to represent tables and relationships. The tables involved with that are pg_class, pg_attribute and pg_constraint, which contain information about tables, columns, primary keys, and foreign keys.
It's important to get the oidfrom pg_class, as that will be the key for the data as we massage it into a way the browser can render it. Specifically, the table id is used as the identifier for the nodes on the directional graph that will be rendered. More on that later.