Fast sitemap.xml generation using Postgres.

create-sitemap.sh

#/bin/sh
(
  echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?><urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">"
  psql -c "COPY (SELECT xmlelement(name url, xmlforest('http://writebox.co.uk/news/' || slug AS loc, to_char(updated_at, 'YYYY-MM-DDThh:mm:ss+07:00') AS lastmod)) FROM news) TO STDOUT" writebox_$2
  echo "</urlset>"
) > $1/public/sitemap.xml
cat $1/public/sitemap.xml | gzip -9 > $1/public/sitemap.xml.gz

sitemap.js

var grunt = require('grunt');
var exec = require('child_process').exec;
var fs = require('fs');

function createSitemap(cb) {
  if(grunt.file.isFile('public/sitemap.xml')) return cb();
  // Tell the creation script the base directory to work from and the environment (for database name)
  exec('./script/create-sitemap.sh ' + process.cwd() + ' ' + process.env.NODE_ENV, function(err, stdout, stderr) {
    cb(err);
  });
}

exports.sitemap = function sitemap(req, res) {
  createSitemap(function(err) {
    if(err) return res.status(503);
    if(grunt.file.isFile('./public/sitemap.xml.gz')) {
      res.header('Content-Encoding', 'gzip');
      fs.createReadStream('./public/sitemap.xml.gz').pipe(res);
    }
    else {
      fs.createReadStream('./public/sitemap.xml').pipe(res);
    }
  });
}