Features

• Automatically discovers schema and replication topology
• Zero boilerplate
• Exposes CRUD methods
• Saves nested objects
• Object caching & query memoization

Database Support

• PostgreSQL 9+

Installation

npm install oreo
npm install pg

Example

† see the example database schema below

var oreo = require('oreo')

// discover schema and replication topology
var db = oreo({
  driver: 'pg',
  hosts: ['localhost:5432'],
  name: 'my_db',
  user: 'postgres',
  pass: 'password'
}, runExampleQueries)

function runExampleQueries(err) {

  // Insert a new book and its author
  db.books.insert({
    title: 'Fear and Loathing in Las Vegas',
    author: {
      name: 'Hunter S.Thompson'
    }
  }, function(err, book) {
    console.log(book) // { id: 1, title: Fear and Loathing in Las Vegas, author_id: 1 }

    // Get a linked object
    book.hydrate('author', function(err, author) {
      console.log(book.author) // { id: 1, name: Hunter S. Thompson, books: [] }

      // Get multiple books using array of primary keys
      db.books.mget(author.books, function(err, books) {
        console.log(books)
      })
    })

    // Get an author by primary key
    db.authors.get(1, function(err, author) {
      console.log(author)
    })

    // Find authors by criteria
    db.authors.find({
      where: {
        author_id: 1
      }
    }, function(err, authors) {
      console.log(authors) // [{ id: 1, name: Hunter S. Thompson, books: [] }]
    })

    // Update the book
    book.update({
      title: 'The Rum Diary'
    }, function(err, book) {
      console.log(book) // { id: 1, title: The Rum Diary, author_id: 1 }
    })
  })
}

Example database schema:

CREATE TABLE authors (
  id SERIAL,
  name VARCHAR,
  books INTEGER[],
  CONSTRAINT author_pkey PRIMARY KEY(id)
);

CREATE TABLE books (
  id SERIAL,
  title VARCHAR,
  author_id INTEGER,
  CONSTRAINT book_pkey PRIMARY KEY(id),
  CONSTRAINT author FOREIGN KEY (author_id) REFERENCES authors(id)
);

Pro Tip: Create a trigger to auto-populate author.books[].

---

Don't use oreo if you want:

• Schema configuration
• Naming conventions
• Migrations
• Join-based hydration

Documentation

Main

• oreo
• discover
• execute

Table

• find
• findOne
• get
• insert
• mget
• save

Row

• hydrate
• save
• set
• update

---

Main

## oreo( opts, [cb] )

Instantiates the db object and configures the database connection string(s).

• opts {Object} options
• cb {Function} (optional) callback(err)

var oreo = require('oreo')
var db = oreo({
  driver: 'pg',
  hosts: ['localhost:5432'],
  name: 'database',
  user: 'username',
  pass: 'password',
  debug: false,
  memoize: 150, // ms to cache data objects in app ram
  cache: null // object with get/set methods to cache data objects, i.e. redis client
}, function(err) {
  db.execute('select now() as now', function(err, rs) {
    console.log('now:', rs[0].now)
  })
})

Hacker Tip: Replicate to Redis so your cache is never stale.

## db.discover( [cb] )

Re-discover the schema in the database.

• cb {Function} (optional) callback(err)

For each table in the database, defines a property db.<table_name> whose value is a Table object. Automatically runs when oreo is instantiated. Also, you can specify methods that will be bound to each Row object that is returned by Table.get().

db.discover(function(err) {
  // the Table API (see docs below) is now available:
  // db.authors
  // db.books

  // bind a method to all "book" objects
  db.books._methods.getTitle = function() {
    return this.title
  }
})

## db.execute( query, [data], [options], [cb] )

Executes an arbitrary SQL query.

• query {String|Array} the SQL statement
• data {Object} (optional, unless options is specified) parameterized query data
• options {Object} (optional) query options
  • write (optional) if truthy, forces query to run on master db, otherwise attempts to run on a read-only host
  • conString (optional) the connection string of the db
• cb {Function} (optional) callback(err, results)

db.execute([
  'select now()', // arrays can be used for multi-line convenience
  'as now'
], function(err, rs) {
  console.log(rs[0]) // 2014-06-24 21:03:08.652861-04
})

