freshgiammi-lab / connect-typeorm

A TypeORM-based session store.

🔗 connect-typeorm

A TypeORM-based session store.

Setup & Usage

Configure TypeORM with back end of your choice:

NPM

npm install connect-typeorm express-session typeorm sqlite3
npm install -D @types/express-session

Implement the Session entity:

// src/domain/Session/Session.ts

import { ISession } from 'connect-typeorm';
import { Column, DeleteDateColumn, Entity, Index, PrimaryColumn } from 'typeorm';

@Entity()
export class Session implements ISession {
  @Index()
  @Column('bigint')
  public expiredAt = Date.now();

  @PrimaryColumn('varchar', { length: 255 })
  public id = '';

  @Column('text')
  public json = '';

  @DeleteDateColumn()
  public destroyedAt?: Date;
}

Pass repository to TypeormStore:

// src/app/Api/Api.ts

import { TypeormStore } from 'connect-typeorm';
import { getRepository } from 'typeorm';
import * as Express from 'express';
import * as ExpressSession from 'express-session';

import { Session } from '../../domain/Session/Session';

export class Api {
  public sessionRepository = getRepository(Session);

  public express = Express().use(
    ExpressSession({
      resave: false,
      saveUninitialized: false,
      store: new TypeormStore({
        cleanupLimit: 2,
        limitSubquery: false, // If using MariaDB.
        ttl: 86400,
      }).connect(this.sessionRepository),
      secret: 'keyboard cat',
    })
  );
}

TypeORM uses { "bigNumberStrings": true } option by default for node-mysql, you can use a Transformer to fix this issue:

import { Bigint } from "typeorm-static";

@Column("bigint", { transformer: Bigint })

Options

Constructor receives an object. Following properties may be included:

• cleanupLimit For every new session, remove this many expired ones (does not distinguish between users, so User A logging in can delete User B expired sessions). Defaults to 0, in case you need to analyze sessions retrospectively.

• limitSubquery Select and delete expired sessions in one query. Defaults to true, you can set false to make two queries, in case you want cleanupLimit but your MariaDB version doesn't support limit in a subquery.

• ttl Session time to live (expiration) in seconds. Defaults to session.maxAge (if set), or one day. This may also be set to a function of the form (store, sess, sessionID) => number.

• onError Error handler for database exception. It is a function of the form (store: TypeormStore, error: Error) => void. If not set, any database error will cause the TypeormStore to be marked as "disconnected", and stop reading/writing to the database, therefore not loading sessions and causing all requests to be considered unauthenticated.

License

MIT

connect-typeorm's Issues

[FEAT]: Documentation about TypeOrmStore in express-session in NestJS using PostgreSQL

Description

As Typeorm has depricated the getConnection, getRepository methods, we are unable to store the session in the DB.

Another way is as follows:

import { TypeormStore } from 'connect-typeorm/out';
import { DataSource } from 'typeorm';
import * as session from 'express-session';
// other imports

const sessionRepository = app.get(DataSource).getRepository(SessionsEntity);

app.use(
    session({
      // other session options
      store: new TypeormStore().connect(sessionRepository),
    }),
);

Additional Information

No response

Is this project dead?

Seems this project didn't update for one year, i used it in some project , but currently has compatibility issue with typeorm.

How does stale session cleanup work?

It appears to me that most session stores manually implement some maxAge based session cleanup sweep. After inspecting the source code and viewing the README, it doesn’t appear that such a feature exists in this package. As a result, I would expect the backend storage to continuously grow.

Can a periodic automatic deletion of stale records be added?

[BUG]: Spam Update after start server

Contact Details

No response

Bug description

After start server NestJs + TypeOrm + Graphql
session few times tried to update

[Nest] 10205  - 23.02.2023, 11:16:55     LOG [GraphQLModule] Mapped {/graphql, POST} route +198ms
[Nest] 10205  - 23.02.2023, 11:16:55     LOG [NestApplication] Nest application successfully started +3ms
🚀 Server server is running on : http://[::1]:4000
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151031456,"PNDawtNPJili9eClxEz2LZlJyv9Zj1Om"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151032792,"PNDawtNPJili9eClxEz2LZlJyv9Zj1Om"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151033425,"PNDawtNPJili9eClxEz2LZlJyv9Zj1Om"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151034445,"PNDawtNPJili9eClxEz2LZlJyv9Zj1Om"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151035425,"PNDawtNPJili9eClxEz2LZlJyv9Zj1Om"]

