Customizable database-based reporting

My Final Project

I often struggled when I was trying to create customizable reporting tool for my projects. I wanted to have ability to have secure data mining tool which is simple and easy to use.

Of course, there are a lot of project like Metabase or Kibana. These tools are super cool, but sometimes to complicated or hard to customize for special use-case. You can also use GraphQL, but you still need to specify queries on the back-end.

I decited to create a simple JSON-based query language, which is converted into raw SQL query on the server side. Sounds too crazy? Yeah, because it is kinda crazy (because of security and stuff).

My idea was to create subset of DQL (data query language) functionality interpreted as JSON objects on the front-end application. To be sure about security, build query is checked on the API if it's valid and user is trying to access only his data.

This projects was subject to my final thesis.

Link to Code

I created library called duckql, which converts JSON into raw SQL.

For example, JSON object like this:

{
  "obj": "structures.Query",
  "entity": "users",
  "properties": [
    {
      "obj": "properties.Property",
      "name": "users.name"
    },
    {
      "obj": "properties.Property",
      "name": "users.surname"
    }
  ],
  "conditions": {
    "obj": "structures.Comparision",
    "properties": [
      {
        "obj": "properties.Property",
        "name": "users.age"
      },
      {
        "obj": "properties.Constant",
        "value": "15"
      }
    ],
    "operation": "gte"
  }
}

is converted into query like this:

SELECT users.name, users.surname FROM users WHERE (users.age >= 15);

My solution is based on awesome pydantic library.

Source code is avalaible on GitHub: duckql-python.