Use MySQL As Session Store?

[JohnPostlethwait]

I have searched all over – is this possible at the moment?

I found the documentation on Mongo and Redis, but nothing for other adapters...

If this is not possible, do I have any options for storing sessions at the moment besides switching/adding Mongo/Redis?

[sgress454]

Short answer: no, there is no MySQL session store bundled with Sails.

Long answer: it looks like there are a couple of projects on GitHub trying to create a MySQL session store for Node, here and here. To use a custom store, add a "store" property in your config/sessions.js file like:

store: new (require('connect-mysql')(require('<path to Sails>/node_modules/express')))({<configuration for session store>})

Let us know if you have any luck with either of the MySQL session adapters!

[chrislbennett]

I know this one has been closed for 4 months, but I wanted to add a comment. I was able to get one of the mysql session stores working using the above comment. Here is what my session.js looks like ... (be sure to install connect-mysql-session, it was only one I could get working.)

var express = require('/usr/lib/node_modules/sails/node_modules/express'),
MySQLSessionStore = require('connect-mysql-session');

module.exports.session = {

secret: ' secret goes here',
store: new (MySQLSessionStore(express))('database', 'username',
    'passport', { host: 'hostname'})

};

[mikermcneil]

@chrislbennett thanks for sharing!

[mikermcneil]

@JohnPostlethwait @sgress454 @chrislbennett Update: all connect session stores are now supported, see #171 (comment)

[kai23]

Hello ! I'm trying to use the mysql connect session store, but I don't see how to use it. Any Idea ? I've trie the

adapter: 'mysql',

But it says :

error: Error: Invalid session adapter :: mysql

Any Idea ?

[sahanDissanayake]

@chrislbennett How did you remove unwanted console.logs you get on terminal when you are using connect-mysql-connection ?

Have you come across this at all ?

Thank buddy

[macedd]

An example would be welcome..

[chrislbennett]

Here is how I was able to make it work before:
Add the following within the session.js config file.

store: new (MySQLSessionStore(express))('db', 'user',
'pass', { host: 'server'})

Hope that helps ...

[chrislbennett]

Apologize, @sahanDissanayake I didn't see your message until just a little bit ago. I don't remember there being an excess of console entries, I'll have to try it again and see what happens.

Thanks

[chrislbennett]

Looks like the connect-mysql-session has been abandoned, so its better to use express-mysql-session. Luckily, its actually easier to configure in the session.js config file.

var MySQLSessionStore = require('express-mysql-session');

var store_options = {
host: 'server',
port: 3306,
user: 'user',
password: 'pass',
database: 'db'
}

module.exports.session = {
secret: 'secret goes here',
store: new MySQLSessionStore(store_options)
}

Hopefully that will get you rolling.

[kevintechie]

Is there a way to re-use the connection used by the models for the session store? Or is there a way to specify the connection parameters for the session store in the environment config files?

[neonexus]

For anyone that stumbles upon this, looking to use the same credentials from an already existing connection, where the credentials are only stored in local.js for security (as it should be), try a project hook!

Create a file in api/hooks (call it whatever you like).js, drop in the following code:

var MySQLSessionStore = require('express-mysql-session');

module.exports = function setupSessionStore(sails){
    return {
        configure: function(){
            var conn = sails.config.models.connection;

            sails.config.session.store = new MySQLSessionStore({
                host: sails.config.connections[conn].host,
                port: sails.config.connections[conn].port,
                user: sails.config.connections[conn].user,
                password: sails.config.connections[conn].password,
                database: sails.config.connections[conn].database,
                createDatabaseTable: true
            });
        }
    };
};

Don't forget to install the package:

npm install express-mysql-session --save

And now you have MySQL session storage.

[icodeforlove]

@neonexus That worked great for me, excepted i needed to modify the first line to

var session = require('express-session'),
    MySQLSessionStore = require('express-mysql-session')(session);

Thanks again.

[neonexus]

@icodeforlove Sounds like you probably have automatic session handling disabled, which would make your case a little different.

[wZVanG]

The before solutions does not work in Sails V1.0.0-46:
I fixed it with this configuration:

File: /config/session.js

  ...
  adapter: 'connect-mysql',
  config: {
    user: 'my_dbuser', 
    password: 'my_dbpassword', 
    database: 'my_dbname',
  },
  table: 'sessions',
 ...

Do not forget that you must install these packages first:

npm install sails-mysql@beta --save
npm install connect-mysql --save

[neonexus]

I had to laugh at myself a little bit, because I stumbled upon this thread, once again, found my original post about the hook to re-use already established credentials, thought to myself "I need to give a thumbs up to the poster, exactly what I needed!", only to realize I posted it -_-.

Anyway! I've come to make an update to my original post, to work with the new Sails v1 standard of "datastores", instead of "connections". Here is the new hook that just worked for me:

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

module.exports = function setupSessionStore(sails){
    return {
        configure: function(){
            const conn = sails.config.models.datastore;

            sails.config.session.store = new MySQLSessionStore({
                host: sails.config.datastores[conn].host,
                port: sails.config.datastores[conn].port,
                user: sails.config.datastores[conn].user,
                password: sails.config.datastores[conn].password,
                database: sails.config.datastores[conn].database,
                createDatabaseTable: true
            });
        }
    };
};

Don't forget to install the package:

npm install express-mysql-session --save

Oh, and keep the default session handling turned on in Sails, otherwise you will have to do what @icodeforlove did.