createTableIfMissing not working

[vogler]

With the following

import connectPgSimple from 'connect-pg-simple';
const store = new (connectPgSimple(session))({createTableIfMissing: true} as any);
app.use(session({secret: '...', saveUninitialized: true, resave: false, store})); // defaults: httpOnly

I just get Failed to prune sessions: relation "session" does not exist on start and error: relation "session" does not exist on the first request.

Also, without as any I get Argument of type '{ createTableIfMissing: boolean; }' is not assignable to parameter of type 'PGStoreOptions'., so @types/connect-pg-simple seems to be outdated.

    "@types/connect-pg-simple": "^4.2.2",
    "connect-pg-simple": "^6.2.1",
    "express": "^4.17.1",
    "express-session": "^1.17.1",
    "typescript": "^4.1.5",

[voxpelli]

The @types/connect-pg-simple should not be used, connect-pg-simple comes with its own types, I should look into getting that one deprecated.

Regarding main issue: I'll give it a look

[voxpelli]

Sorry, I misspoke earlier, this is the one of my modules that actually doesn't yet generate its own types, see #155

I unfortunately also got sidetracked with an issue in another project that affected this project, so never got around to look at this in the weekend, sorry :/

[vogler]

No problem, I'm using https://github.com/kleydon/prisma-session-store - since I'm using Prisma anyway this is a better fit.

[prionkor]

Hello, I am having the same issue. Table is not created if I set createTableIfMissing as true

Code sample:

const PgSession = pgSession(session);
const pool = new Pool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
});

const store = new PgSession({
  createTableIfMissing: true,
  tableName: 'UserSessions',
  pool,
});

This flag is very useful for containerised environments.

[prionkor]

This bug makes the module unusable for my use cases. So I have to move to a new module. https://www.npmjs.com/package/connect-session-sequelize

[voxpelli]

@prionkor You can always set up the tables yourself, as shown in: https://github.com/voxpelli/node-connect-pg-simple#installation

The createTableIfMissing is a helper for those who can't.

I haven't got that much to go on here and I'm quite short on time at the moment so I would have to debug it deeper, and can't promise when I get time to do that.

But for future reference, for those who might arrive here: If createTableIfMissing doesn't work, just go with the default behavior of seting up the table yourself.

[lf94]

I too am hitting this bug. It just won't create itself.

[lf94]

The @types/connect-pg-simple should not be used, connect-pg-simple comes with its own types, I should look into getting that one deprecated.

I don't see its types declared anywhere? How are you using its types? (Looking at it, tsconfig points to index.js, and there is lots of annotations)

[lf94]

I suggest anyone coming to the repo try out https://www.npmjs.com/package/connect-session-sequelize instead, which is much more maintained. Nothing against voxpelli, they have done excellent work with connect-pg-simple. connect-pg-simple works but does not fit my need of TypeScript + auto creating the tables.

[voxpelli]

I don't see its types declared anywhere? How are you using its types? (Looking at it, tsconfig points to index.js, and there is lots of annotations)

I corrected myself earlier:

Sorry, I misspoke earlier, this is the one of my modules that actually doesn't yet generate its own types, see #155

Part of the reason is that this has to interact with express-session and for a long time those types were a bit so-so, making it hard to make something useful of it.

Apart from that: The focus of this module is to be the simplest postgres session module for express, thus depending on nothing but the pg module itself, enabling it to be used for most scenarios.

If one needs a more complex solution, then it’s correct to look for that in another solution.

If one believes this module fails in delivering what it intends to deliver, then let’s fix it 🙂

[lf94]

Well said 🙂 I want to heavily emphasize I am not implying anything negative about the work - it is once again, excellent. I would try to fix the mentioned issues myself, I just don't have the time either right now. 😆

[rafgk]

I am facing the same problem, I think the issue is with the sql script for the table creation. When running the table.sql in my database I get SQL Error [42P01]: ERROR: relation "session" does not exist.

I found that removing WITH (OIDS=FALSE); from the script fixes the issue and creates the table. I made the change in the project files but for some reason the error doesn't go away when launching the app without an existing table. I just figured it would be good to share this finding.

[vladtabachuk]

Hello Mr. voxpelli as i understand for now we better use this?
`CREATE TABLE "session" (
"sid" varchar NOT NULL COLLATE "default",
"sess" json NOT NULL,
"expire" timestamp(6) NOT NULL
)
WITH (OIDS=FALSE);

ALTER TABLE "session" ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;

CREATE INDEX "IDX_session_expire" ON "session" ("expire");
`

[aboqasem]

Make sure you install connect-pg-simple@next everyone!

The @types/connect-pg-simple has the createTableIfMissing option, but latest connect-pg-simple (6.2.1) does not. Thus, you have to install the next version (^7.0.0-0).

[voxpelli]

Thanks @aboqasem, I had forgotten that 7.0.0 was still in a prerelease state, I have now released a stable 7.0.0.

Sorry everyone for not thinking of this myself earlier. I'll close this issue as solved for now, if an update to 7.0.0 does not solve it, then comment and I can reopen.

[JadedChara]

I've come across the same error as specified prior:

Failed to prune sessions: relation "sessions" does not exist
node:events:368
      throw er; // Unhandled 'error' event
      ^

I currently remain unable to implement database storage because of this.

[isaacgr]

Thanks @aboqasem, I had forgotten that 7.0.0 was still in a prerelease state, I have now released a stable 7.0.0.

Sorry everyone for not thinking of this myself earlier. I'll close this issue as solved for now, if an update to 7.0.0 does not solve it, then comment and I can reopen.

I'm seeing this with 7.0.0. Once created manually theres no issue.