Updating the schema of CAPM without losing the data.

[JinoyVarghese]

I want to add one more column 'status' to the Books table. I'm doing this by changing the code  like, adding status below the attachments and using the command 'cds deploy' this to the sqlite db. It is working fine but the issue is that the entire data in the db is wiped. I want to keep the data in the db only but need to update the schema of the Books table. How to do that?

[catano · Accepted solution]

Hi @JinoyVarghese ,

Most likely you are looking for Schema Evolution, could you take a look at it, please?

Regards,
Peter

[JinoyVarghese · Accepted solution]

I tried adding "schema_evolution": "auto" to the package.json file but while typing the cds deploy command, it is showing error in the terminal that the deployment is failed. Showing table already exists.

[catano · Accepted solution]

Hi @JinoyVarghese ,

Could you try schema evolution with a new database file?
For example:

• move/rename: move your current database file out of the project directory to a safe place, or rename it to e.g. my-bookshop.sqlite.bkp
• run cds deploy with the schema evolution option turned on
• insert some data into the table you want to modify
• make some modifications on your schema
• run cds deploy
• check that your table has been updated and the data was kept 

I'm asking, because it seems that schema evolution only works on database files generated with the option turned on.
If the option is turned on, the database will contain a table named cds_model to keep track of schema modifications.

Regards,
Peter

[JinoyVarghese · Accepted solution]

Thanks, It is working now.