From local MongoDB to production PostgreSQL

Hi again. I’m about to deploy to production and I’ve got two hurdles in my way. In this post I will refer to the second.

I have built a web application using MongoDB as my development local DB for good reason. But it’s time to deploy and I realize PostgreSQL was the right choice to make. So I need a plan to migrate.

What would be the outlined steps to do so? How do I handle Strapi’s internal schemas? Which are the things I should keep in mind?

Some guidance should be very appreciated.

1 Like

Hi @mrwindmills

First of all, good choice on making the switch to PostgreSQL :wink:

When it comes to migrating between NoSQL and SQL the biggest issue is going to be remapping all your relations due to the difference in the ID type (Mongo uses an ObjectID and PG uses an integer). Currently we don’t have any supporting tools to do this and will likely require a custom migration script to be built.

As to the schema, Strapi will initialize the PG DB schema on it’s first boot, after that it’s simply migrating data and rebuilding the relations, the downside to the relations mapping however is they are handled differently between the two.

With SQL on certain relations you have a middle table used to connect the two model tables, on MongoDB this isn’t usually the case. I would advise you take a dump of your entire mongo database into a format that you can import into PG (skipping the ObjectIDs during the import, let PG recreate new IDs) and try do remap them.

Unfortunately this will not be an easy process, and I would recommend going forward, using PG locally as well (or even just SQLite)

4 Likes

Got it. So, let’s imagine I switch to PG in local development and recreate the db schema. I suppose there’s no issue in restoring my previously dumped admin configuration running strapi confg:restore command, right? I’ve put some time in customizing labels in the admin UI, and I wonder if the DB schema remapping could affect that.

It shouldn’t, where that is stored; the core_store table is very specifically built for Strapi. The dump/restore uses .json files regardless of the database and it’s stored in the database as json, so there should be no issue there.

1 Like

Thank you so much for your help. I will definitely switch to local PG. Now I can forsee what the steps to migrate the DB are. Wish me luck :wink:

No luck needed, you got this :muscle:

Already done! It wasn’t that difficult :muscle:

1 Like

Hello, @DMehaffy can you please share why you said “First of all, good choice on making the switch to PostgreSQL”? As one of the solutions engineers, I’d really like to know why you feel this was a good choice over staying with MongoDB.

Another reason I’m interested is I have experience with SQL Server, MySQL and MongoDB. I’ve seen them all run well under high loads with relational data and I’ve seen them all perform terribly. Obviously setup and optimization had a lot to do with it in each case I’ve seen. I’ve been sticking with MongoDB for things I build from scratch as it’s been more forgiving for someone like me who is experienced but doesn’t consider myself a pro-database admin. Plus it’s allowed me to get better at handling MongoDB and solving related problems.

As you know, Strapi doesn’t have a process for handling schema migrations (referenced topic) that is my biggest fear for trying to use strapi with anything other than MongoDB (besides not having as much end to end SQL experience).

When working with Ruby on Rails apps, I had the migration feature with Active Record that helped tremendously with avoiding some problems. I’m about to start a project with a lot of relational data that I know how to make perform well with MongoDB (when building manually…not necessarily with Strapi) but I wanted to try PostgreSQL. I found your comment when searching for information about PostgreSQL and MongoDB in Strapi.

When i saw your comment, I wanted to get more information about why you felt moving from MongoDB to PostgreSQL was a good choice despite not knowing anything about mrwindmills database needs. Can you elaborate please? Is Strapis integration with MongoDB broken or not as first class as the other supported databases?

Note i’m not trying to trigger a religious debate. I just wanted to hear more of your thoughts on PostgreSQL over Mongodb with Strapi.

@DMehaffy I see one huge reason to push PostgreSQL over MongoDB here (Dropping MongoDB support). I’m so glad i found that thread considering the docs haven’t been changed yet. So many good points and arguments in that thread. Given the reasons pointed out there, i agree. I know what it’s like trying to keep everybody happy while in exchange watching productivity on core features go out the window. I can imagine how much that problem gets magnified on open source projects.

Strapi main attraction for me is the time it can save scaffolding and getting a proof of concept to market. I’m glad to have found the project. I have no idea if it can perform as well as RESTful API’s i’ve built from scratch with MongoDB. But with the time it can save, I’m looking forward to seeing if it can.