Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

An npm module extending node-mysql to make datacalls a little easier

branch: master

Fetching latest commit…

Cannot retrieve the latest commit at this time

README.md

MySqlHELPER

A class created in CoffeeScript that makes working with felixge's node-mysql a little easier (I think / hope)

To get the most out of this module, I highly recommend you use it in conjunction with CoffeeScript (especially for extending classes). This module will still work fine with Regular JS.

Installation

npm install mysqlhelper

Simple Usage

db = require("mysqlhelper")

#Open the connection to the Mysql Database
db.createClient
    host: "localhost"
    database: "database"
    user: "root"
    password: ""

#Create a helper object
helper = new db.DBHelper()

helper.get
    table: "users"
    onComplete: -> console.log arguments

API

Documentation is a work in progress.... Thanks so much for your patience.

createClient([options])

Creates the mysql client instance. The options are the exact same as the mysql module.

DBHelper Class & Methods

Constructor

Holds a series of methods to make working with node-mysql a little quicker. I use this class primary to build other classes with CoffeeScript.

New Instance Usage:

helper = new db.DBHelper()

helper.get
    table: "users"
    id: 1
    onerow: true
    onComplete: -> console.log "Hello number 1 user", arguments

Class Extension Usage (recommended):

class Users extends db.DBHelper
    getByID: (id, onComplete) ->
        @get
            table: "users"
            id: id
            onerow: true
            onComplete: onComplete

users = new Users()
users.getByID 1, -> console.log "Hello number 1 user", arguments

DBHelper Public Properties

  • client (obj) :The reference to the mysql client created with createClient. This is an instance of the node-mysql object. Every client method found at node-mysql can be used here

  • completeCleaner (bool) : This is a public object property that can be set to TRUE to make sure that no SCRIPT tags get inserted / updated into the tables
    (defaults to false)

DBHelper Public Methods

@insert(params)

Insert Data Into a Table

params

  • table (string) : the table to insert data into (required)

  • data (obj) : data to insert into the table
    (required)

This will be entered in a {field: value} format

{
    name: "David Roberts",
    age: 34,
    hasdog: true
}
  • replace (bool) : Use a REPLACE statement instead of INSERT
    (defaults to false)

  • cleanValues (bool) : This will override the completeCleaner public property to allow (or not allow) script tags
    (defaults to NULL by default, but if you set it to TRUE or FALSE it will take precedence)

  • onComplete(cb(err, dbinfo, insertid)) : The callBack once the operation has been performed
    (optional (though recommended))

    • cb args:
      • err : error information sent back from mysql library
      • dbinfo : database information sent back from mysqllibrary
      • insertid : NEW! the insert id of the record inserted

Usage:

dbHelper.get
    table: "products"
    data: 
        title: "shoe"
        description: "so comfy"
        qty: 25
    cleanValues: true
    onComplete: (err, dbinfo, insertid) ->
        console.log "Inserted New Product: #{insertid}"

@q(query, cb)

@onerow(query, cb)

@get(params)

Retrieve data from a specified mysqlsql table

params

  • table (string) : the table to query (required)
  • id (number) : the id of the table (table has to have an id field) (this will override the onerow param to true)
  • fields (array) : A list of fields to return in the query (returns all by default)
  • where (object) : field / value of how you want to filter your result set (required)
  • keyById (bool) : return result as an object keyed by the id field of the table (false by default)
  • onerow (bool) : return only the first row (false by default)
  • orderby (string) : order query by specific field in the string (ex/ "name ASC")
  • resultsReturn (bool) : only return the results instead of the error and fields in the callback
  • onComplete (callback) : callback once the query is complete (required)

Usage:

#Grabbing a group of Products
dbHelper.get
    table: "products"
    fields: ["id", "title", "description", "qty"]
    where: {type: "clothing", in_stock: true}
    orderby: "title ASC"
    resultsReturn: true
    onComplete: (products) ->
        console.log products


#Grabbing a single Product
dbHelper.get
    table: "products"
    id: productID
    onComplete: (product) ->
        console.log product

@update(params)

@deletion(params)

@now()

@escapedNow()

@cleanValues(values)

@use(database)

@getMultipleTables(params)

@truncate(table, cb)

Something went wrong with that request. Please try again.