PCS JS Immersion

Postgres and PSQL

SQL via Javascript: A Quick Look at Node's PG module

So how do we write SQL from JavaScript? One way to do it is to connect to the SQL server and write the SQL queries by hand. Let's give it a try:

npm install pg
// node index.js
var pg = require('pg');
var settings = "postgres://localhost/test"; // "postgres://username:password@localhost/database";
var id = process.argv[2];

if (process.argv.length <= 2) { return console.error('please provide an id to look up'); }

var client = new pg.Client(settings);
client.connect(function(err) {
    return console.error('could not connect to postgres', err);

  client.query('select * from people where id = $1::int', [id], function(err, result) {
      return console.error('error running query', err);

    console.log('%j', result.rows[0]);



Knex.js is a query builder which allows us to build queries from JavaScript expressions that can be converted to any supported DBMS.

Theoretically, it lets us write code that would work with any of the SQL databases without having to worry about differences between them. In practice, since the differences are subtle, the best practice is to choose one database for a project, and use it for production and for the entire development team.

To install, we'll use three modules: pg, knex, and Bluebird, a promise library. You'll see promises in action in just a second!

npm install --save knex pg bluebird


Let's specify the creation of tables and relationships directly in JavaScript. To do so, run:

`npm bin`/knex init

Then modify the knexfile.js file so that the development section contains:

client: 'postgres',
connection: {
  host     : process.env.APP_DB_HOST     || '',
  user     : process.env.APP_DB_USER     || '',
  password : process.env.APP_DB_PASSWORD || '',
  database : process.env.APP_DB_NAME     || 'jsi-knex-db'

Finally, we can create a migration:

`npm bin`/knex migrate:make countries

Once ready, we can fill out our 20140510084914_countries.js. Yours is named differently? It better be. Using timestamps allows Knex.js to know the order in which to apply migrations. It also avoids the possibility of two developers creating a migration with the same name (which would cause merge conflicts in our version control system).

Migrations allow us to collaborate better. Without this we would have to alter the database schema to match the changes that another developer made. Doing this manually is error prone, so most communities have built automation tools to aid with this process.

Modify your file corresponding to 20140510084914_countries.js to describe this two-way migration:

'use strict';

exports.up = function(knex, Promise) {
  return knex.schema.createTable('countries', function(table) {

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('countries');

We can now migrate forward or backward:

`npm bin`/knex migrate:latest
`npm bin`/knex migrate:rollback

If you add debug: true to your knexfile.js development setting, you can even see the SQL queries that Knex.js is running.


Create another migration for the cities table. This should match up with the cities table that we created before.

Basic Manipulation

Now we need to learn how to use Knex.js to interact with the database. To set up Knex within a node file, include these lines:

var env = process.env.NODE_ENV || 'development';
var knexConfig = require('./knexfile.js')[env];
var knex = require('knex')(knexConfig);

Now knex is an object capable of building and running SQL queries.


Here's a basic example of adding data via knex:

knex('countries').insert([{name: 'Xanadu'}]).then();


Here's an example of a simple select query:

  .then(function(result) {

Notice that knex returns a promise object, and then handles the asynchronous outcome of the query.