koa 连接sqlite3 项目目录结构

版本说明

目录结构

安装koa

npm install -g koa-cli

更新koa

npm update -g koa-cli

创建项目，使用koa2创建，否则是koa1项目

koa2 my-koa-project

config

连接数据库

const { Sequelize } = require('sequelize')

const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: './test.db'
})

// try {
//     sequelize.authenticate()
//     console.log('连接正常')
// } catch (err) {
//     console.log('连接失败', err)
// }

module.exports = { sequelize }

schema

定义模型的字段

const { DataTypes } = require('sequelize')

module.exports = function(sequelize){
    return sequelize.define('student', {
        name: {
            type: DataTypes.STRING,
            allowNull: false,
            field: 'name'
        },
        age: {
            type: DataTypes.INTEGER,
            allowNull: false,
            field: 'age'
        },
        gender: {
            type: DataTypes.STRING,
            allowNull: false,
            field: 'gender'
        },
        grade: {
            type: DataTypes.STRING,
            allowNull: false,
            field: 'grade'
        }
    })
}

modules

根据schema生成数据表 定义对数据库的操作 供controller调用

const db = require('../config/db')
const { DataTypes } = require('sequelize')

const sequelize = db.sequelize

// const student = sequelize.import('../schema/student')(sequelize, DataTypes)
const student = require('../schema/student')(sequelize)
student.sync({force: false})

class studentModel {
    static async createStudent(data){
        return await student.create({
            name: data.name,
            age: data.age,
            gender: data.gender,
            grade: data.grade
        })
    }

    static async getStudentDetail(id){
        return await student.findOne({
            where: {
                id
            }
        })
    }
}

module.exports = studentModel

controller

用modules层提供的方法实现业务逻辑

const StudentModel = require('../modules/student')

class studentController {
    static async create(ctx) {
        let req = ctx.request.body
        if(req.name && req.age && req.gender && req.grade){
            try{
                const ret = await StudentModel.createStudent(req)
                const data = await StudentModel.getStudentDetail(ret.id)

                ctx.response.status = 200
                ctx.body = {
                    code: 200,
                    msg: '创建学生信息成功',
                    data
                }
            } catch(err) {
                ctx.response.status = 412
                ctx.body = {
                    code: '412',
                    msg: '创建学生信息失败',
                    data: err
                }
            }
        } else {
            ctx.response.status = 416
            ctx.body = {
                code: 200,
                msg: '参数不全'
            }
        }
    }

    static async detail(ctx){
        let id = ctx.params.id
        if(id){
            try{
                let data = await StudentModel.getStudentDetail(id)
                ctx.response.status = 200
                ctx.body = {
                    code: 200,
                    msg: '查询成功',
                    data
                }
            }catch(err){
                ctx.response.status = 412
                ctx.body = {
                    code: 412,
                    msg: '查询失败',
                    data: err
                }
            }
        }else{
            ctx.response.status = 416
            ctx.body = {
                code: 416,
                msg: '缺少id'
            }
        }
    }
}

module.exports = studentController

router

定义api， 调用controller层的业务方法

const router = require('koa-router')()

const StudentController = require('../controllers/student')

router.prefix('/student')

router.post('/create', StudentController.create)

router.get('/:id', StudentController.detail)

module.exports = router

注册路由

const index = require('./routes/index')
const users = require('./routes/users')

const student = require('./routes/student')

...

// routes
app.use(index.routes(), index.allowedMethods())
app.use(users.routes(), users.allowedMethods())

app.use(student.routes(), student.allowedMethods())

实现跨域

const cors = require('koa-cors')
...
app.use(cors())