streamsql

A streaming, backend agnostic SQL ORM heavily inspired by <a href="https://github.com/rvagg/node-levelup">levelup</a>

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

<a href="#connect"><code>base.connect()</code></a>

DB

<a href="#table"><code>db.table()</code></a>
<a href="#relationships">relationships</a>

Table

<a href="#put"><code>table.put()</code></a>
<a href="#get"><code>table.get()</code></a>
<a href="#get"><code>table.getOne()</code></a>
<a href="#get"><code>table.getAll()</code></a>
<a href="#del"><code>table.del()</code></a>
<a href="#readStream"><code>table.createReadStream()</code></a>
<a href="#keyStream"><code>table.createKeyStream()</code></a>
<a href="#writeStream"><code>table.createWriteStream()</code></a>

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.

<code>definition</code>

primaryKey: the primary key for the table. Defaults to id
tableName: the name of the table in the actual database. Defaults to localName
fields: an array representing all the fields this table has. Example: ['id', 'first_name', 'last_name', 'created_at']
methods: (optional) methods to add to a row object as it gets emitted from the database (when using the default constructor). this in the function context will be a reference to the row. Example:

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

constructor: (optional) method to call when creating a row object as it gets emitted from the database. The default constructor should be sufficient for most scenarios, which returns the data combined with any given methods. Example:

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

<code>options.relationships</code>

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.

type: Either "hasOne" or "hasMany".
foreign: Definition for the right side of the join.
- table: The name of the table. This should be the name you used to register the table with db.table.
- as: How to alias the table when performing the join. This is mostly useful when doing a self-join on a table so you don't get an ambiguity error. Defaults to the name of the table.
- key: The foreign key to use.
local: Definition for the left side of the join. If you're just joining on a key normally found in the current table, this can be a string. If you are doing a cascading join (i.e., joining against a field acquired from a different join) you can use an object here:
- table: The name of the table. Important if you aliased the table with as, use the alias here.
- key: Key to use
via: Used for many-to-many relationships, where a third table is required to maintain data associations:
- table: The name of the linking table, as registered with db.table.
- local: The key in the linking table associated with the local table.
- foreign: The key in the linking table associated with the foreign table.
optional: Whether or not the relationship is optional (INNER vs LEFT join). Defaults to false.

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.

<code>options</code>

uniqueKey: This option changes the way a put() turns in

Streamsql

Install / Use

README