Database Management
Managing your databases with db-Store
To manage the framework's backend connections, a special backend layer was implemented. The db-Store now acts as the layer's head. It processes the backend configuration and initializes all backends with their corresponding connections. Furthermore, it provides the possibility to integrate a different ORM for each backend. db-Store works absolutely asynchronous, which results in the layer's high performance. Additionally, db-Store includes and defines a set of methods for every backend interface. A purposeful and easy workflow is established using these methods. The current backend adapters are currently limited to MySQL, MongoDB, and Memcached. Every adapter provides the possibility to use the backend's native implementation, making specialised accesses easy.
db-Store's methods
db-Store should be the first big initialisation as it is of vital importance for persisting data. If the adapter's configuration fails, the worker has to be shut down by normal means. After shutting down the worker, the master tries to spawn a new worker to take the previous one's place which enables the worker to start a fresh backend connection.
Unless interrupted manually, the server tries to spawn workers indefinitely until the designated amount of workers is reached. Database failures and other external failures can be bypassed by this measurement, without requiring manual interference.
db-Store in action
Configuration
db-Store can handle multiple backend connections by using a structure like this:
/**
* Current Environment specific Database connections
*/
config.database = {
mysql: {
demo: {
username : 'root',
password : '',
db : 'demo_development',
host : '127.0.0.1',
port : 3306
},
// Next connection
connection: {...}
},
// Next backend
backend: {...}
}Initialisation
The generic worker implementation will setup the db-Store automatically by your given configuration. So after booting the worker you can use all configured backend connections.
Accessing a backend connection
The db-Store provides the method get() to get the backend connection.
This is just a wrapper class around the real backend adapter.
var connection = greppy.db.get('mysql.demo');Methods of a backend connection
Methods wrapped by the backend adapters are:
constructor(name, backend, config)
configure(callback)
getORM(callback)
close(callback)Properties of a backend connection
A backend connection also ships with some public accessible properties:
errors // All errors which came from the backend adapter
instance // The plain backend connection instance
name // Name of the connection
config // Configuration of the connectionWorking with backends
MySQL
The db-Store MySQL adapter is based on the npm package mysql and on the
sequelize ORM. Sequelize offers the possibility to describe and manage
models, migrations, fixtures and associations between the models. Next we will
describe the features of this backend adapter.
Models
The structure of a model definition looks like the following:
/**
* Lease Model
*/
module.exports = function(sequelize, DataTypes)
{
return sequelize.define(
// Define name of the model
'Lease',
// Define all properties for the model
{
id: {
type : DataTypes.STRING,
primaryKey : true,
autoIncrement : false
},
expires_at: {
type: DataTypes.DATE,
}
},
// Specific Options for the model
{
underscored : true,
charset : 'utf8',
collate : 'utf8_general_ci',
timestamps : false
}
);
}Every model is represented by it's own file, which is named after the model.
Migrations
To stick with our previous example of the Lease model, let's take a look at
the migrations for this case:
/**
* Leases Migration
*/
module.exports = {
// Forward-migration method
up: function(migration, DataTypes, done)
{
migration.createTable(
// Define name of the table
'Leases',
// Define all columns for the table
{
id: {
type : DataTypes.STRING,
autoIncrement : false,
primaryKey : true
},
expires_at: {
type : DataTypes.DATE,
allowNull : false
}
},
// Specific Options for the table
{
engine : 'InnoDB',
collate : 'utf8_general_ci'
}
).complete(function(err) {
done();
});
},
// Backward-migration method
down: function(migration, DataTypes, done)
{
migration.dropTable('Leases').complete(done);
}
}The filename of migrations must be in the following format:
YYYYMMDDhhmmss-OPERATION_TABLE_DETAILED_OPERATION.jsMigrations are deployed under the path database/migrations/CONNECTION.
Fixtures
Because fixtures don't make sense for Leases, we use the DataSource model as
an example:
/**
* Fixtures for DataSources
*/
module.exports = function(orm, models, share, utils, callback)
{
chainer = new utils.QueryChainer;
var dataSources = [
{
source_id : 'b7bfa6bb-b121-4e4b-b97f-2de78655e5f2',
url : 'http://google.com/robots.txt',
creator_id : share.users[0].id
}
];
dataSources.forEach(function(item) {
chainer.add(models.DataSource.create(item));
});
chainer.run().success(function(results) {
share.dataSources = results;
callback && callback();
}).error(function(err) {
callback && callback(err);
});
}The filename of fixtures needs to be in the following format:
GROUP-modelName.jsThe GROUP is a numeric value, which shows the dependencies of the model.
The smaller this value, the earlier the fixture will be queued.
If for example the model DataSource is based on the User model, it's
group would be higher than that of the user.
00_user.js
10_dataSource.jsFixtures need to be deployed under database/fixtures/CONNECTION.
ORM
The access to Sequelize as MySQL ORM is pretty easy with the db-Store.
greppy.db.get('mysql.demo').getORM(function(orm, models) {
models.DataSource.find(1).success(function(record) {
// Your application code here
}).error(function(err) {
// Do something usefull in case of errors
log.error(err);
});
});Further documentation
- Documentation of
mysqlhttps://github.com/felixge/node-mysql/blob/master/Readme.md - Documentation of
sequelizehttp://sequelizejs.com/documentation
MongoDB
The db-Store MongoDB adapter is based on the npm package mongodb and on the
mongoose ORM. Mongoose offers the possibility to describe schemas and manage models.
The adapter got functionality to perform migrations and loading fixtures.
Next we will describe the features of this backend adapter.
Models
The structure of a model definition looks like the following:
/**
* User model
*/
var User = new Schema({
role_id: {
type: Number,
default: 1
},
fullname: {
type: String
},
email: {
type: String,
index: true
},
password: {
type: String
},
deleted_at: {
type: Date,
default: null
}
});
module.exports = User;You are free to require() other schemas to build associations, load plugins
or do some validation stuff which is supported by Mongoose at this point.
Every model is represented by it's own file, which is named after the model.
Migrations
Migrations are very useful if you don't want to use Mongoose, but still need to create indexes or do some other stuff like manipulating your datasets. A simple example for a migration could be the following:
/**
* Users Migration
*/
module.exports = {
// Forward-migration method
up: function(db, orm, done)
{
db.collection('users', function(err, collection) {
if (err) {
return done(err);
}
collection.ensureIndex({
'email': 1,
'fullname': 1
}, {
name: 'email_fullname',
unique: true,
background: true
}, done);
});
},
// Backward-migration method
down: function(db, orm, done)
{
db.collection('users', function(err, collection) {
if (err) {
return done(err);
}
collection.dropIndex('email_fullname', done);
});
}
}The filename of migrations must be in the following format:
YYYYMMDDhhmmss-OPERATION_TABLE_DETAILED_OPERATION.jsMigrations are deployed under the path database/migrations/CONNECTION.
Fixtures
An application without data is most often very useless, so you surely want to prepare a set of documents, which will be inserted if you rebuild the whole database. Here's an easy example for a user fixture:
/**
* Fixtures for Users collection
*/
module.exports = function(orm, models, share, utils, callback)
{
var records = [
{
"role_id" : 1,
"fullname" : "Hermann Mayer",
"email" : "hermann.mayer92@gmail.com",
"password" : "894904fa3048a795284a51233792f737"
},
{
"role_id" : 1,
"fullname" : "Patrick Jaksch",
"email" : "mail@deadly-silence.de",
"password" : "c4ef76b05908a729f4f857ddee667c14"
}
];
async.map(records, function(record, callback) {
models.User.create(record, function (err, savedUser) {
if (err) {
return callback && callback(err);
}
callback && callback(null, record);
});
}, function(err, records) {
if (err) {
return callback && callback(err);
}
share.Users = records;
callback && callback();
});
}The filename of fixtures needs to be in the following format:
GROUP-modelName.jsThe GROUP is a numeric value, which shows the dependencies of the model.
The smaller this value, the earlier the fixture will be queued.
Fixtures need to be deployed under database/fixtures/CONNECTION.
ORM
The access to Mongoose is pretty easy with the db-Store.
greppy.db.get('mongodb.demo').getORM(function(orm, models) {
models.User.findOne({'fullname': 'Test User'}, function(err, document) {
if (err) {
// Do something usefull in case of errors
return log.error(err);
}
// Your application code here
});
});Further documentation
- Documentation of
mongodbhttp://mongodb.github.io/node-mongodb-native/ - Documentation of
mongoosehttp://mongoosejs.com/docs/guide.html
