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

[chill117]

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);
});

[voxpelli]

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
}),

[voxpelli]

@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

[lf94]

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

[asbjornu]

@voxpelli Thanks, I'll try that tonight. But optimally, the conString property shouldn't be necessary, right? I already have Sequelize configured in the application, so shouldn't connect-pg-simple be able to use it somehow? How?

[voxpelli]

@asbjornu Well, this module talks directly to PostgreSQL through the pg module and isn't specifically tailored to any of the ORM:s out there – so it doesn't know what configuration an ORM like Sequelize has set up for it.

What it however does to is to default to any connection string found in the DATABASE_URL environment variable as that's how eg. Heroku by default specify their database connection strings, so as long as one follow that same pattern, one doesn't have to send in a connection string manually.

[asbjornu]

@voxpelli Ah, I haven't seen that before, no. Thanks for mentioning it, I'll definitely check it out! :)

[asbjornu]

@voxpelli connect-session-sequelize worked perfectly. Thanks for the awesome tip! 😄

[s2t2]

also running into the error relation "session" does not exist. intuitively, it would be helpful for the library to use the same pg connection as is defined elsewhere in the project. I'm using knex only, not sequelize. Any suggestions on how to fix the error?

EDIT: I will try using https://www.npmjs.com/package/connect-session-knex

[voxpelli]

@s2t2 Did you try specifying the connection details as noted in my first comment here?

This module tries to reuse the same connection details as elsewhere as much as it can, but apart from relying on standard environment variables it can't automatically detect such details really and then you have to manually send it in by doing what suggested in that comment.

I myself use this module with Knex, there's really no need to use something Knex-specific, one just need to provide it with the same connection info as the one that is given to Knex itself.

[voxpelli]

@s2t2 The reason one has to provide the connection details one way or the other, through a standard environment variable or manually, is because of how the connection pool works with Postgres in node. One provides the connection details and it ensures one gets a working connection from its pool of connections for those details. Here's the docs if you're curious to read more about it: https://github.com/brianc/node-postgres/wiki/pg#connectstring-connectionstring-function-callback

[s2t2]

Thanks for following up. I will note these instructions for future usage. The knex-specific library was what I was looking for, and is suiting my needs at this time.

[rjt123]

Hi, wanna understand that everything working fine in connect pg simple module ,connected to database and done with inserting in table but when i tried to run application end up with no session working up.

please make under stand what needs to be done..

[j4p3]

Anyone stumbling on this error in the future may want to check if Sequelize is still sneakiy pluralizing their table name from session to sessions (sequelize/sequelize#7307).

[cedrickvstheworld]

(connect-pg-simple)
i have run onto this problem and i just created the table manually in the heroku database

CREATE TABLE public.session (
sid character varying NOT NULL,
sess json NOT NULL,
expire timestamp(6) without time zone NOT NULL
);

and boom! it works.

[MykolaYeninDevPro]

(connect-pg-simple)
i have run onto this problem and i just created the table manually in the heroku database

CREATE TABLE public.session (
sid character varying NOT NULL,
sess json NOT NULL,
expire timestamp(6) without time zone NOT NULL
);

and boom! it works.

CREATE TABLE public.session (
sid character varying PRIMARY KEY NOT NULL,
sess json NOT NULL,
expire timestamp(6) without time zone NOT NULL
);

Would feet better! :)

[dan-ling]

Im having the same issue, i am using sails js as a frame work and Iv tried every way to connect, pool, conObject and conString but every time it says Failed to prune sessions: relation “session” does not exist.
I have tested the connection by connecting and selecting the table names, and session is there.

const pgPool = new Pool({
  connectionString: ‘<connectionString>’,
  ssl: {
    sslmode: ‘require’,
  }
});
...
...
  session: {
    adapter: ‘connect-pg-simple’,
    store:  new pgSession({
      pool: pgPool,
      tableName: ‘session’
    }),
    secret: ‘shhhhhhh’,
    cookie: {
      secure: true,
      maxAge: 24 * 60 * 60 * 1000,  // 24 hours
    },
    resave: true,
    saveUninitialized: true
  }

any help would be great.

[voxpelli]

@dan-ling Can you open a new issue + preferable provide a working reproduction in eg https://codesandbox.io/

[dan-ling]

Will make a new issue now