Small node command line script to run SQL against postgres and/or CartoDB API

run_sql.js

#!/usr/bin/env node
'use strict';
var dir = require('node-dir');
var fs = require('fs');
var http = require('http');
var _ = require('lodash');
var CartoDB = require('cartodb');
var pg = require('pg');


/*
Runs SQL against postgres DB or CartoDB.

Options:
  -d, --destination  Destination for SQL query 		[required] [choices: "cartodb", "postgres"]
  -p, --pgurl        Postgresql connection url including database name
  -u, --cartodbuser  Cartodb username
  -k, --cartodbkey   Cartodb Key env variable
  -f, --sqlfile      Sql file                                         [required]
  --help             Show help                                         [boolean]

Examples:
  node run_sql.js  node run_sql.js -d cartodb -u btno -k "CARTODB_KEY" -s
                   mysqlfile.sql
  node run_sql.js  node run_sql.js -d postgres -p postgresql:///mydb -s
                   mysqlfile.sql

*/

var argv = require('yargs')
  .example('node run_sql.js','node run_sql.js -d cartodb -u btno -k "CARTODB_KEY" -s mysqlfile.sql')
  .example('node run_sql.js','node run_sql.js -d postgres -p postgresql:///mydb -s mysqlfile.sql')
  .alias('d', 'destination')
  .describe('d', 'Destination for SQL query')
  .choices('d', ['cartodb', 'postgres'])
  .alias('p', 'pgurl')
  .describe('p', 'Postgresql connection url including database name')
  .alias('u', 'cartodbuser')
  .describe('u', 'Cartodb username')
  .alias('k', 'cartodbkey')
  .describe('k', 'Cartodb Key env variable')
  .alias('f', 'sqlfile')
  .describe('f', 'Sql file')
  .demand('d')
  .demand('f')
  .help('help')
  .argv;


var executeSQL = function(sql) {

	if (argv.destination === 'cartodb') {
		var client = new CartoDB({user: argv.cartodbuser ,api_key: process.env[argv.cartodbkey]});
		client.query(sql, function(err, data){
	        if (err) {
	          console.error(err);
	        } else {

	          console.log(data);

	          //if sql includes create table, then it must be cartodbified
	          var has_create_table = sql.match(/CREATE TABLE (.*?) \(/i);
	          var has_create_table = sql.match(/CREATE TABLE IF NOT EXISTS (.*?) \(/i);
	          if (has_create_table) {
	          	console.log("Cartodb-i-fying-table " + has_create_table[1]);

	          	client.query("select cdb_cartodbfytable('" + has_create_table[1] + "');", function(err, data){
	          		console.log(err,data);
	          	});
	          }

	  	    }
	      });
	} else if (argv.destination === 'postgres') {
		var client = new pg.Client(argv.pgurl);
		client.connect(function(err) {
		  if(err) {
		    return console.error('could not connect to postgres', err);
		  }
		  client.query(sql, function(err, result) {
		    if(err) {
		      return console.error('error running query', err);
		    }
		    console.log(result);
		    client.end();
		  });
		});
	}
}

//read file
fs.readFile(argv.sqlfile, 'utf8', function (err,sql) {
  if (err) {
    return console.log(err);
  }
  executeSQL(sql);
});