node-connect-pg-simple: error: relation "session" does not exist

A note is included in the documentation that says it is necessary to create the session table yourself:

Once npm installed the module, you need to create the session table in your database. For that you can use the table.sql file provided with the module:

Though it is easy to overlook. I did at first 😃

You can either manually generate the table by executing it via psql:

psql mydatabase < node_modules/connect-pg-simple/table.sql

Or you can create the table before creating your instance of pgSession. Here’s an example using knex:

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

const options = {
	host: 'localhost',
	port: 5432,
	user: 'postgres',
	password: '',
	database: 'postgres',
};

const knex = require('knex')({
	client: 'pg',
	connection: options,
});

knex.schema.hasTable('session').then(exists => {
	if (exists) return;
	return new Promise((resolve, reject) => {
		const schemaFilePath = path.join(__dirname, 'node_modules', 'connect-pg-simple', 'table.sql');
		fs.readFile(schemaFilePath, (error, contents) => {
			if (error) return reject(error);
			const sql = contents.toString();
			knex.raw(sql).then(() => {
				resolve();
			}).catch(reject);
		});
	});
}).then(() => {
	// Session table ready.
	const pgSession = require('connect-pg-simple')(session);
	const sessionStore = new pgSession({
		conObject: options,
	});
	console.log(sessionStore);
});

The conString should be sent into new (require('connect-pg-simple')(session))() like:

store: new pgSession({
  conString : 'pg://' + config.username + ':' + config.password + '@' + config.host + '/' + config.database
}),

@asbjornu Just in case you haven’t seen it, there’s another project that is built specifically for Sequelize: https://www.npmjs.com/package/connect-session-sequelize

As of March 23 2021, this again has struck me!