MySQL Chassis

A promise-based API for mysqljs (mysqljs is formerly called node-mysql). It provides easy SQL methods including:

• db.query()
• db.queryFile()
• db.select()
• db.selectFile()
• db.selectWhere() In Development, coming soon
• db.insert()
• db.update()
• db.insertUpdate() In Development, coming soon
• db.insertIgnore() In Development, coming soon
• db.delete()
• db.sqlWhere()

It also provides a middleware layer for db.onBeforeResults and db.onResults.

Key features:

• Promise-based layer over mysqljs
• External SQL files
• Named bind-parameter placeholders, similar to what PDO offers
• Better results for SELECT ... LIMIT BY 1 (see middleware)
• SQL templates with EJS (see middleware)
• "Transforms Feature" to normalize JS to SQL

Install

npm install --save mysql-chassis

Connect

// Non-ES6
// var MySQl = require('mysql-chassis').default;

// ES6
import MySQL from 'mysql-chassis';

const db = new MySQL({
  database: 'databasename',
  user: 'username'
}, err => {
  console.log('MySQL Error: ', err)
});

Note that if you don't provide password or host options, MySQL Chassis will pass an empty string as the password and mysqljs already passes localhost by default.

The options passed into MySQL Chassis will be passed directly to the mysqljs createConnection() method. The db instance returned gives you access to the MySQLChassis API. It also has mysqljs's connection as an attribute:

// mysqljs's connection API
db.connection

mysqljs has an extensive API which you'll still have access to via the db.connection object. See more details at their documentation

MySQL Chassis Connection Options

Aside from the connection options required for mysqljs, you can also pass in these additional options for MySQL Chassis:

• sqlPath: A filepath where SQL files can be found. This is used by selectFile() and queryFile(). If no value is passed, it defaults to ./sql
• transforms: An object for transform settings. See Transforms below

To see a full list of other options that mysqljs can use, see their documentation

Example Connection

Here's a more elaborate example of what your connection to MySQL Chassis might look like

import MySQL from 'mysql-chassis';
import path from 'path';

const db = new MySQL({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  sqlPath: path.join(process.cwd(), './sql')
}, err => {
  console.log('MySQL Error: ', err)
});

export default db

This assumes you have process.env setup to be different depending on production vs development. Also, this will allow you to place your SQL files in an sql folder at the project root. Adjust as needed.

See the .queryFile() method below for an example of how to use SQL statements as files instead of inline strings.

Results

All query execution methods return a promise with the results of the SQL:

db.query('SELECT * FROM user').then(function(results) {
  console.log(results)
}).catch(function(error) {
  console.log(error)
})

When a SELECT statement is passed in, the results will contain the following properties:

• rows: The same data that mysqljs would give you
• fields: The same data that mysqljs would give you
• sql: The SQL which was executed.

For non-SELECT statements, the results returned will be the same as mysqljs results which can contain any of the following properties depending on what type of SQL was performed:

• affectedRows
• insertId
• changedRows
• fieldCount
• serverStatus
• warningCount
• message

In addition to these, there will also be a sql property returned in the results. The purpose of returning the SQL statement in the results is mostly for debugging. Many of the methods below allow you to do PDO-Style bound parameters, and some methods will even write your SQL for you if you choose to use those features. So it's nice to know what eventual SQL was executed.

Errors

If there's an error, the promise .catch(err) will give you:

• err: The error provided by mysqljs
• sql: The SQL which was executed

API Methods

db.query(sql, [bindValues])

query() can be used to execute any type of SQL. If the query is an SELECT statement, you can access the rows returned via result.rows:

db.query('SELECT * FROM user').then(function(result) {
  console.log(result.rows)
})

If you need to pass dynamic values into your query, use the bindValues option which will properly escape the values with mysqljs's connection.escape() method.

let bindValues = {id: 1}
db.query('SELECT * FROM user WHERE user_id = :id', bindValues)

When you use the bindValues option, you'll also use placeholders in your SQL (such as :id) to map where the values should be placed.

---

db.queryFile(filename, [bindValues])

Works just like query() except it allows you to pass a filename instead of SQL directly. The filename will be appended to your sqlPath settings (configured at the time of connection, see above)

As an example:

let bindValues = {id: 1}
db.queryFile('somefile', bindValues)

This assumes somefile.sql exists in the sqlPath folder and looks like this:

SELECT * FROM user WHERE user_id = :id

If the file exists, .queryFile() will behave exactly like .query()

Also, if you want to organize your SQL files into sub folders of the sqlPath, you can access those files as:

let bindValues = {id: 1}
db.queryFile('path/to/somefile', bindValues)

path/to in this case is relative to the path supplied by sqlPath.

Note that the filename can written with or without the .sql extension. If no extension is provided, then .sql will be added to your filename.

---

db.select(sql, [bindValues])

Works just like .query() except it will return only the rows from the promise instead of a results object that contains rows with other meta data. Also it's only meant to be used on SELECT statements.

Use .select() over .query() if

• You are running a SELECT statement, and
• You don't care about the extra meta data returned from .query()

As a proof of concept, these two method calls would output the same data for rows:

db.select('SELECT * FROM user').then(function(rows) {
  console.log(rows)
})

