From SQL to NoSQL: why and how we migrated our app without losing our minds
When you start building an app, it is natural to reach for what you know well. For us, SQL was “old reliable”. Its solid, familiar structure gave us the confidence to move fast in the early days. Who doesn't love a good relational database, with its perfectly organized relationships and those complex queries that make you feel like a Jedi master of data?
But as the app grows and evolves, that same rigidity and structure that used to be an advantage slowly turns into a limitation. That is how “old reliable” started giving us more headaches than peace of mind.
The decision to migrate
Our app had grown and mutated a lot since day one. The data model had turned into a monster that was hard to handle. We created intermediate tables for small things, we started using jsonb to store data that didn't fit the traditional structures, and we deleted records under specific conditions that added more complexity than we needed.
We realized we were pushing our relational database past its natural limits.
That is when MongoDB showed up as the answer. Moving to NoSQL gave us advantages that SQL simply couldn't:
Flexible schemas: we could add or drop fields without redesigning the entire database structure.
Simpler queries: some operations were far more intuitive in MongoDB than in SQL.
Horizontal scalability: as we grew, we could spread the data across multiple servers more easily.
The problem was clear: SQL was no longer what we needed for the future of our app.
Simplifying the complexity
Our old entity-relationship diagram (ERD) in SQL had become a real maze. Every new feature meant more tables, more relationships, more joins. Trying to understand how all those little tables worked felt like debugging non-stop, except there were no actual bugs in the code.
Our SQL diagram looked something like this:
After a good number of modeling sessions, we managed to simplify our data schema and cut the number of "tables" by more than 50%. The result was a model that was clearer, more efficient and much easier to work with.
Here is what the new MongoDB diagram looked like:
The impact
This new structure gave us:
Faster queries: pulling the details of a validation is now a matter of fetching a single document.
Cleaner code: we got rid of the maze of complex relationships.
More flexibility: adding or removing attributes is as easy as editing a document, with no migrations needed. We can even nest whole documents inside others.
Like everything in life, this has its downsides too.
If we are not careful, MongoDB's flexibility can introduce inconsistencies or bugs that are hard to spot. But we learned to work through those challenges:
Keeping flexibility in check: we use document typing to avoid inconsistencies.
Optimized queries: we added indexes to improve the performance of certain frequent lookups.
Handling inconsistencies: we wrote data migrations for fields that lost their format during the migration.
How to migrate from SQL to MongoDB
If you have ever had to migrate a production database, you know it is not something you want to do on a Friday afternoon. It is a complex process, and plenty of things can go wrong:
There is a risk that some data doesn't make it across, or gets corrupted during the transfer.
Poor planning or a badly estimated migration window can take the app out of service and affect users.
Changing the database structure can break existing queries, because the relationships and schemas change significantly
Even so, we pulled it off, and our app didn't stop working for a single moment.
Here is how we did it, step by step:
1. Preparation and testing
We built and tested scripts to move the data from PostgreSQL to MongoDB. We tested, adjusted and tested again until we were absolutely sure.
2. First migration:
We moved all the existing data to MongoDB, but kept running on PostgreSQL. For a few minutes, our app lived in a Schrödinger state, existing in SQL and NoSQL at once.
3. The big switch:
We changed the code so Mongoid replaced ActiveRecord, and from that moment on our app started working with MongoDB.
4. Second migration:
Right after the switch, we migrated again to capture any new data created between the first migration and the cutover.
5. Verification and adjustments:
We checked that everything worked properly. Last step: breathe easy and celebrate.
This process let us migrate with no downtime and no data loss. It felt like changing the wheels on a moving car.
After the migration
Months on, we have no doubts: it was the right call. We cut down bugs, simplified queries, and the time it takes us to change the data model has dropped considerably.
For example, changing the structure of a table in SQL used to force us into complex, coordinated migrations. Now, with MongoDB, adding a new field to a document is as simple as including it in an update. Queries that used to need several joins are now resolved with a single lookup on a nested document.
Migrating from SQL to NoSQL was not easy, but the result is a system that is more agile, flexible and scalable.
If you are considering a similar move, look hard at your needs, plan it well and don't be afraid to adapt. In the end, the effort pays off.
And even though SQL will always be “old reliable”, sometimes the best move is to embrace the new.
