node.js 使用Sequelize mysql ORM 来搞RESTful API

1.安装sequelize

npm install sequelize --save

2. 先看mysql 数据库表结构

 

3. node.js 目录结构

4. config.json

{
  "development": {
    "username": "root",
    "password": "root",
    "database": "baidusong",
    "host": "127.0.0.1",
    "port": 3306,
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": "root",
    "database": "baidusong",
    "host": "127.0.0.1",
    "port": 3306,
    "dialect": "mysql"
  }
}

5. models/index.js

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((file) =>
        (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;

6. models/gedan.js

module.exports = (sequelize, DataTypes) => {
    const Gedan = sequelize.define('gedan', {
        id: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true,
            primaryKey: true
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false,
        },
        author: {
            type: DataTypes.STRING,
            allowNull: false,
        },
        playCount: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        favCount: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        shareCount: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        // indexes: [{unique: true, fields: ['id']}],
    },{
        timestamps:false,
        freezeTableName:true
    });
    return Gedan;
};

7.constrollers/index.js

const gedan = require('./gedan');
// const gedan_detail = require('./gedan_detail');

module.exports = {
    gedan,
    // gedan_detail,
};

8. controllers/gedan.js

const Gedan = require('../models/index').gedan;

module.exports = {
    create(req, res) {
        return Gedan
            .create({
                id:req.body.id,
                name: req.body.name,
                author: req.body.author,
                playCount: req.body.playCount,
                favCount: req.body.favCount,
                shareCount: req.body.shareCount,
            })
            .then((gedan) => res.status(201).send(gedan))
            .catch((error) => res.status(400).send(error));
    },

    list(req, res) {
        return Gedan
            .findAll({
                order: [
                    ['id', 'DESC'],
                    // [{ model: TodoItem, as: 'todoItems' }, 'createdAt', 'ASC'],
                ],
            })
            .then((gedans) => res.status(200).send(gedans))
            .catch((error) => res.status(400).send(error));
    },

    retrieve(req, res) {
        return Gedan
            .findById(req.params.id, {

            })
            .then((gedan) => {
                if (!gedan) {
                    return res.status(404).send({
                        message: 'Todo Not Found',
                    });
                }
                return res.status(200).send(gedan);
            })
            .catch((error) => res.status(400).send(error));
    },

    update(req, res) {
        return Gedan
            .findById(req.params.id, {
            })
            .then(gedan => {
                if (!gedan) {
                    return res.status(404).send({
                        message: 'Gedan Not Found',
                    });
                }
                return gedan
                    .update({
                        name: req.body.name || gedan.name,
                        author: req.body.author || gedan.author,
                        playCount: req.body.playCount || gedan.playCount,
                        favCount: req.body.favCount || gedan.favCount,
                        shareCount: req.body.shareCount || gedan.shareCount,
                    })
                    .then(() => res.status(200).send(gedan))
                    .catch((error) => res.status(400).send(error));
            })
            .catch((error) => res.status(400).send(error));
    },

    destroy(req, res) {
        return Gedan
            .findById(req.params.id)
            .then(gedan => {
                if (!gedan) {
                    return res.status(400).send({
                        message: 'gedan Not Found',
                    });
                }
                return gedan
                    .destroy()
                    .then(() => res.status(204).send())
                    .catch((error) => res.status(400).send(error));
            })
            .catch((error) => res.status(400).send(error));
    },
};

9. routes/gedan.js

var express = require('express');
var router = express.Router();


const gedanController = require('../controllers').gedan;


/* GET home page. */
router.get('/', function(req, res, next) {
    gedanController.list(req,res)
});

router.get('/:id', function(req, res, next) {
    gedanController.retrieve(req,res)
});

router.post('/', function(req, res, next) {
    gedanController.create(req,res)
});

router.delete('/:id', function(req, res, next) {
    gedanController.destroy(req,res)
});


router.put('/:id', function(req, res, next) {
    gedanController.update(req,res)
});

module.exports = router;