Tutorial

The following tutorial uses PostgreSQL for its examples but other database engines will work quite similarly. See the platform-specific notes for the database you are interested in for differences.

##Creating a database Write a SQL script to create the first version of your database and put it in a file called MyDatabase.0000.sql.

-- You are responsible for including transactional semantics in your scripts if that is what you desire.
BEGIN TRANSACTION;

CREATE TABLE thing
(
	thing_id serial NOT NULL PRIMARY KEY,
	name text NOT NULL
);

CREATE TABLE person
(
	person_id serial NOT NULL PRIMARY KEY,
	name text NOT NULL
);

CREATE TABLE person_thing_map
(
	person_thing_map_id serial NOT NULL PRIMARY KEY,
	person_id int NOT NULL REFERENCES person (person_id),
	thing_id int NOT NULL REFERENCES thing (thing_id)
);

INSERT INTO thing
(name)
VALUES
('head'),
('computer'),
('laser gun');

INSERT INTO person
(name)
VALUES
('Greg');

INSERT INTO person_thing_map
(person_id, thing_id)
SELECT person_id, thing_id
FROM person
JOIN thing ON thing.name IN ('head', 'computer')
WHERE person.name = 'Greg';

COMMIT TRANSACTION;

• MyRepo/Database/MyDatabase.0000.sql

You can run the script on a PostgreSQL server running on your local computer with

C:\MyRepo\Database>pgdbsc checkout -u <your PostgreSQL username>

pgdbsc will prompt you for your PostgreSQL password, then create a database called MyDatabase and run your script to initialize the database. pgdbsc figures out the name of the database from the name of the SQL script. If you wanted to create the database with a different name, you could run

C:\MyRepo\Database>pgdbsc checkout -u <your PostgreSQL username> -targetDb MyDatabaseWithADifferentName

To connect to a remote database you can do

C:\MyRepo\Database>pgdbsc checkout -u <your PostgreSQL username on dev-pg.mycompany.local> -targetDbServer dev-pg.mycompany.local

##Updating a database Later you decide to add a column to a table. You write another SQL script and call it MyDatabase.0001.sql.

ALTER TABLE person
ADD website text NULL;

• MyRepo/Database/MyDatabase.0000.sql
• MyRepo/Database/MyDatabase.0001.sql

You can update your database with

C:\MyRepo\Database>pgdbsc update -u <your PostgreSQL username>

pgdbsc stores metadata in a table called dbsc_metadata, including what version number the database is currently on, so it knows that it only needs to run the script for revision 1.

Now that there are multiple scripts, doing a pgdbsc checkout will run all of the scripts.

##Importing data Let's say you want to create a local development database with data from your QA environment. You can do this with

C:\MyRepo\Database>pgdbsc checkout -u <your PostgreSQL username> -sourceDbServer qa-pg.mycompany.local -sourceUsername <your PostgreSQL username on qa-pg.mycompany.local>

If the database on the source database server has a different name than the database name in the SQL file names, you can do

C:\MyRepo\Database>pgdbsc checkout -u <your PostgreSQL username> -sourceDbServer qa-pg.mycompany.local -sourceDb MyDatabaseQA -sourceUsername <your PostgreSQL username on qa-pg.mycompany.local>

##More options ###Stopping at a certain revision If you want to have a database at a certain revision, you can use -r <revision number>

C:\MyRepo\Database>pgdbsc checkout -r 0 -u <your PostgreSQL username>

C:\MyRepo\Database>pgdbsc update -r 0 -u <your PostgreSQL username>

###Customizing database creation By default, dbsc will do a simple "CREATE DATABASE $DatabaseName$" to create the database before running the first script. You can customize this process by writing a SQL script to run instead. $DatabaseName$ will be replaced with the name of the database being created. Then pass the -dbCreateTemplate option when creating the database.

C:\MyRepo\Database>pgdbsc checkout -dbCreateTemplate MyDatabaseCreateTemplate.sql -u <your PostgreSQL username>

--- Here's an example of a template that sets the encoding and collation of the database.
CREATE DATABASE $DatabaseName$
       ENCODING = 'UTF8'
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252';

A database creation template is a good place to set database options and create database users and assign permissions. You might have one template for your development environment and another for your QA environment.

###Script naming

Scripts take the form of MasterDatabaseName.RevisionNumber.Comment.sql. MaserDatabaseName is the default name of the database. The comment is optional and can be left out. It can be useful to easily see what a script's purpose is without opening it. The script for revision 1 above could be called MyDatabase.0001.person_website.sql. Revision numbers may be zero-padded. MyDatabase.0001.sql and MyDatabase.1.sql are equivalent.