# nodejs连接数据库

**Repository Path**: pdxnb/nodejs-connection-database

## Basic Information

- **Project Name**: nodejs连接数据库
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 3
- **Created**: 2022-04-07
- **Last Updated**: 2022-05-26

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# nodejs连接数据库

## 连接SQLServer

```js
let sequelize = new Sequelize('demo_db', 'sa', '123456', {
    host: 'localhost',
    dialect: 'mssql',
});
// 数据库连测试
(async () => {
    try {
        await sequelize.authenticate();
        console.log('连接成功.');
    } catch (error) {
        console.error('连接失败:', error);
    }
})()
```

## 模块安装
```
yarn add koa koa-router koa-bodyparser koa-cors nodemon sequelize tedious
```

## 后端-backend
```js
'use strict'
// 引入模块
const Koa = require('koa')
const router = require('koa-router')()
const bodyparser = require('koa-bodyparser')
const cors = require('koa-cors')
const { Sequelize, DataTypes, Op } = require('sequelize')

// 连接数据库
let sequelize = new Sequelize('demo_db', 'sa', '123456', {
    host: 'localhost',
    dialect: 'mssql'
})

// 创建模型表
let Blogs = sequelize.define('blogs', {
    title: {
        type: DataTypes.STRING,
        allowNull: false
    },
    summary: {
        type: DataTypes.STRING,
        allowNull: false
    },
    content: {
        type: DataTypes.STRING,
        allowNull: false
    },
    sort: {
        type: DataTypes.STRING,
        allowNull: false
    },
    author: {
        type: DataTypes.STRING,
        allowNull: false
    }
})

// 同步模型和数据到数据库
sequelize.sync({ force: true }).then(function () {
    Blogs.bulkCreate([
        {
            title: "js中常见的方法",
            summary: "js中常见的方法",
            content: "js中常见的方法",
            sort: "javascript",
            author: "li_dfaf",
        },
        {
            title: "nodejs中的路由",
            summary: "nodejs中的路由",
            content: "nodejs中的路由",
            sort: "nodejs",
            author: "node大佬",
        }
    ])
})

// new一个Koa实例
let app = new Koa()

// 获取列表数据
router.get('/blog', async (ctx, next) => {
    let keyword = ctx.request.query.keyword;
    ctx.body = await Blogs.findAll({
        order: ['id']
    })
    if (keyword) {
        ctx.body = await Blogs.findAll({
            where: {
                [Op.or]: [
                    { id: isNaN(keyword) ? 0 : parseInt(keyword) },
                    { title: keyword },
                    { summary: keyword },
                    { content: keyword },
                    { sort: keyword },
                    { author: keyword },
                ]
            },
            order: ['id']
        })
    }
})

// 删除数据
router.delete('/blog/:id', async (ctx, next) => {
    let id = ctx.request.params.id;
    await Blogs.destroy({
        where: { id: id }
    })
    ctx.body = await Blogs.findAll({
        order: ['id']
    })
})

// 新增数据
router.post('/blog', async (ctx, next) => {
    let obj = ctx.request.body;
    await Blogs.create({
        title: obj.title,
        summary: obj.summary,
        content: obj.content,
        sort: obj.sort,
        author: obj.author,
    })
    ctx.body = {
        code: 1000,
        msg: '添加成功'
    }
})

// 获取数据id
router.get('/blog/:id', async (ctx, next) => {
    let id = ctx.request.params.id;
    ctx.body = await Blogs.findByPk(id)
})

// 修改数据
router.put('/blog', async (ctx, next) => {
    let obj = ctx.request.body;
    if (obj.id) {
        await Blogs.update({
            title: obj.title,
            summary: obj.summary,
            content: obj.content,
            sort: obj.sort,
            author: obj.author,
        }, {
            where: { id: obj.id }
        })
        ctx.body = {
            code: 1000,
            msg: '编辑成功'
        }
    }
})

// 注册中间键
app.use(cors())
app.use(bodyparser())
app.use(router.routes())

// 监听端口
app.listen(8000)
console.log('服务器运行在如下地址：http://localhost:8000');
```

## 前端-frontend
```js
'use strict'

let baseUrl = 'http://localhost:8000/blog'

$(function () {
    fn_list()
})

// 渲染数据
function fn_list(baseData) {
    $.get(`${baseUrl}`, data => {
        if (baseData) {
            data = baseData
        }
        $('.row').remove()
        data.forEach(item => {
            let html = `
                    <tr class="row">
                        <td>${item.id}</td>
                        <td>${item.title}</td>
                        <td>${item.summary}</td>
                        <td>${item.content}</td>
                        <td>${item.sort}</td>
                        <td>${item.author}</td>
                        <td>${item.createdAt}</td>
                        <td>
                            <input type="button" value="编辑" onclick="edit(${item.id})">
                            <input type="button" value="删除" onclick="del(${item.id})">
                        </td>
                    </tr>
                    `
            $('#tbData').append(html)
        });
    })
}

// 查询
function query() {
    let keyword = $('#keyword').val()
    $.get(`${baseUrl}?keyword=${keyword}`, data => {
        fn_list(data)
    })
}

// 删除
function del(id) {
    if (confirm('确认删除吗？')) {
        $.ajax({
            url: `${baseUrl}/${id}`,
            type: 'delete',
            success: data => {
                location.href = './index.html'
            }
        })
    }
}

// 编辑
function edit(id) {
    window.location.href = `addOrEdit.html?${id}`
}
// 新增
function add() {
    window.location.href = `addOrEdit.html`
}

// 获取编辑的id
let id = location.search.split('?')[1];

// 判断是不是编辑
if (id) {
    $.get(`${baseUrl}/${id}`, data => {
        console.log(data);
        $('[name=id]').val(data["id"]);
        $('[name=title]').val(data["title"]);
        $('[name=summary]').val(data["summary"]);
        $('[name=content]').val(data["content"]);
        $('[name=sort]').val(data["sort"]);
        $('[name=author]').val(data["author"]);
    })
}

// 保存
function save() {
    let id = $('[name=id]').val()
    let title = $('[name=title]').val();
    let summary = $('[name=summary]').val();
    let content = $('[name=content]').val();
    let sort = $('[name=sort]').val();
    let author = $('[name=author]').val();
    let obj = {
        title,
        summary,
        content,
        sort,
        author
    }
    if (id) {
        obj.id = id
        $.ajax({
            url: `${baseUrl}`,
            type: 'put',
            data: obj,
            success: res => {
                if (res.code = 1000) {
                    window.location.href = 'index.html'
                }
            }
        })
    } else {
        $.post(`${baseUrl}`, obj, res => {
            if (res.code = 1000) {
                window.location.href = 'index.html'
            }
        })
    }
}

//取消
function cancel() {
    window.location.href = 'index.html'
}
```