Skip to main content

NoSQL Data Stores

So-called "Schema-less" databases allow for arbitrary keys and values within the entries in the database. K/V stores and Objects add additional restrictions.

note

These data stores are capable of storing structured data. Those use cases are covered in the Database demo.

Arbitrary Data to Spreadsheets

There is no natural way to translate arbitrarily shaped schemas to worksheets in a workbook. One common trick is to dedicate one worksheet to holding named keys. For example, considering the JS object:

{
"title": "SheetDB",
"metadata": {
"author": "SheetJS",
"code": 7262
},
"data": [
{ "Name": "Barack Obama", "Index": 44 },
{ "Name": "Donald Trump", "Index": 45 },
]
}

A dedicated worksheet should store the one-off named values:

XXX|        A        |    B    |
---+-----------------+---------+
1 | Path | Value |
2 | title | SheetDB |
3 | metadata.author | SheetJS |
4 | metadata.code | 7262 |

Data Stores

Redis

Redis has 5 core data types: "String", List", "Set", "Sorted Set", and "Hash". Since the keys and values are limited to simple strings (and numbers), it is possible to store complete databases in a single worksheet.

SheetJSRedis.xlsx

Mapping

The first row holds the data type and the second row holds the property name.

Strings can be stored in a unified String table. The first column holds keys and the second column holds values:

XXX|    A    |   B   |
---+---------+-------+
1 | Strings | |
2 | | |
3 | Hello | World |
4 | Sheet | JS |

The SheetJS array-of-arrays representation of the string table is an array of key/value pairs:

let aoa = ["Strings"]; aoa.length = 2; // [ "Strings", empty ]
const keys = await client.KEYS("*");
for(let key of keys) {
const type = await client.TYPE(key);
if(type == "string") aoa.push([key, await client.GET(key)]);
}

Example

Complete Example (click to show)

0) Set up and start a local Redis server

1) Download the following scripts:

2) Install dependencies and run:

npm i --save https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz redis
node SheetJSRedisTest.mjs

Inspect the output and compare with the data in SheetJSRedisTest.mjs.

Open SheetJSRedis.xlsx and verify the columns have the correct data

PouchDB

Database#allDocs is the standard approach for bulk data export. The generated row objects have an additional _id and _rev keys that should be removed.

Nested objects must be flattened. The "Tutorial" includes an example of constructing a simple array.

function export_pouchdb_to_xlsx(db) {
/* fetch all rows, including the underlying data */
db.allDocs({include_docs: true}, function(err, doc) {

/* pull the individual data rows */
const aoo = doc.rows.map(r => {
/* `rest` will include every field from `r` except for _id and _rev */
const { _id, _rev, ...rest } = r;
return rest;
});

/* generate worksheet */
const ws = XLSX.utils.json_to_sheet(aoo);

/* generate workbook and export */
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "SheetJSPouch.xlsx");
});
}
Complete Example (click to show)

0) Download the "Working Version" from the Getting Started guide.

ZIP

The ZIP file should have MD5 checksum ac4da7cb0cade1be293ba222462f109c:

curl -LO https://github.com/nickcolley/getting-started-todo/archive/master.zip
md5sum master.zip || md5 master.zip
### the checksum will be printed

If the download is unavailable, a mirror is available at https://docs.sheetjs.com/pouchdb/master.zip

1) Unzip the master.zip file and enter the folder:

unzip master.zip
cd getting-started-todo-master

2) Edit index.html to reference the SheetJS library and add a button:

index.html
  <body>
<script src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
<button id="xport">Export!</button>
<section id="todoapp">

3) Just before the end of app.js, add a click event listener:

app.js
  if (remoteCouch) {
sync();
}

document.getElementById("xport").addEventListener("click", function() {
db.allDocs({include_docs: true}, function(err, doc) {
const aoo = doc.rows.map(r => {
const { _id, _rev, ... rest } = r.doc;
return rest;
});
const ws = XLSX.utils.json_to_sheet(aoo);
const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "SheetJSPouch.xlsx");
});
});
})();

4) Start a local web server:

npx http-server .

Access http://localhost:8080 from your browser. Add a few items and click the "Export!" button to generate a new file.