在Sequelize中使用迁移

Sequelize是Nodejs生态中一个比较出名的ORM框架。通过ORM框架，可以使用对象来操作数据库表数据，提高了开发效率和代码可读性，也方便后期维护。

今天主要介绍通过 迁移[Migration] 来创建数据库，表。

迁移的好处，可以类比git。通过每次创建迁移文件，来支持更新，回滚数据库表结构，也方便协同开发，也避免人工手动去直接修改数据库，用代码自动管理。换个电脑，也不用去拷贝数据库，直接运行迁移就可以完全恢复开发环境，极大减轻了心智负担。

1. 创建项目, 安装node package依赖

mkdir node_work

cd node_work

mkdir app

npm init -y

npm i sequelize-cli sequelize mysql2 koa

2. 初始化Sequelize

npx sequelize init

运行之后，会产生四个目录：

config, migrations, models, seeders

config:

{
  "development": {
    "username": "root",
    "password": "root",
    "database": "app_development",
    "host": "127.0.0.1",
    "port": 8889,
    "dialect": "mysql",
    "timezone": "+08:00"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "app_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "app_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

环境env => {配置}

不同环境，对应不同的配置，也可以自定义环境，比如home

env指的是 process.env.NODE_ENV ，

可以通过设置环境变量来改变，比如 export NODE_ENV=production ；

迁移时候，也可以指定环境： npx sequelize db:migrate --env production ，来连接production对应配置的数据库

创建数据库：

npx sequelize db:create

说明 npx 是npm5.2之后，自带的一个命令。可以不用全局安装sequelize，使用时候，如果本地没有，就去npm仓库下载；下载完后或者本地已经下载过，就运行脚本命令。这样可以避免本地全局包过期，环境问题，每次都使用最新版本

migrations: 迁移文件

npx sequelize model:generate --name User --attributes username:string

执行后，会生成 20180918055558-create-user.js 迁移文件，和 models/user.js 模型文件

其他字段可以在迁移文件中补全，最后再运行 npx sequelize db:migrate ，就可以在数据库中看到生成了users表

'use strict';
  module.exports = {
    up: (queryInterface, Sequelize) => {
      return queryInterface.createTable('Users', {
        id: {
          allowNull: false,
          autoIncrement: true,
          primaryKey: true,
          type: Sequelize.INTEGER
        },
        username: {
          type: Sequelize.STRING(20),
          allowNull: false
        },
        password: {
          type: Sequelize.CHAR(32),
          allowNull: false
        },
        createdAt: {
          allowNull: false,
          type: Sequelize.DATE
        },
        updatedAt: {
          allowNull: false,
          type: Sequelize.DATE
        }
      }, {
        tableName: 'users',
        charset: 'utf8mb4',
        collate: 'utf8mb4_bin',
        define: {
          timestamps: true
        }
      }).then(() => {
        // 添加索引
        return queryInterface.addIndex('users', {
          name: 'username',
          unique: true,
          fields: ['username']
        });
      });
    },
    
    // 回退时执行，删除表
    down: (queryInterface, Sequelize) => {
      return queryInterface.dropTable('Users');
    }
  };

执行迁移：

npx sequelize db:migrate

npx sequelize db:migrate:all

撤销迁移：

npx sequelize db:migrate:undo 最近一次的

npx sequelize db:migrate:undo:all

npx sequelize db:migrate:undo:all --to XXXXXXXXXXXXXX-create-posts.js

--from, --to 参数，可以指定迁移文件

models: 模型文件

model:generate 生成的model都在这个目录中

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      username: {
        type: Sequelize.STRING(20),
        allowNull: false
      },
      password: {
        type: Sequelize.CHAR(32),
        allowNull: false
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    }, 
    {
      tableName: 'users',
      charset: 'utf8mb4',
      collate: 'utf8mb4_bin',
    }).then(() => {
      return queryInterface.addIndex('users', {
        name: 'username',
        unique: true,
        fields: ['username']
      });
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Users');
  }
};

模型对象创建，默认会自动赋值，更新createdAt, updatedAt两个timestamps字段。下边会给出完整示例。

seeders: 填充数据文件

创建seed文件：

npx sequelize seed:generate --name demo-user

执行之后，会得到 20180918090545-demo-user.js

'use strict';

const md5 = require('md5')

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', [
      {
        username: 'Kimoo',
        password: md5('123456'),
        createdAt: new Date(),
        updatedAt: new Date(),
      },
      {
        username: 'Reci',
        password: md5('123321'),
        createdAt: new Date(),
        updatedAt: new Date(),
      }
    ], {});
  },

  down: (queryInterface, Sequelize) => {
    /*
      Add reverting commands here.
      Return a promise to correctly handle asynchronicity.

      Example:
      return queryInterface.bulkDelete('Person', null, {});
    */
    return queryInterface.bulkDelete('Users', null, {});
  }
};

