Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a need to change the database schema . I'm planning to write Schema change and update scripts for tracking database changes and updating them. I followed

Versioning Databases – Change Scripts

for a start, I got a gist of what he is getting at however since I haven't worked much on SQL scripts before, a tutorial or something to start with would be good. I did some research on the web and came to know that most people use Automatic comparing tools to generate the script which I don't want to do for obvious reason that I won't learn the anything in the process.

I'm looking for some tutorials/links on How to write Change scripts and Update scripts ? Especially update scripts as I couln't find even a single script/pseudo-code on how to do update schema by comparing SchemaChangeLog table, connecting to the table using scripts...

Thanks in advance!

share|improve this question
Although it is for PostgreSQL, you can collect some ideas from depesz's great article on the topic. We use it at my workplace extensively (and sucessfully). – dezso Aug 20 at 8:04
Great resource :) – Learner Aug 20 at 17:12

closed as off-topic by Jon Seigel, dezso, Michael - sqlbot, Mark Storey-Smith, bluefeet Aug 22 at 19:36

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Jon Seigel, dezso, Michael - sqlbot, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 0 down vote accepted

First, I would go with an existing solution, such as Liquibase. If you use an ORM such as Hibernate, it can also do schema migrations for you.

Otherwise, it would go something like this:

Start with an initial database schema script, and give it a version number, such as 0.0.0001.sql. It would contain mostly CREATE statements to create tables, etc, such as:

-- use me to keep track of what version the database schema is using:
create table schema_version (
  name text primary key
);

create table contact (
  id bigserial primary key,
  first_name text not null
);

insert into schema_version values ('0.0.0001');

You would store all of your .sql schema scripts in source control, such as git.

Then, as needed, you would add patch files, such as 0.0.0002.sql. It would normally contain ALTER statements that adjust your database schema:

alter table contact add column last_name text;

insert into schema_version values ('0.0.0002');

You will probably need to read the documentation for your particular database, as they often have slightly different ALTER syntax.

You would then have a non-sql script (bash perhaps), that would check which schema version the database is using and apply any available patches that are greater than that version, or the first sql script if there is no schema_version table.

This book is highly rated on Amazon:

Refactoring Databases: Evolutionary Database Design

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.