gx0r/connect-session-knex

A knex.js session store for express-session, supporting PostgreSQL, MySQL, MariaDB, Oracle and SQLite.

connect-session-knex's Issues

Use text instead of JSON with older MySQL versions

I'm working with an older database (MySQL 5.5) that doesn't support the JSON data type.
I decided to make my own table and use text instead of json while setting createTable to false which seems to work. Are there any repercussions of using this approach?

MySQL multiple ER_PARSE_ERROR

I just decided for the first time to use a way to store express sessions with my already-existing database, which uses MariaDB version 10.1.44.

First of all, I experienced the #50 issue despite having the correct package version (1.7.3). I created the table myself, to avoid this error, following the instructions in #38.

And now, when I edit a session in my code (a basic thing like req.session.test = "123"), I face the nice error saying (node:25462) Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-translator (sid, expired, sess) values ('h4aCPnBIR_GWjqMSAvUj7gmt5u2zJZ0F', '20' at line 1

Full trace here:

[ERROR]  (node:25462) UnhandledPromiseRejectionWarning: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-translator (sid, expired, sess) values ('h4aCPnBIR_GWjqMSAvUj7gmt5u2zJZ0F', '20' at line 1
    at Query.Sequence._packetToError (myfolder/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (myfolder/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (myfolder/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (myfolder/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (myfolder/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (myfolder/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (myfolder/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (myfolder/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:314:20)
    at addChunk (_stream_readable.js:304:12)
    at readableAddChunk (_stream_readable.js:280:9)
    at Socket.Readable.push (_stream_readable.js:219:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    --------------------
    at Protocol._enqueue (myfolder/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (myfolder/node_modules/mysql/lib/Connection.js:198:25)
    at myfolder/node_modules/knex/lib/dialects/mysql/index.js:135:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (myfolder/node_modules/knex/lib/dialects/mysql/index.js:129:12)
    at Client_MySQL.query (myfolder/node_modules/knex/lib/client.js:169:17)
    at Runner.query (myfolder/node_modules/knex/lib/runner.js:134:36)
    at myfolder/node_modules/knex/lib/runner.js:40:23
    at myfolder/node_modules/knex/lib/runner.js:260:24
    at processTicksAndRejections (internal/process/task_queues.js:93:5)

I need to quickly start working on sessions, so if you have any idea about how to resolve that... thanks!

Compatibility with old node.js versions

In package.json your declare that connect-session-knex support node >= 0.10 but in https://github.com/llambda/connect-session-knex/blob/master/index.js#L258 you used a ES6 syntax in 258 line

Update package at NPM

The last version at http://npmjs.com/package/connect-session-knex is 2 years old and has no stopDbCleanup() feature.

Sessions table creation error on MariaDB

Hi, i tried to use this module to store my sessions, but I'm encountering an error when it tries to create a dedicated table to store the sessions:

Unhandled rejection Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json not null, 'expired' datetime not null)' at line 1

It is clearly due to the fact that the json format is only supported by MariaDB 10.2+.

Are session ids encrypted before saving?

They should be encrypted before saving right? so in the case of a database leak session ids cannot be used.

Bluebird warning caused by touch()

I'm trying to upgrade an application from bluebird v2 to bluebird v3 but started seeing some bluebird warnings triggered by express routes. Here is a sample application that causes the warning:

'use strict';
var express = require('express');
var session = require('express-session');
var cookieParser = require('cookie-parser');
var KnexSessionStore = require('connect-session-knex')(session);
var knex = require('./DB').knex; //knex object is initialized in DB.js

var app = express();
app.set('port', process.env.PORT || 3000);

var server = require('http').createServer(app);
app.use(cookieParser());

var sessionStore = new KnexSessionStore({
    knex: knex,
    tablename: 'sessions', // optional. Defaults to 'sessions'
    createtable : false
});

var sessionConfig = {
    name : 'sessionid',
    resave : false,
    rolling : true,
    saveUninitialized : true,
    secret : 'some secret',
    store : sessionStore,
    cookie : {
        secure : 'auto',
        maxAge : 7200000
    }
};

app.use(session(sessionConfig));
app.get('/', function(req, res, next) {
    knex('users')
        .then(users => {
            // Deep inside res.json() a call to KnexSessionStore.touch() is made. Bluebird thinks we forgot to return
            // this Promise from this handler.
            res.json(users);
        })
        .catch(next);
});

// Start Express server.
server.listen(app.get('port'), function() {
    console.log('Express web server listening on port %d over %s in %s mode', app.get('port'), 'HTTP', app.get('env'));
});

The warning is:

(node:5088) Warning: a promise was created in a handler at /opt/apps/web/app.js:39:17 but was not returned from it, see http://goo.gl/rRqMUw
    at Promise.then (/opt/apps/node_modules/bluebird/js/release/promise.js:125:17)

The reason the warning appears is that when I call res.json(), deep down in the stack, a call to the session store's touch() method is made and the method creates a new Promise. Bluebird thinks that when a Promise is instantiated from inside a handler, it is likely that the developer forgot to return the Promise (to wait for its completion). In the present case, I cannot and do not wish to wait for .touch()'s completion.

Reading the Bluebird documentation about the warning, it seems that to prevent the warning from occuring, I should return null from the handler like this:

app.get('/', function(req, res, next) {
    knex('users')
        .then(users => {
            res.json(users);
            return null;
        })
        .catch(next);
});

Before I go over the many routes of my application to add return null statements, I was wondering if there is another way to solve this problem, if anybody has ever worked around this problem. Many thanks!

Document required schema

Pretty minor, as one can just look at the code, but I think it'd be handy to document the schema that connect-session-knex is expecting on the readme page.

For an example use case, I tried using it for the first time (without setting up any schema, just seeing what happened) and got a "permission denied" message I wasn't expecting. In my case, it was because the database user I have set up for running in the application has no permission to modify the schema and the tool was trying to create the table. The easiest way forward for me is to create the table connect-session-knex is looking to use with a more privileged user, but to do so I need to know the schema.

It jumps out of the code pretty quickly, but I think it's worth pulling out into the readme.

return self.knex.schema.createTable(self.tablename, function (table) {
    table.string(self.sidfieldname).primary();
    table.json('sess').notNullable();
    if (['mysql', 'mariasql'].indexOf(self.knex.client.dialect) > -1) {
      table.dateTime('expired').notNullable();
    } else {
      table.timestamp('expired').notNullable();
    }
});

Add cockroach support

right now the getPostgresFastQuery is broken for cockroach

https://github.com/gx0r/connect-session-knex/blob/master/lib/utils.js#L99

changing the line from
+ ' values (?, ?::timestamp with time zone, ?::json)'
to
+ ' values (?::string, ?::timestamp with time zone, ?::json)'

fixes this

MSSQL Schema Equivalents - Clustered/Non-Clustered?

Hello, I'm not completely familiar with PostgreSQL/SQLite nomenclature (i.e.: btree). I don't have an option to create a Clustered index for the Expired column.... can it be Non-Clustered?

Best I can glean from Microsoft's documentation is that both Clustered and Non-Clustered are considered "b-tree structures".

I've got the columns setup. I used datetimeoffset(7) for Expired, if you think that's cool:

	[sid] [varchar](255) NOT NULL,
	[sess] [nvarchar](max) NOT NULL,
	[expired] [datetimeoffset](7) NOT NULL,

Feature Request: Add options to specify column names of sessions table

Hi, I'd like to be able to specify the column names of the sessions table.

In my case, I'd like the expired column to be named expired and the sess column to be named data.

Also, I'd like to have the option to store the expired column as a unix timestamp

For now, it's like this:

 type ConfigType = {
      tablename?: string;
      sidfieldname?: string;
      knex?: Knex;
      createtable?: boolean;
      clearInterval?: number;
      disableDbCleanup?: boolean;
      onDbCleanupError?: (error: Error) => void
    };

I'd like to have:

 type ConfigType = {
      tablename?: string;
      sidfieldname?: string;
      // ADDED
      sessdatafieldname?: string;
      expiredfieldname?: string;
      storeExpiredColumnAsUnix?: boolean;
      // ADDED
      knex?: Knex;
      createtable?: boolean;
      clearInterval?: number;
      disableDbCleanup?: boolean;
      onDbCleanupError?: (error: Error) => void
    };

Knex v2.0.0?

Hi there!

The new Knex 2.0.0 version was released recently. Would be nice to have connect-session-knex updated to the new Knex version. It looks like it is mostly backward compatible so maybe there's no changes required at all.

Thank you for the great library and good luck!

Does not support deleting all sessions belonging to the same user (from different clients)

I am using MySQL, but not able to delete all sessions belonging to the same user when the user changes his/her password.

Wonder if I missed anything or this functionality is not supported...

default sqlite3 being used instead of mysql table sessions, option issue?

const KnexSessionStore = require("connect-session-knex")(session);

app.use(bodyParser.urlencoded({ extended: true }));

app.use(bodyParser.json());

app.use(express.static("public"));
app.use(cors());

let config = {
    timezone: "gmt",
    host: "host",
    user: "user",
    database: "database",
    password: "password"
};

const store = new KnexSessionStore({
     tablename: "sessions",
     sidfieldname: "sid"
});

app.use(
session({
    secret: "secret",
    store: store,
    resave: false,
    saveUninitialized: false,
    cookie: { sameSite: "strict" },
  }),
);

I didn't see an example with the options written in the creation of the KnexSessionStore object so I'm not sure that's right.

I've tried store as:

const store = new KnexSessionStore(
  tablename='sessions',
  sidfieldname='sid'
);

const store = new KnexSessionStore({
  tablename='sessions',
  sidfieldname='sid'
});

const store = new KnexSessionStore({
  tablename: 'sessions',
  sidfieldname: 'sid'
});

Error message:

D:\appTestLab\node_modules\knex\lib\client.js:235
throw new Error(${message}\n${e.message});

Error: Knex: run
$ npm install sqlite3 --save
Cannot find module 'sqlite3'

If anyone could help guide me that'd be great!

Library Creates Tables but Provides no (Documented) Way to Remove Them

I saw a reference to a dbCleanup function in one of the issues (I guess it wasn't working in SQLite?), but no other mention of what it is or how it works anywhere.

Beyond that, I also can't find any reference anywhere to a command to clear or remove the tables this library creates.

It's pretty standard practice in Knex to use Knex migrations to change the database, so it seems like this library really should have a way to generate a migration to add the table, rather than just adding them automatically within the app itself.

But separate from that, there absolutely should be some way to "tear down" the DB work done by the library (and that way should be documented).

Couldn't get it to work using MySQL

I keep getting an error ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes when trying to create the required sessions table, any ideas?

[docs] Create a Changelog.md File

Dear Contributor / Maintainer,
Since the release page does not contain information on what changes were made in the release, it is essential to have a changelog.md file which can be referred for all the changes which are released in a version.

Tests not exiting because of uncanceled timeout

I am trying to write unit tests for my rest api and after they are run the proccess doesn't exit.
After some poking around i'm confident that this is the case because this library uses a timeout that doesn't get removed.

I found the function stopDbCleanup in the source code and but it seems like it doesn't exist in the version released to npm.

I'm trying to use it like this:

...
  const store = new KnexSessionStore({
    knex,
    tablename: 'session',
    createtable: true,
    clearInterval: 1000 * 60 * 30, //30 Min
  });

  fastify.addHook('onClose', (instance, done) => {
    store.stopDbCleanup(); // TypeError: store.stopDbCleanup is not a function
    done();
  });

  fastify.register(session, {
    cookie: {
      httpOnly: true,
      secure: config.isProduction,
      maxAge: 1000 * 60 * 60 * 24 * 30, //30d
    },
    saveUninitialized: false,
    secret: config.sessionSecret,
    store,
  });
...

Is there a workaround I could put in the onClose hook to stop the timeout?

Thanks

Conflict between autocommit and connection pooling in mysql

When autocommit is turned off (i.e., in transaction mode), connect-session-knex no longer works in MySQL. This is because session update statements might be executed on different connections, and a table lock is acquired for each as soon as the insert statement is executed. If a second connection attempts to do the same, it hangs waiting for the lock. There are no commit statements so the lock isn't released unless/until knex times out the connection.

This could be supported by wrapping the update command, even the MySQL optimised one, in a transaction statement -- or at least allowing that wrapping as an option.

MySQL: Error on touch with Datetime ISO

Hello.

First, thank you for the lib.

I'm using it, but I've found an issue: using MYSQL, touch method isn't working. The broken code is on line 250:
expired: new Date(sess.cookie.expires).toISOString()

When touching, update expired column, but differently of set method. On set method, using mysql, the value to insert is this (line 200):
new Date(expired).toISOString().slice(0, 19).replace('T', ' ')

which become this date: '2016-02-12 02:04:13'.

On touch method, the value just turn into ISOString like '2016-02-12T01:07:22.999Z'.

Doing that, throws that error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect datetime value: '2016-02-12T01:07:22.999Z',

This can be fixed just changing expire value, creating an unique method that returns the correct value and using it in touch AND get methods.

nowAsISO

Other point to review is the function nowAsISO, that return the date as the same format that touch ethod uses to update. This method must return the correct value too, because is used on touch and methods.

Should i make a pull request with the fixed code?

Ps.: Sorry for my english

incompitable types: 'generateDdlCommands' is missing in type Raw<any> but required in Knex.Raw<any>

"knex": "^0.95.11"

Add custom fields as a column

Hi

I was wondering if it's possible to add custom fields as a column to the sessions table? Is there any specific way of doing that?

For example by default there are 3 columns: sid, sess and expired. Is there any way to add new columns like userid etc?

Column	Type
userid	int
sid	character varying(255)
sess	json
expired	timestamp with time zone

setTimeout(...).unref is not a function

console.warn node_modules/bluebird/js/release/debuggability.js:873
Unhandled rejection TypeError: setTimeout(...).unref is not a function

I'm seeing this error when running jest tests for my node api. The error appears to come from line node_modules/connect-session-knex/index.js:176:53.

https://github.com/llambda/connect-session-knex/blob/master/index.js#L176

UnhandledPromiseRejectionWarning when used with knex with mysql2 driver and MySQL 5.7

Sorry if this has been posted before, but Node.js gives out a warning like below every time it tries to clean up the sessions although it does successfully do so.

UnhandledPromiseRejectionWarning: TypeError: Cannot set property 'nextDbCleanup' of undefined
    at D:\Projects\test\node_modules\connect-session-knex\index.js:242:31
    at <anonymous>
    at processTicksAndRejections (internal/process/task_queues.js:97:5)

Here is my setup

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

const KnexSessionStore = require('connect-session-knex')(session);

const app = express();

const knex = require('knex')({
    client: 'mysql2',
    connection: {
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'test'
    }
});

app.use(
    session({
        secret: 'secret',
        resave: false,
        saveUninitialized: true,
        cookie: { maxAge: 36 },
        store: new KnexSessionStore({
            clearInterval: 8,
            createtable: true,
            knex: knex
        })
    })
);

Touch is always called?

I may not understand how the touch() functionality is supposed to work, so correct me if I'm wrong - but as it stands, this package's touch() method is always called - is that right? In other words, when using this package, there is no current way to say, "Don't touch these sessions automatically"?

spammed queries to clear cookies

I have automated tests every time I save any file (with mocha --watch), and this means knex store is initialized A LOT throughout my day.

I've noticed that after a few writes, any attempted knex connection to postgres results in this:

Unhandled rejection error: sorry, too many clients already
    at Connection.parseE (/app/node_modules/pg/lib/connection.js:606:11)
    at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:403:19)
    at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:123:22)
    at Socket.emit (events.js:210:5)
    at addChunk (_stream_readable.js:308:12)
    at readableAddChunk (_stream_readable.js:289:11)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:182:23)

The cause seems to be, at least partially, that all the session expiry queries persist after the application is shut down. From psql:

somedbname=# select pid, query from pg_stat_activity;
 pid  |                                    query
------+----------------------------------------------------------------------------
   29 |
 1389 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1410 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1388 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
  624 | select pid, query from pg_stat_activity;
 1337 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1412 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1419 |
 1270 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1338 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1404 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1405 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1390 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1429 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1367 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1408 |
 1430 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1234 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1368 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1413 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1241 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1414 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1415 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1416 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1335 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1417 |
 1418 |
 1421 |
 1264 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1277 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
  647 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1420 |
  651 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1248 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1422 |
 1393 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1301 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1423 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1395 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1396 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1397 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1321 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1424 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1425 |
 1398 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1426 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1351 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1382 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1399 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1427 |
 1327 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1401 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1403 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1407 |
 1354 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
 1431 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)

Is there a workaround for this?

I don't know if this should be fixed on connect-session-knex side, or I should be doing something differently from my code.

While this gets sorted, does anyone know how to remove those queries programmatically, ideally efficiently? My best guess was select pid, query from pg_stat_activity where query like '%sessions%expired%' and pid != pg_backend_pid() and pg_cancel_backend(pid), but it seems pg_cancel_backend is ran before the pid != pg_backend_pid check, so the command never finishes and a lot of those cookie clearing queries persist.

My understanding of postgres is quite limited.

Timezone Support and PostgreSQL

I ran into issues when trying to find expired sessions.
As ISO Dates contain a time zone modifier but the default for a SQL timestamp is without time zone, the expires column may contain values that do not respect the timezone and are saved as UTC instead.

This makes comparing with des PostgreSQL current_timestamp difficult.

You can try it yourself with the following two queries:

The time value is provided by Javascript's:

new Date().toISOString() // 2015-09-08T08:28:29.910Z

This will then be inserted into the database:

-- To PostgresSQL this is 10:28 (correct local time)
INSERT INTO sessions (sid, sess,expired)
 VALUES ('Good','{}', '2015-09-08T08:28:29.910Z'::timestamp with time zone);

-- To PostgresSQL this is 08:28 (incorrect local time)
INSERT INTO sessions (sid, sess,expired)
 VALUES ('Bad','{}', '2015-09-08T08:28:29.910Z'::timestamp);

-- Comparing the dates now yields the following results:
SELECT * from sessions;
-- Good {}  2015-09-08 10:28:29.91
-- Bad  {}  2015-09-08 08:28:29.91

This is no problem unless you try to compare these dates using built-in functions, because they respect time zones and treat 'timestamps without time zone' as local times, in my case UTC+2 😕

I can try to send a pull request.

TypeError: Cannot read property 'hasTable' of undefined

Hi Anyone please help on the below error
\node_modules\connect-session-knex\index.js:208
self.ready = self.knex.schema.hasTable(self.tablename)
^

TypeError: Cannot read property 'hasTable' of undefined
at new KnexStore (D:\WebPro\Dev\stormy-sea-60405\node_modules\connect-session-knex\index.js:208:33)
at Object. (D:\WebPro\Dev\stormy-sea-60405\script.js:20:10)
at Module._compile (module.js:635:30)
at Object.Module._extensions..js (module.js:646:10)
at Module.load (module.js:554:32)
at tryModuleLoad (module.js:497:12)
at Function.Module._load (module.js:489:3)
at Function.Module.runMain (module.js:676:10)
at startup (bootstrap_node.js:187:16)
at bootstrap_node.js:608:3

when i am trying to use connect-session-knex to added sessions to my app, i am getting the above error actually it is throwing error from connect-session-knex module

allow not using `new`

I was trying to figure out why I had an obscure EventEmitter error, and it took me very long to figure out that I missed out new.

Most of the express ecosystem constructs instances without using new, which is probably why I was confused. I'm not suggesting new is completely dropped as that would break everyone else's code, but that not using new should work as well.

It should be quite straight forward to implement. For plain node, this works:

> function X() { if (this === global) return new X(); this.y = 2 }; [X(), new X()]
[ X { y: 2 }, X { y: 2 } ]

I'm not sure if this would work with bundlers.

If you don't want to allow that behaviour, it would be nice to at least throw an error saying that you didn't use new, instead of resulting in this getting thrown:

events.js:85
  if (this._events === undefined ||
           ^

TypeError: Cannot read property '_events' of undefined
    at EventEmitter.init (events.js:85:12)
    at EventEmitter (events.js:44:21)
    at Store (/app/node_modules/express-session/session/store.js:33:16)
    at KnexStore (/app/node_modules/connect-session-knex/index.js:190:9)
    at Object.<anonymous> (/app/src/app.js:20:24)
    ...

Timestamps are not stored correctly in Postgres

If you set maxAge to 30minutes and your timezone is +1 (e.g. London), this library creates already expired sessions. Creating a session and trying to read it back queries back nothing since the timestamp in get or set query is in the past.

Support knex v3

knex recently released major version 3, due to the end-of-life of node 16. It clashes with the 2.5.1 version depended on by connect-session-knex.

plugins/auth-backend/src/service/router.ts:107:11 - error TS2322: Type 'import("/home/runner/work/backstage/backstage/node_modules/knex/types/index").Knex<any, any[]>' is not assignable to type 'import("/home/runner/work/backstage/backstage/node_modules/connect-session-knex/node_modules/knex/types/index").Knex<any, any[]>'.
  The types of 'raw(...).transacting' are incompatible between these types.
    Type '(trx: import("/home/runner/work/backstage/backstage/node_modules/knex/types/index").Knex.Transaction<any, any[]>) => import("/home/runner/work/backstage/backstage/node_modules/knex/types/index").Knex.Raw<any>' is not assignable to type '(trx: import("/home/runner/work/backstage/backstage/node_modules/connect-session-knex/node_modules/knex/types/index").Knex.Transaction<any, any[]>) => import("/home/runner/work/backstage/backstage/node_modules/connect-session-knex/node_modules/knex/types/index").Knex.Raw<any>'.
      Types of parameters 'trx' and 'trx' are incompatible.
        Type 'import("/home/runner/work/backstage/backstage/node_modules/connect-session-knex/node_modules/knex/types/index").Knex.Transaction<any, any[]>' is not assignable to type 'import("/home/runner/work/backstage/backstage/node_modules/knex/types/index").Knex.Transaction<any, any[]>'.
          The types of 'query(...).client.config.client' are incompatible between these types.
            Type 'string | typeof import("/home/runner/work/backstage/backstage/node_modules/connect-session-knex/node_modules/knex/types/index").Knex.Client | undefined' is not assignable to type 'string | typeof import("/home/runner/work/backstage/backstage/node_modules/knex/types/index").Knex.Client | undefined'.
              Type 'typeof Client' is not assignable to type 'string | typeof Client | undefined'.

              knex: await authDb.get(),
              ~~~~

Note that these are two different Client - the one in the repo root, and the one hoisted into connect-session-knex.

dbCleanup broken with sqlite3

The SQL generated by the dbCleanup function doesn't work with sqlite3. The where clause generated is:

expired < CAST('2015-11-06T02:52:00.375Z' as timestamp)

The cast returns the number 2015, because sqlite3 doesn't have a timestamp type, and when it applies its column affinity logic to that type name, it results in a numeric type (see https://www.sqlite.org/datatype3.html#affname).

A correct where clause should probably use the datetime function like this:

datetime(expired) < datetime('2015-11-06T02:52:00.375Z')

sqlite3 doesn't actually have any datetime data type unfortunately. I think the get function would be similarly broken, but by chance it works because the comparison is:

CAST('2015-11-06T03:31:25.059Z' as timestamp) <= expired

which is pretty much always true unless the year changes.

I'm testing a local fix so I'll issue a pull request soon.

Connect-session-knex does not work with older versions of mysql

I'm trying to perform a deployment of an app on Heroku that uses clearDB in version 5.5.62 that doesn't support json type fields.
I saw that recently there was a merge regarding issue #50 but I still have the error. I am using version 1.7.2.
Alternatively, how can I implement what was described in #50?

#UnhandledPromiseRejectionWarning: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'json not null, expired datetime not null)' at line 1

Tks

Doc and example out of sync

Both in readme and https://github.com/llambda/connect-session-knex/blob/master/example-postgres.js#L19 you ask to use field called knex when actually you expect Store

Crash when using existing knex instance

I'm trying to reuse my existing knex instance like so:

var session = require('express-session')
var SessionStore = require('connect-session-knex')
var db = require('./lib/db') // actually a Bookshelf instance already initialized
var sessionOptions = {
  /* ... */
  store: new SessionStore({knex: db.knex})
}

app.use(session(sessionOptions))

But when I start the app it crashes with the following error:

util.js:555
  ctor.prototype = Object.create(superCtor.prototype, {
                                          ^
TypeError: Cannot read property 'prototype' of undefined
    at Object.exports.inherits (util.js:555:43)
    at new module.exports (/home/ricardo/src/w20-balletrosa.com/node_modules/connect-session-knex/lib/connect-session-knex.js:84:6)
...

[email protected]
[email protected]
[email protected]
[email protected]

TypeError: self.knex.schema.hasTable is not function

✖ Initialising Keystone instance
TypeError: self.knex.schema.hasTable is not a function
at new KnexStore (E:\projects\blogging\node_modules\connect-session-knex\lib\index.js:106:8)

```

const { KnexAdapter: Adapter } = require('@keystonejs/adapter-knex');
const adapterConfig = { knexOptions: { connection: process.env.DATABASE_URL }, /*dropDatabase: true/ };
const session = require('express-session');
const KnexSessionStore = require('connect-session-knex')(session);

const knex = new Adapter(adapterConfig);

const sessionsStore = new KnexSessionStore({
knex,
tablename: "sessions"
});

const keystone = new Keystone({
adapter: knex,
sessionStore: sessionsStore
});

Knex Connection clears session table with server restart.

code

const session = require('express-session');
const KnexSessionStore = require('connect-session-knex')(session);
const store = new KnexSessionStore({
    knex: Model.knex,
    tablename: 'sessions',
    clearInterval: 3600000
    
})

app.use(session({
  secret: 'keyboard cat',
  resave: false,
  saveUninitialized: false,
  cookie: {
      maxAge: 30000 // 2 seconds for testing
  },
  store: store}));
app.use(passport.initialize());
app.use(passport.session());


function authenticationMiddleware(){
  return (req, res, next) => {
    console.log(req.session);
    console.log(`req.session.passport.user:`, req.session.passport);
    if(req.isAuthenticated()) return next();
    return res.status(403).json({status:"failed", message: "Could not authenticate user."});
    console.log("failed sucker");
  }
}

For some reason the database info will not persist after the server is being restarted. I am not sure if this is a configuration error on my part of it is a bug in the code.

Cannot set property 'domain' of undefined

Using the example, I have the following:

import express from 'express';
import session from 'express-session';
import knexSessionStore from 'connect-session-knex';
import {knex} from './db';

const app = express();

app.use(session({
  secret: 'notsosecret',
  resave: false,
  saveUninitialized: false,
  cookie: { maxAge: 60000 },
  store: knexSessionStore(session)({knex})
}));

The error has the following stack trace:

[2] [piping] error given was: TypeError: Cannot set property 'domain' of undefined
[2]     at EventEmitter.init (events.js:43:15)
[2]     at EventEmitter (events.js:12:21)
[2]     at Store (/Users/rb/dev/mydrip/node_modules/express-session/session/store.js:33:16)
[2]     at KnexStore (/Users/rb/dev/mydrip/node_modules/connect-session-knex/index.js:91:9)
[2]     at Object.<anonymous> (api.js:27:10)
[2]     at Module._compile (module.js:541:32)

express: 4.13.3
express-session: 1.14.0
node: 6.3.1

I've been unable to pin down what the problem is. Any idea?

Publish update to npm

The last update to the package on NPM was 6 months ago. Currently there are some incompatibility issues with the latest Knex version (0.95.11) with the NPM version (2.1.0) of this repo, which have been solved in the master branch. A work around is setting the dependency version to this repo.

Temporary solution:

"dependencies": {
    "connect-session-knex": "https://github.com/gx0r/connect-session-knex.git",
    "knex": "^0.95.11",
}

To make this issue searchable I'll include the error here:

Type 'import("./node_modules/knex/types/index").Knex<any, unknown[]>' is not assignable to type 'Knex<any, unknown[]>'.
 Types of property 'raw' are incompatible.
   Type 'import("./node_modules/knex/types/index").Knex.RawBuilder<any, any>' is not assignable to type 'import("./node_modules/connect-session-knex/node_modules/knex/types/index").RawBuilder<any, any>'.
     Types of parameters 'value' and 'value' are incompatible.
       Type 'import("./node_modules/connect-session-knex/node_modules/knex/types/index").Value' is not assignable to type 'import("./node_modules/knex/types/index").Knex.Value'.
         Type 'Raw<any>' is not assignable to type 'Value'.
           Property 'generateDdlCommands' is missing in type 'import("./node_modules/connect-session-knex/node_modules/knex/types/index").Raw<any>' but required in type 'import("./node_modules/knex/types/index").Knex.Raw<any>'.

Configurable Callback for Destroy function

Express session says that a session store implementation must implement destroy. I'm hoping the library can be updated to allow for a way to configure the callback when a session is destroyed.

Should use setTimeout instead of setInterval

Currently, this library is using setInterval for cleanup scheduling. However, this will not deal well with slow cleanup queries - it should be using a recursive setTimeout instead, as described here.

Add example Knex Migration to docs

It would be very useful to provide an example knex migration creating a sessions table in the documentation. I am not sure if I have this right, but this is what I have so far:

exports.up = (knex) => {
  return knex.schema
    .createTable('sessions', (table) => {
      table.string('sid').notNullable();
      table.json('sess').notNullable();
      table.timestamp('expired').notNullable();
    });
};

exports.down = (knex) => {
  return knex.schema.dropTableIfExists('sessions');
};

Would there be any issue with changing `sess` column to `jsonb` in postgres?

I have tested locally, seems to work OK - is there any reason why I shouldn't do this?

Session store breaks when knex fails during its initialization

When creating new knex session store it performs some database queries (i.e. checks whether session table exists). If some of these queries fail, session store is unusable, because sessionStore.ready promise is rejected and therefore store is not able to perform any more queries.

IMHO there isn't much to do when this happens, but it would be nice if this behaviour is mentioned in README. My proposed fix is to add information about sessionStore.ready promise, which can be checked for rejected status if initialization failed.

const session = require('express-session');
const KnexSessionStore = require('connect-session-knex')(session);
const sessionStore = new KnexSessionStore({ knex });
sessionStore.ready.catch((err) => {
  handleStoreError(err);
});

Cannot use v2.1.1 in TypeScript

It seems #89 actually breaks usage in TypeScript projects:

$ tsc && DEBUG=true node ./dist/server.js
node_modules/connect-session-knex/typings/index.d.ts:19:5 - error TS2666: Exports and export assignments are not permitted in module augmentations.

19     export = initFunction;
       ~~~~~~


Found 1 error.

To be honest, I don't think the change was correct; it appears TypeScript only allows esm module syntax inside of module blocks.

Edit: It appears, if you use require, it works, but now all TypeScript projects are forced to use require syntax. The original author could have solved their problem with:

import sessionModule from 'connect-session-knex';
const session = sessionModule.default;

IMHO, that's preferable to disallowing import session from 'connect-session-knex';.

Unhandled rejection in dbCleanup

It appears that the periodic session clean-up function, dbCleanup, misses error handling. Also, there is no point in the API that would allow the user of the library to add such handling. This causes an unhandled promise rejection in case the clean-up fails for any reason (connection error, statement failure, etc.):

Unhandled rejection Error: connect ECONNREFUSED 127.0.0.1:3306
    at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1141:16)
    --------------------
    at Protocol._enqueue (/home/dev/mypackage/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/home/dev/mypackage/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/home/dev/mypackage/node_modules/mysql/lib/Connection.js:116:18)
    at /home/dev/mypackage/node_modules/knex/lib/dialects/mysql/index.js:69:18
    at Promise._execute (/home/dev/mypackage/node_modules/bluebird/js/release/debuggability.js:384:9)
    at Promise._resolveFromExecutor (/home/dev/mypackage/node_modules/bluebird/js/release/promise.js:518:18)
    at new Promise (/home/dev/mypackage/node_modules/bluebird/js/release/promise.js:103:10)
    at Client_MySQL.acquireRawConnection (/home/dev/mypackage/node_modules/knex/lib/dialects/mysql/index.js:64:12)
    at create (/home/dev/mypackage/node_modules/knex/lib/client.js:290:39)

Sure, the timeout does get re-registered via .finally(). However, it seems like having unhandled promise rejections is a bad idea in general, and Node.js always complains that it's going to cause process crashes in the future.

Errors need to be caught, and preferably handled via a user-defined function; that would allow one to inject a logger, for example.

broken with [email protected] since it no longer includes Bluebird

Error message is:

UnhandledPromiseRejectionWarning: TypeError: self.knex.select(...).from(...).where(...).andWhereRaw(...).then(...).asCallback is not a function
    at /node_modules/connect-session-knex/index.js:264:5

See knex/knex#3704

Does not support MSSQL

Still one of the most popular DB choices, unfortunately.

Requests generate the following:

Unhandled rejection RequestError: Arithmetic overflow error converting expression to data type datetime.
    at /mnt/c/Users/Terry/Projects/gohealth/node_modules/mssql/lib/tedious.js:739:17
    at emitOne (events.js:96:13)
    at Connection.emit (events.js:191:7)
    at Parser.<anonymous> (/mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/lib/connection.js:204:16)
    at emitOne (events.js:96:13)
    at Parser.emit (events.js:191:7)
    at Parser.<anonymous> (/mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/lib/token/token-stream-parser.js:42:15)
    at emitOne (events.js:96:13)
    at Parser.emit (events.js:191:7)
    at readableAddChunk (/mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:212:18)
    at Parser.Readable.push (/mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:171:10)
    at Parser.Transform.push (/mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:123:32)
    at doneParsing (/mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/lib/token/stream-parser.js:87:17)
    at /mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/lib/token/infoerror-token-parser.js:46:5
    at /mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/lib/token/infoerror-token-parser.js:13:19
    at /mnt/c/Users/Terry/Projects/gohealth/node_modules/tedious/lib/token/stream-parser.js:224:9

Documentation?

RIght now, the module README doesn't mention that the session table is created on runtime. Perhaps it'd be a good idea to add this? It took me a bit of time to figure this out myself, and when I recommended the module to somebody else just now, they asked me the exact same question, so there's clearly demand for this kind of information.

Additionally, the documentation is a bit sparse in general - possibly there are more things missing from it?

Use given schema

Apparently, this module only allows one to pass a custom table name, but not a schema name. As a result, it is impossible to use a schema other than the default search path indicates.

An example usage of schema specification would look like this:

  new knexStore({
    knex: myKnexInstance,
    tablename: 'sessions',
    schemaname: 'myapp'
  })

Any thoughts?