module-db-wrapper v1.0.7

module-db-wrapper

This module implements a generic wrapper around the database, allowing for vastly improved unit testing and abstraction away from a particular database implementation.

Example

To use the db-wrapper module rather than raw SQL, you replace calls like this:

var results = await db.query(`SELECT * FROM ${TBL_USR}
                              WHERE (deleted_at IS NULL)
                              AND (status='pending')
                              AND (signup_token_valid_to < ?`,
                             [now])

with a more structured call to one of the db-wrapper module's public functions:

var results = await db.select({
  table: TBL_USR,
  fields: '*',
  where: {
    deleted_at: null,
    status: 'pending',
    signup_token_valid_to_LT: now
  }
})

This allows you to concentrate on what your database call should be doing, rather than worrying about writing SQL statements.

To test a function which uses the db-wrapper module, you simply install the "test driver" before running your tests. For example:

before(function() {
  db.init(db.testDriver)
})

Then, in your unit test, instead of stubbing out db.query(), you allow the function to run as normal, except that you call db.setTable() to set up the structure of the database as you want it to be before the function being tested is called. For example:

db.setTable({
  table: 'user',
  data: [
    {id: 1, status: 'pending', signup_token_valid_to: past},
    {id: 2, status: 'pending', signup_token_valid_to: future},
    {id: 3, status: 'pending', signup_token_valid_to: past}
  ]
})

You then call the function as usual, and once it returns you call db.checkTable() to see if the database structure now matches what you expect. For example:

db.checkTable({
  table: 'user',
  data: [
    {id: 1, deleted_at_NE: null},
    {id: 2, deleted_at_EQ: null},
    {id: 3, deleted_at_NE: null}
  ]
}).should.equal(true)

These examples check to see that the 'user' records with a signup_token_valid_to value in the past have their deleted_at field set to a non-null value.

Advantages

While the above is a simplistic example, it shows how the db-wrapper module allows you to test the desired behaviour of a function in terms of changes to the database, rather than testing the way the function has been implemented. The unit tests become much more robust, and changes to the internal implementation of a function will no longer cause the unit tests to fail.

A second major advantage of the db-wrapper module is that it allows you to install a different database engine without having to change any of your existing database-oriented code.

Usage

To install this module, add the module to your package.json file, eg:

{
  "dependencies": {
    "module-db-wrapper": "^0.0.1",
    ...
  }
  ...
}

You then simply require this module whenever you need to use it. For example:

var db = require('module-db-wrapper')

Before accessing the database, you have to install a "driver" for the appropriate type of database you want to use. For example:

db.init(db.mysqlDriver)

Once the database has been set up, you simply use it in your code by calling the various database-access functions described below.

The db-wrapper API

Most of the public functions defined by the db-wrapper module are asynchronous; they return a promise which must either be awaited or chained using .then() before processing the results. For example, using async/await syntax:

var rows = await db.select({...})

Alternatively, using promises directly:

