Fork me on GitHub

Postgres Dev Box

The easiest way to get going quickly with PostgreSQL. Download and install this vagrant box and be ready in minutes.

Donations

If you use the pgdevbox please consider donating a small sum to Nic Ferrier, who maintains it for you

What you need

If you use OSX

If you use Ubuntu

Do this:

sudo apt-get install virtualbox
curl -L http://files.vagrantup.com/packages/0219bb87725aac28a97c0e924c310cc97831fd9d/vagrant_1.2.4_i686.deb -o ~/vagrant_1.2.4_i686.deb 
sudo dpkg -i ~/vagrant_1.2.4_i686.deb 
                                    
Note this is for the 32 bit version of vagrant, see the vagrant site for information on how to install the 64 bit version

If you use Windows

Firstly, consider switching. Windows really is a useless developers operating system. But, if you really can't swap then:

Get going quickly

Here's how to get going.

mkdir mypg-project
cd mypg-project
curl http://www.pgdevbox.com/Vagrantfile -o Vagrantfile
vagrant up
                        

Now you have a running PostgreSQL server.

It's easy to to play with it using the built in psql shell inside the vagrant machine; from your shell:

$ vagrant ssh -- -t psql
psql (9.3beta2)
Type "help" for help.

vagrant= \d

        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+---------
 public | a    | table | vagrant
(1 row)
                        

will show a list of the tables installed, or show the structure of this table:

vargrant= \d a

     Table "public.a"
 Column | Type | Modifiers 
--------+------+-----------
 a      | json | 

                        

finally \quit will quit the psql shell.

You can find out more about psql here.

What does the server have?

The pgbox postgres has a single PostgreSQL 9.3 server with:

  • a superuser called vagrant
  • a database called vagrant owned by the user vagrant
  • a single JSON column table in the vagrant database called a

How do I use it with node.js?

Use SQL INSERT to put some JSON in the table:

var data = { "a": "10", "b": "20" };
var pg = require('pg'); 
var client = new pg.Client("tcp://vagrant@localhost/vagrant"); 
client.connect(
    function (err) { 
        if (err) { 
            return console.error("bah!"); 
        } 
        else { 
            return client.query(
                "insert into a (a) values ('" + JSON.stringify(data) + "'::json)", 
                function (err, res) { 
                    if (err) { 
                        return console.error("whoops!"); 
                    } 
                    else { 
                        client.end(); 
                        return res;
                    } 
                } 
            ); 
        } 
    } 
); 
                                

Or SQL SELECT something from the existing table:

var pg = require('pg'); 
var client = new pg.Client("tcp://vagrant@localhost/vagrant"); 
client.connect(
    function (err) { 
        if (err) { 
            return console.error("bah!"); 
        } 
        else { 
            return client.query(
                "select * from a where a ->> 'a' = '10'", 
                function (err, res) { 
                    if (err) { 
                        return console.log("whoops!"); 
                    } 
                    else { 
                        var row = res.rows[0]; 
                        client.end(); 
                        return console.log("result! ", row["a"].a); 
                    } 
                } 
            ); 
        } 
    } 
); 
                                

Create another table in the database:

var pg = require('pg');
var client = new pg.Client("tcp://vagrant@localhost/vagrant");
client.connect(
    function (err) { 
        if (!err) {
            return client.query (
                "create table b (id integer, txt text);", 
                function (err, res) { 
                    if (! err) { 
                        console.log("result:" , res);
                        client.end();
                        return "created";
                    }
                }
            );
        }
    }
);