Building a Data Connector for DADI API | Edge Blog

Building a Data Connector for DADI API | Edge Blog

const config = require('../config')
const debug = require('debug')('api:mysql')
const EventEmitter = require('events').EventEmitter
const util = require('util')
const uuid = require('uuid')
 
// require MySQL module
const mysql = require('mysql2')

/\*\*
 \* Connect to the database
 \*
 \* @param {ConnectionOptions} options
 \*/
DataStore.prototype.connect = function (options) {
  debug('connect %o', options)
 
  return new Promise((resolve, reject) => {
    // read configuration options from config/mysql.<environment>.json
    const dbConfig = config.get('database')
 
    // connect!
    this.database = mysql.createConnection(dbConfig)
 
    // everything is ok, emit 'DB\_CONNECTED' event
    this.readyState = STATE\_CONNECTED
    this.emit('DB\_CONNECTED', this.database)
 
    // problem connecting? emit 'DB\_ERROR' event
    // this.emit('DB\_ERROR', err)
    // return reject(err)
 
    return resolve()
  })
}

return new Promise((resolve, reject) => {
  const insertQuery = \`INSERT INTO ${collection} SET ?\`
  const findQuery = \`SELECT \* FROM ${collection} WHERE \_id = ?\`
 
  return this.database.query(insertQuery, data\[0\], (err, results, fields) => {
    if (err) {
      return reject(err)
    }
 
    // query the database for the new document(s)
    return this.database.query(findQuery, data\[0\].\_id, (err, results, fields) => {
      return resolve(results)
    })
  })
})

const post  = {id: 1, title: 'Hello MySQL'}
 
let query = connection.query('INSERT INTO posts SET ?', post, (error, results, fields) => {
  if (error) throw error
 
})
 
console.log(query.sql) // INSERT INTO posts SET \`id\` = 1, \`title\` = 'Hello MySQL'

/\*\*
 \* Create a new MySQL table if the specified one doesn't exist
 \*
 \* Queries the MySQL information\_schema database, returning a count of
 \* tables that exist for the specified database + table\_name parameters
 \* 
 \* If the table doesn't exist, build a CREATE TABLE statement to execute against the database
 \*
 \* @param {String} name - the name of the table to check or create
 \* @param {Object} schema - the API collection schema fields
 \* @returns {Promise.<undefined, Error>} A promise that returns an Array of results,
 \*     or an Error if the operation fails
 \*/
DataStore.prototype.createTable = function (table, schema) {
  return new Promise((resolve, reject) => {
    // does the table exist?
    let tableQuery = \`SELECT COUNT(\*)
        FROM information\_schema.TABLES
        WHERE TABLE\_SCHEMA = '${config.get('database.database')}' AND TABLE\_NAME = '${table}'\`
 
    return this.database.query(tableQuery, (err, result) => {
      if (result\[0\]\['COUNT(\*)'\] === 0) {
        let createTableQuery = 'CREATE TABLE ' + table + ' ('
 
        // add an \_id column as a default
        createTableQuery += '\_id VARCHAR(100),'
 
        // add columns based on the collection schema
        Object.keys(schema).forEach(key => {
          createTableQuery += \`${key} ${schema\[key\].type === 'String' ? 'VARCHAR(255)' : 'INT'},\`
        })
 
        createTableQuery += 'PRIMARY KEY(\_id))'
 
        this.database.query(createTableQuery, (err, result) => {
          return resolve()
        })
      }
 
      // the table already exists, return
      return resolve()
    })
  })
}

  return new Promise((resolve, reject) => {
    const insertQuery = \`INSERT INTO ${collection} SET ?\`
    const findQuery = \`SELECT \* FROM ${collection} WHERE \_id = ?\`
 
    // first check the table exists
    return this.createTable(collection, schema).then(() => {
      return this.database.query(insertQuery, data\[0\], (err, results, fields) => {
        if (err) {
          return reject(err)
        }
 
        // query the database for the new document(s)
        return this.database.query(findQuery, data\[0\].\_id, (err, results, fields) => {
          return resolve(results)
        })
      })
    })
  })

insert
  ✓ should insert a single document into the database
  - should insert an array of documents into the database
  ✓ should add \_id property if one isn't specified
  ✓ should use specified \_id property if one is specified

beforeEach(function (done) {
  done()
})
``````javascript=
beforeEach(function (done) {
  var apiConnector = new ApiConnector()
 
  // clear the 'users' table
  apiConnector.connect({ database: 'content', collection: 'users' }).then(() => {
    apiConnector.dropDatabase('users').then(() => {
      done()
    })
  })
})

DataStore.prototype.dropDatabase = function (collectionName) {
  if (this.readyState !== STATE\_CONNECTED) {
    return Promise.reject(new Error('DB\_DISCONNECTED'))
  }
 
  debug('dropDatabase %s', collectionName || '')
 
  return new Promise((resolve, reject) => {
    const deleteQuery = \`DELETE FROM ${collectionName}\`
 
    // delete all
    return this.database.query(deleteQuery, (err, results, fields) => {
      return resolve()
    })
  })
}

    find
      ✓ should find a single document in the database
      - should return the number of records requested when using \`limit\`
      - should sort records in ascending order by the \`createdAt\` property when no query or sort are provided
      - should sort records in ascending order by the query property when no sort is provided
      - should sort records in ascending order by the specified property
      - should sort records in descending order by the specified property
      - should return only the fields specified by the \`fields\` property

$ dadi api new todo-api
✔ Checking the available versions of DADI API
✔ Pulling the list of available database connectors from NPM
? Which database engine would you like to install? (Use arrow keys)
❯ @dadi/api-mongodb — A MongoDB adapter for DADI API 
  @dadi/api-filestore — A JSON datastore adapter for DADI API