Next issue after when I used Mutation to login and inserted first session to entity, server just started spam update and changing session.destroyedAt. Its not good practice to spam like that every milisecond.

query: INSERT INTO "public"."sessions"("expiredAt", "id", "json", "destroyedAt") VALUES ($1, $2, $3, DEFAULT) RETURNING "destroyedAt" -- PARAMETERS: [1677154720896,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI","{\"cookie\":{\"originalMaxAge\":3600000,\"expires\":\"2023-02-23T12:18:40.696Z\",\"secure\":true,\"httpOnly\":true,\"path\":\"/\",\"sameSite\":\"none\"},\"userId\":\"b018aec8-27b5-4239-810e-edf5b1fc2074\",\"language\":\"pl\"}"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151121208,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: UPDATE "public"."sessions" SET "expiredAt" = $1 WHERE "id" IN ($2) -- PARAMETERS: [1677154721442,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151122208,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: UPDATE "public"."sessions" SET "expiredAt" = $1 WHERE "id" IN ($2) -- PARAMETERS: [1677154722368,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151123207,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: UPDATE "public"."sessions" SET "expiredAt" = $1 WHERE "id" IN ($2) -- PARAMETERS: [1677154723299,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151124212,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: UPDATE "public"."sessions" SET "expiredAt" = $1 WHERE "id" IN ($2) -- PARAMETERS: [1677154724292,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151125218,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: UPDATE "public"."sessions" SET "expiredAt" = $1 WHERE "id" IN ($2) -- PARAMETERS: [1677154725301,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151126212,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: UPDATE "public"."sessions" SET "expiredAt" = $1 WHERE "id" IN ($2) -- PARAMETERS: [1677154726301,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151127207,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: UPDATE "public"."sessions" SET "expiredAt" = $1 WHERE "id" IN ($2) -- PARAMETERS: [1677154727288,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: SELECT "session"."expiredAt" AS "session_expiredAt", "session"."id" AS "session_id", "session"."json" AS "session_json", "session"."destroyedAt" AS "session_destroyedAt" FROM "public"."sessions" "session" WHERE ( "session"."expiredAt" > $1 AND "session"."id" = $2 ) AND ( "session"."destroyedAt" IS NULL ) -- PARAMETERS: [1677151128209,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]
query: UPDATE "public"."sessions" SET "expiredAt" = $1 WHERE "id" IN ($2) -- PARAMETERS: [1677154728277,"5bLbGqQp5MEusImGFnPUXZdulu_uHHHI"]

Its any chance to disabled that to setup manually or adjust time to next update ?

My configuration

    const sessionMiddleware = session({
      // store: new RedisStore({
      //   client: redis as any,
      // }),
      store: new TypeormStore().connect(getRepository(SessionEntity)),
      name: process.env.SESSION_COOKIE_NAME,
      secret: process.env.SESSION_SECRET,
      resave: false,
      saveUninitialized: false,
      // rolling: true,
      cookie: {
        httpOnly: true,
        maxAge: Number(process.env.SESSION_MAXAGE),
        sameSite: 'none',
        secure: PORT !== 8080,
      },

Steps to reproduce

.

Additional Information

No response

Session destroy does not work

Bug

Symptom

If one tries to destroy a session via req.session.destroy() the destroy method of TypeormStore is called. Within this method (TypeormStore.ts, line 106), there is a TypeORM call which does not work:
TypeError: this.repository.deleteById is not a function

Solution

The TypeORM method is called "delete" so line 106 should be changed to:
this.repository.delete(x)
At least thats working for me.

PS: Is there a typo in TypeormStore.ts line 23 or what does the '!' stand for?

[BUG]: getRepository 'typeorm' is deprecated

Contact Details

No response

Bug description

The documentation for connect-typeorm shows the session store connection being set to the entity repository from the depreciated TypeORM getRepository method. Is there a replacement for getRepository or a workaround for this issue?

Steps to reproduce

See documentation (README.md) for this repository.

Additional Information

No response

Let users define the name of the `expiredAt` column

We use snake_case in our project, would be cool to just be able to pass this as an option

Question about the Bigint transformer in the README

Hi Denys, Nice TypeORM - Session Express integration! I'm going to start using it on one of my projects.
I have a quick question about the documentation:

What is the benefit of using transformer: Bigint?
@Column("bigint", { transformer: Bigint })

instead of:
@Column('bigint')

typeorm-static Is a nice library but I feel that it should be optional and independent from connect-typeorm documentation examples. What do you think? I sent you a PR if you like the idea of changing this :)

Thanks!

TypeORM: Delete statement was used alias name cause of exception if Table Name was changed.

Entity with name 'Session':

@Entity('Session')
export class Session implements ISession {
    @Index()
    @Column({
        type: 'bigint',
        transformer: Bigint
    })
    expiredAt = Date.now();

    @PrimaryColumn({
        length: 255
    })
    id: string;

    @Column()
    json: string;
}

Code Line 112:

  this.repository
            .createQueryBuilder("session")
            .delete()
            .where(`session.id IN (${ids})`)
            .execute(),

Exception:

query: DELETE FROM "Session" WHERE session.id IN (NULL)
query failed: DELETE FROM "Session" WHERE session.id IN (NULL)
error: { error: missing FROM-clause entry for table "session"

Suggestion:

this.repository
           .createQueryBuilder()
           .delete()
           .where(`id IN (${ids})`)
           .execute();

Maintainership of connect-typeorm

@0x0ece @Kehrlann @jdhuntington

Hello. I appreciate your contributions to connect-typeorm. The library has helped many people for over three years.

Where I work now, Node.js isn't a primary tool, and I no longer dogfood this library. I have recurring health issues and spend most of my free time far from a computer.

It'd be great if any of you whom I mentioned above could take over the v2 release and further maintenance of connect-typeorm. Please reply and I'll be happy to send you repository and npm access.

Allow TypeormStore to recover from failed request

Hey there 👋
First, thanks for the lib !

We had an issue in prod the other day ; our (Postgres) database threw an exception at some point while fetching the sessions. However it was just a flake, not a complete crash of the connection. When this happens, the TypeormStore emits a disconnect event, and then never reconnects. We did a quickfix but that's nasty (see below).

I don't think the store should disconnect on every time there's an exception. Not sure what the ideal behavior would be:

• Do not disconnect, just log the errors ? Most of the connect-xyz implementations do not emit connect/disconnect events, see Redis: https://github.com/tj/connect-redis/
• Buffer the errors and only disconnect after a certain number of errors ?
• Include a "reconnect" mechanism ?
• Include a "circuit-breaker" type mechanism ?
• Give the users a way to define a failure handler ?

Any thoughts ?

---

For reference, our quickfix is to do something like:

  const store = new TypeormStore({
    cleanupLimit: 2,
    ttl: 86400,
  })
    .connect(sessionRepository)
    .on("disconnect", () => setTimeout(() => store.emit("connect"), 100));

Note: The timeout is to give the disconnect event more time to propagate before we fire a connect event, I think even a 0 timeout would work.

misused double quotes lead to error on PostgreSQL

By executing the following line, the resulted expression contains double quotes " that lead to an error on PostgreSQL.

https://github.com/nykula/connect-typeorm/blob/6723f9963d437681c1e4ac6389ed8caec11b4683/src/app/TypeormStore/TypeormStore.ts#L109

On Postgres, the double quotes are used to escape identifiers meanwhile single quotes are used to escape values.

npm install throws an error Property 'observable' does not exist

npm install throws an error:

node_modules/ava/index.d.ts:5:10 - error TS2339: 
Property 'observable' does not exist on type 'SymbolConstructor'.
5  [Symbol.observable](): ObservableLike;

Bigint issue with tedious

I'm pretty new with node so forgive me if I'm asking in the wrong place.
After following the readme, I'm running into this error: RequestError: Validation failed for parameter '0'. Value must be between -2147483648 and 2147483647

The query it is trying to run:
query failed: SELECT "session"."id" AS "session_id", "session"."expiredAt" AS "session_expiredAt", "session"."json" AS "session_json" FROM "session" "session" WHERE "session"."expiredAt" > @0 AND "session"."id" = @1 -- PARAMETERS: [1614629900663,"2XztQAQI5icnM_us9nQvu-VramBgMxQ_"]

After debuging some I found that issue seems to be at the Request.validateParameters method for the tedious library is getting a type INT8 instead of a type BigInt.

name:'1'
output:false
precision:undefined
scale:undefined
type:{id: 127, type: 'INT8', name: 'BigInt', declaration: ƒ, writeTypeInfo: ƒ, …}
value:1614724349837

This is trying to process it as an int instead of the string that tedious needs as described here:
http://tediousjs.github.io/tedious/api-datatypes.html

Any idea on how to pass the ExpiresAt property as a string to tedious?

Code:

//indxes.ts
import { ApolloServer } from 'apollo-server-express';
import express from 'express';
import expressSession from 'express-session';
// import { Db } from 'typeorm-static';
import { createConnection } from 'typeorm';
import { Session } from './src/typeorm/entity/Session';
import { TypeormStore } from 'connect-typeorm';
import schema from './src/schema';
const sessionSecret = "keyboard cat"; //TODO: move to .env

//typeorm createConnection uses settings in ormconfig.json
createConnection().then(async (connection) => {
  const server = new ApolloServer({
    schema,
    context: req => ({
      ...req,
    })
  });

  const app = express();
  const sessionRepository = connection.getRepository(Session);
  const session = expressSession({
    name: "midas-4-auth",
    resave: false,
    saveUninitialized: false,
    store: new TypeormStore({
      cleanupLimit: 2,
      ttl: 86400,
    }).connect(sessionRepository),
    secret: sessionSecret,
  });
  app.use(session);

  server.applyMiddleware({
    app,
    cors: {credentials: true, origin: true }
  });
  app.listen({ port: 4000 });
}).catch((error) => 
  console.log(error)
);

import { ISession } from "connect-typeorm";
import { Column, Entity, Index, PrimaryColumn } from "typeorm";

@Entity()
export class Session implements ISession {
  @PrimaryColumn("varchar", { length: 255 })
  public id: string;

  @Index()
  @Column("bigint")
  public expiredAt: number;

  @Column({ type: "varchar", length: 250 })
  public json: string;
}

Use

req.session.user = { username };

Thanks

Error Types [email protected]

Types of parameters 'selection' and 'selection' are incompatible.
Type 'string' is not assignable to type '(qb: SelectQueryBuilder) => SelectQueryBuilder'

cleanupLimit > 0 causes failure on MySQL & MariaDB backends

Version: 1.1.0

When the cleanupLimit option is set to a number greater than zero setting a session will fail with the error "ER_NOT_SUPPORTED_YET: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'".

I believe this is because of the query used to delete expired sessions - the MySQL documentation describes this error occuring when a subquery contains LIMIT - https://dev.mysql.com/doc/refman/8.0/en/subquery-errors.html

Session `expiredAt` field is always updated in store (even if `resave` option set to false)

Is it expected behaviour that expiredAt field is always updated in store if session configuration includes resave option set to false?
As far as I see it is triggered from touch method (https://github.com/nykula/connect-typeorm/blob/master/src/app/TypeormStore/TypeormStore.ts#L199).

I have looked at other implementations of session store (e.g. connect-mongo) and there expires is not changed if session cookie already has expiration date (place in sources).

Add tests

The tests should exercise all public functions for all supported versions of typeorm and test the package as a “real” install. I may try contributing something if I can figure it out.

Softdelete and race condition on logout

Hi, currently express-session + connect-typeorm suffer from a race condition on logout:

1. ReqA starts and maybe modifies the session
2. ReqB arrives and logs out
3. ReqA terminates after ReqB and overrides, by resaving the session in the db

One possible solution is to add an extra column destroyed to the schema (default 0). Destroying a session would only flag the record as destroyed=1. All updates should only update when destroyed=0.

In short, it's a soft delete. It relies on the current cleanup to purge sessions. By adding the extra condition on updates, it'd prevent the race condition.

I can file a PR but since it's a pretty profound change, I wanted to check in first. Thoughts? Thank you.