Handling tree data models with PostgreSQL and Java
As consultant / developer, one of the things I enjoy the most is building bridges between the current world and the future, between the current system and the future system, in this post I will tell of tale of a similar bridge building exercise in the SQL / Java / Docker land.
I will like to tease your appetite to read this article with a starting question and context setting .
Have you ever faced the need to store in SQL something that it is a tree like data structure ?
Examples of this requirement are recurring and I have seen it on a couple of different occasions like:
- Product catalog
- Document management system
- Employee / company management system
A few months ago I came across a very elegant solution to this problem and it is based on the PostgreSQL LTREE extension and an amazing set of articles by Pat Shaughnessy on this topic, so this is not a generic solution for any RBMS but it is leveraging PostgreSQL custom data types and if you are using PostgreSQL by reading this article you will be able replicate this solution.
Additionally Pat’s article is very good for you to understand why this solution is a good solution and I will not try to replicate this my post but I will build on top of his article and show you how to implement the solution in a modern way with :
- Schema migrations using Flyway
- Test automation using Testcontainers
- How to access the custom PostgresSQL data type from Java using Micronaut Data JDBC.
- Provision the database and application container using Docker Compose and Terraform
If you are like me you will probably be thinking in your head: “Show me the code” well the complete solution is available at my GitHub account at:
The all example code is nicely brought together by the Micronaut Framework, the example schema I am using for the testing is the tree structure bellow:
To create this the following SQL DDL statement are issued:
The statement at line 1 enable the LTREE custom data type in the target schema thus making sure that it is available for use to use it line 5 as part of the table creation as you can see this table has a column called path of type LTREE, and there is more to say about the content of this column shortly.
Additionally in line 8 we need to let PostgreSQL know that we need a GIST index for the path column.
In the image above you can see from line 1 to 7 each of the tree nodes been created in SQL code and the important part is the path column where at each record we are defining is location in the tree with each node parent node preceded by a “.” .
By using Micronaut Flyway I able to write schema creation and versioning so when the application boot it will connect to the database and figure out that if the required schema has not been create already it needs to issue the SQL statements over to the database.
In the example code you will find standard bread and butter Micronaut JDBC Data code data access code in the domain and repository packages.
In the domain layer package I have created the classes Record, LTree and LTreeConverters.
The Record class is a direct mapping one to one of the records in the previously created SQL table ‘tree’ what is particularly interesting in the domain package is the LTree class.
In the image above see the contents of the LTree class and this is Micronaut’s way of supporting custom SQL data types in JDBC code thus allowing the mapping of the LTREE PostgreSQL custom data type into an equivalent Java type, that we do not have support out the box in standard JDBC.
Micronaut uses the TypeDef annotation to know when creating reading a record from the database that it has to use a type converter for the path column custom SQL data type.
The image above it the content of the LTree type converter code allowing us to read and write to the LTREE column in the database it is on the Java side we are handling the contents as a String and Micronaut is able to use dependency injection to discover the needed type converters and the most beautiful part is that it is able to do all of this whiteout using Java Reflection as it is using annotations preprocessing at compile time, so that the generated code is efficient on memory usage.
Next up is the repository package where use the domain layer object to perform database access code.
The image above you can see the contents of the RecordRepository class and although small it is packed with juice and there is a lot going on, let us unpack and understand what is going on where.
In line 17 we are using the JdbcRepository annotation that tells Micronaut that this is a JDBC data access object and that we are talking using the PostgreSQL dialect.
In line 18 we are implementing the CrudRepository interface and this will hint to Micronaut to link for use the all basic CRUD database access pluming code. The reason this is an abstract class is because we are not the one creating instances of this class but the object of this class will be given to us by Micronaut at runtime.
The childrenOf method is where the magic of the LTREE type comes to live as you can see in line 28 we are making a SQL select query using the special “@>” operator that it means give all the node having me as parent.
Additionally in line 38 it is very important the use of the Types.OTHER as this tell the JDBC driver that this parameter is a custom SQL data type.
Finally in line 32 we are reading the result of the query to the database and mapping it to a list of Record object instances and this is possible because we have defined the LTree and LTreeConverters in the domain layer.
There is more interesting code in the controller package where I create the tree representation as a JSON object to return as part of the API response.
The image above shows how to run the code from the command line and as you can see the Flyway database migrations have been run and it found that the schema in the database is up to date.
The image above this the result of executing the a simple test using VSCode REST Client plugin.
