Database Jones

Introduction

This package provides a fast, easy, and safe framework for building database applications in Node.js. It is organized around the concept of a database session, which allows standard JavaScript objects to be read from and written to a database.

This example uses a session to store a single object into an existing MySQL table:

var jones = require("database-jones");

var connectionProperties = new jones.ConnectionProperties("mysql");

jones.openSession(connectionProperties).then(
  function(session) {
    var user = { id: 1, name: "Database Jones"};
    return session.persist("user", user);
  }
).then(
  function success() { 
    console.log("Complete");
    jones.closeAllOpenSessionFactories();
  },
  function failure(error) { 
    console.log("Error", error);l
  }
);

Key features include:

• Simple API for create, read, update, delete
• Bulk operations for high performance
• Support for ACID transactions, both explicit and implicit
• Flexible mapping from JavaScript objects to relational tables
• A fluent Query language using domain model tokens
• Default mapping of relational tables to simple objects
• Complex mapping of relational tables to complex objects
• Asynchronous API using well-known node.js callback patterns
• Promises/A+, allowing easier management of callbacks
• Connection pooling, allowing in-process scale up

Quick Install

The whole project can be used directly from a github clone if core.symlinks is set to true.

git config --global --add core.symlinks true
git clone http://github.com/mysql/mysql-js

To use the mysql adapter, you will also need node-mysql. To use the ndb adapter, you need MySQL Cluster, and you must use node-gyp to build the C++ source code under jones-ndb.

Supported Databases and Connection Properties

Jones provides a common data management API over a variety of back-end database connections. It does not include any backend adapters. At least one adapter must be installed separately.

Each backend adapter supports its own set of connection properties.

• The mysql adapter supports any MySQL database and uses the all-JavaScript mysql connector https://github.com/felixge/node-mysql.
• An ndb adapter, which optimized high-performance access to MySQL Cluster using the NDB API, is also available. See https://github.com/jdduncan/beta-jones-ndb

Session

The central concept of Jones is the Session. A session provides a context for database operations and transactions. Each independent user context should have a distinct session. For instance, in a web application, handling each HTTP request involves opening a session, using the session to access the database, and then closing the session.

Session methods

Most methods on session() are available on several varieties: they may take either a mapped object or a literal table name; they may take a callback, or rely on the returned promise for continuation; and they may take any number of extra arguments after a callback.

Each of the following methods is asynchronous and returns a promise:

• find() Find an instance in the database using a primary or unique key.
  • find(Constructor, keys, [callback], [...])
  • find(Projection, keys, [callback], [...])
  • find(tableName, keys, [callback], [...])
• load(instance, [callback], [...]) Loads a specific instance from the database based on the primary or unique key present in the object.
• persist() Insert an instance into the database.
  • persist(instance, [callback], [...])
  • persist(Constructor, values, [callback], [...])
  • persist(tableName, values, [callback], [...])
• remove() Delete an instance by primary or unique key.
  • remove(instance, [callback], [...])
  • remove(Constructor, keys, [callback], [...])
  • remove(tableName, keys, [callback], [...])
• update() Update an instance by primary or unique key without necessarily retrieving it.
  • update(instance, [callback], [...])
  • update(Constructor, keys, values, [callback], [...])
  • update(tableName, keys, values, [callback], [...])
• save() Write an object to the database without checking for existence; could result in either an update or an insert.
  • save(instance, [callback], [...])
  • save(Constructor, values, [callback], [...])
  • save(tableName, values, [callback], [...])
• createQuery() Create an object that can be used to query the database
  • createQuery(instance, [callback], [...])
  • createQuery(Constructor, [callback], [...])
  • createQuery(tableName, [callback], [...])
• getMapping() Resolve and fetch mappings for a table or class
  • getMapping(object, [callback], [...])
  • getMapping(Constructor, [callback], [...])
  • getMapping(tableName, [callback], [...])
• close([callback], [...]) Close the current session

The following methods are immediate:

• createBatch(). Returns a batch.
• listBatches(). Returns an array of batches.
• isClosed(). Returns boolean.
• isBatch(). Returns boolean.
• currentTransaction(). Returns a Transaction.

See the Complete documentation for Session

SessionFactory

A SessionFactory is a heavyweight master connection to a database, i.e. for a whole process or application.

A SessionFactory generally makes use of network resources such as TCP connections. A node.js process will often not exit until all SessionFactories have been closed.

Promises and Callbacks

The majority of the asynchronous API methods in Jones return a Promises/A+ compatible promise.

