node + sequelize MySQL数据库操作

data_
287 阅读2分钟
  • 安装依赖
npm i sequelize sequelize-cli
// 如果使用 typescript npm i @types/sequelize -D
  • server
// server/db/config.ts
const env = process.env.CONFIG_ENV || 'test' // 配置测试与线上两套环境，开发->dev, 生产测试->test，生产线上->production 
console.log('process.env.CONFIG_ENV', env)
const isDev = env === 'dev' || env === 'test'
const username = isDev ? 'username_pre' : 'username'
const password = isDev ? '********' : '********'
const database = isDev ? 'xxx_pre' : 'xxx_dinet'
const host = isDev ? 'xx.xx.xx.xx' : 'xx.xx.xx.xx'
const port = isDev ? 3000 : 3001
const config = {
  username,
  password,
  database,
  options: {
    host,
    port,
    dialect: 'mysql',
    logging: false,
    dialectOptions: {
      charset: 'utf8mb4'
    },
    define: {
      charset: 'utf8mb4'
    },
    pool: {
      max: 1,
      min: 0,
      idle: 3000,
      handleDisconnects: true
    }
  }
}
export {
  config
}


// server/db/index.ts
import { Sequelize } from 'sequelize'
import { config } from './config'
// @ts-ignore
const sequelize = new Sequelize(config.database, config.username, config.password, config.options) // 实例化sequelize
sequelize.authenticate().then(() => { // 链接sequelize
  console.log('authenticate success') // 链接成功
}).catch(err => {
  console.log('authenticate err: ', err) // 失败打印失败原因
})
export sequelize


// server/db/model/xxxedit.ts // 创建 MySQL数据库模块
import { INTEGER, BIGINT, STRING, DATEONLY } from 'sequelize'
import { sequelize } from '../index'
export const Xxxxx = sequelize.define('表名称', { // 表名称换成对应的表
  id: {
    type: INTEGER,
    autoIncrement: true,
    primaryKey: true,
    allowNull: false
  },
  name: {
    type: STRING(100),
    allowNull: false
  },
  status: {
    type: INTEGER,
    allowNull: false
  },
  created_at: {
    type: DATEONLY,
    allowNull: false
  },
  updated_at: {
    type: DATEONLY,
    allowNull: false
  }
}, {
  freezeTableName: true,
  underscored: false,
  timestamps: false,
  tableName: '表名称',
  charset: 'utf8mb4'
})


// server/controller/xxxedit/getXxxedit.ts
import { Xxxxx } from '../../db/model/xxxedit'
export const getXxxxx = async (params: {[propName: string]: any}) => {
  try {
    const data = await Xxxxx.findAll({
      raw: true,
      limit: +params.limit,
      offset: +params.offset
    })
    const dataTotal = await Xxxxx.count()
    return {
      code: 1,
      message: '查询成功',
      data: {
        data,
        dataTotal
      }
    }
  } catch (error) {
    return {
      code: 0,
      message: (error as any).stack,
      data: null
    }
  }
}


// server/controller/xxxedit/createtXxxedit.ts
import { Xxxxx } from '../../db/model/xxxedit'
export const createXxxxx = async (params: {[propName: string]: any}) => {
  try {
    const data = await Xxxxx.create(params)
    return {
      code: 1,
      message: '创建成功',
      data
    }
  } catch (error) {
    return {
      code: 0,
      message: (error as any).stack,
      data: null
    }
  }
}


// server/controller/xxxedit/updateXxxedit.ts
import { Xxxxx } from '../../db/model/xxxedit'
export const updateXxxxx = async (params: {[propName: string]: any}) => {
  try {
    const data = await Xxxxx.update(params, {
      where: {
        id: params.id
      }
    })
    if (data[0] === 1) {
      return {
        code: 1,
        message: '更新成功',
        data
      }
    }
    return {
      code: 0,
      message: '更新失败',
      data
    }
  } catch (error) {
    return {
      code: 0,
      message: (error as any).stack,
      data: null
    }
  }
}


// server/controller/xxxedit/deleteXxxedit.ts
import { Xxxxx } from '../../db/model/xxxedit'
export const deleteXxxxx = async (params: {[propName: string ]: any}) => {
  try {
    const data = await Xxxxx.destroy({
      where: params
    })
    return {
      code: 1,
      message: '删除成功',
      data
    }
  } catch (error) {
    return {
      code: 0,
      message: (error as any).stack,
      data: null
    }
  }
}
// server/controller/xxxedit/index.ts
export * from './getXxxedit'
export * from './createtXxxedit'
export * from './updateXxxedit'
export * from './deleteXxxedit'
  • koa-router
