PostgreSQL: Fun with Create From Template

elephante_paper.jpg

Did you know that when you "CREATE DATABASE" you are not actually "creating" anything? Instead you are actually copying from an existing database. This existing database is called, template1This is the default behavior of PostgreSQL, but you can actually tell PostgreSQL to use another database as the template.


  -- CREATE DATABASE WITH TEMPLATE statement
  
  CREATE DATABASE new_db WITH TEMPLATE orig_db OWNER you;

This is a very quick way of making a copy of your database. There is one gotcha with this command, the orig_db needs to be idle during the copying process. This problem is the main reason why we can't use this as a general "COPY DATABASE" command, but you should keep it in mind for writing or reviewing new code that requires migrations.

Here are the basic steps:

1. Create a "master" database that stays in sync with the master branch of your code.

2. When you checkout a new branch create a new database where the TEMPLATE is the master branch database from step 1.

3.  Make your project connect to that new database.

4. Write/Review your new code using the new database.

Here are some scripts that you can use in your Rails projects to help with this process:

1. Add git hook (.git/hooks/post-checkout)

 #!/usr/bin/env ruby
 require 'pg' 
 require 'git'
 
 branch = Git.open('.').current_branch
 conn   = PG.connect(dbname: 'postgres')
 
 begin
   conn.exec("CREATE DATABASE proj_#{branch} WITH TEMPLATE proj_master OWNER me;" )
 rescue => e
   puts e
 end
  
 begin
   conn.exec("CREATE DATABASE proj_test_#{branch} WITH TEMPLATE proj_test_master OWNER me;")
 rescue => e
   puts e
 end

This git hook simply creates a new development and test database from your proj_master and proj_test_master database templates. The first time you checkout a new branch, it might take and extra couple of seconds, but the next time you checkout it will throw a database already exists exception. (Warning, if you use a branch name like feature/something the creation of the database will break because of the / character.)

2. Edit your config/database.yml

<% branch = Git.open('.').current_branch %> 

development:
  adapter: postgresql
  database: proj_<%= branch %>
  host: localhost
  pool: 5
  timeout: 5000
  
test:
  adapter: postgresql
  database: proj_test_<%= branch %>
  host: localhost
  pool: 5
  timeout: 5000

This database.yml file edit forces Rails to connect to the newly created branch database. 

There are some downsides to this approach, for example, having to restart your Rails project to connect to the new database. The benefit though is that you can work/review multiple branches and be confident that your local database is in the state you want it to be.