session.js

"use strict";

var db = require('./db');
var express = require('express');
var util = require('util');
var moment = require('moment');

var Store = express.session.Store;

var ICSessionStore = module.exports = function ICSessionStore () {
    Store.call(this);
}

util.inherits(ICSessionStore, Store);

ICSessionStore.prototype.get = function get (sid, cb) {
    db.get_one_row("SELECT data FROM sessions WHERE id=$1", [sid], function (err, row) {
        if (err) return cb(err);
        if (row && row.data && row.data.cookie) return cb(null, row.data);
        return cb();
    });
}

ICSessionStore.prototype.set = function set (sid, session_data, cb) {
    var expiration = null;
    if (session_data.cookie && session_data.cookie.expires) {
        expiration = session_data.cookie.expires;
    }
    else {
        expiration = moment().add('days', 1);
    }
    if (expiration == 'Invalid Date') {
        expiration = moment().add('days', 1);
    }
    db.upsert_object("sessions", "id", sid, {id: sid, data: session_data, expiration: expiration}, cb);
}

ICSessionStore.prototype.set_if_unset = function set_if_unset (sid, session_data, cb) {
    var expiration = null;
    if (session_data.cookie && session_data.cookie.expires) {
        expiration = session_data.cookie.expires;
    }
    else {
        expiration = moment().add('days', 1);
    }
    db.insert("Sessions", {id: sid, data: session_data, expiration: expiration}, cb);
}

ICSessionStore.prototype.destroy = function destroy (sid, cb) {
    db.query("DELETE FROM sessions WHERE id=$1", [sid], cb);
}

ICSessionStore.prototype.length = function length (cb) {
    db.get_one_row("SELECT COUNT(*) AS total FROM sessions WHERE expiration < now() OR expiration IS NULL", [], function (err, row) {
        if (err) return cb(err);
        return cb(null, row.total);
    })
}

ICSessionStore.prototype.clear = function clear (cb) {
    db.query("DELETE FROM sessions", [], cb);
}

session.sql

CREATE OR REPLACE FUNCTION removed_expired_sessions() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM sessions WHERE expiration < now();

    PERFORM deactivate_pipelines();

    RETURN NULL;
END;
$$ language 'plpgsql';

DROP TABLE IF EXISTS "sessions";

CREATE TABLE "sessions" (
  "id" TEXT PRIMARY KEY,
  "data" JSON NOT NULL,
  "expiration" TIMESTAMP DEFAULT now() + '1 day'::interval
);

CREATE INDEX sessions_expiration_idx on sessions (expiration);

CREATE TRIGGER sessions_remove_expired_trg AFTER INSERT OR UPDATE ON sessions
EXECUTE PROCEDURE removed_expired_sessions();