Node module for converting Sqlite3 tables to JSON
JavaScript
Latest commit 3ab4bab Nov 4, 2015 @fitnr 1.0.1
Permalink
Failed to load latest commit information.
bin
.gitignore update readme and tests Nov 1, 2015
.jshintrc
.travis.yml allow failures in node 5.0.0 Oct 31, 2015
LICENSE init Apr 20, 2015
README.md
foo.db add column option Apr 20, 2015
index.js
package.json 1.0.1 Nov 4, 2015
test.js

README.md

sqlite-json

Convert Sqlite3 tables to JSON

Command line Interface

  Usage: sqlite-json [options] <database> [sql]

  Export a SQLite table to JSON

  Options:

    -h, --help            output usage information
    -V, --version         output the version number
    -k, --key <key>       Key output to column
    -t, --table <table>   table to query
    -c, --columns <list>  Comma-delimited list of columns to output (Default: all)
    -w, --where <clause>  WHERE clause to add to table query
    -o, --output <file>   Save result to file

One can either pass SQL directly to SQLite or use the table, columns and/or where options to contrust a query.

By default, sqlite-json returns lists of JSON objects. Use the key option to return an object with rows keyed to a value from your table.

By default, the cli outputs to stdout. Use the --output option to specify a destination file.

Examples

 sqlite-json data.db --key ID "SELECT ID, name FROM myTable"
 sqlite-json data.db --table myTable --key ID -o output.json
 sqlite-json data.db -t myTable | other_program > output.json

Note that currently only a single query is supported. Attaching databases or doing multiple queries will produce an error.

API

constructor(database)

Create an instance of sqlite-json.

Example:

const sqliteJSON = require('sqlite-json');
const exporter = sqliteJson('example.db');

database

The path to an SQLite database or a sqlite3 client instance.

Type: sqlite3.Database or string

Example:

const sqlite3 = require('sqlite3');
var db = new sqlite3.Database('./mydb.sqlite3');
exporter = sqliteJson(db);

json(sql, options, callback)

Export JSON from a specified table, and use it in the given callback.

Example:

exporter.json('select * FROM myTable', function (err, json) {
  // handle error or do something with the JSON
  // "[{"foo": 1}, {"foo": 2}, {"foo": 3}]"
});

options.columns

An optional list of columns to output.

Type: Array

Example:

exporter.json({table: 'myTable' columns: ['foo']}, function (err, json) {
  // "[{"foo": 1}, {"foo": 2}, {"foo": 3}]"
});

options.key

An optional column name.

By default, the result is an JSON array of objects. If key is given, a JSON object is returned, each row keyed to the given column value.

Type: string

Example:

exporter.json('myTable', {key: 'foo'}, function (err, json) {
  // "{"1": {"foo": 1}, "2": {"foo": 2}, "3": {"foo": 3}}"
});

options.table

A table to address with the columns, and where options.

Type: string

options.where

A where clause to add to the query.

Type: string

Example:

exporter.json({table: 'myTable', where: 'foo > 1'}, function (err, json) {
  // "[{"foo": 2}, {"foo": 3}]"
});

tables(cb)

List all tables in the current database.

Example:

exporter.tables(function (err, tables) {
  // tables === ['foo', 'bar', 'baz']
});

save(table, filename, cb)

Save the contents of a table to the specified output file.

Example:

exporter.save('table_name', 'data/table_name.json', function (err, data) {
    // Optionally do something else with the JSON.
});