nodejs之資料庫連線

nodejs 對 MySQL、mongodb、redis 資料庫的連線方式。

MySQL：

var mysql = require('mysql')
var { MYSQL } = require('../config/default.js')

// 建立連線池
var pool = mysql.createPool({
  host: MYSQL.HOST,
  port: MYSQL.PORT,
  user: MYSQL.USERNAME,
  password: MYSQL.PASSWORD,
  database: MYSQL.DATABASE
})

// 查詢函式
const query = (sql, params) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        resolve(err)
      } else {
        connection.query(sql, params, (error, results, fields) => {
          if (error) {
            reject(error)
          } else {
            resolve(results)
          }

          connection.release(err => {
            if (err) {
              console.log('DB-關閉資料庫連線異常')
            }
          })
        })
      }
    })
  })
}

module.exports = query

用法示例：

const query = require('../mysql')

// 查詢所有使用者
const getallusers = () => {
  const _sql = 'SELECT * FROM tb_user'

  return query(_sql)
}

// 查詢單個使用者資訊
const getuser = (username) => {
  const _sql = 'SELECT * FROM tb_user WHERE username = ?'

  return query(_sql, [username])
}

module.exports = {
   getallusers,
   getuser
}

 

mongodb：

const MongoClient = require('mongodb').MongoClient
const { MONGO } = require('../config/default')

// 無賬號登入
let url = 'mongodb://' + MONGO.HOST + ':' + MONGO.PORT + '/'

// 有賬號登入，對於有特殊字元的密碼需要 encodeURIComponent
if (MONGO.USERNAME && MONGO.PASSWORD) {
  url = 'mongodb://' + MONGO.USERNAME + ':' +
    encodeURIComponent(MONGO.PASSWORD) + '@' + MONGO.HOST + ':' + MONGO.PORT + '/'
}

class Mongo {
  static getInstance() {
    if (!Mongo.instance) {
      Mongo.instance = new Mongo
    }
    return Mongo.instance
  }

  constructor() {
    this.client = ''
    this.connect()
  }

  connect() {
    return new Promise((resolve, reject) => {
      if (!this.client) {
        new MongoClient(url, { useNewUrlParser: true, useUnifiedTopology: true }).connect((err, client) => {
          if (err) {
            reject(err)
          } else {
            this.client = client.db(MONGO.DATABASE)
            resolve(this.client)
          }
        })
      } else {
        resolve(this.client)
      }
    })
  }
}

// 暴露例項，全域性通用
module.exports = Mongo.getInstance()

用法示例：

const mdb = require('../../mdb')

module.exports = {
  // 更新文章
  updateArticleInfo (id, data) {
    return new Promise((resolve, reject) => {
      mdb.connect().then(async db => {
        let articleColl = db.collection('article')

        articleColl.updateOne({ _id: ObjectId(id) }, {
          $set: data
        }, async (err, res) => {
          if (!err) {
            let [_data] = await articleColl.find({ _id: ObjectId(id) }).toArray()
            resolve(_data)
            return
          }
          reject(err)
        })
      })
    })
  }
}

 

redis：

// https://www.npmjs.com/package/ioredis
const Redis = require('ioredis')
const { REDIS } = require('../config/default')

// redis 直接暴露 ioredis 的例項即可
module.exports = new Redis({
  port: REDIS.PORT,         // Redis port
  host: REDIS.HOST,         // Redis host
  prefix: REDIS.PREFIX,     //存諸字首
  // ttl: REDIS.TTL,        //過期時間   
  // family: REDIS.FAMILY,
  password: REDIS.PASSWORD
  // db: 0
})

用法示例：

const Redis = require('../redis')

const redisFun = {
  set(key, value) {
    return new Promise((resolve, reject) => {
      Redis.set(key, value).then(res => {
        resolve()
      }).catch(err => {
        reject()
      })
    })
  },
  get(key) {
    return new Promise((resolve, reject) => {
      Redis.get(key).then(res => {
        resolve(res)
      }).catch(err => {
        reject(err)
      })
    })
  }
}

module.exports = redisFun