Deleting/Updating content-type doesn't drop/migrate the database

[piere129]

Informations

• Node.js version: 9.9.0
• npm version: 5.6.0
• Strapi version: 3.0.0-alpha 12.1.2
• Database: MySQL
• Operating system: Windows 10

What is the current behavior?

Deleting a content-type does not drop the database table in MySQL, & when I navigate to roles & permissions, I can still see the deleted content-type and edit its permissions. When i create a new user role however, it only shows the non-deleted content-types. So for some reason, the content-types don't get deleted from the memory of existing user roles

Steps to reproduce the problem

1. create content-type
2. add a string field to it
3. save
4. delete content-type
5. content-type is not in the list of content-types anymore but the database table still exists + the reference to the content-type remains in the roles & permissions folder of a user role, when editing permissions.

What is the expected behavior?

deleting a content-type should remove all references of this content-type ever existing + drop its database automatically. Dropping the database manually still doesn't make the reference to the content-type in roles & permissions disappear. Dropping the whole database and re-creating it does however fix the problem

Suggested solutions

N.A.

• [ x ] I'm sure that this issue hasn't already been referenced

[soupette]

Hi @piere129!
As stated in #1025, we don't delete your tables we just create new ones, therefore the behavior you described is the correct one.

However, the behavior you about the users-permissions plugin is not the one expected.

[derrickmehaffy]

@soupette for ease of use and general non-technical users I think the delete function is quite important. I referenced something similar with MongoDB before as well. If you create a endpoint, then remove it, it should drop those tables.

We wouldn't want in-experienced people having to drop tables manually in mysql, there is no case in which that is the proper way to handle it :P

[derrickmehaffy]

If its not already in the works to do, I think its something that should be looked at again, I understand it isn't possible currently but in the future it will need to be. Everything should be possible via the adminUI.

[Aurelsicoko]

@derrickmehaffy I don't agree, we have to think for more use cases. What happens if you start the project with a production database and one the model name doesn't match with one of the tables in your database? Do we drop it? No, we can't do that. We want to avoid as many as we can to delete tables and data. We don't want to be the cause of data loss.

I think the best solution is to add a strict mode where the database structure follows the exact same schema defined in the project. If this mode is disabled, we will only create or add new table/column without deleting anything (as it is currently the case). We could also imagine a button in the UI to clear all the unnecessary tables and columns. It means that the user understands what it does and we won't be blamed for data loss.

[derrickmehaffy]

The problem I've noticed though @Aurelsicoko that in some cases Strapi likes to rename certain endpoints and ends up creating a new table. Or in some cases if you change the name of a Column especially with relations it will add the new column and leave the old one.

With mongoDB this usually isn't an issue but with MySQL this could cause issues. I understand that in production environment the over-all database structure shouldn't change and should be locked in, But in a development/staging mode the deletes allow for quick and easy development.

I know for the most part Strapi is being built as an API for content management, thus most of the stuff I'm suggesting don't normally apply to such a case. I for one am more so viewing Strapi as not just CMS but a general API + AdminUI for multiple purposes. And in this case complete database management to me should be key. As I have expressed before basic CRUD features should be a major priority with any development around REST APIs and I feel this should include the database structure as well.

[Aurelsicoko]

We agree on the production mode, we shouldn't allow any changes. However, we are trying to be very careful even in development because most of our users aren't senior developers and it happens that they use the same database in development and production. It means that if they make a mistake with the Content-Type Builder or in the models' structure, they can lose an entire part of their database.

I really want to avoid that case. I don't want to see some members of our community opening issues and saying: "I lost all my users in my database. You drop the entire table. I don't have a backup... What can I do to restore them?".

But, I also understand your point especially when a table or column is just renamed. However, I still think the strict mode is a great solution. By default, it will be disabled. Otherwise, if enabled Strapi will detect when a model is removed and it will also remove the table. It could also identity when a many-to-many relationship is updated to one-to-many so it means that we have to delete the many-to-many relation table.

{
  "defaultConnection": "default",
  "connections": {
    "default": {
      "connector": "strapi-bookshelf",
      "settings": {
        "client": "mysql",
        "host": "localhost",
        "port": 8889,
        "database": "strapi",
        "username": "root",
        "password": "*********"
      },
      "options": {
        "strict": "true"
      }
    }
  }
}

[derrickmehaffy]

@Aurelsicoko I think the strict mode is a great solution, even if it's enabled by default. I just wanted to share my opinion that the delete functions shouldn't be axed completely to be used as a data safety net for inexperienced developers.

After all in order to learn from mistakes they have to be made by someone :) And by axing a, as I feel, a very important feature for the power users, it isolates us to use work aroundss that could cause further issues.

[derrickmehaffy]

As a side note, I'm not sure with Mongo as I'm not as experienced with it, but with MySQL it is extremely easy to export a database as a backup. Obviously this is something that could be extremely taxing to the system. However it could prove useful if you needed to make a so called emergency backup before major deletes are done. Especially in regards to Strapi version changes where it's possible and update may break the database.

[Aurelsicoko]

@derrickmehaffy We could generate a backup but it could be very long if the database has a lot of data... Could it be another option to add? Quick vs safe migration.