How to use pg - 10 common examples

/* global describe, before, it */
'use strict';
var co = require('co'),
		should = require('chai').should(),
		pg = require('../lib')(require('pg').native),
		testHelper = require('./test-helper');


var query = 'select name from person order by name';

describe('CoClient-Native', function() {
	var connString = null;

	before(function() {
		return co(function*() {
			var config = yield testHelper.getConfig();
			connString = config.connectionStrings.main;
		});
	});

	describe('#connectPromise()', function() {

var lodash          = require('lodash');
var dbConfig        = { host: 'localhost', port: 5432, user: 'user', password: 'password', database: 'pg_generator_test_625393' };
var generator       = require('../../lib/index.js');

var conString           = 'postgres://' + dbConfig.user + ':' + dbConfig.password + '@' + dbConfig.host + ':' + dbConfig.port + '/'; //'postgres://user:pass@host:port/'
var conStringTest       = conString + dbConfig.database;                                                                //'postgres://user:pass@host:port/db'
var conStringTemplate   = conString + 'template1';                                                                      //'postgres://user:pass@host:port/db'

var sql = {
    createDB        : "CREATE DATABASE pg_generator_test_625393 WITH ENCODING = 'UTF8' TEMPLATE = template0;",
    dropDB          : "DROP DATABASE IF EXISTS pg_generator_test_625393;",
    createSchema    : function (sqlID) { return fs.readFileSync(path.join(__dirname, 'create-test-db-' + sqlID  + '.sql')).toString(); },
    dropConnection  : "SELECT pg_terminate_backend(pid) FROM pg_stat_activity where datname='pg_generator_test_625393';"
};

pg.on('error', function (err) {
    // Do nothing on termination due to admin command. We do this to drop previously created test db.
    if (!err.message.match('terminating connection due to administrator command')) { console.log('Database error!', err); }
});

module.exports.dbConfig = dbConfig;

module.exports.generate = function generate(sqlId, options, callback) {
    module.exports.resetDB(sqlId, function () {
        generator(function (err) {
            if (err) { callback(err); return; }
            callback();
        }, lodash.defaults(options, {
            database: 'pg_generator_test_625393',
            user: dbConfig.user,
            password: dbConfig.password,
            output: path.join(__dirname, '..', 'model'),

var async           = require('async');
var dbConfig        = { host: 'localhost', port: 5432, user: 'user', password: 'password'  };


var conString           = 'postgres://' + dbConfig.user + ':' + dbConfig.password + '@' + dbConfig.host + ':' + dbConfig.port + '/'; //'postgres://user:pass@host:port/'
var conStringTest       = conString + 'pg_generator_test_724839';                                                       //'postgres://user:pass@host:port/db'
var conStringTemplate   = conString + 'template1';                                                                      //'postgres://user:pass@host:port/db'

var sql = {
    createDB        : "CREATE DATABASE pg_generator_test_724839 WITH ENCODING = 'UTF8' TEMPLATE = template0;",
    dropDB          : "DROP DATABASE IF EXISTS pg_generator_test_724839;",
    createSchema    : function (sqlID) { return fs.readFileSync(path.join(__dirname, 'create-test-db-' + sqlID  + '.sql')).toString(); },
    dropConnection  : "SELECT pg_terminate_backend(pid) FROM pg_stat_activity where datname='pg_generator_test_724839';"
};

pg.on('error', function (err) {
    // Do nothing on termination due to admin command. We do this to drop previously created test db.
    if (!err.message.match('terminating connection due to administrator command')) { console.log('Database error!', err); }
});

module.exports.dbConfig = dbConfig;

module.exports.resetDB = function resetDB(sqlID, callback) {
    if (sqlID === undefined) { sqlID = 1; }
    var client = new pg.Client(conStringTemplate);

    client.connect(function () {
        async.series([
            client.query.bind(client, sql.dropConnection),
            client.query.bind(client, sql.dropDB),
            client.query.bind(client, sql.createDB),
            function (next) {

it('continues processing after a connection failure', (done) => {
    const Client = require('pg').Client
    const orgConnect = Client.prototype.connect
    let called = false

    Client.prototype.connect = function (cb) {
      // Simulate a failure on first call
      if (!called) {
        called = true

        return setTimeout(() => {
          cb(connectionFailure)
        }, 100)
      }
      // And pass-through the second call
      orgConnect.call(this, cb)
    }

    const pool = new Pool({

it('continues processing after a connection failure', (done) => {
    const Client = require('pg').Client
    const orgConnect = Client.prototype.connect
    let called = false

    Client.prototype.connect = function (cb) {
      // Simulate a failure on first call
      if (!called) {
        called = true

        return setTimeout(() => {
          cb(connectionFailure)
        }, 100)
      }
      // And pass-through the second call
      orgConnect.call(this, cb)
    }

    const pool = new Pool({
      Client: Client,
      connectionTimeoutMillis: 1000,
      max: 1

var expect = require('chai').expect;
var sinon = require('sinon');
var pg    = require('pg').native;
var PostGISSource = require(__dirname + '/../datasources/PostGIS');

var async = require('async');
var __ = require('lodash')


var source = new PostGISSource({
  connectionString: process.env.DATABASE_URL || "tcp://postgres@localhost/postgis", //required
  tableName: "rpd_parks", // required
  geomField: "wkb_geometry", // required
  fields: "map_park_n, ogc_fid", //faster if you specify fields, but optional
  name: "sf_parks", // optional, defaults to table name
  projection: "EPSG:4326" // Lat/Long
});

describe('PostGIS Data Source', function() {

tracer.startRootSpan({ name: 'selectEventRootSpan' }, (rootSpan: RootSpan) => {
        const q = 'SELECT 4 as value'
        // Must use new Query for eventemitter
        // https://node-postgres.com/guides/upgrading#client-query-submittable-
        const query = client.query(new pg.Query(q))
        query.on('end', res => {
          assert.strictEqual(rootSpanVerifier.endedRootSpans.length, 0)
          rootSpan.end()
          assert.strictEqual(rootSpanVerifier.endedRootSpans.length, 1)
          assert.strictEqual(rootSpanVerifier.endedRootSpans[0].spans.length, 1)
          assert.strictEqual(rootSpanVerifier.endedRootSpans[0].spans[0].attributes.query, q)
          assertSpan(rootSpanVerifier, 'pg-query', SpanKind.CLIENT)
          done()
        })
      })
    })

expect(pgp.as.format('$1^', 'one')).toBe('one');
        expect(pgp.as.format('$1:raw', 'one')).toBe('one');

        expect(pgp.as.format('$1', ['one'])).toBe('\'one\'');
        expect(pgp.as.format('$1^', ['one'])).toBe('one');
        expect(pgp.as.format('$1:raw', ['one'])).toBe('one');

        expect(pgp.as.format('$1, $1', 'one')).toBe('\'one\', \'one\'');

        expect(pgp.as.format('$1$1', 'one')).toBe('\'one\'\'one\'');
        expect(pgp.as.format('$1^$1^', 'one')).toBe('oneone');

        expect(pgp.as.format('$1', [userObj])).toBe(pgp.as.text(JSON.stringify(userObj)));
        expect(pgp.as.format('$1^', [userObj])).toBe(JSON.stringify(userObj));

        expect(pgp.as.format('$1, $2, $3, $4', [true, -12.34, 'text', dateSample])).toBe('true, -12.34, \'text\', \'' + $pgUtils.prepareValue(dateSample) + '\'');

        expect(pgp.as.format('$1 $1, $2 $2, $1', [1, 'two'])).toBe('1 1, \'two\' \'two\', 1'); // test for repeated variables;

        expect(pgp.as.format('Test: $1', ['don\'t break quotes!'])).toBe('Test: \'don\'\'t break quotes!\'');

        // testing with lots of variables;
        let source = '', dest = '';
        const params = [];
        for (let i = 1; i <= 1000; i++) {
            source += '$' + i;
            dest += i;
            params.push(i);
        }
        expect(pgp.as.format(source, params)).toBe(dest);

        // testing various cases with many variables:

"use strict";

const cfg            = require('../../modules/confighandler/cfg.js');
const moment         = require('moment');
const readEnv        = require('../../../setup/readEnv');
const path           = require('path');
const pg             = require('pg');

let connStr;

if (process.env.DATABASE_URL /* Running on heroku */) {
	connStr = process.env.DATABASE_URL;
	pg.defaults.ssl = true;
} else {
	let env = readEnv(path.join(__dirname, "../../../"));

	if (env === null) {
		console.error("Couldn't connect to the database!\n" +
			"The .ENV file was not found, or couldn't be parsed.");
		process.exit(1);
	}

	if (env.useSSL) {
		pg.defaults.ssl = true;
	}

	connStr = env.url;
}

/**
 * Interface to PostgreSQL client
 * */
const pg = require('pg');
const knex = require('knex');
const config = require('../config');

// remember: all values returned from the server are either NULL or a string
pg.types.setTypeParser(20, val => (val === null ? null : parseInt(val, 10)));
console.log('connecting %s', config.POSTGRES_URL);
const db = knex({
  client: 'pg',
  connection: config.POSTGRES_URL,
  pool: {
    afterCreate: (conn, done) => {
      // Set the minimum similarity for pg_trgm
      conn.query('SELECT set_limit(0.6);', (err) => {
        // if err is not falsy, connection is discarded from pool
        done(err, conn);
      });
    },
  },
});
db.on('query-error', (err) => {
  throw err;