FastLegS

PostgreSQL ORM on top of node-postgres

#FastLegS

PostgreSQL ORM on top of node-postgres.

##Installation

npm install FastLegS

##Quickstart

NOTE: As of version0.2.0, both PostgreSQL and MySQL are supported. You indicate which database you are using at object instantiation time. All other operations and interfaces behave the same as older versions.

var FastLegS = require('FastLegS');
...
FastLegS.connect(connectionParams);
...
var FastLegSBase = require('FastLegS');
var FastLegS = new FastLegSBase('mysql');
...
FastLegS.connect(connectionParams);
...
var FastLegSBase = require('FastLegS');
var FastLegS = new FastLegSBase('pg');
...
FastLegS.connect(connectionParams);
...
var FastLegSBase = require('FastLegS');

// gonna use PostgreSQL
var FastLegS = new FastLegSBase('pg');

var connectionParams = {
  user: 'shes'
, password: 'got'
, database: 'legs'
, host: 'localhost'
, port: 5432
}

FastLegS.connect(connectionParams);

var Post = FastLegS.Base.extend({
  tableName: 'posts',
  primaryKey: 'id'
});

Post.create(
  { title: 'Some Title 1', body: 'Some body 1' }, 
  function(err, results) {
    Post.find(
      { 'title.ilike': '%title%' }, 
      { only: ['id', 'body'] }, 
      function(err, post) {
        // Hooray!
      }
    );
  }
);

#The Full Monty

The following examples use these database tables as examples:

idtitleblurbbodypublished
1Some Title 1Some blurb 1Some body 1false
2Some Title 1Some blurb 2Some body 2true
3Some Title 1Some blurb 3Some body 3false
4Some Title 1Some blurb 4Some body 4true
idpost_idcommentcreated_at
11Comment 12012-12-11
21Comment 22012-12-11
32Comment 32012-12-11
42Comment 42012-12-11
53Comment 52012-12-11
63Comment 62012-12-11
74Comment 72012-12-11
84Comment 82012-12-11

Given this setup:

var FastLegSBase = require('FastLegS');

// gonna use PostgreSQL
var FastLegS = new FastLegSBase('pg');

var connectionParams = {
  user: 'shes', password: 'got',
  database: 'legs', host: 'localhost', port: 5432
}

FastLegS.connect(connectionParams);

var callback = function(err, results) {
  console.dir(err);
  console.dir(results);
}

var Comment = FastLegS.Base.extend({
  tableName: 'comments',
  primaryKey: 'id'
});

var Post = FastLegS.Base.extend({
  tableName: 'posts',
  primaryKey: 'id'
});

The following are examples of basic CRUD operations:

##Create

Calls to create can take an object or an array of objects.

Post.create(
  { id: 5, title: 'Some Title 5', body: 'Some body 5' },
  callback
)

Post.create(
  [{ id: 6, title: 'Some Title 6', body: 'Some body 6' },
   { id: 7, title: 'Some Title 7', body: 'Some body 7' }],
  callback
)

The results passed to the callback are different depending on the database.

In the case of PostgreSQL, the results will be an object of the form:

{
  rows: [{ id: 5,
         title: 'Some Title 5',
         blurb: null,
         body: 'Some body 5',
         published: null }],
  command: INSERT,
  rowCount: 1,
  oid: 0
}

In the case of MySQL, the results will be an object of the form:

{
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: ''
}

##Read

The various forms of the find command are very flexible. We'll present a few of them here.

####All:

Post.find({}, callback)

outputs:

[ 
  { id: 1,
    title: 'Some Title 1',
    blurb: null,
    body: 'Some body 1',
    published: null,
    created_at: null,
    updated_at: null },
  ...
  { id: 5,
    title: 'Some Title 5',
    blurb: null,
    body: 'Some body 5',
    published: null,
    created_at: null,
    updated_at: null },
  { id: 6,
    title: 'Some Title 6',
    blurb: null,
    body: 'Some body 6',
    published: null,
    created_at: null,
    updated_at: null },
  { id: 7,
    title: 'Some Title 7',
    blurb: null,
    body: 'Some body 7',
    published: null,
    created_at: null,
    updated_at: null } 
]

