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.gitcd json-table-litenpm inpm 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.