Quick overview of RootDB, an open-Source self-hosted application (AGPLv3), allowing you to generate reports based on SQL queries.
Summary
Genesis of the project
For the needs of a client, I had to quickly generate various and varied usage statistics (based on tables and graphs), from several classic relational databases and integrate these reports into a back office.
The first instinct was to turn to a solution that I used for around ten years previously and which is called MyDBR. It perfectly met his needs while being affordable. MyDBR, well mastered, allows you to do a lot of things, but the interface is really dated and access to the functionalities of the graphic libraries is done via wrappers in SQL.
I looked for alternatives, self-hosted, simple to set up, maintained and with the same logic for creating reports but I did not find what I was looking for. There are, obviously, plenty of solutions that exist but there was always something that went wrong after testing, whether in the way of generating reports or the sometimes complicated prerequisites for hosting. .
Hence the idea of creating, with a colleague, our own reporting solution – because why not, after all.
Open-Source
This project was not originally open-Source and we were simply thinking of selling user licenses.
Except that today a lot of people use the cloud, and the latter comes with its integrated reporting solutions, effectively limiting the interest in this type of project. To make a long story short, I remain convinced that not everyone is on the cloud and that this type of solution may still interest some people.
Because of doubts about the very relevance of the project, I never seriously sought financing, which never allowed me to be on it full time. So it took us a long time to produce something usable in a production environment: around a year and a half.
Added to this is the fact that this project would not exist without all the open-Source building blocks on which it is based. And since it is open-Source that has sustained me for a number of years, it ultimately seemed much more natural to me to make this project open-Source (AGPLv3 license) than to try to sell it by encrypting the Source code.
RootDB?
Being familiar with SQL and JavaScript, we wanted to have a solution that did not put obstacles in the developer’s wheels, namely:
- mainly use SQL for data retrieval and processing;
- have full access to the chosen graphics library;
This choice to prefer a developer-oriented report development environment is assumed, hence the name of the project.
Features
I’m not going to show you all the features because the main website and the demo instance already show them correctly. I will therefore rather highlight the specificities of the project.
Websocket
SQL queries can take a long time to run, especially if the tables are not properly optimized. Consequently the interface relies heavily on websockets in order to avoid timeout problems. When a report is executed, the execution of the different queries is dispatched asynchronously and the views display results only when the data arrives on the report websocket.
Generally speaking, the entire interface is refreshed by websocket.
Graphic libraries to choose from
We provide access to Chart.js or D3.js, without limitation, without wrapper. It is therefore possible to refer directly to the official documentation of these two libraries.
We like the menus. -
-
