Sacrilege: Why I decided to use JSON in a relational database to manage visa checklists?

So the consensus is that JSON in a relational database is bad. Now, while I’m not a database expert by no stretch of the imagination, I will just say that there’s nothing wrong about it if used in the right way.

So my problem: modelling visa checklists

I built the frontend to look like this:

So users can:

1. Create own checklists for visa documents.

2. They can add and remove documents required for the visa.

3. And then for each document, they can outline the steps needed for it to be completed.

Thinking this through, in terms of tables, you’d need:

1. A table for the visa itself.

2. A table for the documents.

3. Then a table for the steps.

4. And potentially a pivot table if I wanted to really normalise things.

Now three or four tables is nothing to complain about. But I was starting to question whether it’s worth modelling a very simple feature this way in the database.

• But then, if users can add steps and remove them at will, and add documents and remove them at will, now, imagine the database queries to do that.

• We’d need a transaction to first add or remove documents from the table, and then add or remove documents from the steps, especially if multiple people can edit that.

• And while I’m definitely not an expert, hard deleting from a database has to be treated carefully so as to not cause gap locks. Gap locks then cause concurrency problems. Now we have to handle retries. It’s just a pain.

Realising I was creating configuration data

So, naturally, I thought, that’s a lot of work for the database to do for basically what is configuration. And there’s literally no benefit because I don’t any indices on configuration data that I’d need. I don’t need to normalise it. Yes, I’d be repeating data to some degree, but not enough for me to worry about space issues.

Even more so, when it came to the UI, it was super easy to just build the documents using pure Vue.js and that naturally created a JSON data structure.

I was able to send the JSON to the backend endpoint, validate the shape and required step types using PHP enums, and then simply save that data in the backend as is, pure JSON!

And then, sending the JSON object back to the front end meant that Vue.js, simply hydrated the component that managed the visa documents with zero fuss. No translation or restructuring of data was needed between the front end or the backend. The backend saved JSON. The front end hydrated it. Voila!

What if indices become a problem?

Now, if indices were to somehow become a problem, there are ways around it.

For example, we can use generated columns in MySQL and thankfully in SQLite. I learnt that from Aaron Francis’ article on Planetscale. If you’re using Postgres, then apparently you can create indices more easily with JSONB and Gin.

So hopefully I’m forgiven for using JSON in relational SQL. And if you’re interested, you can find the functional for visa documents on Melimundo.com where the plan is to help people prepare for things like the Digital Nomad Visa in Spain. It’s a work in progress but I’m happy about that functionality.

Thanks for reading!