Ability to change DB after API creation?

[mraak]

Since I'm stuck due to #204 I wonder if it's possible to create my API's now with the disk storage, and later switch to PostgreSQL once the issue is resolved. It would be great if I could also move the data so that models in admin would remain the same. Is something like this possible?

[Aurelsicoko]

We are aware of these difficulties to switch to a database to another one. However, it's very difficult to handle without having the risk to lose data. It's not our priority for now. We recommend choosing the final database from the beginning of your project development.

In your case, I suppose there is an issue with Postgres that could be caused by Waterline.

I'm closing this issue, feel free to open it again.

[mraak]

One of the great things about Django is that you can do exactly that. You use built in sqlite for prototyping, and then you can switch to any DB back and forth, their ORM is powerful enough to handle that. Besides, it feels that Waterline is a dead project so maybe it'd be better for Strapi to move to something else like Sequilize?

[Aurelsicoko]

We already moved from Waterline. We are compatible with Mongoose and Bookshelf, and we could integrate more ORMs in the future. If the ORM can only handle SQL databases, it's easy to switch between databases. The real issue is when you need to switch from SQL to NoSQL.

We chose to start every project with MongoDB because it's schemaless and avoid the migration part for beginners. I'm pretty sure we will provide a way for the future to start with SQLite and easily migrate to a SQL database. However, as you know, it will take time and right now, we are focusing on the next release to stabilize the current ecosystem and solution. This kind of enhancements will come later. I hope you understand our position.

[mraak]

Ah, I completely forgot you have Mongo to support and that is a pain, yes. We had to ditch Meteor because it didn't work with SQL DB's and now I see Strapi is also suffering because of it. Strapi was one of very rare Node frameworks to treat SQL as first class citizen but even here we seem to hit the wall.

An idea would be to let the user select the type of DB in advance and then if it's SQL-ish you use sqlite as prototyping DB and if it's NoSQL-ish you use disk. You would then be able to switch between the DB's of the same type, but not across types.

[Aurelsicoko]

Sure, we have to make hard choices sometimes. For newcomers, it's easiest to start with MongoDB than SQL databases. However, most of the times, SQL databases are a better choice than NoSQL. That's why we decided to take sides to handle both.

Your arguments are completely understandable and your idea is one of the solutions to explore to solve this issue. We have in mind to develop a disk database that follows the Mongoose API to easily switch between disk to MongoDB. Then, I think that using SQLite for prototyping for SQL users could be a great alternative. However, the user has to make a strong choice at the very beginning of his project.

[Aurelsicoko]

FYI, in the new version of Strapi, this is no longer an issue. The generated templates are database agnostic. It means that whatever the database you use the templates are the same. You can easily switch from a SQL database to a NoSQL database without having to make any modification in your codebase.

The only issue we still need to figure out is the data migration...

[GraxMonzo]

FYI, in the new version of Strapi, this is no longer an issue. The generated templates are database agnostic. It means that whatever the database you use the templates are the same. You can easily switch from a SQL database to a NoSQL database without having to make any modification in your codebase.

The only issue we still need to figure out is the data migration...

Thanks!!!!

[MrToxy]

@Aurelsicoko that's all good and what not but there's no mention of migrations in the docs whatsoever or what needs to be done when you switch to another provider.... I've been running in circles regarding these migrations

[danielocampo2]

Hi @MrToxy , have you found out how to do this migration? I am particularly looking to migrate from SQLite to Postgres because I need to use some functions (cos, sin, etc) that are not in SQLite.

Thanks.

[deevannaveed]

Just change your database config and then yarn build or strapi build or npm run build, it should generate all the tables automatically.

[jiangwei1995]

I want to switch PostgreSQL to MySQL. What do I need to do @Aurelsicoko

[derrickmehaffy]

@jiangwei1995 might be better to open a thread on our forum: https://forum.strapi.io to have a convo like that.

[krazylearner]

Tables are generated automatically but how to migrate content from old db to new db like from sqllite to postgres ? It looks there is no good plugin which can do that

[DivyatejaChe]

Tables are generated automatically but how to migrate content from old db to new db like from sqllite to postgres ? It looks there is no good plugin which can do that

Hi, I am new to Strapi and playing around with it using --quickstart. I'd want to migrate from default DB i.e., SQLite to MySQL. Can you/ anyoe please suggest how do I do that?

[mayrsascha]

Tables are generated automatically but how to migrate content from old db to new db like from sqllite to postgres ? It looks there is no good plugin which can do that

I have stumbled over this issue and found an answer that helped me https://github.com/dimitri/pgloader