How to export in nodejs a csv file from nodejs with a PostgreSQL

Question

the nodejs file is on Heroku and I'm using PostgreSQL as a Database I would like to export a csv file from a view :

// My PostgreSQL query

var copyTo = require('pg-copy-streams').to; 
var csv = require('csv');
var fs = require('fs');

var stream =  client.query(copyTo('COPY (SELECT * FROM v_metric) TO
STDOUT WITH CSV HEADER DELIMITER as \'|\''));

// export in csv

csv().from(stream.pipe(process.stdout,{ end: false)).to(fs.createWriteStream('sample.csv'))

I dont have any result, sample.csv is empty.

any idea?

thank you in advance

Answer 1

You can use pg-copy-streams npm module made specifically for this:

    let data = '', copyTo = require('pg-copy-streams').to;
    pool.connect(function(pgErr, client, done) {
      if(pgErr) {
        //handle error
        return;
      }
      var stream = client.query(copyTo(`COPY (${query}) TO STDOUT With CSV HEADER`));
      stream.on('data', chunk => {
        data += chunk;
      })
      stream.on('end', response => {
        console.log(data)
        done();
      });
      stream.on('error', err => {
        done();
      })
    })

Hope this helps.

Answer 2

The SQL Server's STDOUT will be different than your node processes's. You could try streaming the query results:

var query = client.query('SELECT * FROM v_metric');
query.on('row', function(row) {
    //handle the row here
});

Then in your handler if your data isn't complicated (i.e. no delimiters or double quotes) you could skip using csv and iterate over the columns to convert them into a string that you write to the write stream. May be easier to have the column names in an array which you can pass as the SQL (via joining with ', ') and iterate over in the handler, but you could also extract the column names using Object.keys(row).

UPDATE: Example based on your comment:

var columns = ['country_cd','product_name','product_lvel','month_year','metric_name','val'];
var ws = fs.createWriteStream('sample.csv');

var query = client.query('SELECT '+columns.join(', ')+' FROM users');
query.on('row', function(row) {
    var values = [];
    // process column values; if you need to do special formatting (i.e. dates) don't loop and instead handle each one specially
    columns.forEach(function(col) {
        values = row[col];
    });
    ws.write(values.join('| '));
});

query.on('end', function(result) {
    ws.close();
});

If you do want to use csv you can create a stream that you write to write data to in the handler and pip that to csv.

One other note, , is the default delimter so if you want to use something else, like |, you will need to specify that in the options.