search

brianloveswords / streamsql

A streaming, backend agnostic SQL ORM heavily inspired by levelup
MIT License
67 stars 7 forks source link

readme

streamsql Build Status

A streaming, backend agnostic SQL ORM heavily inspired by levelup

Install

$ npm install streamsql

You will also need to install either mysql or sqlite3 depending on which driver you plan on using:

# EITHER: mysql driver
$ npm install mysql

# OR: sqlite3 driver
$ npm install sqlite3

API

Base

DB

Table

base.connect(options)

Establish a database connection

options.driver can either be mysql or sqlite3.

Super Important Note

streamsql loads drivers on demand and does not include them as production dependencies. You will need to have either one mysql (tested against 2.0.0-alpha9) or sqlite3 (tested against 2.1.19) in your package.json in addition to streamsql.

mysql options

See the documentation for the mysql module for full details. The options object will be passed over to that.

const streamsql = require('streamsql')
const db = streamsql.connect({
  driver: 'mysql',
  user: process.env['DB_USER'],
  password: process.env['DB_PASSWORD'],
  database: 'music'
})

sqlite3 options

Takes just one option, opts.filename. This can be set to :memory: for an in-memory database.

const streamsql = require('streamsql')
const db = streamsql.connect({
  driver: 'sqlite3',
  filename: ':memory:',
})

Returns a db object

db.table(localName, definition)

Registers a table against the internal table cache. Note, this does not create the table in the database (nor does it run any SQL at all).

localName is the name the table will be registered under. You can use this later with connection.table() to get a handle for the table.

definition

db.table('friendship', {
  fields: [ 'id', 'screen_name', 'friend' ],
  methods: {
    hifive: function hifive() {
      return this.screen_name + ' deserves a hifive!'
    }
  }
})
function Friendship (data) {
  this.id = data.id
  this.screen_name = data.screen_name
  this.friend = data.friend
}

Friendship.prototype.hifive = function () {
  return this.screen_name + ' deserves a hifive!'
}

db.table('friendship', {
  fields: [ 'id', 'screen_name', 'friend' ],
  constructor: Friendship
})

options.relationships

You can define relationships on the data coming out createReadStream , get or getOne. hasOne relationships will translate to JOINs at the SQL layer, and hasMany will perform an additional query.

options.relationships is an object, keyed by property. The property name will be used when attaching the foreign rows to the main row.

The results of the fulfilled relationship will be attached to the main row by their key in the relationships object. All foreign items will have their methods as you defined them when setting up the table with db.table, or use their configured constructor where applicable.

Example

band table

id | name          | founded | disbanded
---|---------------|---------|-----------
 1 | Squirrel Bait |    1983 |      1988
 2 | Slint         |    1986 |      1992

album table

id | bandId | name          | released
---|--------|---------------|----------
 1 |      1 | Squirrel Bait |     1985
 2 |      1 | Skag Heaven   |     1987
 3 |      2 | Tweez         |     1989
 4 |      2 | Spiderland    |     1991

member table

id | firstName | lastName
---|-----------|----------
 1 | Brian     | McMahon
 2 | David     | Pajo
 3 | Todd      | Brashear
 4 | Britt     | Walford

bandMember table

id | bandId | memberId
---|--------|----------
 1 |      1 |        1
 2 |      1 |        4
 3 |      2 |        1
 4 |      2 |        2
 5 |      2 |        3
 6 |      2 |        4
const band = db.table('band', {
  fields: [ 'name', 'founded', 'disbanded' ],
  relationships: {
    albums: {
      type: 'hasMany',
      local: 'id',
      foreign: { table: 'album', key: 'bandId' }
    },
    members: {
      type: 'hasMany',
      local: 'id',
      foreign: { table: 'member', key: 'id' },
      via: { table: 'bandMember', local: 'bandId', foreign: 'memberId' }
    }
  }
})

const album = db.table('album', {
  fields: [ 'bandId', 'name', 'released' ]
})

const member = db.table('member', {
  fields: [ 'firstName', 'lastName' ],
  relationships: {
    bands: {
      type: 'hasMany',
      local: 'id',
      foreign: { table: 'band', key: 'id' },
      via: { table: 'bandMember', local: 'memberId', foreign: 'bandId' }
    }
  }
})

const bandMember = db.table('bandMember', {
  fields: [ 'bandId', 'memberId' ]
})

// NOTE: for efficiency, relationships are not automatically populated.
// You must pass { relationships: `true` } to fulfill the relationships
// defined on the table at time of `get` or `createReadStream`

band.get({}, {
  debug: true,
  relationships: true
}, function (err, rows) {
  console.dir(rows)
})

Will result in:

