How to visualize/display raw Postgres Ltree data?

Ltree Visualizer Golang Library

Why do we need Ltree?

Apps Taxonomy

Suppose you had a hierarchical data structure in your application something like the above. How would you save it in a database and how do you represent the above complex tree into flat rows and columns?

If you think you can solve it with foreign key relationships then it is not easy because maintaining this tree and retrieving the data using recursive queries is not easy and it is going to be very expensive if the above tree getting modified.

Another approach would be using graph-oriented databases like neo4j which are designed to solve these use-cases, But you don't want to leave Postgres behind because you already had a working, well-tested application.

Ltree:

Ltree is a data type that is used to represent the hierarchical tree-like structures in flat rows and columns in Postgres DB For more info-refer this https://www.postgresql.org/docs/9.1/ltree.html

I have been using Ltree Extension for quite a long time to store hierarchical data and it is working perfectly fine.

Sample Ltree Data looks like this for the above tree.

Source: https://gist.github.com/jinagamvasubabu/14dbce3ca89199e083488d80f2d80d64

path column does all the magic, "1.2.9" means the parent of Telegram(9) is Messenger(2) and parent of Messenger(2) is Apps(1)

Why do we need LtreeVisualizer?

The simple answer is Postgres doesn't provide any UI to visualize the Ltree Data 😟

Ltree Labels are separated using Dot like 1.2.3.4 and it is not easy to visualize like a tree. I have around 5000 Nodes in my production data and it is very tough for me to visualize any subtree for debugging purposes even though Ltree provides good querying capability. So I thought of writing one library to visualize the above data using Dot Graph and Graphviz.

DOTGraph is a graph description language, using this language we can represent Directed, Undirected, and FlowCharts. https://en.wikipedia.org/wiki/DOT_(graph_description_language)

digraph graphname {
    a -> b -> c;
    b -> d;
}

Graphviz is open source graph visualization software and it can visualize DOT Graphs

How to use it?

DB Way (Connect to DB and fetch the Ltree Data):

LtreeVisualizer is capable of connecting to your DB using gorm and can fetch the data according to provided query and convert it to LtreeData which LtreeVisualizer can understand.

Simple Example:

Note: Query resultset should contain id, name, path, please use an alias if your column names are different

Using Interim JSON:

You need to prepare your Ltree Data as per the below struct

//VisualizerSchema Contract to send to ltreevisualizer
type VisualizerSchema struct {
	Data []data `json:"data"`
}

type data struct {
	ID   int32  `json:"id"`
	Name string `json:"name"`
	Path string `json:"path"`
	Type string `json:"type"`
}

That's it, pass this data to LtreeVisualizer and you convert it to either DOT Graph string or an Image.

Refer Ltree Visualizer ReadMe.Md for more info on how to use

Examples:

You can refer to examples https://github.com/jinagamvasubabu/ltreevisualizer/tree/main/examples to play around and see how it works.

Conclusion:

Ltree is a great extension of Postgres DB and the only thing which is lacking is UI to visualize this data. I hope LtreeVisualizer can fill this gap.

Let me know in the comments section if you want a dedicated Ltree tutorial.

If you like this repo and my idea, please give a ⭐ for this library

https://github.com/jinagamvasubabu/ltreevisualizer

References:

http://patshaughnessy.net/2017/12/11/trying-to-represent-a-tree-structure-using-postgres