Getting Started with PostgreSQL on Heroku

The instructions on Heroku are clear except for advice to promote the original database name such as HEROKU_POSTGRESQL_WHITE_URL to DATABASE_URL and then to remove the database with the original "colour" name to avoid charges for exceeding the free quota. When we removed the original we were left without a database at all, so started again and worked with the "colour" name without problems. We uploaded to the root folder the same file of SQL commands (sql.txt) as we used with MySQL and executed all the instructions in the file from the command line:

heroku pg:psql HEROKU_POSTGRESQL_WHITE_URL (to log on)
\ir sql.txt (to instruct SQL commands to be taken from the local file) 
\q (to log off)

The commands were all accepted, but the table identifiers were changed to lower case, so we advise you to use lower-case table names from the outset. The code below shows access to the database and retrieval of selected data.

The PostgreSQL Demo is based on our PHP/MySQL version described on our Pascal Programming for Schools website. You can see typical node.js code below and compare it with the PHP. We modified the client program by removing the .php extensions from the filenames of the http requests. (This is less confusing than leaving them in place and handling them with node.js code in the server).

else if (path=="/get_programs") { var pg = require('pg'); pg.connect(process.env.heroku_postgresql_white_url, function(err, client) { response.writehead(200, {"content-type": "text/plain"}); response.write('<p><a href="index.html">home</a></p><table><tr><th><b>name</b></th><th><b>description</b></th></tr>'); var student_id = querystring.parse(query_str)["q"]; var progquery = 'select name, url, description from programs where programmerid = '+ student_id + 'order by name'; if (student_id=='999') { progquery = 'select name, url, description from programs order by name'; }; var query = client.query(progquery); query.on('row', function(row) { response.write('<tr><td><a href="http://www.pp4s.co.uk/main/prog-'+ row.url+'.html" target="_blank">'+ row.name+'</a></td><td>'+row.description+'</td></tr>'); }); query.on('end', function() { response.end("</table></body></html>"); }); }); }

<< The previous page provides advice on getting started with Heroku.