SQLite database Upgrade Schema without loosing data

Question

I have a small C# Windows Forms application. It uses SQLite DB on the client's PC. I started with a Schema DB and with time I added more tables and columns to my local DB (not the client's).

I want to upgrade the client's DB Schema without losing data. So far I could update the schema but some information is lost because of drop tables and recreating them.

Can anyone help me?

This is an example of 2 tables. I just add new columns. No easy diagram generator for SQLite. Add **** for added columns

Table Usuarios CLIENT

UserId Integer 
NombreUsuario TEXT 
Password TEXT 
ProfesionID Integer 
Mail TEXT 

Table Usuarios NEW VERSION

UserId Integer 
NombreUsuario TEXT 
Password TEXT 
ProfesionID Integer 
Mail TEXT 
UseImages Integer **** 
Color Integer **** 
BackUpDate TEXT ****

Table EquiposHabilitados CLIENT

EquipoID TEXT 

Table EquiposHabilitados NEW VERSION

Equipo TEXT **** 
EquipoID TEXT 
EquipoFechaVencimiento TEXT **** 
UltimoTiempo TEXT **** 

• J.D.: Please add your Table definitions for both the client's instance and your local version, some example data, and the changes you're trying to release to the client's instance. Answers will vary depending on exactly what kind of schema changes you're trying to make.
• Guillermo: Is better now? Or should I generate the Diagram?
• J.D.: In your EquiposHabilitados table, you changed the column order by adding the new column Equipo before the EquipoID. Is it acceptable for you to design it instead to add Equipo after EquipoID?...This would simplify the solution, and generally you shouldn't care about column order in Tables too much.
• Guillermo: I'm OK with not changing order. This was a one time change. I will never change positions again :)

Answer 1

I'd alter existing tables when adding new columns but I read there are some concerns to considerate when altering tables, check https://www.sqlite.org/lang_altertable.html

Focus in the 7. Making Other Kinds Of Table Schema Changes section, in the square of correct and incorrect steps to drop/re-create a table