####By primary key:

Post.find(6, callback)

outputs:

{
  id: 6,
  title: 'Some Title 6',
  blurb: null,
  body: 'Some body 6',
  published: null,
  created_at: null,
  updated_at: null
}

####Only show some fields:

Post.find(6, {only: ['id','title']}, callback)

outputs:

{ id: 6, title: 'Some Title 6' }

####Some clauses:

Post.find({'title.like': 'Some%'}, callback)
Post.find({'id.in': [6, 7]}, callback)
Post.find({'id.nin': [6]}, callback)
Post.find({'$or': {'id.equals': 5, 'body.like': '%body 7'}}, callback)

####Order, offset, limit

Post.find({}, { order: ['-id'] }, callback)
Post.find({}, { offset: 1, limit: 1 }, callback)

####Count:

Post.find({}, {count: true}, callback)

outputs:

{ count: 7 }

##Update

Post.update(
  { title: 'Some Title 6' },
  { title: 'Renamed title' },
  callback
)

##Delete

Post.destroy({ 'id.in': [5, 7]}, callback)
Post.truncate(callback)

##A Taste of Relationships

You can call out relationships when you extend FastLegS.Base:

var Post = FastLegS.Base.extend({
  tableName: 'posts',
  primaryKey: 'id',
  many: [
    { 'comments': Comment, joinOn: 'post_id' }
  ]
});

You can then create complex object relationships with join logic:

Post.find(
  {}, 
  { include: { comments: { only: ['id', 'comment'] } } },
  callback
)

outputs:

[
  {
      body: 'Some body 1',
      title: 'Some Title 1',
      id: 1,
      updated_at: null,
      published: false,
      blurb: 'Some blurb 1',
      created_at: null,
      comments: [
          { id: 1, comment: 'Comment 1' },
          { id: 2, comment: 'Comment 2' }
      ]
  },
  {
      body: 'Some body 2',
      title: 'Some Title 2',
      id: 2,
      updated_at: null,
      published: true,
      blurb: null,
      created_at: null,
      comments: [
          { id: 3, comment: 'Comment 3' },
          { id: 4, comment: 'Comment 4' }
      ]
  },
  ...
]

Here's a many-to-many example based on these tables:

idname
1Abe
2Ben
3Christine
4Delia
5Egwene
idname
6Felix
7Garret
8Horton
9Irene
10Jane
student_idprofessor_id
16
26
37
47
58
18
29
39
410
510
var Student = FastLegS.Base.extend({
  tableName: 'students',
  primaryKey: 'id',
});

var Professor = FastLegS.Base.extend({
  tableName: 'professors',
  primaryKey: 'id',
})

var StudentProfessor = FastLegS.Base.extend({
  tableName: 'student_professor',
  foreignKeys: [
     { model: Student, key: 'student_id' },
     { model: Professor, key: 'professor_id' }
  ]
})

Student.many = [{
  professors: Professor,
  assoc: StudentProfessor
}]

Professor.many = [{
  students: Student,
  assoc: StudentProfessor
}]

Professor.findOne(
  9,
  {include: { students: {} }},
  function(err, result) {
    inspect(result)
  }
)

outputs:

{
  id: 9,
  name: 'Irene',
  students: [
      { id: 2, name: 'Ben' },
      { id: 3, name: 'Christine' }
  ]
}

This shows that professor Irene has students Ben and Christine

##Summary

The tests are an excellent reference for the various modifiers and syntactic sugar you can use in FastLegS.

##ToDo

Right now, the codebase is split because of syntactic differences between PostgreSQL and MySQL. There is a lot of duplicated code, however. Future versions should abstract out the differences and merge the duplicated code.

Watch for updates to examples in the near future to show features like relationships and advanced queries.

#Contributors