Connect-pg-simple express: Failed to prune sessions: relation "session" does not exist. PostgreSQL

Question

I am a creating my first app in express and I want a user to stay logged in when making changes in express. I am using postgres, and I tried using connect-pg-simple to achieve that (https://www.npmjs.com/package/connect-pg-simple). Here's the code in app.js:

const session = require('express-session');

app.use(session({
    store: new (require('connect-pg-simple')(session))(),
    cookie: { maxAge: 30 * 24 * 60 * 60 * 1000 },
    secret: 'secret',
    resave: true,
    saveUninitialized: true
}));

Anyways, I am getting this error and I couldn't find a solution: (Failed to prune sessions: relation "session" does not exist) P.S: I already created the session table in my database - psql mydatabase < node_modules/connect-pg-simple/table.sql

Answer 1

Reason:

You are having that error because you probably do not have the session table in your database.

Probably fix:

• you may want to create a session table, or what I would really recommended, let the module create it for you
  • how to auto create it: read Code section
  • if you want to manually create it, here is the table format just for reference:
    • column sid PK, datatype: character varying NON NULL
    • column sess datatype: JSON NON NULL
    • column expire datatype: timestamp with time zone, length: 6 NON NULL

Code:

// 1st: create a session pg-related
const expressSession = require('express-session');
const pgSession = require('connect-pg-simple')(expressSession);

// 2nd: prepare configuration stuff for creating of a Connection Pool to PostgreSQL
const poolConfigOpts = {
  // check interface ClientConfig and interface PoolConfig to see what options you can use https://github.com/DefinitelyTyped/DefinitelyTyped/blob/master/types/pg/index.d.ts
}
const poolInstance = new Pool(poolConfigOpts);

// 3rd: create a PostgreSQL Store that will handles all sessions logic from here (e.g set/create a session record in `session` table; prune/delete out-dated sessions; etc)
// To create a Store, you need a Connection Pool (recommended by author)
const postgreStore = new pgSession({
  // check interface PGStoreOptions for more info https://github.com/DefinitelyTyped/DefinitelyTyped/blob/master/types/connect-pg-simple/index.d.ts
  pool: poolInstance,
  createTableIfMissing: true, // this will create a `session` table if you do not have it yet
})

// 4th: finally use that Store inside `express-session`
app.use(session({
    store: postgreStore,
    cookie: { maxAge: 30 * 24 * 60 * 60 * 1000 },
    secret: 'secret',
    resave: true,
    saveUninitialized: true
}));