使用Sequelize连接freedb.tech在线SQL数据库失败求助

问题 #

学习API开发时，尝试将API与SQL数据库连接。本地使用GitHub上的node-mysql-registration-login-api代码连接数据库一切正常，但切换到freedb.tech在线数据库时，出现权限拒绝错误：

throw new SequelizeErrors.AccessDeniedError(err);
AccessDeniedError [SequelizeAccessDeniedError]: Access denied for user 'freedb_moruuser'@'localhost' (using password: YES)
    at ConnectionManager.connect (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\sequelize\lib\dialects\mysql\connection-manager.js:118:17)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async ConnectionManager._connect (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\sequelize\lib\dialects\abstract\connection-manager.js:318:24)
    at async C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\sequelize\lib\dialects\abstract\connection-manager.js:250:32
    at async ConnectionManager.getConnection (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\sequelize\lib\dialects\abstract\connection-manager.js:280:7)
    at async C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\sequelize\lib\sequelize.js:613:26
    at async MySQLQueryInterface.createTable (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\sequelize\lib\dialects\abstract\query-interface.js:225:12)
    at async Function.sync (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\sequelize\lib\model.js:1300:5)
    at async Sequelize.sync (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\sequelize\lib\sequelize.js:793:35)
    at async initialize (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\_helpers\db.js:27:5) {
  parent: Error: Access denied for user 'freedb_moruuser'@'localhost' (using password: YES)
      at Packet.asError (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\mysql2\lib\packets\packet.js:712:17)
      at ClientHandshake.execute (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\mysql2\lib\commands\command.js:28:26)
      at Connection.handlePacket (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\mysql2\lib\connection.js:417:32)
      at PacketParser.onPacket (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\mysql2\lib\connection.js:75:12)
      at PacketParser.executeStart (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\mysql2\lib\packet_parser.js:75:16)
      at Socket.<anonymous> (C:\Users\Agung Suhermin\Documents\Code\attacktest\nodelearn-jasonwatmore\node-mysql-registration-login-api\node_modules\mysql2\lib\connection.js:82:25)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10) {
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10) { 
    code: 'ER_ACCESS_DENIED_ERROR',
    errno: 1045,
    sqlState: '28000',
    sqlMessage: "Access denied for user 'freedb_moruuser'@'localhost' (using password: YES)"

已确认用mysql2直接连接该freedb数据库成功，但Sequelize连接报错。当前代码如下：

const config = require('config.json');
const mysql = require('mysql2/promise');
const { Sequelize } = require('sequelize');

module.exports = db = {};

initialize();

async function initialize() {
    // create db if it doesn't already exist
    const { host, port, user, password, database } = config.database;
    const connection = await mysql.createConnection({ host, port, user, password });
    await connection.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);

    // connect to db
    const sequelize = new Sequelize(database, user, password, { dialect: 'mysql' });

    // init models and add them to the exported db object
    db.User = require('../users/user.model')(sequelize);

    // sync all models with database
    await sequelize.sync();
}

数据库配置：

{
    "database": {
        "host": "sql.freedb.tech",
        "port": 3306,
        "user": "freedb_moruuser",
        "password": "F%#!7Bdja*V?48A",
        "database": "freedb_MoruDatabase"
    },
    "secret": "THIS IS USED TO SIGN AND VERIFY JWT TOKENS, REPLACE IT WITH YOUR OWN SECRET, IT CAN BE ANY STRING"
}

疑问：无法连接是因为mysql2和freedb.tech的MySQL8版本不兼容？还是其他原因？（已尝试替换为mysql库，问题依旧）

解决方法 #

问题核心是Sequelize实例初始化时没有传入host和port参数，导致Sequelize默认尝试连接localhost，而非配置中指定的sql.freedb.tech，这就是错误提示里出现'freedb_moruuser'@'localhost'的原因。

修改Sequelize初始化代码，补充host和port配置：

// 修改前
const sequelize = new Sequelize(database, user, password, { dialect: 'mysql' });

// 修改后
const sequelize = new Sequelize(database, user, password, { 
  dialect: 'mysql',
  host: host,
  port: port
});

或者更清晰的写法：

const sequelize = new Sequelize({
  dialect: 'mysql',
  host: host,
  port: port,
  username: user,
  password: password,
  database: database
});

额外注意两点：

• freedb.tech的在线数据库通常已提前创建好指定库，可删除CREATE DATABASE IF NOT EXISTS代码，避免不必要的权限问题
• MySQL 8.0默认使用caching_sha2_password认证插件，若后续遇到SSL或认证错误，可在Sequelize配置中添加：

  dialectOptions: { 
    ssl: { rejectUnauthorized: false },
    authPlugins: { 
      caching_sha2_password: require('mysql2/lib/auth_plugins/caching_sha2_password.js') 
    }
  }
  

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