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 Im not a database expert by no stretch of the imagination, I will just say that theres nothing wrong about it if used in the right way.

So my problem: modelling visa checklists

I built the frontend to look like this:

https://lh7-rt.googleusercontent.com/docsz/AD_4nXeuob_7mZXdBqXm0rivtfWx0MqxY41XRB54p_CPbf8fHYJohJsLeWvWefn9AC97kONuOr3E_s3JKebCxGUcKjD83ohgsk6CZwi9bHi2gXXiZs9Am9nZTFjerLJjczvnuXDtDD_tuuRc6kUco7CMFeQeGRjv?key=2PRJ3Zt1zIoz1c_AAE_1VA" alt />

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, youd 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 its 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.

• Wed 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 Im 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. Its just a pain.

Realising I was creating configuration data

So, naturally, I thought, thats a lot of work for the database to do for basically what is configuration. And theres literally no benefit because I dont any indices on configuration data that Id need. I dont need to normalise it. Yes, Id 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!

https://cdn.hashnode.com/res/hashnode/image/upload/v1726740276375/0bbd01fe-48cd-4329-b663-5105a1515f97.png" alt class="image--center mx-auto" />

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 https://planetscale.com/learn/courses/mysql-for-developers/schema/generated-columns">generated columns in MySQL and thankfully in SQLite. I learnt that from Aaron Francis article on Planetscale. If youre using Postgres, then apparently you can create indices more easily with https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql/">JSONB and Gin.

So hopefully Im forgiven for using JSON in relational SQL. And if youre interested, you can find the functional for https://melimundo.com">visa documents on Melimundo.com where the plan is to help people prepare for things like the https://melimundo.com/spain-digital-nomad-visa">Digital Nomad Visa in Spain. Its a work in progress but Im happy about that functionality.

Thanks for reading!