express-mysql-session可写入数据库但无法读取的问题求助

我采用ES模块（ECMA语法）编写Node.js应用，包含dependencies.mjs和index.js。此前在CommonJS项目中成功配置express-mysql-session与express-session，但当前项目出现异常：用户登录时session能正确写入MySQL数据库，但访问登录墙后的路由时，获取到的session与数据库中存储的不一致。

相关代码 #

index.js #

'use strict';
import dotenv from 'dotenv';
dotenv.config();
import dependencies from "./dependencies.mjs";
const { DateTime, express, connectToDb, cors, mysql2 } = dependencies;
import session from "express-session";
import MySQLStore from "express-mysql-session";

const sessconn = mysql2.createPool({
  host: process.env.SESSION_DB_HOST,
  user: process.env.SESSION_DB_USER,
  password: process.env.SESSION_DB_PASSWORD,
  database: process.env.SESSION_DB_NAME,
  socketPath: process.env.SOCKET_PATH,
  connectionLimit: 1,
});

const sessionStore = new (MySQLStore(session))({
  clearExpired: true,
  expiration: 86400000,
  checkExpirationInterval: 3600000,
  createDatabaseTable: true,
}, sessconn);
    
connectToDb().then(()=>{
  console.log('connected to db!!');
}).catch((err)=>{
  return res.status(500).send({msg: 'Error connecting to database', err: err.message})
})

const app = express();
app.use(express.json({ limit: '300kb' }))
app.use(cors({
  origin: process.env.WEBSITE_DOMAIN,
  allowedHeaders: ["content-type", "authorization"],
  credentials: true,
}));
app.use(session({
  name: 'session_name',
  store: sessionStore,
  secret: process.env.SESSION_SECRET,
  saveUninitialized: false,
  resave: false,
  cookie: {
    sameSite: 'none',
    secure: false
    maxAge: +process.env.COOKIE_MAXAGE,
    httpOnly: true,
  }
}));

app.use(express.static('public'));

import { router as authRoutes } from './routes/auth.mjs';
app.use('/auth', authRoutes)

// auth middleware / login wall
app.use((req,res,next) => {
  if (req.session.user) {
    let expiry = DateTime.now().plus({hours: 1}).toISO();
    req.session.user.expiry = expiry;
    next();
  } else {
    res.status(403).send({ msg: "You must be logged in to access this resource."});
  }
})

app.listen(port, () => console.log(`Its alive on port ${port}!`))

dependencies.mjs #

"use strict";
import dotenv from 'dotenv';
dotenv.config();
import express from 'express';
import cors from 'cors';
import { DateTime, Settings } from 'luxon';
Settings.defaultZone = "Australia/Brisbane";
import validation from 'express-validator';
const { body, validationResult, param, check } = validation;
import mysql2 from 'mysql2/promise';

const dbconn = await mysql2.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  socketPath: process.env.SOCKET_PATH,
});

async function connectToDb() {
  try {
    await dbconn.connect()
  } catch (err) {
    return err
  }
}

import bcrypt from 'bcrypt'
import crypto from 'crypto'
import sgMail from '@sendgrid/mail';
sgMail.setApiKey(process.env.SENDGRID_API_KEY);

export default { 
  DateTime, 
  Settings, 
  express, 
  body,
  check,
  param,
  validationResult, 
  dbconn, 
  bcrypt, 
  crypto, 
  sgMail, 
  cors,
  connectToDb,
  mysql2
};

认证路由代码 #

if(req.session.user){
  return res.status(200).send({msg: "Logged in already.", user: req.session.user});
} else {
  const expireTime = DateTime.now().plus({hours: 1}).toISO();
  req.session.cookie.user = {
      id: user_object.id,
      email: req.body.username,
      name: `${first} ${last}`,
      expiry: expireTime
  }

  return res.status(200).send({msg: "Session created", user: req.session.user});
} 

问题排查与修复 #

1. 核心错误：session数据写入位置错误 #

你将用户数据写入了req.session.cookie.user，但cookie对象是用来配置session的HTTP Cookie属性（如过期时间、SameSite等），不是存储用户业务数据的容器。正确的写法是直接写入req.session.user：

// 错误写法
// req.session.cookie.user = { ... }

// 修正写法
req.session.user = {
    id: user_object.id,
    email: req.body.username,
    name: `${first} ${last}`,
    expiry: expireTime
}

2. Cookie配置语法错误 #

index.js中session的cookie配置里，secure: false后缺少逗号，导致后续的maxAge、httpOnly等配置无效，可能影响session的正常读取：

// 错误部分
cookie: {
  sameSite: 'none',
  secure: false
  maxAge: +process.env.COOKIE_MAXAGE,
  httpOnly: true,
}

// 修正后
cookie: {
  sameSite: 'none',
  secure: false,
  maxAge: +process.env.COOKIE_MAXAGE,
  httpOnly: true,
}

3. 数据库连接错误处理无效 #

connectToDb()的catch回调中使用了res对象，但此时res还未定义（在app实例创建之前），会导致未捕获的异常，影响后续流程。修正为直接打印错误并退出进程：

connectToDb().then(()=>{
  console.log('connected to db!!');
}).catch((err)=>{
  console.error('Error connecting to database:', err.message);
  process.exit(1); // 连接失败时终止进程，避免后续异常
})

4. Session更新后需手动保存 #

登录墙中间件中更新了req.session.user.expiry，但因为配置了resave: false，session不会自动同步到数据库，需要手动调用req.session.save()：

app.use((req,res,next) => {
  if (req.session.user) {
    let expiry = DateTime.now().plus({hours: 1}).toISO();
    req.session.user.expiry = expiry;
    // 手动保存session到数据库
    req.session.save((err) => {
      if (err) console.error('Session save error:', err);
      next();
    });
  } else {
    res.status(403).send({ msg: "You must be logged in to access this resource."});
  }
})

内容的提问来源于stack exchange，提问作者David