How to visualize/display raw Postgres Ltree data?

Updated: Sep 21, 2021


Visualize Postgres Ltree Data using LtreeVisualizer golang librarry
Ltree Visualizer Golang Library

Why do we need Ltree?



Apps Taxonomy
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


205 views4 comments

Recent Posts

See All
DSC_0719_edited.jpg

Vasu Jinagam

Hi Myself Vasu Jinagam from Bengaluru India and I am a senior product Engineer at SIXT.

 

HangoutDude is a No Nonsense Tech and story blog and I mainly talk about Golang, Java, Distributed Microservices, Opensource and Cloud computing

You can reach out to me @ jinagamvasubabu@gmail.com