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":

@Entity(name = "InvoiceGet")
@Table(name = "INVOICE_GET")
@Inheritance(strategy = InheritanceType.JOINED)

public class InvoiceGet
implements Serializable, Equals, HashCode
{

protected String invoiceNumber;
protected String typeCode;
@XmlSchemaType(name = "dateTime")
protected XMLGregorianCalendar issueDate;
protected String buyerReference;
protected String projectReferenceId;
protected String contractReferenceId;
....
...
@XmlAttribute(name = "Hjid")
protected Long hjid;
....
....
@ManyToOne(targetEntity = AllowanceChargeGetList.class, fetch= FetchType.EAGER,cascade = {
CascadeType.ALL
})
@JoinColumn(name = "ALLOWANCE_CHARGE_LIST_INVOIC_1")
public AllowanceChargeGetList getAllowanceChargeList() {
return allowanceChargeList;
}

which, as seen from the relationship, is mapped to another entity AllowanceChargeGetList by FK

AllowanceChargeGetList
@Entity(name = "AllowanceChargeGetList")
@Table(name = "ALLOWANCE_CHARGE_GET_LIST")
@Inheritance(strategy = InheritanceType.JOINED)

@OneToMany(targetEntity = AllowanceChargeGet.class, fetch= FetchType.EAGER , cascade = {
CascadeType.ALL
})
@Fetch(value = FetchMode.SUBSELECT)
@JoinColumn(name = "ALLOWANCES_CHARGES_ALLOWANCE_1")
public List<AllowanceChargeGet> getAllowancesCharges() {
if (allowancesCharges == null) {
allowancesCharges = new ArrayList<AllowanceChargeGet>();
}
return this.allowancesCharges;
}

which itself is mapped to yet another entity, AllowanceChargeGet

AllowanceChargeGet
@Entity(name = "AllowanceChargeGet")
@Table(name = "ALLOWANCE_CHARGE_GET")
@Inheritance(strategy = InheritanceType.JOINED)
public class AllowanceChargeGet
implements Serializable, Equals, HashCode
{

public BigDecimal amount;
protected BigDecimal baseAmount;
protected BigDecimal percentage;
protected String vatCategoryCode;
protected BigDecimal vatRate;
protected String reason;
protected String reasonCode;
protected Boolean chargeIndicator;
protected String amountCurrencyId;
protected String baseAmountCurrencyId;
protected String taxSchemeId;
@XmlAttribute(name = "Hjid")
protected Long hjid;

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?'
because after the app is released there will be need to integrate older peripheral applications to it. So you need to know your schema to make the amendments.

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 :

create table invoice_get (
hjid number(19,0) not null,
buyer_electronic_address_id varchar2(255 char),
buyer_legal_name varchar2(255 char),
buyer_legal_registration_id varchar2(255 char),
buyer_party_id varchar2(255 char),
buyer_reference varchar2(255 char),
buyer_tax_number varchar2(255 char),
buyer_trading_name varchar2(255 char),
contract_reference_id varchar2(255 char),
....
primary key (hjid)
);

create table allowance_charge_get_list (
hjid number(19,0) not null,
primary key (hjid)
);

create table allowance_charge_get (
hjid number(19,0) not null,
amount number(20,10),
amount_currency_id varchar2(255 char),
base_amount number(20,10),
base_amount_currency_id varchar2(255 char),
charge_indicator number(1,0),
percentage number(20,10),
reason varchar2(255 char),
reason_code varchar2(255 char),
tax_scheme_id varchar2(255 char),
vat_category_code varchar2(255 char),
vat_rate number(20,10),
allowances_charges_allowance_1 number(19,0),
primary key (hjid)
);

alter table invoice_get
add constraint FK8nuo69gnpvxe02hdnqul5h86y
foreign key (allowance_charge_list_invoic_1)
references allowance_charge_get_list;

alter table allowance_charge_get
add constraint FKhgf0f8q6e7cdterrpmyx2k6i4
foreign key (allowances_charges_allowance_1)
references allowance_charge_get_list;

I uploaded the whole script to Azimutt, chose Oracle from the list of the available RDBMSs and finally ended up having a clean visual representation of my schema with which I could interact to pinpoint exactly the spots where more insight was necessary.

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:

• search everywhere
• show, hide and organize tables
• show, hide and sort columns
• search for relevant tables, follow the relations
• build possible paths between two tables you want to join
• list relations
• analyze your schema to provide you insights
• save your schema
• share your schema

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!

More Information

Azimutt

Azimutt on GitHub

Related Articles

Apache OpenJPA - Life Beyond Hibernate?

Hibernate goes Reactive - What Does That Mean?

SQL Snippets - Crowdsourcing SQL Queries

Analytic SQL for Developers

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.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info