These promises are objects that implement the method then(onFulfilled, onRejected): If the asynchronous call completes succesfully, onFulfilled will be called with one parameter holding the value produced by the async call; if it fails, onRejected will be called with one parameter holding the error condition. The then() method also returns a promise, which allows promise calls to be chained.

Async calls also support standard node.js callbacks. If a callback is provided, it will be called with parameters (error, value) on the completion of the call.

The top level API

var jones = require("database-jones");
var properties = new jones.ConnectionProperties("mysql");
properties.mysql_host = "productiondb";
var mapping = new jones.TableMapping("webapp.users");
jones.connect(properties, mapping, onConnectedCallback);

• ConnectionProperties(adapterName): Constructor. Creates a ConnectionProperties object containing default values for all properties.
• TableMapping(tableName): Constructor. Creates a new TableMapping.
• Projection(mappedConstructor): Constructor. Creates a new Projection.
• *connect(properties, [mappings], [callback], [...]): ASYNC. The callback or promise receives a SessionFactory.
• *openSession(properties, [mappings], [callback], [...]): ASYNC. An implicit SessionFactory is opened if needed; the callback or promise receives a Session.
• getOpenSessionFactories() Returns an array
• closeAllOpenSessionFactories() Returns undefined

See the complete documentation for the top-level API

Mapped Objects

A TableMapping is an entirely optional part of the API that allows you to fine-tune the relations between JavaScript objects and database records. All of the data management calls available on session can take either a table name (so that they work without any mapping), or a mapped object. When a table name is used with find(), for instance, the returned object contains one property for every database column, with each property name the same as the corresponding column name, and the property value of a default JavaScript type based on the column type. When find() is used with a TableMapping, it can return an object with some subset of the fields from the mapped table (along perhaps with some non-persistent fields), using cusom type conversions, created from a particular constructor, and connected to a class prototype.

  function User() {     // Constructor for application object
  }

  var userTable = new jones.TableMapping("webapp.user");  // map a table
  userTable.mapField("firstName","first_name"); // customize the mapping
  userTable.applyToClass(User);  // apply the mapping to the constructor

See the complete documentation for TableMapping.

Converters

The data types stored in a particular database do not always correspond to native JavaScript types. For instance, most databases support 64-bit signed and unsigned integers, while JavaScript does not. MySQL-JS allows users to customize data conversion in these cases using Converter classess. A Converter class marshalls data between an intermediate format and a desired JavaScript format by means of two methods, toDB() and fromDB().

The intermediate format for each column type is defined by the backend database driver; e.g. the mysql and ndb drivers use string as the intermediate type for BIGINT columns.

To declare a converter universally for a particular column type, use sessionFactory.registerTypeConverter(). To declare a specific converter for a particular TableMapping, assign it to the converter property of a mapped field.

Batches

MySQL-JS allows flexible batching of operations. Many of the Session operations are also supported by Batch. A variety of operations can be defined in a batch, and will all be executed together at once. Callbacks are available for each completed operation and for the batch as a whole.

  var batch = session.createBatch();
  for(i = 0; i < itemDetails.length ; i++) {
    batch.persist(itemDetails[i]);
  }
  batch.update(userHistory);
  batch.update(userStatistics, onStatsUpdatedCallback);
  batch.remove(unsavedCart);
  batch.execute(batchCallback);

Transactions

Each Session includes a single current Transaction, which is obtained using the session.currentTransaction() call.

  var transaction = session.currentTransaction();
  transaction.begin();
  session.update(user);
  session.update(cart);
  transaction.commit(onCommitCallback);

By default, operations happen in auto-commit mode, with each operation enclosed in a transaction of its own.

Queries

While session.find() can be used to fetch a single database record using primary or unique index access, more complex queries are provided through the Query class

Queries are defined by a filter that specifies which database rows should be returned. The filter is declared fluently, combining queryable columns with comparators and parameters.

session.createQuery('employee').then(function(query) {
  query.where(
   query.salary.gt(query.param('low_salary')
   .and(query.salary.lt(query.param('high_salary')))));
});

Query execution is governed by a parameter object that can include values for named parameters for the query as well as options to sort or paginate the result. Query execution returns a promise but can also use the standard callback mechanism.

This query will return at most 20 objects that satisfy the filter, in ascending order. The same query object can be reused with different parameters and options.

query.execute({low_salary: 10000, high_salary:20000, limit: 20, order: 'asc"})
 .then(function(result) {console.log(result));

Standardized Errors

MySQL-JS provides a common representation of database errors, independent of backend adapters. This representation is based on SQLState, as used in the SQL 1999 standard. The DatabaseError object and supported SQLState codes are described in the Error documentation.