How can I control database version without losing data, more like of github

Question

I am using postgres for my database and because our product is in development mode, sometimes we need to change our database scheme. If we find some problem in that we need to revert our changes, We have to do this manually. Although we have a option to make database backup before making any change. But I was looking for some solution more kind of git. So, that we don't have to do this manually.

Answer 1

I would suggest you script the creation of your database. Save this SQL script in a SqlScripts directory at the base of your project and check this into source control with the rest of your project. Then if you need to alter your database schema, you alter or add to you SQL script or scripts. In that way the structure of your database is versioned but the data is not in source control, which you would not typically want to do.

One short-coming here is you may have to manually run this script when reverting commits, but this isn't a situation that should happen too often.