Paul Dirac was a theoretical physicist who made fundamental contributions to the early development of both quantum mechanics and quantum electrodynamics.
Dirac.js is an extendable and configurable database layer built on top of MoSQL and made for Node Postgres. It has a pluginable architecture and ships with sensible middleware so you can avoid repetitive tasks.
var dirac = require('dirac');
// Register a table with dirac
dirac.register({
name: 'users'
, schema: {
id: { type: 'serial', primaryKey: true }
, email: { type: 'text', unique: true }
, createdAt: { type: 'timestamp', default: 'now()' }
}
});
// Connect to database
dirac.init({ database: 'my_app' });
// Perform non-destructive database sync
dirac.sync();
dirac.dals.users.findOne( 7, function( error, user ){
/* ... */
});
Dirac.js is meant to be a solid starting point for your main database module. While you could have all of your logic in a single file, I like to group code by table as seen in the file structure below:
- lib/
- db/
- tables/
- groups.js
- users.js
- users_groups.js
- index.js
- app.js
var dirac = require('dirac');
var utils = require('../lib/utils');
var config = require('../config');
// Tell dirac to use all of the schemas in `/tables`
dirac.use( dirac.dir( __dirname + '/tables' ) );
// Set the DB Config
dirac.init( config.db );
// Export the dals namespace for easy access
module.exports = utils.extend( {}, dirac.dals );
The `dirac.dals` namespace is where each table interface is accessed. In the example above, groups, users, and users_groups would be available under `dirac.dals`.
var db = require('./db');
// Get the Database up-to-date with current schema
db.sync();
// Query where clause
// See https://github.com/goodybag/mongo-sql/blob/master/docs/conditional-helpers.md
// For all where clause options
var $where = {
created_at: { $gt: new Date('2012-01-01') }
};
// Other query options
// See https://github.com/goodybag/mongo-sql/blob/master/docs/query-types.md#type-select
// for full options
var options = {
order: { id: 'desc' }
, limit: 10
};
// Find all users created after specified date
// order by id desc and limit 10
db.users.find( $where, options, function( error, users ){
if ( error ) process.exit(1);
console.log( "Users:", users );
process.exit(0);
});
The dirac is split up into two main API's: The root API and the DAL (Data Access Layer) API. The root API involves any non-table-specific functionality while the DAL API is table-specific.
Connect to Postgres
Execute a MoSQL query
var dirac = require('/dirac');
var $query = {
type: 'select'
, table: 'users'
, columns: [ 'id', 'name' ]
, where: {
id: {
$in: [ 1, 2, 3, 4 ]
}
}
};
dirac.query( $query, function( error, users ){
/* ... */
});
Execute a SQL query
var dirac = require('/dirac');
var sql = 'select * from users where id = $1';
var values = [ 50 ];
dirac.raw( sql, values, function( error, users ){
/* ... */
});
Registers a new table or view with dirac. Will not actually create the table until dirac.sync() is called. Alternatively, you could call: dirac.dals.table.createIfNotExists() to manually add it. However, sync will resolve table dependencies and it will also save the database state so dirac can reason about your current table structure.
var dirac = require('/dirac');
// Register table
dirac.register({
name: 'users'
, schema: {
id: {
type: 'serial'
, primaryKey: true
}
, name: { type: 'text' }
}
});
// Register View
dirac.register({
name: 'bobs'
, type: 'view'
, query: {
type: 'select'
, table: 'users'
, where: { name: { $ilike: 'bob' } }
}
});
dirac.init(/* ... */);
dirac.dals.users.findOne( 7, function( error, user ){ /* ... */ });
Run the command for dropping all registered tables.
Get Database up-to-date with latest schemas
Pass a function to dirac to be called whenever `dirac.init` is called. Useful for initializing before/after filters and adding database-wide properties to all schemas.
/**
* Automatically adds created_at/updated_at fields to schemas
*/
var dirac = require('/dirac');
var utils = require('utils');
module.exports = function( options ){
utils.defaults( options, {
createdAt: {
name: 'created_at'
, type: 'timestamp'
, default: 'now()'
}
, updatedAt: {
name: 'updated_at'
, type: 'timestamp'
, default: 'now()'
}
});
return function( dirac ){
// Adds fields to a DAL
var addFields = function( dal ){
var schema = dirac.dals[ dal ].schema;
// Add createdAt if it's not already there
if ( !(options.createdAt.name in schema) ){
schema[ options.createdAt.name ] = options.createdAt;
}
// Add updatedAt if it's not already there
if ( !(options.updatedAt.name in schema) ){
schema[ options.updatedAt.name ] = options.updatedAt;
}
};
// Before filter adds updatedAt = 'now()' to the update query
var updateFilter = function( $query, schema, next ){
// Updates may be on values or updates
var values = 'values' in $query ? $query.values : $query.updates;
values[ options.updatedAt.name ] = 'now()';
next();
};
// Registers before filters to update updatedAt
var addFilters = function( dal ){
dirac.dals[ dal ].before( 'update', updateFilter );
};
// Add fields to each DAL
Object.keys( dirac.dals ).forEach( addFields )
// Add filters to each DAL
Object.keys( dirac.dals ).forEach( addFilters )
};
};
/**
* Register our database middleware
*/
var dirac = require('/dirac');
var middleware = {
createdAt: require('./middleware/created-at')
};
dirac.use(
middleware.createdAt({
updatedAt: {
name: 'last_updated'
, type: 'timestamp'
, default: 'now()'
}
})
);
// DAL registration
// ...
// ...
// After init is called, all functions specified in use are called
dirac.init( config.db );
Save an entry in the dirac_schemas table of the current DAL state in memory. This happens everytime you call `sync`
Select documents in `table_name`. The `$query` object is the `where` property of a MoSQL object. The `options` object is mixed into the main query object sent to dirac.dals.table.query and makes up the rest of the query.
// Query where condition
var $query = {
rating: { $gte: 3.5 }
, high_score: { $lt: 5000 }
, name: { $in: [ 'Bob', 'Alice', 'Joe', 'Momma' ] }
};
// Other options for the query
var options = {
columns: [
'*' // users.*
, { // Get average user high_score
type: 'average' // Name of the function
, as: 'average_score' // Name of the column
, expression: 'users.high_score' // Function argument
}
]
, offset: 50
, limit: 25
, order: { column: 'id', direction: 'desc' }
, group: [ 'id', 'name' ]
};
dirac.dals.users.find( $query, options, function( error, results ){
/* ... */
});
Identical to find only it adds a limit: 1 to the options and will return an object rather than an array. Substitute an ID for $query.
// Find user with { id: 1 }
dirac.dals.users.find( 1, function( error, user ){
/* ... */
});
Removes a document from the database. Substitute an ID for $query.
// Remove user with { id: 1 }
dirac.dals.users.remove( 1, function( error, user ){
/* ... */
});
Update documents in the database. Substitute an ID for $query.
// Update all bobs to bills
var $query = {
name: { $ilike: 'Bob' }
};
var $update = {
name: 'Bill'
};
var options = {
returning: ['id', 'name']
};
dirac.dals.users.update( $query, $update, options, function( error, users ){
/* ... */
});
Insert a single document or an array of documents.
// Update all bobs to bills
var user1 = {
name: 'bob'
};
db.users.insert( user1, function( error, result ){
console.log( result.id );
});
// In this case, other_field will be null for the other targets
db.users.insert([
{ name: 'bob' }
, { name: 'bill', other_field: true }
, { name: 'alice' }
]);
Add a before filter to the DAL. Before filters are like middleware layers that get run before the query is executed. You can add as long as a chain as you'd like.
// Register a new table
dirac.register({
name: 'books'
, schema: {
id: { type: 'serial', primaryKey: true }
, name: {
type: 'text'
// Dirac doesn't know anything about this object
// So we can use it for our own benefit
, validation: {
type: 'string'
, max_length: 250
}
}
}
})
// Before inserting books, validate the doc
dirac.dals.books.before( 'insert', function( $query, schema, next ){
if ( typeof $query.values.name != schema.name.validation.type )
return next({ type: 'VALIDATION_ERROR', message: 'invalid type for `name`' });
if ( $query.values.name.length > schema.validation.max_length )
return next({ type: 'VALIDATION_ERROR', message: 'invalid length for `name`' });
/* ... */
});
Add an after filter to the DAL. After filters are like middleware layers that get run after the query is executed.
// Register a new table
dirac.register({
name: 'books'
, schema: {
id: { type: 'serial', primaryKey: true }
, num_words: {
type: 'text'
// node-pg returns bigints as strings
// Tell casting after filter to cast to a number
, cast: 'number'
}
}
})
// Crappy casting
dirac.dals.books.after( 'find', function( results, $query, schema, next ){
var casts = {};
for ( var key in schema ){
if ( 'cast' in schema ) casts[ key ] = schema[ key ][ cast ];
}
// Transform result set
for ( var i = 0, l = results.length; i < l; ++i ){
for ( var key in casts ){
switch ( casts[ key ] ){
case 'int': results[ i ][ key ] = parseInt( results[ i ][ key ] ); break;
case 'number': results[ i ][ key ] = parseFloat( results[ i ][ key ] ); break;
case 'string': results[ i ][ key ] = "" + results[ i ][ key ]; break;
default: break;
}
}
}
next();
});