Storing session data in a database

[jrunning]

The docs on sessions briefly touch on storing session data in a database:

For larger applications or when more advanced session management is needed, session data can be stored in a database. This approach is similar to the cookie-based approach, but with some key differences:

• The session data is stored in the database, associated with the session ID.
• Only the session ID is stored in the cookie, not the session data.
• The session data is retrieved from the database using the session ID, instead of being retrieved directly from the cookie.
• Upon expiration, in addition to the session cookie, the database record containing the session data is also removed.

SolidStart does not automatically handle interactions with a database; you need to implement this yourself.

...and that's it. There's no guidance on how to implement any of this. I've googled high and low, going so far as to poke around the Vinxi and H3 source code, and come up with nothing concrete. Can anybody give me any hints on accomplishing this?

[katywings]

Without going too much into details 😅, the approach of storing the session data in the db might probably look something like this:

type SessionData = {
  id: string
};

export async function useUserSession() {
  "use server";
  const session = await useSession<SessionData>({
    password: process.env.SESSION_SECRET as string,
    name: "user",
  });

  if (!session.data.id) {
    const result = db.sessions.create();
    await session.update({
      id: result.id
    });

    return result; 
  } else {
    const result = db.sessions.get(session.data.id); 
    if (result.updatedAt + maxSessionAge < now) {
      return
    } 
    
    db.sessions.update(session.data.id, { 
      updatedAt: "now()"
    })

    return result
  }
}

The real complexity here comes from properly invalidating/clearing up old sessions in the db. It's recommended to regularely clean up the old sessions e.g. via a cronjob 😅.

[jrunning]

Thanks, @katywings. That's really helpful!

[tamish-max]

A good mental model is: useSession() manages the cookie, while you manage the database row that the cookie points to.

So instead of storing the whole user/session payload in the cookie, store only a generated session id there, then load the rest from your DB on each request.

// lib/session.ts
import { useSession } from 'vinxi/http';
import { db } from './db';

export async function getAppSession() {
  const cookieSession = await useSession<{ sid?: string }>({
    password: process.env.SESSION_SECRET!,
    name: 'sid'
  });

  if (!cookieSession.data.sid) {
    const sid = crypto.randomUUID();
    await db.session.create({ data: { id: sid, userId: null } });
    await cookieSession.update({ sid });
  }

  return db.session.findUnique({
    where: { id: cookieSession.data.sid! }
  });
}

Then in login/logout flows, update or delete that DB row.

await db.session.update({ where: { id: sid }, data: { userId } });
// logout
await db.session.delete({ where: { id: sid } });
await cookieSession.clear();

The important extras are expiration + cleanup (cron/background job) and rotating the id on login if you want stronger session fixation protection. But architecturally, that is the whole pattern.