nodeJS接口开发(3): 连接mysql

一、写在前面

之前我们通过路由展示静态的数据模型，在实际使用过程当中，需要数据库的配合，调用以及存储数据库中的内容。接下来介绍在node中如何连接关系型数据库。

二、实现过程

1、安装相关包

	//安装sequelize和mysql包装器
	npm install sequelize mysql2 --save

2、连接数据库

mysql数据库需要提前安装

新建lib/config.js，存储数据库连接配置

```javascript
module.exports = {
  database: "test",
  username: "root",
  password: "123456",
  params: {
    host: "localhost",
    dialect: "mysql"
  }
};
```

根目录创建db.js

```javascript
var Sequelize = require("sequelize");
const config = require("./libs/config.js");
let sequelize = null;

module.exports = () => {
  if (!sequelize) {
    sequelize = new Sequelize(
      config.database,
      config.username,
      config.password,
      config.params
    );
  }
  return sequelize;
};
```

编写测试文件，调整libs/boot.js文件

```javacript
module.exports = app => {
  app.db
    .authenticate()
    .then(() => {
      console.log("Connection has been established successfully.");
      app.listen(app.get("port"), () => {
        console.log(`监视端口 ${app.get("port")}。。。`);
      });
    })
    .catch(err => {
      console.error("Unable to connect to the database:", err);
    });
};
```

调整入口文件index.js

```javascript
var express = require("express");
	var consign = require("consign");
	const app = express();
	consign()
	  // 引入db.js文件
	  .include("db.js")
	  .include("models")
	  .then("libs/middlewares.js")
	  .then("routes")
	  .then("libs/boot.js")
	  .into(app);
```

运行程序，提示数据库连接成功

3、查询数据库

创建数据库表格

create table if not exists task(
	id int unsigned auto_increment,
	title varchar(100) not null,
	done smallint not null,
	primary key(id)
);

create table if not exists user(
	id int unsigned auto_increment,
	name varchar(40) not null,
	password varchar(40) not null,
	email varchar(40) not null,
	primary key(id)
);

我创建了user表格和task表格.

创建与数据库表格对应的模型

model/tasks.js

module.exports = (sequelize, DataType) => {
  const task = sequelize.define(
    // 定义模型名称
    "task",
    // 定义模型字段
    {
      id: {
        type: DataType.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      title: {
        type: DataType.STRING,
        allowNull: false,
        validate: {
          notEmpty: true
        }
      },
      done: {
        type: DataType.INTEGER,
        allowNull: false,
        defaultValue: 0
      }
    },
    // 模型其他属性
    {
      freezeTableName:true
    }
  );
  return task;
};

model/user.js

module.exports = (sequelize, DataType) => {
  const user = sequelize.define(
    "user",
    {
      id: {
        type: DataType.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: DataType.STRING,
        allowNull: false,
        validate: {
          notEmpty: true
        }
      },
      password: {
        type: DataType.STRING,
        allowNull: false,
        validate: {
          notEmpty: true
        }
      },
      email: {
        type: DataType.STRING,
        unique: true,
        allowNull: false,
        validate: {
          notEmpty: true
        }
      }
    },{
      freezeTableName:true
    }
  );
  return user;
};

调整db.js文件，引入model

var Sequelize = require("sequelize");
var fs = require("fs");
var path = require("path");

let db = null;

module.exports = app => {
  if (!db) {
  	// 这里的config从入口文件index.js引入，参下。
    const config = app.libs.config;
    const sequelize = new Sequelize(
      config.database,
      config.username,
      config.password,
      config.params
    );
    db = {
      // 实例化的sequelize
      sequelize,
      // Sequelize类
      Sequelize,
      // 存储的模型
      models: {}
    };
    // 获取根目录的models文件夹路径
    const dir = path.join(__dirname, "models");
    // 读取models文件，存储在db中
    fs.readdirSync(dir).forEach(file => {
      const modelDir = path.join(dir, file);
      const model = sequelize.import(modelDir);
      db.models[model.name] = model;
    });
  }
  return db;
};

调整routes/tasks.js文件

module.exports = app => {
  const Tasks = app.db.models.task;
  app.get("/tasks", (req, res) => {
    // findAll为模型方法，这里不传参数
    Tasks.findAll({}).then(tasks => {
      res.json({ tasks: tasks });
    });
  });
};

调整入口文件index.js

var express = require("express");
var consign = require("consign");
const app = express();
consign()
  .include("./libs/config.js")
  .include("db.js")
  .then("libs/middlewares.js")
  .then("routes")
  .then("libs/boot.js")
  .into(app);

运行程序，接口调用，显示数据库数据

三、结语

• sequelize支持的关系型数据库以及模型属性，请参考sequelize doc