Categories
Dev ops

What is SQL versioning?

A quick introduction about SQL versioning and what it does.

Ever had to deal with different product versions and different database schemas?

Our client needs the v1 installed but we have changed the database ever since… can you sort it out?

Alfred from marketing

This view is broken now, can someone revert it?

Jane the tech lead

SQL Versioning is solving these kind of problems and is an important step for projects to scale up and evolve, allowing changes of database schemas easily and reliably across all instances. This is necessary for truly agile projects.

Pros

  • SQL versioning eases collaboration – it’s easier to share code changes 
  • It allows rolling back to retrieve previous versions of the DB 
  • Allows automatic deployments – no more manual operation needed 
  • Allows keeping track between databases (test, prod, local instances, etc.) 
  • Faster deployments reducing outage time 
  • Adding security by controlling ‘what’ and ‘who’ edits the database and deploys changes 
  • Single repository for ‘static’ data and database scripts 

Cons

  • Adding complexity – adding a new pipeline to the CI/CD to migrate the DB and new processes
  • Adding new responsibilities – Who triggers the deployment?
  • The team needs to learn to use the tool(s)

Example of DB versioning workflow

One way of applying database versioning to your project is by letting a developer push some database changes to a repository (say git), this change then gets reviewed by a peer and merged into a common branch of the project, triggering a CI pipeline (such as Jenkins, Gitlab or Bamboo) and running all the usual checks/tests/building/etc. jobs.
Then later on, when the branch gets deployed onto a server, the database changes are applied to the database using a tool such as Flyway or Liquibase.
You can always ‘go back in time’ and rollback your database changes to a specific version if you need to (a nasty bug making it to prod? But this never happen… right? 😉 ).

Tools

There is a number of tools used to deal with database changes such as Flyway and SQL Source Control from Redgate or Liquibase. Choosing the right tool really depends on the project, team and budget.

The big advantage of Flyway is the simplicity of its use and setup.
Liquibase is more complex to install and setup but brings more features than flyway (for the free versions) the main one being rollback. 
There is no free version of SQL Source Control, which can be a problem for small/medium projects.

Notes:  

  • Flyway doesn’t support rollback in the community edition.
  • Free version of Flyway is not compatible with old versions of MariaDB, and probably other databases.

Conclusion

To embrace agile methodologies and keep track of the database changes, DB versioning is a must have. As said, it all depends but I would usually go for the community version of Liquibase to start with and then consider upgrading if the project gets bigger.

In the next article I’ll describe how to use and setup Liquibase to work directly with plain SQL files.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

code