0

So I'm diligently making SQL scripts for all my database changes so that they can be committed to source control but should the SQL be full of differential?

For example, if my initial commit has a 'create table' and a later update requires the removal of a column from this table, do I modify the 'create' statement so that it doesn't include the column or do I leave it unchanged and add an 'alter table' statement?

I suppose if its differential, it would be possible to take a database at any state and update it to a later state. Whereas with full, one would always have to start with a new database.

Ian Warburton
  • 15,170
  • 23
  • 107
  • 189
  • differential work well. Use what works for you and your environment. – Mitch Wheat Oct 16 '12 at 23:40
  • This sounds like an unusual approach. Generally it's best to keep the database schema as static as possible and to only change the data. Why would you want to continually change the database structure? – snibbets Oct 16 '12 at 23:46
  • The structure would change during the normal course of development of the application. – Ian Warburton Oct 16 '12 at 23:50
  • 1
    This looks like a fairly comprehensive answer: http://stackoverflow.com/questions/988426/how-should-you-build-your-database-from-source-control – snibbets Oct 16 '12 at 23:56

1 Answers1

1

Both. You have the individual "create" script for each DB object. This can be used to create the DB from scratch. In addition, this makes committing changes easier to diff visually - you see the changes to the create script of the object in addition to reading (sometimes verbose) change scripts.

Also script the differential SQL, named in such a way that it's easy to sort and apply in order. This lets you take a DB at point X up to the current schema.

Nathanial Woolls
  • 5,231
  • 24
  • 32
  • What if I don't need to create the db from scratch? (Why would I need to do that once the application is in production?) – meriton Oct 17 '12 at 00:20
  • 1
    @meriton You might want to deploy a new instance of the app perhaps for a new customer, host or test/development environment. Still, I don't see why you couldn't just run all the update scripts for the same end result. – Ian Warburton Oct 17 '12 at 00:46
  • How would you know what X is? A table in the DB with a row for the current version? – Ian Warburton Oct 17 '12 at 01:06
  • In my experience, having the creation scripts makes for easier deployment to new instances and easier reading. In theory you could run all of the diff scripts in order. In practice I think that would get unwieldily. Regarding "point X", if you are using a VCS to fetch the scripts, you can "get latest" and run new scripts, or store the revision in the DB as you suggest. – Nathanial Woolls Oct 17 '12 at 14:07