|Real World Schema Exploring With Azimutt|
|Written by Nikos Vaggalis|
|Thursday, 11 August 2022|
Azimutt is a free and open source database schema explorer with many great features. I put it to test when designing a Spring Boot application. Here's what I discovered.
Azimutt's main functionality allows exploring your schema by searching for relevant tables, following the relations and even finding paths between tables you want to connect. It is web based and as such runs within the browser therefore there's no separate binaries to download and install, something that makes it portable.
My discovery of Azimutt came at a crucial moment when developing a Spring Boot application. To set the scene, the application interacts with a SOAP web service and uses Hibernate to store the data that it consumes. On top of that the UI is written in Vaadin.
However, the XML schema provided by the SOAP endpoint was very complex, containing many entities and relations between them.To get more insight of the XML schema, a first attempt was made to visualize it trying out a number of tools and ending up with this labyrinth :
Decent attempt, but you really can't make anything out from this diagram, even if fullly expanded.
Let's try something else. Since the schema is overly complex and large, code generation through the powerful Hyperjaxb Java plugin was a godsend. Thus this XML schema was subsequently used as a template that was fed into hyperjaxb in order to generate the Entities, together with their JAXB and JPA annotations and mapped relationships. JAXB was necessary for marshaling the XML SOAP packets to and from the data structures, and JPA, for mapping them to database tables and enabling ORM capabilities.
An example of central to the schema entity generated by Hyperjaxb is "InvoiceGet":
which, as seen from the relationship, is mapped to another entity AllowanceChargeGetList by FK
which itself is mapped to yet another entity, AllowanceChargeGet
and the story goes on. As a whole it was very difficult to visualize what is happening just by reading the code, given the closely related and unitutive table names.
Nevertheless, since Hibernate auto-ddl created the tables and their relationship constructs like the Foreign keys, you can point your favorite SQL devtool, like Oracle SQL Developer, towards your database in order to navigate the relationships graphically. This was the output of one such tool:
Still not satisfied.
At this point, the truth is that, since I've created the tables and their relationships in the database, navigating the schema wouldn't be imperative since Hibernate takes care of managing it. However there's two problems with that statement. First that you've got to know your schema for optimizations and troubleshooting and secondly colleagues are coming up and asking 'how does this table relate to the other?'
So the search continued. Searching for a good tool I couldn't find one that would be portable as well as easily used, properties necessary in order to communicate efficiently with third parties. And then suddenly my Twitter notification lit up. I got followed by Azimutt. What was Azimutt? 'Explore your SQL database schema' was there written on their profile! My first thought was 'Am I being watched?', the next one was 'Let's give it a try'. And I was very glad that I did.
To use it, however, I had somehow to feed it my schema. Hibernate again came to the rescue as Hibernate can also generate the SQL DDL. A sample of what I ended up with was :
So the first thing that I did was to examine that pesky InvoiceGet-to-whatever relation. With a bit of fiddling this is what I got :
Finally I could clearly understand what was happening. I shared the diagram with my colleagues so they would understand it too.
Saying that, I had barely scratched the surface of what Azimutt can do but it looks like it is living up to its promise. You can:
Privacy wise everything is local despite using a browser since everything is stored in your localStorage. You only load your file in your browser, which is then parsed, displayed and stored in your browser. The server is not involved at all, not even an http request.
Also it is fully open source with its source code being hosted on GitHub, therefore if you are a dev you can install it locally on your own machine or self-host it.
And of course what saves the day is the intuitive user interface; good to look at and easy to use.
I'm sold, just get my money! What, it's free? No way!
Apache OpenJPA - Life Beyond Hibernate?
Hibernate goes Reactive - What Does That Mean?
SQL Snippets - Crowdsourcing SQL Queries
SQL Workshop – Having Clause With NULLs
SQL Workshop - Removing Duplicate Rows
SQL Workshop - Subselects And Join
To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.
or email your comment to: firstname.lastname@example.org
|Last Updated ( Friday, 12 August 2022 )|