json-table-lite v0.3.2

json-table-lite

KISS JSON storage facility for Node.js

History Repeating

Small to medium size projects are often in need of a fast, easy and lightweight, yet reliable way of storing data server side. But choosing the obvious often results into trouble.

What kind of trouble?

• Storing plain JSON files
  • To slow
  • Not easy to use
  • Inefficient
  • To much data in memory
• SQL server
  • Sql syntax overhead
  • Not compatible with json data without extension
  • Overkill
  • To centralized, not portable
  • Counterproductive
• SQLite
  • Sql syntax overhead
  • Doesn't configure automatically
  • Counterproductive
• Mongodb
  • Eats to much cpu when idle
  • Overkill
  • To centralized, not portable
• Redis, LevelDB, nedb, nedb-core, etc
  • To much data in memory
• Realm
  • Not bad, wrong license

Solution

A simple, one dimensional parser wrapper around sqlite3, for storing and retrieving JSON data.

Probabilities

• SQLite and it's Node.js client are fast, lightweight and reliable.
• SQLite is portable.

Why no features for handling multiple tables

• The idea behind this approach is storing a table of JSON data, and not so much storing single values in relational tables.
• You can still handle relations between tables with Javascript just as easy (easier).
• SQLite seems to block subsequent queries while it is writing. By using a separate file for each table, writing tables is non-blocking for others.
• No SQL statement syntax please, and Keep It Simple, Stupid.

Wish

• Count
• Basic operators
• Unit tests

Example

Clone source code from Github and run the example.

git clone git@github.com:guilala/json-table-lite.git
cd json-table-lite
npm i
npm run test

The output should be something like this.

[ 'id', 'furniture' ]
[ 'id', 'age', 'surname', 'name' ]
[ 'id', 'age', 'surname', 'name', 'town' ]
[ { id: 1, age: 30, surname: 'Doe', name: 'Jane', town: null } ]
[ { id: 1, age: 31, surname: 'Doe', name: 'Jane', town: null },
  { id: 2, age: 36, surname: 'Doe', name: 'John', town: 'Brussels' } ]
2
[ { id: 1, age: 31, surname: 'Doe', name: 'Jane', town: null } ]

A getProperties will at least return 'id'. This is the unique identifier index column of SQLite.

The first output shows two properties because a first record with the property 'furniture' has been stored in a db with file name 'test2.db'. This action started last in test.mjs, and runs asynchronous in parallel with the first. It outputs first because the record that gets stored is smaller.

[ 'id', 'furniture' ]

The second output shows four properties because a first record with 'name', 'surname' and 'age' has been stored in a db with file name 'test1.db'. You could also set a custom id as long as it's a unique integer.

[ 'id', 'age', 'surname', 'name' ]

After a second record has been stored, containing a new property, you can see that it's automatically added. In SQLite this adds a new column.

[ 'id', 'age', 'surname', 'name', 'town' ]

The next output is from retrieving Jane's record only.

[ { id: 1, age: 30, surname: 'Doe', name: 'Jane', town: null } ]

Following output is from getting all records after Jane's age has been updated.

[ { id: 1, age: 31, surname: 'Doe', name: 'Jane', town: null },
  { id: 2, age: 36, surname: 'Doe', name: 'John', town: 'Brussels' } ]

A count of records with surname 'Doe' results in two records.

2

Last output shows all records after John's record has been deleted.

[ { id: 1, age: 31, surname: 'Doe', name: 'Jane', town: null } ]

Look into test.mjs to see how this has been achieved.