Accepted answer

Try this:

sqlite3 example "DROP TABLE IF EXISTS some_table;";
sqlite3 example "CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, anattr VARCHAR, anotherattr VARCHAR);";
sqlite3 example "INSERT INTO some_table VALUES (NULL, '1stAttr', 'AttrA');";
sqlite3 example "INSERT INTO some_table VALUES (NULL, '2ndAttr', 'AttrB');";

## Create three JSON fragment files
sqlite3 example ".output result_prefix.json" "SELECT '{ \"docs\": ['";
sqlite3 example ".output rslt.json" "SELECT '{ \"_id\": \"someTable_' || SUBSTR(\"000000000\" || id, LENGTH(\"000000000\" || id) - 8, 9) || '\", \"anattr\": \"' || anattr || '\", \"anotherattr\": \"' || anotherattr || '\" },' FROM some_table;";
sqlite3 example ".output result_suffix.json" "SELECT '] }'";

## strip trailing comma of last record
sed -i '$ s/.$//' rslt.json;

## concatenate to a single file
cat result_prefix.json rslt.json result_suffix.json > result.json;

cat result.json;

You should be able simply to paste the above lines onto the (unix) command line, seeing output:

{ "docs": [
{ "_id": "someTable_000000001", "anattr": "1stAttr", "anotherattr": "AttrA" },
{ "_id": "someTable_000000002", "anattr": "2ndAttr", "anotherattr": "AttrB" }
] }

If you have jq installed you can do instead ...

cat result.json | jq .

... obtaining:

  "docs": [
      "_id": "someTable_000000001",
      "anattr": "1stAttr",
      "anotherattr": "AttrA"
      "_id": "someTable_000000002",
      "anattr": "2ndAttr",
      "anotherattr": "AttrB"

You'll find an example of how quickly to initialize PouchDB from JSON files in part 2 of the blog post Prebuilt databases with PouchDB.

So, if you have a CouchDB server available you can do the following;

export COUCH_DB=example;
export COUCH_URL= *** specify yours here ***;
export FILE=result.json;

## Drop database

## Create database

## Load database from JSON file
curl -H "Content-type: application/json" -X POST "${COUCH_URL}/${COUCH_DB}/_bulk_docs"  -d @${FILE};

## Extract database with meta data to PouchDB initialization file
pouchdb-dump ${COUCH_URL}/${COUCH_DB} > example.json

## Inspect PouchDB initialization file
cat example.json | jq .

Obviously you'll need some adaptations, but the above should give you no problems.


Since Couch/Pouch-DB are document-oriented DBs all records aka docs there are just JSON aka JS-objects. In my RN app when I met similar task I just put all docs I wanted to be "prepopulated" in PouchDB in an array of JS-objects, import it as module in my app and then write them during app init to PDB as necessarry docs. That's all prepopulation. How to export your SQL DB records to JSON - you decide, surely it depends on source DB structure and data logic you want to be in PDB.

Related Query

More Query from same tag