db-streamer

db-streamer

A cross-db library to stream data into and out of a SQL database. Currently supports streaming data into or out of PostgreSQL, MySQL or SQLite.

Table of Contents

• Installation
• Inserting
• Querying

Installation

In order to use this library, you must also install the additional libraries in your project depending on the database that you use.

PostgreSQL

npm install pg --save
npm install pg-copy-streams --save
npm install pg-query-stream --save
npm install pg-hstore --save

With pg and node v0.10.x

You must also install the package promise-polyfill and write additional code. See here for more details.

MySQL

npm install mysql --save
npm install streamsql --save

SQLite

npm install sqlite3 --save
npm install streamsql --save

Deferred inserting w/ SQLite

For now, deferred inserting with SQLite assumes that a unix shell is available to pipe commands to the sqlite3 binary tool.

Inserting

var dbStreamer = require('db-streamer'),
  connString = 'postgres://streamer:streamer@localhost:5432/streamer-test';

// create inserter
var inserter = dbStreamer.getInserter({
  dbConnString: connString,
  tableName: 'test_table',
  columns: ['a', 'b', 'c']
});

// establish connection
inserter.connect(function(err, client) {

  // push some rows
  inserter.push({a: 1, b: 'one', c: new Date() });
  inserter.push({a: 2, b: 'two', c: new Date() });
  inserter.push({a: 3, b: 'three', c: new Date() });

  // create child table inserter using deferring strategy
  // this is useful to avoid missing foreign key conflicts as a result of race conditions
  var childInserter = dbStreamer.getInserter({
    dbConnString: connString,
    tableName: 'child_table',
    columns: ['a', 'd', 'e'],
    deferUntilEnd: true
  });

  childInserter.push({a: 2, d: 'asdf', e: new Date() });
  childInserter.push({a: 3, d: 'ghjk', e: new Date() });

  childInserter.setEndHandler(callback);

  // set end callback
  inserter.setEndHandler(function() {
    childInserter.end();
  });

  // announce end
  inserter.end();

});

Inserter Config

Key	Description
dbConnString	A database connection string.
tableName	The tablename to insert into.
columns	Array of column names.
primaryKey	Required if using MySQL or SQLite. String of the primary key (defaults to id if omitted).
deferUntilEnd	Boolean (default=false). Stream output to temporary file which is then streamed in all at once into table upon calling end.
sqliteStorage	Required if using SQLite. String of the filename to load data to. Unfortunately, will not work with :memory: (well it will, but all data will be lost after disconnecting, so it's kind of pointless).

Inserter Config (Sequelize Bulk Insert alternative)

Key	Description
useSequelizeBulkInsert	Boolean. Perform the insert using a combination of async.cargo and sequelize bulkInsert. Must provide sequelizeModel parameter too.
sequelizeModel	The sequelize model to perform a bulk insert with.
deferUntilEnd	Boolean (default=false). Pause all cargo iterations until calling end.

Querying

const querier = dbStreamer.getQuerier({
  dbConnString: 'postgres://streamer:streamer@localhost:5432/streamer-test'
})

querier.execute(
  'SELECT * FROM test_table',
  row => console.log,
  err => {
    console.log('done')
  }
)

Querying Config

Key	Description
dbConnString	A database connection string.
sqliteStorage	Required if using SQLite. String of the filename to load data from. Unfortunately, will not work with :memory: (well it will, but a new connection is opened, so there won't be any data to query, so it's kind of pointless).