Sequelize and express-session model

Question

I'm trying to get express sessions to work with postgres. After couple of hours of debugging I've fixed all of the problems but one. Everything works but following:

If I run this query inside pgAdmin my sessions work correctly

CREATE TABLE "sessions" (
   "sid" varchar NOT NULL COLLATE "default",
   "sess" json NOT NULL,
   "expire" timestamp(6) NOT NULL
)
WITH (OIDS=FALSE);
ALTER TABLE "sessions" ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") 
NOT DEFERRABLE INITIALLY IMMEDIATE;

If I create model with sequelize then it doesn't save sessions. I'm certain and it is obvious I am missing some parts in my model definition and would appreciate any input.

var Sessions = database.define('sessions', {
        sid: {
            type: Sequelize.STRING,
            primaryKey: true
        },
        expire: {
            type: Sequelize.DATE,
            allowNull: true
        },
        sess: Sequelize.JSON
    });
    return Sessions;

Rest of the code working with this

var pgSession = require('connect-pg-simple')(expressSession),
sessionSettings = {
    store: new pgSession ({
        conString: 'postgres://' + dbConfig.user +':' + dbConfig.password+ '@' + dbConfig.host+ ':5432/' + dbConfig.database,
        tableName : 'sessions'
    }),
    secret: 'whatevergoeshere',
    resave: false,
    saveUninitialized: false,
    cookie: {
        maxAge: 7*24*60*60*1000
    }
};
app.use(expressSession(sessionSettings));

I have no idea how to include the missing parts and sequelize documentation is either to short on the topic, or I haven't had enough sleep

Rest of the code is available on request, but pretty certain it does not affect anything since if I don't force sync with sequelize model and use the query, everything works.

Also, here is how it looks through pgAdmin, top one is created with query in pgAdmin, bottom one is created by sequelize

Answer 1

In order that auto-generate session table with "connect-session-sequelize" , you need to invoke sync() method on SequelizeStore instance

var session = require('express-session');
var Sequelize = require('sequelize');
var db = new Sequelize('test', 'root', '****'); 
var SequelizeStore = require('connect-session-sequelize')(session.Store);

var sessionStore = new SequelizeStore({
   db: db,
   checkExpirationInterval: 15 * 60 * 1000,
   expiration: 7 * 24 * 60 * 60 * 1000
});

app.use(session({
  secret: 'keyboard cat',
  resave: false, saveUninitialized: false,
  store: sessionStore
}));

sessionStore.sync()

Answer 2

Sequelize is creating by default the createdAt and updatedAt columns, which are not allowed to be NULL. By using the module "connect-pt-simple" to add the row, it silently fails as it doesn't provide values for these two fields. Either handle the case by yourself and update these values, or in your model set these two with the property "allowNull: true", or add a default value. Of course the first option is preferred and it might be handy in the future.