How to query all sessions of a user?

[OrkhanAlikhanov]

Let's say I've used TypeORMStore for sessions, how can I query all sessions of a user? The userId seems to be stored in a json string in sessionContent column.

foal/packages/typeorm/src/typeorm-store.service.ts

Lines 30 to 37 in 647ea2d

	await this.execQuery(
	`INSERT INTO foal_session (session_id, session_content, updated_at, created_at)
	VALUES (:sessionID, :sessionContent, :date, :date)`,
	{
	date,
	sessionContent: JSON.stringify(sessionContent),
	sessionID,
	}

There seems no reliable and easy way to query a user's sessions. It would be great if could store userId in a different columns so that we could do select * from foal_session where userId = 1

[OrkhanAlikhanov]

Mature frameworks like Laravel should be taken as an example in this session management regard as they seem to be battle tested. No pressure, just sayin' :)

[LoicPoullain]

Hi @OrkhanAlikhanov,

Thank you for submitting this issue.

There seems no reliable and easy way to query a user's sessions.

Indeed it is currently not possible. This is definitely a feature that is missing in the framework and that will be added in the future. In a next release, we will be able to retrieve (and even limit) the session(s) of a given user. The solution will be compatible with any session store (including cache database such as redis).

I'll take a deeper a look at Laravel to get inspired 😉

[OrkhanAlikhanov]

Amazing!

Here is the table that Laravel uses for DB store. From docs

Schema::create('sessions', function ($table) {
    $table->string('id')->unique();
    $table->unsignedInteger('user_id')->nullable();
    $table->string('ip_address', 45)->nullable();
    $table->text('user_agent')->nullable();
    $table->text('payload');
    $table->integer('last_activity');
});

I believe ip_address is used to throttle login attempts (as a default feature), not sure how they use user_agent internally, and finally payload is equivalent of foal's session_content column. All session stuff is nicely organized in Sessions folder here.

[LoicPoullain]

Resolved in v2