Authentication error when connecting to Heroku PostgreSQL database
Solved it by setting PGSSLMODE (http://www.postgresql.org/docs/9.0/static/libpq-envars.html) on Heroku. It tells PostgreSQL to default to SSL. $ heroku config:set PGSSLMODE=require
node-postgres
Import sql file in node.js and execute against PostgreSQL
You can just separate consequent queries with a semicolon when passed to client.query That works: var pg = require(‘pg’); pg.connect(‘postgres://test:test@localhost/test’, function(err, client, done){ client.query(‘CREATE TABLE test (test VARCHAR(255)); INSERT INTO test VALUES(\’test\’) ‘); done(); }); And consequently, that works too: var pg = require(‘pg’); var fs = require(‘fs’); var sql = fs.readFileSync(‘init_database.sql’).toString(); pg.connect(‘postgres://test:test@localhost/test’, function(err, client, … Read more
How do I properly insert multiple rows into PG with node-postgres?
Use pg-format like below. var format = require(‘pg-format’); var values = [ [7, ‘john22’, ‘john22@gmail.com’, ‘9999999922’], [6, ‘testvk’, ‘testvk@gmail.com’, ‘88888888888’] ]; client.query(format(‘INSERT INTO users (id, name, email, phone) VALUES %L’, values),[], (err, result)=>{ console.log(err); console.log(result); });
when to disconnect and when to end a pg client or pool
First, from the pg documentation*: const { Pool } = require(‘pg’) const pool = new Pool() // the pool with emit an error on behalf of any idle clients // it contains if a backend error or network partition happens pool.on(‘error’, (err, client) => { console.error(‘Unexpected error on idle client’, err) // your callback here … Read more
SSL for PostgreSQL connection nodejs
You can achieve this like this: postgres://user:pass@host:port/database?ssl=true
How can I choose between Client or Pool for node-postgres
May I know, what thing I should consider, before choosing between Pool or Client? Use a pool if you have or expect to have multiple concurrent requests. That is literally what it is there for: to provide a pool of re-usable open client instances (reduces latency whenever a client can be reused). In that case … Read more
node-postgres: how to execute “WHERE col IN ()” query?
It looks like you may have been close based on your comment to @ebohlman’s answer. You can use WHERE id = ANY($1::int[]). PostgreSQL will convert the array to the type the parameter is cast to in $1::int[]. So here’s a contrived example that works for me: var ids = [1,3,4]; var q = client.query(‘SELECT Id … Read more