Rails and PostgreSQL for beginners

I've read a few blogs out there on using Ruby on Rails with the very excellent database postgreSQL.  But, I'd not found more than a do the installations and off you go kind of thing.  Given most of the online tutorials assume you click on "About your application's environment" to ensure all your rails malarky is working okay, I thought it sensible to tell you how.

Note: This blog assumes you have the free Aptana studio for RadRails installed, ruby installed on windows, both the rails and the postgres-pr gems installed for ruby, and you've got Postgresql up and running on your local box.  Honestly, there are
a load of online examples of installing Ruby, Rails, Aptana studio, and postgresql for Windows.  I'll not cover them here.

1) Start your postgresql server.

2) Create a directory.

3) Use Aptana Studio to point to this directory location, choosing postgresql as the DB of choice, and Aptana will create the basic sub-directory setup for rails you'll need. 

4) It will also start the rails server. 

5) About this time you'll choosing to view "About your application's environment" to find out what you've got.  This will fail.

Why?, well when you create a default rails project in Aptana Studio, or any other tool that creates the rails structures you get a default database.yml in your top level directory, that you can use to setup your DBs with this project.

But, its default content.

So ................

a) Shutdown your rails server from the Aptana Studio server's tab, or however else you do it.

b) Create a file called newdb.bat in your top level directory of your new project

Its contents should look alot like:-

    psql -h localhost -U postgres <db\create.sql
call rake db:migrate

This allows you to logon to postgres from the windows command line to create the databases the project requires as defined in an SQL batch file.

c) This SQL batch file is called create.sql in your local project db directory, which should look alot (substitute the your_project and your_username, and your_password markers with your own details) like this (in Postgresql format):-

    /* Drop and re-create the development database */
    drop database if exists <your_project>_development;
    create database <your_project>_development
        with owner = postgres
        encoding = 'UTF8'
        tablespace = pg_default;

    /* Drop and re-create the test database */
    drop database if exists <your_project>_test;
    create database <your_project>_test
        with owner = postgres
        encoding = 'UTF8'
        tablespace = pg_default;

    /* Drop and re-create the production database */
    drop database if exists rails_from_scratch_part_one_production;
    create database <your_project>_production
        with owner = postgres
        encoding = 'UTF8'
        tablespace = pg_default;

    /* Drop the user if they exist, and re-create them */
    drop user if exists <your_username>;
    create user <your_username> with password '<your_password>';

    /* Grant the user all privileges on the databases */
    grant all privileges on database <your_project>_development to <your_username>;
    grant all privileges on database <your_project>_test to <your_username>;
    grant all privileges on database <your_project>_production to <your_username>;

5) Go to the windows command line.  Navigate to where your newdb.bat file lives, and run it from there.  This will setup three project databases for you in Postgresql, one for development, one for test, and one for production.

6) If you have properly installed Postgresql properly, you can check the DB content from your PgAdmin console.  With Rails
2.1.0 this will create a table called schema_migrations in each of your three databases.  We'll come back to this soon once we've got tables to make.

6) Refresh your project in Aptana studio or whatever.  Now restart your server from the server list tab, or however you do it
and THEN click (or in my case double click) on "About your Application's environment". 

Hey Presto, you will now be presented with the environment, including the development database in which your project resides.

You are now up and running with postgresql and rails.