[ { id: 1,
    name: 'Squirrel Bait',
    founded: 1983,
    disbanded: 1988,
    albums:
     [ { id: 1, bandId: 1, name: 'Squirrel Bait', released: 1985 },
       { id: 2, bandId: 1, name: 'Skag Heaven', released: 1987 } ],
    members:
     [ { id: 1, firstName: 'Brian', lastName: 'McMahon' },
       { id: 4, firstName: 'Britt', lastName: 'Walford' } ] },
  { id: 2,
    name: 'Slint',
    founded: 1986,
    disbanded: 1992,
    albums:
     [ { id: 3, bandId: 2, name: 'Tweez', released: 1989 },
       { id: 4, bandId: 2, name: 'Spiderland', released: 1991 } ],
    members:
     [ { id: 1, firstName: 'Brian', lastName: 'McMahon' },
       { id: 2, firstName: 'David', lastName: 'Pajo' },
       { id: 3, firstName: 'Todd', lastName: 'Brashear' },
       { id: 4, firstName: 'Britt', lastName: 'Walford' } ] } ]

Returns a table object.

db.table(localName)

Return a previously registered table. If the table is not in the internal cache, db.table will throw an error.

Returns a table object.

table.put(row, [options, [callback]])

Inserts or updates a single row. If callback is not provided, returns a promise.

An insert will always be attempted first. If the insert fails with an duplicate entry error (as tested by the specific driver implementation) and the row contains the table's primaryKey, an update will be attempted

callback will receive two arguments: err, result. Result should have three properties, row, sql, and insertId. This behavior can be changed with the uniqueKey option, see below.

If the result of a put() is an update, the result will have affectedRows instead of insertId.

options

table.get(conditions, [options, [callback]])

table.getOne(conditions, [options, [callback]])

table.getAll([options, [callback]])

Gets all, some, or one row from the table. If callback is not provided omitted, returns a promise.

Example:

// Get all rows, promise style...
const getAlbums = albums.getAll()
getAlbums.then(function(albums){
  // do stuff with albums
}).error(function(err){
  // handle errors
})

// ...or use callback style
albums.getAll(function(error, albums){
  if (error) {
    // handle errors
    return;
  }
  // do stuff with albums
})

conditions

conditions can be in any number of forms depending on how you're trying to select things

Simple, uses = for comparison:

albums.get({ artist: 'Hookworms' }, function(err, rows){ ... })

Explicit comparison operation:

albums.get({
  artist: {
    value: 'Hookworms',
    operation: '=',
  },
  release_year: {
    operation: '<=',
    value: 2012
  }
}, function(err, rows){ ... })

Implicit in comparison:

albums.get({
  artist: [
    'My Bloody Valentine',
    'Slowdive',
    'Ride'
  ]
}, function(err, rows){ ... })

Multiple conditions on a single column:

albums.get({
  artist: 'David Bowie',
  release_year: [{
    operation: '>=',
    value: 1976
  }, {
    operation: '<='
    value: 1978
  }]
}, function(err, rows){ ... })

**Simple OR queries

All of the examples above are inclusive – the where statement is joined with AND – so the row must match all of the parameters to be included. However, by passing in multiple conditions in an array, it is possible to generate an OR query.

albums.get([
 {
   artist: ['Queen', 'Pink Floyd'],
   release_year: 1975
 },
 {
   artist: ['Electric Light Orchestra', 'Led Zeppelin'],
   release_year: 1973
 }
], function(err, rows){ ... })

Raw sql

There's a final option that lets you do whatever you want. Note, you can use $table as a placeholder for the current table so you don't have to hardcode it.

albums.get([
  'SELECT `release_date` AS `date` FROM $table WHERE `title` = ? AND `artist`= ?',
  ['Siamese Dream', 'The Smashing Pumpkins']
], function(err, rows){ ... })

options

table.del(conditions, options, [callback])

Deletes rows from the database. If callback is omitted, returns a promise.

Be careful – you can truncate an entire table with this command.

garbage.del({}, function(err){
  // garbage is now empty.
})

table.createReadStream(conditions, options)

Create a ReadStream for the table.

pause() and resume()

pause() and resume() will attempt to operate on the underlying connection when applicable, such as with the mysql driver)

Events

table.createKeyStream(conditions)

Emits a data event for each row with just the primary key of that row.

See above for definition of conditions

table.createWriteStream(options)

Creates a WriteStream to the table.

The write() method on the stream takes row data. When a row is successfully written, a meta event is emitted and passed a meta object containing row, sql and insertId

An internal buffer is not kept, so all calls to write()s will return false to signal a ReadStream to pause(). Once a row has been succesfully stored, a drain event will be emitted.

If options.ignoreDupes, any duplicate key errors will be ignored instead of emitting an error event. Ignored rows will be emitted as dupe events.

Example

const ws = band.createWriteStream()

ws.on('error', function (err) {
  console.log('Oh my!', err)
})

ws.on('close', function () {
  console.log('Done!')
})

ws.write({ name: 'Brian', instrument: 'bass', food: 'burritos' })
ws.write({ name: 'Jeremy', instrument: 'drums', food: 'cheese' })
ws.write({ name: 'Travis', instrument: 'tambourine', food: 'tofu' })
ws.end()

License

MIT

Copyright (c) 2013 Brian J. Brennan

Permission is hereby granted, free of charge, to any person obtaining a
copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:

The above copyright notice and this permission notice shall be included
in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.