创建基于 Sequelize ORM 和 Sqlite 的 Node Express 项目

  约 2000 字 预计阅读 4 分钟 - 次阅读  
目录

如何使用 Sequelize ORM 来创建一个 Express 的项目

设置项目 

1
2
3
4

mkdir project-dir
cd project-dir
mkdir bin server
npm init -y

Express 

1
2

yarn add express body-parser morgan
touch app.js

app.js 文件内容

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

const express = require('express')
const logger = require('morgan')
const bodyParser = require('body-parser')

const app = express()

app.use(logger('dev'))
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({extended: false}))

app.get('*', (req, res) => {
  res.status(200).send({
    message: 'Welcome to the beginning of nothingness.'
  })
})

module.exports = app

在 bin 目录中创建 www 文件

1
2
3
4
5
6
7
8

const http = require('http')
const app = require('../app')

const port = parseInt(process.env.PORT, 10) || 8000
app.set('port', port)

const server = http.createServer(app)
server.listen(port)

为了避免每次修改代码都需要重新启动服务，我们使用 nodemon。

1

yarn add nodemon --dev

修改 package.json 创建启动服务器的命令。

1
2
3
4
5
6

...
"scripts": {
  "start:dev": "nodemon ./bin/www",
  "test": "echo \"Error: no test specified\" && exit 1"
},
...

尝试启动服务器

1

yarn run start:dev

打开浏览器可以看见结果

Sequelize 设置

安装 Sequelize CLI 工具

1

npm install -g sequelize-cli

创建 .sequelizerc 文件描述路径。

1
2
3
4
5
6
7
8

const path = require('path')

module.exports = {
  "config": path.resolve('./server/config', 'config.json'),
  "models-path": path.resolve('./server/models'),
  "seeders-path": path.resolve('./server/seeders'),
  "migrations-path": path.resolve('./server/migrations')
}

安装 sequelize sqlite

1

yarn add sequelize sqlite3

初始化 sequelize

1

sequelize init

修改 server/models/index.js 从 es5 修改为 es2015

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

'use strict';

