Features

No dependencies
Database-first ORM with no configuration needed
Auto-detects tables, columns, (composite) primary keys and foreign keys
Saves multi-table nested objects with an atomic transaction
Detects primary and read-only hosts

Database Support

PostgreSQL 9+
MySQL

Installation

npm i oreo pg@6 mysql

Quick Example

import oreo from 'oreo'

const db = oreo({
  driver: 'pg',
  hosts: ['localhost'],
  name: 'my_db',
  user: 'root',
  pass: '',
  ssl: true
}

db.onReady(async () => {
  // Assuming you have a table "artists"
  // Get an artist by primary key
  const artist = await db.artists.get(id)
  console.log(artist)
})

Documentation

Usage

oreo

Table

count
find
findOne
get
insert
mget
save

Row

delete
hydrate
save
set
update

Full Example

† see the example database schema below

import oreo from 'oreo'

// initialize oreo: auto-detects the schema and determines writable/read-only hosts
const db = oreo({
  driver: 'pg',
  hosts: ['localhost:5432'],
  name: 'my_db',
  user: 'username',
  pass: 'password',
  ssl: true,
  debug: console.log, // optional
  memoize: 150, // optional duration in ms to memoize rows
  cache: redisClient, // optional
  Promise: Promise, // optional, default: global.Promise
  models: {}, // optional
  schema: {} // optional skips auto-detect schema
})

async function examples() {

  // Insert a new book, its author and some reviews (in a single transaction)
  let book = await db.books.insert({
    title: 'Fear and Loathing in Las Vegas',
    author: {
      name: 'Hunter S.Thompson'
    },
    reviews: [ // shorthand for 'book:reviews'
      { stars: 5, body: 'Psychadelic!'},
      { stars: 4, body: 'Bizarre, unpredictable yet strangely alluring.'}
    ]
  })
  console.log(book) // { id: 1, title: Fear and Loathing in Las Vegas, author_id: 1 }

  // Get the book's author (1-to-1 linked row)
  await book.hydrate('author')
  console.log(book.author) // { id: 1, name: Hunter S. Thompson }

  // Get the book's reviews (1-to-many linked rows)
  await book.hydrate('reviews')
  console.log(book.reviews) // array

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

  // Delete a book
  await book.delete()
  console.log(book) // {}

  // Get an author by primary key
  let author = await db.authors.get(1)
  console.log(author) // { id: 1, name: Hunter S. Thompson }

  // Get multiple authors by primary key
  let authors = await db.authors.mget([1])
  console.log(authors) // [ { id: 1, name: Hunter S. Thompson } ]

  // Find authors
  authors = await db.authors.find({
    where: {
      name: 'Hunter S. Thompson'
    },
    order: 'name asc',
    limit: 10,
    offset: 0
    }
  })
  console.log(authors) // [ { id: 1, name: Hunter S. Thompson } ]

  // Find one author
  author = await db.authors.findOne({
    where: [
      "name like 'Hunter %'"
    ]
  })
  console.log(author) // { id: 1, name: Hunter S. Thompson }
}

db.onReady(examples)

Example database schema:

create table authors (
  id serial,
  name varchar,
  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)
);

create table reviews (
  id serial,
  book_id integer,
  stars integer,
  body varchar,
  constraint review_pkey primary key(id),
  constraint book foreign key (book_id) references book(id)
);

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

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

Usage

oreo( opts, [cb] )

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

opts {Object} options
- cache {Object} (optional, default false) object with get(key) and/or set(key, val) methods (i.e. redis) to cache full rows (indefinitely). Cached rows are recached after save()/insert()/update()/delete(). The Table functions fetch rows from the cache (and only fetch from sql the rows that are not cached).
- Promise {Object} (optional, default global.Promise) You may plug in your own Promise library that is compatible with native promises, i.e. Promise: require('bluebird'). Then a promise will be returned if a callback is not specified.
- models {Object} (optional) each table may have a model "class" specified which will be used to instantiate rows from that table. For example, models.my_table = class MyTable {}
- schema {Object} (optional) initialize oreo faster by specifying the schema, for example JSON.parse(JSON.stringify(db))
cb {Function} (optional) callback(err)

const oreo = require('oreo')
const db = oreo({
  driver: 'pg',
  hosts: ['localhost:5432'],
  name: 'database',
  user: 'username',
  pass: 'password',
  //ssl: false,
  //debug: false, //console.log
  //memoize: 0,
  //cache: null,
  //Promise: global.Promise
  //models: {},
  //schema: {}
}, function (err) {
  db.execute('select now() as now')
  .then(rows => {
    console.log('now:', rows[0].now)
  })
})

Db

db.execute( sql, [data], [opts], [cb] )

Executes an arbitrary SQL query.

sql {String|Array} the SQL statement
data {Object} (optional, unless opts is specified) parameterized query data
opts {Object} (optional) query options
- write {Boolean} if truthy, forces query to run on master db, otherwise attempts to run on a read-only host
cb {Function} (optional) callback(err, rows) If cb is not provided, a Promise is returned.