Parameterized query (SQL injection safe):

db.execute([
  'select id',
  'from authors',
  'where name = :name'
], {
  name: 'Jack Kerouac',
}, function(err, rs) {
  console.log(rs[0].id) // 1
})

If no callback is provided a stream is returned:

db.execute('select now()')
.on('data', function(row) {

})
.on('error', function(error) {

})
.on('end', function(result) {

})

Table

## db.table.find( opts, [cb] )

Finds one or more rows:

db.authors.find({
  where: ["name ilike 'Jack%'"],
  order: 'name asc',
  offset: 5,
  limit: 5
}, function(err, authors) {
  console.log(authors[0].id) // 1
})

The where option has several valid formats:

• {String}

  where: "field = 'abc' and field2 > 1"

• {Array}

  where: ["field = 'abc'", "field2 > 1"]

• {Object} recommended, blocks SQL injection

  where: {
    field: 'abc',
    field2: ['>', 1]
  }

If no callback is provided a stream is returned.

## db.table.findOne( opts, [cb] )

Finds exactly one row:

db.authors.findOne({
  where: ["name ilike 'Jack%'"],
  order: 'name asc',
  offset: 5
}, function(err, author) {
  console.log(author.id) // 1
})

If no callback is provided a stream is returned.

## db.table.get( primaryKey, [cb] )

Finds a row by primary key:

db.authors.get(1, function(err, author) {
  console.log(author) // { id: 1, name: Jack Kerouak, books: [1] }
})

Multi-column (composite) primary key:

db.parts.get({
  company: 'Cogswell Cogs',
  part_no: 'A-12345'
}, function(err, part) {

})

## db.table.insert( data, [cb] )

Inserts a new row.

db.books.insert({
  title: 'On the Road',
  author_id: 1
}, function(err, book) {
  console.log(book)
  // { id: 1, title: On the Road, author_id: 1 }
})

Insert multiple rows into related tables in a single transaction:

db.books.insert({
  title: 'On the Road',
  author: {
    name: 'Jack Kerouac'
  }
}, function(err, book) {
  console.log(book)
  // { id: 1, title: On the Road, author_id: 1 }
  book.hydrate(function(err, book) {
    console.log(book)
    // { id: 1, title: On the Road, author_id: 1, author: { id: 1, name: Jack Kerouac, books: [1] } }
  })
})

## db.table.mget( primaryKeys, [cb] )

Gets many rows from the database by primary key:

var bookIds = [1]
db.books.mget(bookIds, function(err, books) {
  console.log(books)
  // [ { id: 1, title: On the Road, author_id: 1 } ]
})

If no callback is provided a stream is returned.

## db.table.save( data, [cb] )

Inserts or updates depending on whether the primary key exists in the db.

var formPOST = {
  id: 1,
  title: 'New Title'
}
db.books.save(formPOST, function(err, book) {
  console.log(book)
  // { id: 1, title: New Title, author_id: 1 }
})

Row

## row.hydrate( fkConstraintName, [cb] )

Gets the linked record (foreign key)

db.books.get(1, function(err, book) {
  console.log(book)
  // { id: 1, title: On the Road, author_id: 1 }
  book.hydrate('author', function(err, author) {
    console.log(book)
    // {
    //   id: 1,
    //   title: On the Road,
    //   author_id: 1,
    //   author: { id: 1, name: Jack Kerouac, books: [1] }
    // }
  })
})

## row.save( [cb] )

Saves the modified property values to the database (recursively):

db.books.get(1, function(err, book) {
  console.log(book)
  // { id: 1, title: On the Road, author_id: 1 }
  book.author_id = 2
  book.save(function(err, book) {
    console.log(book)
    // { id: 1, title: On the Road, author_id: 2 }
  })
})

## row.set( data )

Sets multiple property values but does not save yet:

db.books.get(1, function(err, book) {
  console.log(book)
  // { id: 1, title: On the Road, author_id: 1 }
  book.set({
    title: 'New Title',
    author_id: 2
  })
  book.save()
})

## row.update( data, [cb] )

Update an existing row:

book.update({
  title: 'New Title'
}, function(err, book) {
  console.log(book)
  // { id: 1, title: New Title, author_id: 1 }
})