gulp-etl-mysql-adapter

This plugin connects to MySQL databases, running SQL queries and extracting the resulting rows to gulp-etl Message Stream JSON files via its .src() function. In the future it will also have .dest() which writes data to the database. It is a gulp-etl wrapper for mysql2.

This is a gulp-etl adapter, but unlike most of the other gulp-etl modules it is not a gulp plugin; it is actually a vinyl adapter--it features a replacement for gulp.src() (and soon gulp.dest() as well). gulp-etl plugins and adapters work with JSON Lines data streams/files which we call Message Streams and which are compliant with the Singer specification. In the gulp-etl ecosystem, taps tap into an outside format or system (in this case, a MySQL database) and convert their contents/output to a Message Stream, targets convert/output Message Streams to an outside format or system, and adapters may do both (functioning as a both a tap and a target). These modules can then be stacked together to convert from one format or system to another, either directly or with tranformations or other parsing in between. Message Streams look like this:

{"type": "SCHEMA", "stream": "users", "key_properties": ["id"], "schema": {"required": ["id"], "type": "object", "properties": {"id": {"type": "integer"}}}}
{"type": "RECORD", "stream": "users", "record": {"id": 1, "name": "Chris"}}
{"type": "RECORD", "stream": "users", "record": {"id": 2, "name": "Mike"}}
{"type": "SCHEMA", "stream": "locations", "key_properties": ["id"], "schema": {"required": ["id"], "type": "object", "properties": {"id": {"type": "integer"}}}}
{"type": "RECORD", "stream": "locations", "record": {"id": 1, "name": "Philadelphia"}}
{"type": "STATE", "value": {"users": 2, "locations": 1}}

Usage

gulp adapters take two parameters: a glob, which is used to locate file(s) in the file system, and an optional config object with settings specific to the adapter. For example: src('*.txt', {buffer:false})

Since this adapter doesn't pull from an existing file, the "glob" parameter is a "pretend" filename (with optional path info) which is assigned to the data file extracted from the server, e.g. mysqlData.jsonl. And the configObj should look like this:

configObj / mysql-settings.json

{
  "buffer": false,
  "sql": "SELECT * FROM customers LIMIT 2;",
  "connection": {
    "host"     : "example.org",
    "user"     : "bob",
    "password" : "secret",
    "database" : "schemaName"
  }
}

You could embed this information in your gulpfile, but we recommend storing it outside of any repo so that you don't accidentally publish it.

gulp-data

This plugin supports the use of the gulp-data api for passing in its configObj parameter. This allows data/options from the pipeline to be used to create options passed to this plugin when it runs.

Node-RED

Node-RED is a low-code, visual programming environment for event-driven applications. Inside Node-RED, go to Manage Palette and search for gulp-etl-mysql-adapter

Sample gulpfile.js

/* Run select query on the server and save the results in a local CSV file */

var gulp = require('gulp')
var mysqlAdapter = require('gulp-etl-mysql-adapter')
var targetCsv = require('gulp-etl-target-csv').targetCsv

// contains secure info; store in parent folder of this project, outside of repo
let configObj = require('../../mysql-settings.json')

exports.default = function() {
    return mysqlAdapter.src('mysqlResults.jsonl',configObj)
    .pipe(targetCsv({ columns:true }))
    .pipe(gulp.dest('output/'));    
}

Under Construction - notes and warnings

• This is an early-stage module, with much functionality to come. Pardon our dust.

• Much of the upcoming feature set for .src() will be modeled after the Singer MySQL tap

• .dest() does not yet exist. Its feature set will be modeled after the Singer PostgreSQL target

• Tests are not yet added

Quick Start for Coding on This Plugin

• Dependencies:
  • git
  • nodejs - At least v6.3 (6.9 for Windows) required for TypeScript debugging
  • npm (installs with Node)
  • typescript - installed as a development dependency
• Clone this repo and run npm install to install npm packages
• Debug: with VScode use Open Folder to open the project folder, then hit F5 to debug. This runs without compiling to javascript using ts-node
• Test: npm test or npm t
• Compile to javascript: npm run build

Testing

We are using Jest for our testing. Each of our tests are in the test folder.

• Run npm test to run the test suites

Note: This document is written in Markdown. We like to use Typora and Markdown Preview Plus for our Markdown work..