填充数据：

npx sequelize db:seed:all

撤销数据：

npx sequelize db:seed:undo 最近一次的

npx sequelize db:seed:undo --seed name-of-seed-as-in-data 具体某个

npx sequelize db:seed:undo:all

3. 具体实践

app.js

(async function() {

    const Koa = require('koa');
    const KoaStaticCache = require('koa-static-cache');
    const KoaBodyParser = require('koa-bodyparser');
    const router = require('./routers/main');
    const Session = require('koa-session');

    const app = new Koa();

    // app.keys = new KeyGrip(['im a newer secret', 'i like turtle'], 'sha256');

    app.keys = ['app'];

    app.use( Session({
        key: 'koa:sess',
        maxAge: 86400000,
        autoCommit: true,
        overwrite: true,
        httpOnly: true,
        signed: true,
        rolling: false,
        renew: false
    }, app) );

    // app.use( async (ctx, next) => {
    //     ctx.set('Access-Control-Allow-Origin','*');
    //     await next();
    // } );

    app.use( KoaStaticCache('./public', {
        prefix: 'public',
        gzip: true
    }) );

    app.use( KoaBodyParser() );

    app.use( router.routes() );

    app.listen(8088);

})();

models/index.js

'use strict';

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../config/config.json')[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

// 自动导入 models 文件夹下的所有文件，比如user.js这个模型文件

// 自动加载模型并执行
// let users = require('./users');
// let UsersModel = users(sequelize, Sequelize);
// db[UsersModel.name] = UsersModel; // db['Users'] = UsersModel;


// 下面通过fs自动加载所有的文件，并执行，同时生成的模型对象挂载到db对象下面，最后返回出去
fs
  .readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(file => {
    const model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

routers/main.js

const KoaRouter = require('koa-router');
const md5 = require('md5');
const Models = require('../models');
const Sequelize = require('sequelize');

const router = new KoaRouter();

router.post('/register', async ctx => {

    // console.log(ctx.request.body);
    let username = ctx.request.body.username.trim();
    let password = ctx.request.body.password.trim();
    let repassword = ctx.request.body.repassword.trim();

    if (username=='' || password == '' || repassword == '') {
        return ctx.body = {
            code: 1,
            data: '用户名或密码不能为空'
        }
    }
    if (password != repassword) {
        return ctx.body = {
            code: 2,
            data: '两次输入的密码不一致'
        }
    }

    let user = await Models.Users.findOne({
        where: {
            username
        }
    });

    if (user !== null) {
        return ctx.body = {
            code: 3,
            data: '当前用户已经被注册了'
        }
    }

    let newUser = await Models.Users.build({
        username,
        password: md5(password)
    }).save();

    ctx.body = {
        code: 0,
        data: {
            id: newUser.get('id'),
            username: newUser.get('username')
        }
    }

  });

  router.post('/login', async ctx => {
      let username = ctx.request.body.username;
      let password = ctx.request.body.password;

      let user = await Models.Users.findOne({
          where: {
              username
          }
      });

      if (user === null) {
          return ctx.body = {
              code: 1,
              data: '不存在该用户'
          }
      }

      if (user.get('password') !== md5(password)) {
          return ctx.body = {
              code: 1,
              data: '密码错误'
          }
      }

      
      // ctx.cookies.set('uid', user.get('id'), {
      //     httpOnly: false
      // });

      // 服务端发送一个约定好的cookie，来表示当前是登录
      // ctx.cookies.set('uid', user.get('id'), {
      //     // httpOnly，表示当前的cookie是否允许客户端进行操作（js），如果为true，那么就表示这个cookie是能用户http协议的数据传输
      //     httpOnly: true,
      //     signed: true
      // });
      ctx.cookies.set('username', user.get('username'), {
          httpOnly: false
      });

      ctx.session.uid = 1;

      ctx.body = {
          code: 0,
          data: {
              id: user.get('id'),
              username: user.get('username')
          }
      }
  });

})

module.exports = router;

4. 测试接口，注册用户，添加数据

可以在 postman 中测试接口，地址 http://localhost:8088/register ，注册用户

node app.js