Skip to content
SQL CRUD utility for Node.js with schema and topology detection
JavaScript
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
lib
test
.gitignore
.travis.yml
LICENSE
README.md
index.js
oreo.png
package.json

README.md

Oreo

Build Status

Features

  • No dependencies
  • Database-first ORM with no configuration needed
  • Auto-detects tables, columns, 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: ''
}

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

Db

Table

Row


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',
  debug: console.log,
  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).

const oreo = require('oreo')
const db = oreo({
  driver: 'pg',
  hosts: ['localhost:5432'],
  name: 'database',
  user: 'username',
  pass: 'password',
  //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.

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.

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.

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 }
})

See also: hydrate

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.

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!)
You can’t perform that action at this time.