From TypeScript to SQL: Automatically Granting DB Permissions Without Losing Your Mind

TL;DR

Managing database grants across multiple microservice APIs became a repetitive, error-prone task for us at IVAO. Developers often forgot to request new permissions, leading to fragile deployments.

We built a tool extending the Typescript compiler that analyzes the API source code (using AST traversal) to automatically detect which Sequelize models—and therefore which database tables and operations—are actually used. It then generates the exact SQL GRANT statements needed, with zero manual input.

The tool is fully integrated into our CI/CD pipeline and Kubernetes setup, eliminating human error and keeping permissions minimal. We open-sourced it here:

-> ivaoaero/sequelize-grant-generator

A bit of context

Preamble

At IVAO, we partially follow micro-services best practices. This means each business group (e.g., Flight tracking, Flight planning, Air Traffic controlling, Training) has its own NestJS API and ReactJS frontend. But we took a shortcut on the DB side, as we have quite some references across systems, especially for user IDs, airports, etc... We decided to have a single MariaDB instance while having a dedicated logical database for each API, so we still have a separation of concerns but we can guarantee data integrity with foreign keys across those databases.

The "issue" we were facing

Although each API has SELECT, INSERT, UPDATE, DELETE grants on all tables in its own database, we didn’t want to grant these permissions on all databases, as per the principle of least privilege, we only grant what is actually used by the API.

Each time a new feature required new grants, the developer would grant it manually in the staging database to make sure everything worked, and then ask a Tech Lead to do the same in the production database. Now you start understanding how many issues can occur with this workflow...

We needed a deterministic way to manage the DB permissions for each of our APIs to prevent human errors and speed-up our deployment time and migration time when we deploy a new DB instance.

To be honest, this takes us only a few minutes a year, but it's a boring and repetitive task...

Some unsuccessful attempts

Documenting the needed grants

Our first idea was to list all needed grants in the README.md file of the service, so any changes would be easily visible in a Pull Request, hoping the Tech Lead would notice it and make the changes accordingly.

But we anticipated that developers might forget about updating that file and it would quickly end up out-of-sync... Also, it's not a very clean solution. So we decided against it.

Using a 3rd-party tool

On the open-source community, there are many tools that allow you to configure your MariaDB instance with configuration files, as per Infrastructure as Code principle.

With such solution, the developer would need to update a config file (located in the same repository or not) with the needed grants and it would be picked up by the tool that updates the grants automatically.

Although it looked like an elegant solution, it still required developers to manually edit an additional file after working on a feature in the code, and could be forgotten since there was nothing checking its integrity.

A True Source of Truth, Without Extra Work?

Both solutions above forced developers to review the code they worked on, identify the new grants to add or remove, and finally update a separate file to reflect those changes.

I was convinced we could find a way to manage those grants automatically so the developer didn't have to think about it and just import the models he needed in the code.

Over-engineering It (Because Why Not?)

Source

The first question I asked myself: "How can a program know which grants an API needs ?"

The answer was: "Go look in the API's code and figure it out !"

Just as a reminder, our APIs are coded in Typescript with NestJS and Sequelize as our ORM (with the Typescript extension).

My first thought was to write Regexes to extract the models and their usage from the source code, but I knew it would turn into a messy rabbit hole even before I begin.

At this moment I had a flashback from a recent Computer Science course I had: Program Compilers in which we studied the theory behind them and built our own compiler for a simple language (Tiger). There I learnt that compilers actually analyze the whole code and build an Abstract Syntax Tree (AST) that represents the instructions with the correct types, binding, checks, etc...

Indeed, Typescript knows exactly which classes and variables were Sequelize models (as each model extends a shared base Model class) and what methods could be used on them!

One of the key moments from the course that stayed with me, was a quote from the professor:

You can build compiler plugins for any compiled languages to override/extend some actions

-- EPITA professors: Étienne Renaut, Ghiles Ziat, Loïc Blet

Although Typescript isn't a compiled language, it still type-checks the code before transpiling it to JavaScript.

Leverage Typescript type-checking to identify used DB models

In our case, all DB models are defined in a separate NPM package (@ivao.aero/database), which we import in all our APIs, so models can be referenced from other APIs if needed (as explained in the preamble).

In 95% of the case, here is how the code looks like in a given service:

import { Airport, Country } from '@ivaoaero/database';

class CountryService {
    async getCountriesWithAirports() {
        const countries = await Country.findAll({
            /* args */
            include: [Airport]
        })

        // Do some processing

        return countries;
    }
}

For that code, I need my API to have SELECT grants on airports, airport_countries (hidden Many-to-Many table), and countries

By breaking down the problem at hand, I realized I could make it easier by identifying the classes imported at the top of the file, as they would always come from the same package. This is when I found a StackOverflow answer that helped me a lot to lay the foundations for this crazy project.

I also realized that working with AST is very portable from one language to the other, once you understand the theory behind it.

Iterating to improve the tool

At my first attempt, my tool was able to load a Typescript project from the tsconfig.json file, go through each file to get the AST built by the Typescript compiler, and extract all the models imported in the project (ImportDeclaration nodes).
But I still had 2 missing issues:

• I didn't know how those models were used. Did the code only require SELECT or needed INSERT, UPDATE, DELETE as well ?
• I was missing the hidden Many-to-Many tables that are used when building nested relations, as they aren't directly referenced in the code (whole point of the ORM is to abstract this ;))

I understood that I needed to traverse the whole AST, instead of stopping at the import statements, to find the models usage (CallExpression nodes).

It wasn't as straightforward as processing the imports because you can have some edge-cases like this one:

import { Airport } from '@ivaoaero/database';
class AirportEntity extends PickType(Airport, ['id', 'name']) {}
async func() {
    const instance: AirportEntity = await Airport.findOne(/* args */);
    await instance.update(/* args */);
}

In this case, instance isn't of type Airport but AirportEntity as I have selected only from fields/attributes. My tool wasn't able to understand that .update actually referenced the Sequelize method from the parent class.

I will not go into details on every blocker I encountered but I can confirm it was quite fun to debug Typescript typings to figure out which ones were actual Sequelize models. Also, finding hidden Many-to-Many relations between used models...

After some long evenings, I finally had a working tool !

How does it work ?

Now that the tool is finished, here is how it works: Give it the folder in which the API is stored, and it will return a SQL query which will grant that API's DB user all the grants it needs to function!

For IVAO, we have integrated this tool into our CI/CD pipeline:

• When we run yarn build in our pipelines, it also exports the needed grants into a file stored in dist/ which is packaged in the Docker image
• In our K8s environment, we added an Init Container that starts just before the API, reads the needed grants from the packaged file, and applies them.

To conclude

With this tool fully integrated into our development process, developers no longer need to think about DB grants—whether adding new ones or cleaning up unused permissions. The codebase itself is now the source of truth for what’s needed at runtime.

We’ve open-sourced it for others who might face similar issues: GitHub sequelize-grant-generator. There, the complete parsing process is documented if you want more details.

Sure, the manual process wasn’t a huge burden. But building this tool? That was the fun part.