expressのapiでmysqlを使う

mysqlをインストールする

mysqlのインストール

$ npm install mysql --save

get(select)

データを取得して返す。

api/index.js

const express = require('express')
const app = express()

app.get('/', function(req, res) {
  const mysql = require('mysql')
  const connection = mysql.createConnection({
    host: 'localhost',
    database: 'mysql',
    user: 'root',
    password: '',
  })
  connection.connect()
  const sql = 'select id, message from my_table limit ?;'
  const values = [Number.parseInt(req.query.limit)]
  connection.query(sql, values, (error, results, fields) => {
    if (error) throw error
    const data = results.map(result => {
      return {
        id: result.id,
        message: result.message
      }
    })
    res.json({ data })
  })
  connection.end()
})

module.exports = {
  path: '/api',
  handler: app
}

pages/index.vue

export default {
  async asyncData({ $axios }) {
    const response = await $axios.get('http://localhost:3000/api?limit=10')
    return {
      data: response.data.data
    }
  }
}

post(insert)

データを挿入する。

api/index.js

const express = require('express')
const bodyParser = require('body-parser')
const app = express()

app.use(bodyParser.json())

app.post('/', function(req, res) {
  const mysql = require('mysql')
  const connection = mysql.createConnection({
    host: 'localhost',
    database: 'mysql',
    user: 'root',
    password: '',
  })
  connection.connect()
  const sql = 'insert into my_table set ?'
  const values = { message: req.body.message }
  connection.query(sql, values, (error, results, fields) => {
    if (error) throw error
    res.json({ id: results.insertId })
  })
  connection.end()
})

module.exports = {
  path: '/api',
  handler: app
}

pages/index.vue

export default {
  methods: {
    async insert() {
      const response = await this.$axios.post('http://localhost:3000/api', {
        message: 'hello'
      })
      return response.data.id
    }
  }
}

put(update)

データを更新する。

api/index.js

const express = require('express')
const bodyParser = require('body-parser')
const app = express()

app.use(bodyParser.json())

app.put('/:id', function(req, res) {
  const mysql = require('mysql')
  const connection = mysql.createConnection({
    host: 'localhost',
    database: 'mysql',
    user: 'root',
    password: '',
  })
  connection.connect()
  const sql = 'update my_table set message = ? where id = ?'
  const values = [req.body.message, req.params.id]
  connection.query(sql, values, (error, results, fields) => {
    if (error) throw error
    res.json({ count: results.changedRows })
  })
  connection.end()
})

module.exports = {
  path: '/api',
  handler: app
}

pages/index.vue

export default {
  methods: {
    async update(id) {
      const response = await this.$axios.put(`http://localhost:3000/api/${id}`, {
        message: 'こんにちは'
      })
      return response.data.count
    }
  }
}

delete(delete)

データを削除する。

api/index.js

const express = require('express')
const app = express()

app.delete('/:id', function(req, res) {
  const mysql = require('mysql')
  const connection = mysql.createConnection({
    host: 'localhost',
    database: 'mysql',
    user: 'root',
    password: '',
  })
  connection.connect()
  const sql = 'delete from my_table where id = ?'
  const values = [req.params.id]
  connection.query(sql, values, (error, results, fields) => {
    if (error) throw error
    res.json({ count: results.affectedRows })
  })
  connection.end()
})

module.exports = {
  path: '/api',
  handler: app
}

pages/index.vue

export default {
  methods: {
    async remove(id) {
      const response = await this.$axios.delete(`http://localhost:3000/api/${id}`)
      return response.data.count
    }
  }
}