How to maintain a database structure when there are multiple instance of the DB?

Question

I've created an application that can create and update documents. A document is a relational database (it's a SQLite database).

I'm planning to update the structure of the database (add fields, add relations, add tables)

The database stores the database_version in itself. I plan to embed update SQL scripts into the software and apply them depending on the version of the loaded database. Note I will never update or remove tables or fields as I want full retro-compatibility

Is it a good solution or is there a better one?

What language is your application? Many languages have tools specifically for this use case — Daenyth, Sep 23 '16 at 12:46
This was closed as off topic, but it may be what you need: http://stackoverflow.com/questions/6997046/an-orm-for-sqlite-net-for-small-applications — JeffO, Sep 23 '16 at 17:25

score 1 · Answer 1 · answered Sep 27 '16 at 08:04

You are basically home-rolling a database migration tool. While the approach is sound, I'm hesitant to propose that you write this yourself. There are a number of tools out there that can handle this for you that are battle-hardened and have been tested thoroughly across hundreds (if not thousands) of applications. I advise you to look into those.

score 0 · Answer 2 · answered Sep 27 '16 at 08:35

Let me tell you how this is done where I work:

We develop our database (Firebird) with an IDE (IB Expert).
When we come to a point where the the changes are considered complete, we create a structural difference script with the IDE by providing the old and the new database.
We manually extend the diff script with DML statements, if needed.
Then, a new version of our client application is released. On its startup, it checks the database version (saved in the database) and - if the version is older - it applies the script to the database. When the script was applied successfully, the database version in the database is updated.

This relies completely on the capabilities of our database IDE. SQLite provides a tool sqldiff.exe for diffing two databases.

How to maintain a database structure when there are multiple instance of the DB?

2 Answers2