本文介绍如何利用Node.js与Sequelize库实现ORM（对象关系映射），并自动将实体模型同步到MySQL数据库，包括创建表及索引。
nodejs如果采用mysql数据库作为对象持久存储，可以使用sequelize这个库来做orm，同时，这个库可以将模型同步到数据库，无需我们手动创建表，这点可以和hibernate媲美，hibernate通过hibernate.hbm2ddl.auto=update属性，可以让项目在启动时对实体做的新增和修改直接同步到数据库中，同理sequelize让nodejs可以方便的来创建实体到表的映射，而且可以创建索引，这点比hibernate似乎更让人振奋。
这里简单罗列一些主要的文件，然后通过sequelize同步实体模型到数据库。做实体同步映射只需要这三个文件夹bin,models,config,其中bin目录用来存放可执行程序,models存放实体，config存放数据库配置。文件结构如下所示：
bin
migrate.js //模型同步到数据库脚本
config
db.json //数据库配置
models
employee.js //定义实体employee
department.js //定义实体department
index.js //模型层对外接口
package.json //项目配置文件(指定依赖库)
其中employee.js文件内容如下：
'use strict';
const T=require('sequelize');
const employee = {
id:{type:T.INTEGER.UNSIGNED,primaryKey:true,autoIncrement:true},
name:{type:T.STRING(20),allowNull:false},
age:{type:T.INTEGER,allowNull:false,defaultValue:1},
email:{type:T.STRING(20),allowNull:false},
depart_id:{type:T.INTEGER.UNSIGNED,allowNull:false}
};
module.exports = function(sequelize){
return sequelize.define('employee',employee,{
'timestamps': false,
'indexes':[{name:'idx_employee',fields:['name'],uniq:true}]
});
};
比hibernate更有意思的地方是还可以创建索引,但是这是node库，自然无法和hibernate比，这里只是稍微提一下。
department.js文件内容如下：
"use strict";
const T = require('sequelize');
const department = {
id:{type:T.INTEGER.UNSIGNED,primaryKey:true,autoIncrement:true},
name:{type:T.STRING,allowNull:false,defaultValue:''}
};
module.exports = function(sequelize){
return sequelize.define('department',department,{
'timestamps':false
});
}
index.js文件内容如下：
'use strict';
const _ = require('lodash');
const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const config = require('../config/db');
if(process.env.NODE_ENV == 'production') config['logging'] = false;
var sequelize = new Sequelize(config.database, config.username, config.password, config);
var db = {};
fs.readdirSync(__dirname)
.filter((file) => {
return (file.indexOf('.') !== 0) && (file !== 'index.js');
})
.forEach((file) => {
var model = sequelize.import(path.join(__dirname, file));
db[_.upperFirst(model.name)] = model;
});
db.sequelize = sequelize;
module.exports = db; 这里会对models目录下除index.js文件之外的文件都进行同步，并且同步之后，将模型名称首字母大写作为db对外暴露的接口的一个对象。
db.json文件内容如下：
{
"host": "127.0.0.1",
"dialect": "mysql",
"port": 3306,
"username": "game",
"password": "x0k6f78",
"database": "lenovodb",
"logging": false,
"dialectOptions": {
"multipleStatements": true
},
"pool": {
"max": 10,
"min": 0,
"idle": 10000
},
"define": {
"underscored": true,
"freezeTableName": true,
"charset": "utf8",
"collate": "utf8_general_ci",
"timestamps": false
}
} db配置我们还配置了连接池。
migrate.js文件内容如下：
#!/usr/bin/env node
'use strict';
const models = require('../models');
models.sequelize.sync().done(() => {
console.log('sync db done and waiting for 1 minitue to exit,Or CTRL+C to exit.');
},(err) => {
console.log(err);
});
package.json文件内容如下：
{
"name": "index",
"version": "0.0.1",
"private": true,
"scripts": {
"start": "node ./bin/www"
},
"dependencies": {
"asyncawait": "^1.0.6",
"bluebird": "^3.4.1",
"body-parser": "~1.13.2",
"express": "~4.13.1",
"lodash": "~4.13.1",
"moment": "~2.13.0",
"mysql": "^2.11.1",
"sequelize": "^3.23.4",
"redis": "^2.6.2",
"winston": "~2.2.0",
"chance": "^1.0.4",
"serve-favicon":"^2.3.0"
},
"devDependencies": {
"gulp": "^3.9.1",
"gulp-clean-css": "^2.0.13",
"gulp-htmlmin": "^3.0.0",
"gulp-imagemin": "^3.0.3",
"gulp-uglify": "^2.0.0",
"gulp-useref": "^3.1.2",
"gulp-if": "^2.0.1",
"gulp-clean": "^0.3.2"
}
}同步数据用到的库mysql,sequelize。
以上文件准备好了之后就可以运行以下命令来进行同步：
$ cd path/to/project
$ node bin/migrate.js
$ node bin/migrate
sync db done and waiting for 1 minitue to exit,Or CTRL+C to
exit.
最后我们可以查看数据库中的表结构进行验证我们的同步：
mysql> use lenovodb
Database changed
mysql> show tables;
+--------------------+
| Tables_in_lenovodb |
+--------------------+
| department |
| employee |
| student |
+--------------------+
3 rows in set (0.00 sec)
mysql> desc employee;
+-----------+------------------+------+-----+---------+---------------
-+
| Field | Type | Null | Key | Default | Extra
|
+-----------+------------------+------+-----+---------+---------------
-+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment
|
| name | varchar(20) | NO | MUL | NULL |
|
| age | int(11) | NO | | 1 |
|
| email | varchar(20) | NO | | NULL |
|
| depart_id | int(10) unsigned | NO | | NULL |
|
+-----------+------------------+------+-----+---------+---------------
-+
5 rows in set (0.00 sec)
mysql> desc department;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql>查看在employee表上创建的索引:
mysql> show index from employee;
+----------+------------+--------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+-----
----+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comm
ent | Index_comment |
+----------+------------+--------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+-----
----+---------------+
| employee | 0 | PRIMARY | 1 | id |
A | 0 | NULL | NULL | | BTREE |
| |
| employee | 1 | idx_employee | 1 | name |
A | 0 | NULL | NULL | | BTREE |
| |
+----------+------------+--------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+-----
----+---------------+
2 rows in set (0.00 sec)
mysql>
扫一扫
2230
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
