The idea I'm kicking around is this...
Start by defining your schema with JSON structures:
Please note this is a MAJOR not finished not even released project.
var Relational = require('relational');
var schema = new Relational().schema;
schema.addTable({
name: 'user',
columns: [{
name: 'id',
type: 'serial',
primaryKey: true
}, {
name: 'email',
type: 'text',
allowNull: false
}]
});
schema.addTable({
name: 'photo',
columns: [{
name: 'id',
type: 'serial',
primaryKey: true
}, {
name: 'description',
type: 'text'
}, {
name: 'userId',
type: 'int',
foreignKey: {
table: 'user',
column: 'id'
}
}]
});
The schema contains information about the relationships between the tables as well as the individual tables via the foreign key property on a column. What this will allow is something like this:
//continuing the last example...
// Find me users
schema.find('user');
//SELECT * FROM "user"
// Find me users, include photos, let relational figure out how to join
schema.find('user').join('photo');
//SELECT "user"."id", "user.email", "photo.id", "photo.userId",
//"photo.description" FROM "user" INNER JOIN "photo" ON "user.id" EQUALS "photo.userId"
Basically...I really fancy query builder. Something kinda like AREL maybe for ruby.
I'm not sure where I will go after that, but this at least will be really for me. This uses [node-sql] internally so it should be portable between databases with little work.
Other ideas:
- migrations based on reflecting on the schema definition and comparing it to the current database schema
- ORM functionality like CREATE, UPDATE, DELETE on rows
- Lazy loading collections, bla bla