Postgres and PSQL

01 Oct 2015

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) {
  if(err)
    return console.error('could not connect to postgres', err);

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

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

    client.end();
  });
});

The code sample above will work fine for small-scale use, but if you're writing a server which handles many requests, you'll get faster responses if you take advantage of the `pg` module's _client-pooling_ feature. The code is only slightly more complex:

// 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'); }

pg.connect(settings, function(err, client, done) {
  if (err)
    return console.error('error fetching client from pool', err);

  client.query('select * from people where id = $1::int', [id], function(err, result) {
    done(); // done with this client; recycle it

    if (err)
      return console.error('error running query', err);

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

    pg.end(); // completely finished with the database for this app
  });
});

Knex.js

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

Migrations

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

`npm bin`/knex init

That command is a short way of saying `./node_modules/.bin/knex init` but it works from any directory. Beware of directories with spaces in their name; the path replacement won't work if the path it returns contains a space. If that happens, you can glue the whole path together by wrapping it in quotes like this:

"`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     || '127.0.0.1',
  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) {
    table.increments('id').primary();
    table.string('name');
  });
};

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.

Challenge

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.

Insert

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

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

Queries

Here's an example of a simple select query:

knex('countries').select('*')
  .then(function(result) {
    console.log(result);
  });

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