db.execute([
  'select now()', // arrays can be used for es5 multi-line convenience
  'as now'
])
.then(rows => {
  console.log(rows[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',
})
.then(rows => {
  console.log(rows[0].id) // 1
})
.catch(err => {

})

db.executeWrite( sql, [data], [opts], [cb] )

Same as execute but executes the query on a writable (primary) host.

db.onReady( cb )

Queues a function to be called when oreo's schema detection is complete (i.e. when oreo is initialized).

cb {Function} callback()

const db = oreo(config, (err) => {
  console.log('Ready!')
})
.onReady(() => {
  console.log('onReady #1')
})
db.onReady(() => {
  console.log('onReady #2')
})

/*
Output:
onReady #1
onReady #2
Ready!
*/

db.end( [cb] )

Closes the db connection(s).

Table

db.table.count( [opts], [cb] )

Counts the number of rows matching the specified criteria.

opts {Object} (optional) options
- where {String|Array|Object} the where clause criteria
- params {Object} key/value pairs to be substituted for :key patterns in the query
cb {Function} (optional) callback(err, rows) If cb is not provided, a Promise is returned.

db.authors.count({
  where: {
    name: 'Jack'
  }
})
.then(count => {
  console.log(count) // 1
})

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

Finds multiple rows.

opts {Object} (optional) options
- hydrate {String|Array} hydrates the specified foreign keys (see hydrate)
- params {Object} key/value pairs to be substituted for :key patterns in the query
cb {Function} (optional) callback(err, rows) If cb is not provided, a Promise is returned.

db.authors.find({
  where: [ "name like 'Jack%'" ],
  order: 'name asc',
  offset: 5,
  limit: 5,
  hydrate: ['books']
})
.then(authors => {
  console.log(authors)
  // [ { id: 1, name: Jack Kerouac, books: [ { id: 1, title: On the Road, author_id: 1 } ] } ]
})

The where option has several valid formats:

{String}

where: "field = :f1 and field2 > :f2",
params: {
  f1: 'abc',
  f2: 1
}

{Array}

where: [
  "field = :f1",
  "field2 > :f2"
],
params: {
  f1: 'abc',
  f2: 1
}

{Object}

where: {
  field: 'abc',
  field2: { $gt: 1 } // query operators are coming soon
}

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

Finds exactly one row.

opts {Object} same options as find
cb {Function} (optional) callback(err, row) If cb is not provided, a Promise is returned.

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

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

Gets a row by primary key.

primaryKey {String|Number|Object} the primary key of the row to get

opts {Object} (optional) options
- hydrate {String|Array} hydrates the specified foreign keys (see hydrate)
cb {Function} (optional) callback(err, row) If cb is not provided, a Promise is returned.

const primaryKey = 1 // const primaryKey = { id: 1 } // this also works
db.authors.get(primaryKey)
.then(author => {
  console.log(author) // { id: 1, name: Jack Kerouak }
})

Multi-column (composite) primary key:

const primaryKey = {
  company: 'Cogswell Cogs',
  part_no: 'A-12345'
}
db.parts.get(primaryKey)
.then(part => {
  console.log(part) // { company: Cogswell Cogs, part_no: A-12345, price: 9.99, in_stock: true }
})

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

Inserts a new row.

data {Object} the data to insert into the db
cb {Function} (optional) callback(err, row) If cb is not provided, a Promise is returned.

db.books.insert({
  title: 'On the Road',
  author_id: 1
})
.then(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: {  // "author" is the foreign key name (1-to-1)
    name: 'Jack Kerouac'
  },
  reviews: [ // shorthand for 'book:reviews' <foreignKeyName>:<tableName> (1-to-many)
    { stars: 5, body: 'Psychadelic!'},
    { stars: 4, body: 'Bizarre, unpredictable yet strangely alluring.'}
  ]
})
.then(book => {
  console.log(book) // { id: 1, title: On the Road, author_id: 1 }
})

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

Gets many rows by primary key in the specified order. A null value will be returned for each primary key that does not exist.

primaryKeys {Array} the primary keys of the rows to get

opts {Object} (optional) options
- hydrate {String|Array} hydrates the specified foreign keys (see hydrate)
cb {Function} (optional) callback(err, rows) If cb is not provided, a Promise is returned.

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

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

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

data {Object} the data to save to the db
cb {Function} (optional) callback(err, row) If cb is not provided, a Promise is returned.

const formPOST = {
  id: 1,
  title: 'New Title'
}
db.books.save(formPOST)
.then(book => {
  console.log(book) // { id: 1, title: New Title, author_id: 1 }
})

Row

row.delete( [cb] )

Deletes an existing row from the database.

cb {Function} (optional) callback(err) If cb is not provided, a Promise is returned.

book.delete()
.then(() => {
  console.log(book) // {}
})

row.hydrate( propertyName, [cb] )

Hydrates the row(s) linked with the specified foreign key(s) and/or foreign table(s).

propertyName {String|Array} the name of the hydratable property to fetch and attach to this row. There are two types of hydratable property names:
- 1-to-1 foreign key constraint name
- 1-to-many foreign table name
cb {Function} (optional) callback(err) If cb is not provided, a Promise is returned.

db.books.get(1)
.then(book => {
  console.log(book) // { id: 1, title: On the Road, author_id: 1 }

  // hydrate a 1-to-1 linked row
  book.hydrate('author')
  .then(() => {
    console.log(book.author) // { id: 1, name: Jack Kerouac }
  })

  // hydrate 1-to-many linked rows
  book.hydrate('reviews')
  .then(() => {
    console.log(book.reviews) // [ { stars: 5, body: 'Psychadelic!' }, { stars: 4, body: 'Bizarre...' } ]
  })
})

When hydrating a 1-to-1 row, the propertyName is the name of the foreign key constraint.

For example, a book has one author, so we have a table books with a column author_id which has a foreign key constraint named author which links to author.id.

// 1-to-1
book.hydrate('author')
.then(() => {
  console.log(book.author) // { id: 1, name: Jack Kerouac }
})

When hydrating 1-to-many rows, it is recommended to specify the fully qualified hydratable propertyName formatted as foreignKeyName:tableName. However, for convenience, if the foreign table has only one foreign key that references this table, you may omit foreignKeyName: and simply use tableName shorthand notation.

For example, a book has many reviews, so we have a table reviews with a column book_id which has a foreign key constraint named book which links to book.id.

// 1-to-many (fully qualified notation)
book.hydrate('book:reviews')
.then(() => {
  console.log(book['book:reviews'])
  // [ { stars: 5, body: 'Psychadelic!' }, { stars: 4, body: 'Bizarre...' } ]
})

// 1-to-many (shorthand notation)
book.hydrate('reviews')
.then(() => {
  console.log(book.reviews)
  // [ { stars: 5, body: 'Psychadelic!' }, { stars: 4, body: 'Bizarre...' } ]
})

Hydrate multiple properties in parallel:

book.hydrate(['author', 'reviews'])
.then(() => {
  console.log(book)
  // {
  //   id: 1,
  //   title: On the Road,
  //   author_id: 1,
  //   author: { id: 1, name: Jack Kerouac },
  //   reviews: [ { stars: 5, body: 'Psychadelic!' }, { stars: 4, body: 'Bizarre...' } ]
  // }
})

row.save( [cb] )

Saves the modified property values to the database (and saves linked rows recursively).

cb {Function} (optional) callback(err, row) If cb is not provided, a Promise is returned.

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

row.set( data )

Modifies multiple property values but does NOT save to the db.

data {Object} the data to modify

db.books.get(1)
.then(book => {
  console.log(book) // { id: 1, title: On the Road, author_id: 1 }

  book.set({
    title: 'New Title',
    author_id: 2
  })

  book.save()
  .then(book => {
    console.log(book) // { id: 1, title: New Title, author_id: 2 }
  })
})

row.update( data, [cb] )

Updates an existing row. A convenience method for set() then save().

data {Object} the data to save
cb {Function} (optional) callback(err, row) If cb is not provided, a Promise is returned.

book.update({
  title: 'New Title'
})
.then(book => {
  console.log(book) // { id: 1, title: New Title, author_id: 1 }
})

Known Issues

Postgres tables containing JSON data type are not supported (use JSONB instead!)

oreo
Release 0.2.1

Release 0.2.1

0.7.1

0.7.0

0.6.4

0.6.3

0.6.2

0.6.1

0.6.0

0.5.3

0.5.2

0.5.1

Documentation

Features

Database Support

Installation

Quick Example

Documentation

Usage

Db

Table

Row

Full Example

Usage

oreo( opts, [cb] )

Db

db.execute( sql, [data], [opts], [cb] )

db.executeWrite( sql, [data], [opts], [cb] )

db.onReady( cb )

db.end( [cb] )

Table

db.table.count( [opts], [cb] )

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

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

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

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

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

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

Row

row.delete( [cb] )

row.hydrate( propertyName, [cb] )

row.save( [cb] )

row.set( data )

row.update( data, [cb] )

Known Issues

Stats

Development practices

Releases

Contributors

oreo Release 0.2.1

Release 0.2.1 Toggle Dropdown 0.7.1 0.7.0 0.6.4 0.6.3 0.6.2 0.6.1 0.6.0 0.5.3 0.5.2 0.5.1

Documentation

Features

Database Support

Installation

Quick Example

Documentation

Usage

Db

Table

Row

Full Example

Usage

oreo( opts, [cb] )

Db

db.execute( sql, [data], [opts], [cb] )

db.executeWrite( sql, [data], [opts], [cb] )

db.onReady( cb )

db.end( [cb] )

Table

db.table.count( [opts], [cb] )

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

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

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

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

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

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

Row

row.delete( [cb] )

row.hydrate( propertyName, [cb] )

row.save( [cb] )

row.set( data )

row.update( data, [cb] )

Known Issues

Stats

Development practices

Releases

Contributors

oreo
Release 0.2.1

Release 0.2.1

0.7.1

0.7.0

0.6.4

0.6.3

0.6.2

0.6.1

0.6.0

0.5.3

0.5.2

0.5.1