What tools and techniques are used for relational database version control (structure and data)?
24th December 2012
My answer to What tools and techniques are used for relational database version control (structure and data)? on Quora
The term you are looking for is database migrations (sometimes called database change scripts).
The basic concept is pretty straight forward: you set up a table in the database that records which change scripts have already been applied. When you need to make a change (adding a column, adding a table, denormalising some data for performance reasons, adding an index etc) you write a change script that applies the change—in raw SQL or in another programming language, depending on how your migration system is set up.
These change scripts (let’s call them migrations from here) are numbered so they can be applied in the correct order. Then you run a command which checks for scripts that have not yet been applied and runs them in the correct order—then records that they have been run to the relevant database table.
The setup I’ve described above is a pretty good start. Some systems let you have reversible migrations: each migration includes instructions for reversing its effect (removing the index that was added, moving data back to its old location) which lets you run a command to revert back to a previous database state. In practise this is a nice-to-have but not essential: many migrations are by their nature irreversible, but it can make development faster if you can easily try out and then revert a database structure change within your development environment.
Really clever migration systems can even introspect your database, figure out what has changed and attempt to generate the migration scripts automatically! South, the most popular migration system for Django, does this with surprisingly good results for many cases.
If you’re interested in learning more, it’s worth reading through the South documentation: http://south.readthedocs.org/en/...
More recent articles
- Weeknotes: Embeddings, more embeddings and Datasette Cloud - 17th September 2023
- Build an image search engine with llm-clip, chat with models with llm chat - 12th September 2023
- LLM now provides tools for working with embeddings - 4th September 2023
- Datasette 1.0a4 and 1.0a5, plus weeknotes - 30th August 2023
- Making Large Language Models work for you - 27th August 2023
- Datasette Cloud, Datasette 1.0a3, llm-mlc and more - 16th August 2023
- How I make annotated presentations - 6th August 2023
- Weeknotes: Plugins for LLM, sqlite-utils and Datasette - 5th August 2023
- Catching up on the weird world of LLMs - 3rd August 2023
- Run Llama 2 on your own Mac using LLM and Homebrew - 1st August 2023