const fs        = require('fs');
const path      = require('path');
const Sequelize = require('sequelize');
const basename  = path.basename(module.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]);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs
  .readdirSync(__dirname)
  .filter(function(file) {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(function(file) {
    var model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

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

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

module.exports = db;

创建 sqlite 数据库

1

touch todos-dev.db

修改 server/config/config.json

生成模型

我们有两个模型 Todo 和 TodoItem，是一对多的关系。让我们生成 Todo 模型。

1

sequelize model:create --name Todo --attributes title:string

生成了 server/models/todo.js 和 server/migrations/<date>-create-todo.js 两个文件，一个是模型文件，一个是数据库迁移文件。

生成 TodoItem 模型。

1

sequelize model:create --name TodoItem --attributes content:string,complete:boolean

修改 todo.js，添加关系并更改为 es2015

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

module.exports = (sequelize, DataTypes) => {
  const Todo = sequelize.define('Todo', {
    title: {
      type: DataTypes.STRING,
      allowNull: false,
    }
  }, {
    classMethods: {
      associate: (models) => {
        // associations can be defined here
        Todo.hasMany(models.TodoItem, {
          foreignKey: 'todoId',
          as: 'todoItems',
        })
      }
    }
  });
  return Todo;
};

修改 todoitem.js，添加关系并更改为 es2015

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

module.exports = (sequelize, DataTypes) => {
  const TodoItem = sequelize.define('TodoItem', {
    content: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    complete: {
      type: DataTypes.BOOLEAN,
      defaultValue: false
    }
  }, {
    classMethods: {
      associate: (models) => {
        // associations can be defined here
        TodoItem.belongsTo(models.Todo, {
          foreignKey: 'todoId',
          onDelete: 'CASCADE'
        })
      }
    }
  });
  return TodoItem;
};

为了保持一致性，我们修改 <date>-create-todo.js 迁移文件并更改为 es2015

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Todos', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      title: {
        type: Sequelize.STRING,
        allowNull: false
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Todos');
  }
};

同样处理 <date>-create-todo-item.js

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('TodoItems', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      content: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      complete: {
        type: Sequelize.BOOLEAN,
        defaultValue: false,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      todoId: {
        type: Sequelize.INTEGER,
        onDelete: 'CASCADE',
        refrences: {
          model: 'Todos',
          key: 'id',
          as: 'todoId'
        }
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('TodoItems');
  }
};

现在我们可以进行数据库迁移了

1

sequelize db:migrate

创建控制器和路由

创建文件 server/controllers/todos.js

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

const Todo = require('../models').Todo

module.exports = {
  create(req, res) {
    return Todo.create({
      title: req.body.title,
    })
    .then(todo => res.status(201).send(todo))
    .catch(error => res.status(400).send(error))
  }
}

创建 server/controllers/index.js，暴露我们所有的控制器

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

const Todo = require('../models').Todo

module.exports = {
  create(req, res) {
    return Todo.create({
      title: req.body.title,
    })
    .then(todo => res.status(201).send(todo))
    .catch(error => res.status(400).send(error))
  }
}

创建 server/controllers/index.js，暴露我们所有的控制器

1
2
3
4
5

const todos = require('./todos')

module.exports = {
  todos,
}

创建 server/routes/index.js 控制路由

1
2
3
4
5
6
7
8

const todosController = require('../controllers').todos

module.exports = (app) =>  {
  app.get('/api', (req, res) => res.status(200).send({
    message: 'Welcome to the Todos API'
  }))
  app.post('/api/todos', todosController.create)
}

修改 app.js ，添加路由

1
2
3
4
5
6
7

...
app.use(bodyParser.urlencoded({extended: false}))

require('./server/routes/')(app)

app.get('*', (req, res) => {
...

启动 postman 来进行测试 /2017/04/express-sequelize/node-sequelize-sqlite-53958.png

查询全部待办事项

修改 server/controllers/todos.js

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

const Todo = require('../models').Todo

module.exports = {
  create(req, res) {
    ...
  },

  list(req, res) {
    return Todo
      .all()
      .then(todos => res.status(200).send(todos))
      .catch(error => res.status(400).send(error))
  }
}

修改路由文件 server/routes/index.js

1
2
3
4
5
6
7

const todosController = require('../controllers').todos

module.exports = (app) =>  {
  ...
  app.post('/api/todos', todosController.create)
  app.get('/api/todos', todosController.list)
}

使用 postman 测试新路由 /2017/04/express-sequelize/node-sequelize-sqlite-bc8c9.png

创建 Todo Items

  • server/controllers/todoitems.js
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

const TodoItem = require('../models').TodoItem

module.exports = {
  create(req, res) {
    return TodoItem
      .create({
        content: req.body.content,
        todoId: req.params.todoId,
      })
      .then(todoItem => res.status(201).send(todoItem))
      .catch(error => res.status(400).send(error))
  },
}
  • server/controllers/index.js
1
2
3
4
5
6
7

const todos = require('./todos')
const todoItems = require('./todoitems')

module.exports = {
  todos,
  todoItems,
}
  • server/routes/index.js
1
2
3
4
5
6
7
8

const todosController = require('../controllers').todos
const todoItemsController = require('../controllers').todoItems

module.exports = (app) =>  {
  ...
  app.get('/api/todos', todosController.list)
  app.post('/api/todos/:todoId/items', todoItemsController.create)
}

  • postman 测试 /2017/04/express-sequelize/node-sequelize-sqlite-6d615.png

  • 查询某个 todo 中的所有 todo-items server/controllers/todos.js

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

const Todo = require('../models').Todo
const TodoItem = require('../models').TodoItem

module.exports = {
  create(req, res) {
    ...
  },

  list(req, res) {
    return Todo
      .findAll({
        include: [{
          model: TodoItem,
          as: 'todoItems'
        }]
      })
      .then(todos => res.status(200).send(todos))
      .catch(error => res.status(400).send(error))
  },
}

/2017/04/express-sequelize/node-sequelize-sqlite-4d4d2.png

获取指定 todo

  • server/controllers/todos.js
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

const Todo = require('../models').Todo
const TodoItem = require('../models').TodoItem

module.exports = {
  create(req, res) {
    ...
  },

  list(req, res) {
    ...
  },

  retrieve(req, res) {
    return Todo
      .findById(req.params.todoId, {
        include: [{
          model: TodoItem,
          as: 'todoItems'
        }]
      })
      .then(todo => {
        if (!todo) {
          return res.status(400).send({
            message: 'Todo Not Found'
          })
        }
        return res.status(200).send(todo)
      })
      .catch(error => res.status(400).send(error))
  },
}
  • 修改路由 server/routes/index.js
1
2
3
4
5
6
7

const todosController = require('../controllers').todos
const todoItemsController = require('../controllers').todoItems

module.exports = (app) =>  {
  ...
  app.get('/api/todos/:todoId', todosController.retrieve)
  ...

/2017/04/express-sequelize/node-sequelize-sqlite-5084d.png

更新一个 todo

  • server/controllers/todos.js
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

update(req, res) {
  return Todo
    .findById(req.params.todoId, {
      include: [{
        model: TodoItem,
        as: 'todoItems'
      }]
    })
    .then(todo => {
      if (!todo) {
        return res.status(400).send({
          message: 'Todo Not Found'
        })
      }
      return todo
        .update({
          title: req.body.title || todo.title,
        })
        .then(() => res.status(200).send(todo))
        .catch((error) => res.status(400).send(error))
    })
},
  • server/routes/index.js
1
2
3

...
app.put('/api/todos/:todoId', todosController.update)
...

/2017/04/express-sequelize/node-sequelize-sqlite-6835a.png

删除 todo

  • server/controllers/todos.js
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

destroy(req, res) {
  return Todo
    .findById(req.params.todoId)
    .then(todo => {
      if (!todo) {
        return res.status(400).send({
          message: 'Todo Not Found'
        })
      }

      return todo
        .destroy()
        .then(() => res.status(204).send())
        .catch(error => res.status(400).send(error))
    })
    .catch(error => res.status(400).send(error))
}
  • server/routes/index.js
1
2
3

...
app.delete('/api/todos/:todoId', todosController.destroy)
...

/2017/04/express-sequelize/node-sequelize-sqlite-9d858.png

更新和删除 todo items

  • server/controllers/todoitems.js
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

const TodoItem = require('../models').TodoItem

module.exports = {
  create(req, res) {
    return TodoItem
      .create({
        content: req.body.content,
        todoId: req.params.todoId,
      })
      .then(todoItem => res.status(201).send(todoItem))
      .catch(error => res.status(400).send(error))
  },

  update(req, res) {
    return TodoItem
      .find({
        where: {
          id: req.params.todoItemId,
        },
      })
      .then(todoItem => {
        if (!todoItem) {
          return res.status(400).send({message: 'TodoItem Not Found'})
        }

        return todoItem
          .update({
            content: req.body.content || todoItem.content,
            complete: req.body.complete || todoItem.complete
          })
          .then(updatedTodoItem => res.status(200).send(updatedTodoItem))
          .catch(error => res.status(400).send(error))
      })
      .catch(error => res.status(400).send(error));
  },

  destroy(req, res) {
    return TodoItem
      .find({
        where: {
          id: req.params.todoItemId,
        },
      })
      .then(todoItem => {
        if (!todoItem) {
          return res.status(400).send({message: 'TodoItem Not Found'})
        }
        return todoItem
          .destroy()
          .then(() => res.status(204).send())
          .catch(error => res.status(400).send(error))
      })
      .catch(error => res.status(400).send(error))
  }
}
  • server/routes/index.js
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

const todosController = require('../controllers').todos
const todoItemsController = require('../controllers').todoItems

module.exports = (app) =>  {
  app.get('/api', (req, res) => res.status(200).send({
    message: 'Welcome to the Todos API'
  }))
  app.post('/api/todos', todosController.create)
  app.get('/api/todos', todosController.list)
  app.get('/api/todos/:todoId', todosController.retrieve)
  app.put('/api/todos/:todoId', todosController.update)
  app.delete('/api/todos/:todoId', todosController.destroy)

  app.post('/api/todos/:todoId/items', todoItemsController.create)
  app.put('/api/todos/:todoId/items/:todoItemId', todoItemsController.update)
  app.delete('/api/todos/:todoId/items/:todoItemId', todoItemsController.destroy)

  app.all('/api/todos/:todoId/items', (req, res) => {
    res.status(405).send({
      message: 'Method Not Allowed'
    })
  })
}
更新于 2026-10-23 
CC BY-NC-SA 4.0
阅读原始文档
返回 | 主页
切换 选择 ESC 关闭
搜索引擎 Fuse.js