database-jones

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.

$3

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.