Get a CSV download out of PostgreSQL or lowdb database on Node.js

This small tutorial provides a solution to provide a download link for your website users to fetch a csv file including the data out of a PostgreSQL or lowdb database. Have fun! It's also part of the series "How To: Smart Home setup to track indoor temperatures and humidity with sensors, Raspberry Pi, MQTT, Node.js, Vue.js and Chart.js"

At first we create a basic Node.js application with Express.js and setup and connect to PostgreSQL or lowdb database. Make sure your PostgreSQL database is accessable on port 5432!

// import Express.js
const express = require('express');
const app = express();
// import lib needed for csv download in lowdb
const jsonexport = require('jsonexport');
// connect to lowdb
const low = require('lowdb');
const adapter = new FileSync('db.json');
const db = low(adapter);
// connect to PostgreSQL db
const Pool = require('pg').Pool;
let pool;
try {
    pool = new Pool({
      user: 'postgres',
      host: 'localhost',
      database: 'whatever-db-name',
      password: 'password',
      port: 5432,
    })
    pool.query("CREATE TABLE IF NOT EXISTS posts (id VARCHAR(100) PRIMARY KEY, room INT, temp VARCHAR(30), humidity VARCHAR(30), time VARCHAR(100))");
  } catch (e) {
    console.log(e)
  }
// setup Express web app
app.listen(port, () => console.log('App listening on port ', port));

Then we create a new endpoint providing a csv download of the lowdb database:

// download CSV
app.get('/lowdb-download', function (req, res) {
  try {
    var reader = fs.createReadStream('db.json');
    var writer = fs.createWriteStream('db.csv');
    var pipe = reader.pipe(jsonexport()).pipe(writer);
  } catch (e) {
    console.log(e)
  }
});

Or, if you use a PostgreSQL database this snippet works:

app.get('/postgres-download', function (req, res) {
  try {
    var writer = fs.createWriteStream('db.csv');
    pool.connect(function (pgErr, client, done) {
      var stream = client.query(copyTo('COPY posts TO STDOUT'));
      var pipe = stream.pipe(writer);
      pipe.on('finish', function () {
        var stream = fs.createReadStream('db.csv');
        res.attachment('db.csv');
        stream.pipe(res);
      });
    })
  } catch (e) {
    console.log(e)
  }
});

Now start your app with node server.js and check your implementation on http://localhost:3000/postgres-download and http://localhost:3000/lowdb-download. Your browser should start the download automatically.