server/api/xxxedit.ts
// get 
import { getXxxxx } from '../../controller/xxxedit'
export const getXxxxxApi = async (ctx: RouterContext, next: Next) => {
  const params = ctx.query
  const res = await getXxxxx(params)
  ctx.body = res
  await next()
}

// create
import { createXxxxx } from '../../controller/xxxedit'
export const createXxxxxApi = async (ctx: RouterContext, next: Next) => {
  const params = ctx.request.body
  const res = await createXxxxx(params)
  ctx.body = res
  await next()
}

// update
import { updateXxxxx } from '../../controller/xxxedit'
export const updateXxxxxApi = async (ctx: RouterContext, next: Next) => {
  const params = ctx.request.body
  const res = await updateXxxxx(params)
  ctx.body = res
  await next()
}

// delete
import { deleteXxxxx } from '../../controller/xxxedit'
export const deleteXxxxxApi = async (ctx: RouterContext, next: Next) => {
  const params = ctx.request.body
  const res = await deleteXxxxx(params)
  ctx.body = res
  await next()
}


// server/router.ts
import Router, { RouterContext } from 'koa-router'
import { Next } from 'koa'
const prefix = '/api'

import { getXxxxxApi, createXxxxxApi, updateXxxxxApi, deleteXxxxxApi } from './api/xxxedit'

export default (route: Router<any, {}>) => {
  console.log('route', route)
  route.get('/noperm', async (ctx: RouterContext, next: Next) => {
    const url = 'http://localhost:8080'
    ctx.body = `
    <!DOCTYPE html>
    <html lang="en">
      <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta http-equiv="X-UA-Compatible" content="ie=edge">
        <title>无权限页面</title>
      </head>
      <body style="padding: 30px;">
        <h4>欢迎来到XXX! 这是无权限页面!</h4>
        <p>你没有此系统的使用权限, 如果已申请？请<a href="/">重新验证</a>或<a href="/logout">重新登录</a></p>
        <p><a href="${url}" target="_blank">申请此系统权限</a></p>
      </body>
    </html>
    `
    await next()
  })
  // restful风格增删改查，xxxxx一般为表名
  route.get(`${prefix}/xxxxx`, getXxxxxApi)
  route.post(`${prefix}/xxxxx`, createXxxxxApi)
  route.put(`${prefix}/xxxxx`, updateXxxxxApi)
  route.del(`${prefix}/xxxxx`, deleteXxxxxApi)
}
  • 前端请求
// src/api/request.ts
import { message } from 'ant-design-vue'
import axios from 'axios'

const prefix = '/api/'

interface MetadataI {
  count: number; // 249
  limit: string; // "10"
  msgId: number; // 0
  msgInfo: string; // "success"
  offset: string; // "0"
  totalTime: number; // 77
}

export const request = async <T = { code: number, message: string, data: any }>(
  path: string,
  params: { [propName: string]: any } = {},
  options?: {
    oriFetch? : boolean
    errMsg?: string
    method?: 'GET' | 'POST' | 'PUT' | 'DELETE'
    headers?: { [propName: string]: any }
  }
): Promise<T | null> => {
  const url = `${prefix}${path}`
  const { method, oriFetch, headers } = options || { method: 'GET', headers: {} }
  const isGet = method === 'GET'
  try {
    const result = await axios({
      url,
      method,
      [isGet ? 'params' : 'data'] : params
    })
    if (result.status === 200) {
      return result.data
    }
    message.error('网络请求失败, 请联系管理员')
    return null
  } catch (error) {
    message.error('网络请求失败, 请联系管理员')
    console.info(error)
    return null
  }
}


// src/api/xxxedit.ts
import { request } from '../request'

export const getSql = async (url: string, params = {}) => {
  const res = await request(url, params, { method: 'GET' })
  console.log('res.data', res!.data)
  return res?.code === 1 ? res.data : null
}

export const createSql = async (url: string, params: {[propName: string]: any}) => {
  const res = await request(url, params, { method: 'POST' })
  return res
}

export const updateSql = async (url: string, params: {[propName: string]: any}) => {
  const res = await request(url, params, { method: 'PUT' })
  return res
}

export const deleteSql = async (url: string, params: {[propName: string]: any}) => {
  const res = await request(url, params, { method: 'DELETE' })
  return res
}
  • server
avatar
文章
阅读
粉丝