mysql - 如何使用sequelize获取对象数据？

Question

我正在尝试实现 Sequelize作为 NodeJs 中的 ORM，我将它用于 Mysql，
我的示例中有 3 个表 -

1. 角色
2. 用户(有角色)
3.代码(由用户创建)

我无法正确查询表/模型，
因为我应该接收表的模型表示，这被称为外键。

以下是我的数据库结构 -
1.角色表-

<强>2。用户表-

<强>3。代码表-

以下是表创建查询 -
1.角色 - 创建表角色 ( id int(11) NOT NULL AUTO_INCRMENT, 名称 varchar(100) NOT NULL, 主键(id) )；

<强>2。用户-

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `role` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `role` (`role`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`role`) REFERENCES `role` (`id`)
);

<强>3。代码-

CREATE TABLE `code` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `createdBy` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `createdBy` (`createdBy`),
  CONSTRAINT `code_ibfk_1` FOREIGN KEY (`createdBy`) REFERENCES `user` (`id`)
);

以下是我的 app.js 文件 -

 const db = require('./db');
const Code = require('./code');
const User = require('./user');
const Role = require('./role');
const async = require('async');


async.waterfall([
    function(callback) {
        db
        .authenticate()
        .then(() => {
            console.log('Connection has been established successfully.');
            callback(null,"ok");
        })
        .catch(err => {
            return callback(err,null);
        });        
    },
    function(resp,callback) {
        Code.findAll({include: [{ model: User}]})
        .then(code => {
            console.log("All users:", JSON.stringify(code, null, 4));
            callback(null,"ok");
        })
        .catch(err => {
            callback(err,null);
        });

        // Code.findOne({
        //     where: {
        //         id: 1
        //     }
        // })
        // .then(code => {
        //     console.log("All users:", JSON.stringify(code, null, 4));
        // })
        // .catch(err => console.log("Error => \n",err));

    },
],
function(err, resp) {
    if (err) {
        console.log(err);
    } else {
        console.log(resp);
    }
});

以下是我的 db.js 文件 -

const Sequelize = require('sequelize');

module.exports =  new Sequelize('junit', 'root', 'root', {
    host: 'localhost',
    /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */
    dialect: 'mysql',
    //operatorsAliases: false,
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    }
  });

以下是我的 role.js 文件 -

const Sequelize = require('sequelize');
const db = require('./db');
const User = require('./user');

const Role = db.define('role', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull:false,
        autoIncrement: true
    },
    name: {
      type: Sequelize.STRING
    }
  }, {
    tableName: 'role',
    freezeTableName: true,
    timestamps: false
});

associate : (models) => {
    Role.hasMany(models.User,{
        foreignKey: 'role'
    });
};

module.exports = Role;

以下是我的 user.js 文件 -

const Sequelize = require('sequelize');
const db = require('./db');
const Code = require('./code');
const Role = require('./role');

const User = db.define('user', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull:false,
        autoIncrement: true
    },
    name: {
      type: Sequelize.STRING
    },
    email: {
        type: Sequelize.STRING
    },
    role: {
        type: Sequelize.INTEGER,
        references: {
          // This is a reference to another model
          model: Role,

          // This is the column name of the referenced model
          key: 'id'
        }
      }
  }, {
    tableName: 'user',
    freezeTableName: true,
    timestamps: false
});

associate : (models) => {
    User.hasMany(models.Code,{
        foreignKey: 'createdBy'
    });

    User.belongsTo(models.Role,{
        foreignKey: 'role'
    });
};

module.exports = User;

以下是我的 code.js 文件 -

const Sequelize = require('sequelize');
const db = require('./db');
const User = require('./user');
//one-to-many
const Code = db.define('code', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    allowNull:false,
    autoIncrement: true
  },
  name: {
    type: Sequelize.STRING
  },
  // createdBy: {
  //   type: Sequelize.INTEGER
  // },
  createdBy: {
    type: Sequelize.INTEGER,
    references: {
      // This is a reference to another model
      model: User,

      // This is the column name of the referenced model
      key: 'id'
    }
  }
}, {
  tableName: 'code',
  freezeTableName: true,
  timestamps: false
});

associate : (models) => {
  Code.belongsTo(models.User,{
      foreignKey: 'createdBy'
  });
};

module.exports = Code;

当我运行 app.js 文件时，我看不到 User 的模型引用，
但我得到的是通常的整数值，有人可以帮助我如何正确使用这里的模型吗？

错误跟踪 -

Answer 1

看起来您正在尝试在尚未建立连接时获取代码。

试试这个:

const db = require('./db');
const Code = require('./code');
const User = require('./user');
const Role = require('./role');

function run() {
   return db
       .authenticate()
       .then(() => Code.findOne({ // you can try execute whenever query you want here 
          where: {
            id: 1
          }})
          .then(code => {
            console.log("All users:", JSON.stringify(code, null, 4));
          })
          .catch(err => console.log("Error => \n",err)))
        .catch(err => {
          console.error('Unable to connect to the database:', err);
        });
} 

run();