db.query('SELECT * FROM user').then(function(results) {
  console.log(results.rows)
})

---

db.selectFile(filename, [bindValues])

Works just like .queryFile() in the sense that you can pass a filename in, but works like .select() in the sense of how it returns rows instead of results.

---

db.insert(tableName, insertValues)

This method will write your INSERT statement for you and then return the results of .query(). Here's how we can execute an INSERT statement for a user with name and email fields:

let insertValues = {name: 'Brad', email: 'brad@foobar.com'};

db.insert('user', insertValues).then(function(results) {
  console.log(results.insertId)
});

The INSERT statement executed would be:

INSERT INTO user
SET `name` = 'Brad', `email` = 'brad@foobar.com'

---

db.update(tableName, updateValues, whereClause)

This method will write your UPDATE statement for you and then return the results of .query(). Here's how we can execute an UPDATE statement for a user to update name and email fields:

let updateValues = {name: 'Brad', email: 'brad@foobar.com'};
let whereClause = {user_id: 1, active: true};

db.update('user', updateValues, whereClause).then(function(results) {
  console.log(results.changedRows)
});

The UPDATE statement executed would be:

UPDATE user
SET `name` = 'Brad', `email` = 'brad@foobar.com'
WHERE `user_id` = 1
AND `active` = true

See more on .where() below.

---

db.delete(tableName, whereClause)

This method will write your DELETE statement for you and returns the same promise as query().

let  whereClause = {user_id: 1, active: true};

db.delete('user', whereClause).then(function(results) {
  console.log(results.affectedRows)
});

The DELETE statement executed would be:

DELETE FROM user
WHERE `user_id` = 1
AND `active` = true

See more on .sqlWhere() below.

---

db.sqlWhere(whereClause)

This method is normally used by other API methods, such as .update(), and .delete(). You can also use it directly:

console.log(db.sqlWhere({
    user_id: 1
    active: true
})) // outputs: WHERE `user_id` = 1 AND `active` = true

Values passed in will be escaped using mysqljs's connection.escape() method.

If a string is passed in, the string will be returned without changes and without escaping. This allows you to write custom "where-clauses" as needed:

db.update('user', updateValues, 'WHERE user.datetime_added < NOW()')

---

Middleware

For custom functionality, you can add middleware to be ran before or after queries are executed:

db.onBeforeQuery(function(sql, bindValues) {
  // Here you can modify the SQL before it is ran
  return sql;
});

db.onResults(function(sql, results) {
  // Here you can modify the results before they are returned
  return results;
});

Middleware Examples

Example 1: mysqljs always returns an array of rows regardless of how many rows are returned. But if you wanted to modify the results such that when the SELECT statement has a LIMIT 1 at the end, then it will just return an object for the one row, then this is how that could be done:

db.onResults(function(sql, results) {
  if (results.length !== 1) return results;
  return /^SELECT\s(.|\n)+LIMIT 1$/g.test(sql.trim()) ? results[0] : results;
});

Example 2: If you feel inclined to treat your SQL files as templates which can be dynamic depending on the bindValues, you can use middleware with ejs templates

db.onBeforeQuery(function(sql, bindValues) {
  sql = ejs.compile(sql)(bindValues);
  return sql;
});

Now, imagine your SQL statements can be written as follows:

# file.sql
SELECT *
FROM user
WHERE user_id = :id
<% if (active) { %>
  AND active = true
<% } %>

Running this command will now be able to determine if the SQL statement runs with the AND active = true part or not:

db.select('file', {id: 1, active: true})

---

Transforms

Transforms are a means of helping .insert() and .update() methods know what to do when then encounter values that won't go well with MySQL. The transforms object is a map of values that .insert() and .update() could encounter, and what real values we'd rather use in the creation of the SQL statement.

The default transforms are:

transforms: {
  undefined: 'NULL',
  '': 'NULL',
  'NOW()': 'NOW()',
  'CURTIME()': 'CURTIME()'
}

As an example, let's say we pass undefined or an empty string into our .insert() method:

db.insert('user', {name: '', email: undefined});

Ideally for MySQL, you would want those values transformed to MySQL's NULL

# With Transforms (ideal)
INSERT INTO user
SET `name` = NULL, `email` = NULL

# Without Transforms (not ideal)
INSERT INTO user
SET `name` = '', `email` = ''

Note that escaping does not occur on transformed values. The value of the transform is placed directly inside the SQL statement whenever a bindValues value matches a transform key.

As another example, let's say you want to be able to pass the NOW() or CURTIME() MySQL functions as values into your .insert() or .update() methods. As you can see, the transforms allow you to write:

db.insert('user', {name: 'Brad', datetime_added: 'NOW()'});

Which will result in:

# With Transforms (ideal)
INSERT INTO user
SET `name` = 'Brad', `datetime_added` = NOW()

# Without Transforms (will cause an error if datetime_added expects a valid date)
INSERT INTO user
SET `name` = 'Brad', `datetime_added` = 'NOW()'

Custom Transforms

You can modify the default transforms or add your own at connection time:

const db = new MySQL({
  database: 'databasename',
  user: 'username',
  transforms: {
    'lookForThisValue': 'replaceWithThisValue', // Custom
    '': 'Empty Value' // Override default
  }
});