db.select({...}).then(function(rows) {
  ...
}

Almost all the functions accept a single object which has properties containing the desired values. This allows you to make calls such as this:

rows = await db.select({
  table: 'users',
  select: '*',
  where: {deleted_at: null}
})

rather than the terse but more cryptic:

rows = await db.select('users', '*', {deleted_at: null})

This makes your calls to the database clear and easy to understand.

Initialisation

init(driver)

Initialise the db-wrapper module. This must be called before any other function.

driver should be an appropriate database driver. The following drivers are currently supported:

• mysqlDriver: A thin wrapper around the MySQL database.
• testDriver: A custom wrapper used for unit tests.

Drivers for other databases, including nosql databases such as MongoDB, will be added in the future.

Queries

async select({table, fields, where, orderBy, connection})

Select one or more records from the database.

The parameters are as follows:

• table is the name of the database table to query.

• fields is the set of fields to return. This can be the special value *, indicating all fields, a single field name, or an array of field names.

• where is an object mapping field names to values. A record is considered to match the query if all the specified field names match.

• orderBy indicates how to sort the resulting records. If this is null or undefined, the records will be unsorted. If this is a single field name or an array of field names, the returned records will be sorted by the specified field(s). Note that the field name(s) can include the optional suffix _DESC to sort the records in descending order.

• connection is an optional database connection object to use within a transaction.

We return an array with one entry for each record in the given database table which matches the field values specified in 'where'. For each record, the entry in the returned array will be an object mapping field names (taken from fields) to their associated value in the database table.

Note that the field names specified in the where parameter can optionally include a suffix identifying the type of comparison to perform. The following suffixes are currently supported:

• _EQ Equals
• _NE Not equal
• _LT Less than
• _GT Greater than
• _LTE Less than or equal
• _GTE Greater than or equal
• _IN One of the specified values. In this case, the associated value should be an array of possible values.

If no suffix is supplied, _EQ is assumed.

Data Manipulation

async insert({table, data, connection})

Insert a new record into the given database table.

table should be the name of a table in the database, and data should be an object containing the data to store into that table. connection is an optional database connection object to use within a transaction.

We create a new record using the given data. Upon completion, the asynchronous function resolves with the ID of the newly-inserted record.

async update({table, where, changes, connection})

Update one or more existing records in the database.

table should be the name of the database table to update, where should be an object mapping field names to values, and changes should be an object mapping field names to values. connection is an optional database connection object to use within a transaction.

We search for any records in the given database table which match the field values specified in where, updating each matching record to include the values specified in changes.

Note that the field names specified in the where parameter can optional include comparison suffixes, as described in the documentation for the select() function, above.

async delete({table, where, connection})

Delete one or more records from the database.

table should be the name of the database table to update, and where should be an object mapping field names to values.

We delete all the records in the given database table which match the field values specifed in where.

Note that the field names specified in the where parameter can optional include comparison suffixes, as described in the documentation for the select() function, above.

connection is an optional database connection object to use within a transaction.

Database Transactions

async beginTransaction()

Begin a new database transaction. Returns the connection object which should be passed as the connection parameter to all database operations which should use that transaction.

async rollbackTransaction(connection)

Roll back the given database transaction.

async commitTransaction(connection)

Commit the given database transaction.

Testing

async setTable({table, data})

Set the contents of the given database table to the given records.

table should be the name of the database table to query, and data should be an array of objects mapping field names to values.

We simply replace the existing contents of the given database table with the new set of records.

Note that this method is only available when using the testDriver database driver for unit testing.

async checkTable({table, data})

Check that the contents of the given database table matches the given records.

table should be the name of the database table to query, and data should be an array of objects mapping field names to values.

We check to see that the current contents of the given database table matches the supplied data. For each record, we only check the fields which are supplied; any extra fields in the record are ignored. The field names can include WHERE-style suffixes if desired.

Upon completion, this function resolves to either true or false. If there is a discrepency in the table contents, debugging information will be written to the console to help with debugging.

Note that this method is only available when using the testDriver database driver for unit testing.

Current Limitations

The current implementation of the db-wrapper module does not support database joins or aggregation functions (count, sum, etc). The current implementation is sufficient to support 99% of database-oriented code, especially for CRUD operations.

Future Enhancements

The following improvements to the db-wrapper module are planned:

• Adding support for database joins.

• Implementing the most common aggregation functions, including count(), sum() and average().

• Adding support for additional database engines such as postgres and nosql.

• Extending the where syntax to allow for boolean operators. These can be handled by having functions db.AND({...}), db.OR({...}) and db.NOT(...) that operate recursively to build the appropriate compound statement.

Other possible enhancements:

• Allow for case-sensitive versus case-insensitive comparisons within a WHERE clause.

• Allowing users to bypass the wrapper to execute raw SQL queries for those situations where the wrapper is insufficient. Note that the disadvantage of this idea is that the test driver would be unable to test code that uses raw SQL queries.