d3x0r/gun-db

sqlite storage adapter for Gun database (javascript)

gun-db

ODBC/Sqlite native persistence layer for gun! GUN is an Open Source Firebase with swappable storage engines (level, SQLite, etc.) that handles data synchronization across machines / devices.

Get it by

npm install gun-db

Use by

var Gun = require('gun');
require('gun-db');

var gun = Gun({
  file: false // turn off pesky file.js data.json default
  , db: {
    file: "gun.db"
    exclusive : false // default
  }
});

If you want to have maximum speed, you can set exclusive, which will gain about 30-40% speed; but you're only allowed one instance of Gun against this database. You can open multiple instances if they don't have the same database name.

Check the gun docs on how to read/write data, it will then handle sync automatically for you (even to the browser!). Tip: It is a graph database, so you can do key/value, document, relational, or graph based data - here is a crash course on how to use it.

Enjoy!

Or: Complain about bugs. :)

notes

If the filename is '*.db' it defaults to sqlite if it's not it tries it as a DSN (data source name) and then if that doesn't work falls back to use sqlite filename. ODBC can be provided by providing unixodbc on linux, but requires modifying the build to enable; it is by default only enabled for windows.

It also ends up writing a sql.config file somewhere ... there's options you can set there to enable sql logging (optionally with data returned) which goes to stderr under windows this goes to (/programdata/freedom collective/node/...) probably. If your node.exe is not what your running it will be in a folder that is whatever the program name is minus the last (.*) under not windows it probably just goes to ~

VFS Usage

This is an example of how to open the sqlite database in a virtual filesystem storage; the access to the sqlite database is then memory mapped.

var vfs = require( "sack.vfs" );
var vol = vfs.Volume( "MountName", "vfsFile.dat" );

var Gun = require('gun');
require('gun-db');

var gun = Gun({
  file: false // turn off pesky file.js data.json default
  , db: {
    file: "$sack@MountName$gun.db"
  }
});

/* ... your appcode ... */

Changelog

• 1.0.571 Fix depending on gun from past 0.9.96 is kinda required (moved symbols)
• 1.0.570 Fix depending on gun in the future (98 it's only 96)
• 1.0.569 Update to gun 0.9.98 (internal constant moved)
• 1.0.568 Fix generating input to wrong object (should be ctx, not gun)
• 1.0.567 update to gun 0.9.x
• 1.0.566 for very large nodes, batch results into a single 'in' to gun.
• 1.0.565 disable exclusive by default; add option to enable it
• 1.0.564 test sqlite database without exclusive, but with URI filename and nolock option.
• 1.0.563 remove excess logging
• 1.0.562 store json string in data otherwise simple number types come back as numbers and not strings (invalid graph! error)
• 1.0.561 fix last minute typo
• 1.0.56 fix writing null value and relation; fixed relation restore; remove unused code; optimize existance check
• 1.0.55 More cleanup; if database open fails, don't register handlers.
• 1.0.54 fixed typo
• 1.0.53 a little cleanup; move varibes to closure (bad debug typo)
• 1.0.52 remove noisy logging when record already up to date; post reply acking the transation.
• 1.0.51 Update docs; add gitter badge
• 1.0.4 fix excessively slow load; misported from sqlite.gun.
• 1.0.3 fix database performance options.
• 1.0.2 update to Gun 0.8.3
• 1.0.1 First usable version

Benchmark Results

(sqlite, native filesystem, windows)
__ Small Nodes: 10 Properties Each __
Write 10000 nodes: : 19841ms; 19.841s; 1.984 ms/node; errors: 0.
Read 10000 nodes: : 7690ms; 7.69s; 0.769 ms/node; errors: 0.
Update 10000 nodes: : 22273ms; 22.273s; 2.227 ms/node; errors: 0.
Update single field on 10000 nodes: : 3215ms; 3.215s; 0.321 ms/node; errors: 0.
__ Medium Nodes: 1000 Properties Each __
Write 100 nodes: : 18155ms; 18.155s; 179.752 ms/node; errors: 0.
Read 100 nodes: : 15554ms; 15.554s; 154.000 ms/node; errors: 0.
Update 100 nodes: : 21044ms; 21.044s; 208.356 ms/node; errors: 0.
Update single field on 100 nodes: : 245ms; 0.245s; 2.426 ms/node; errors: 0.
__ Large Nodes: 10000 Properties Each __
Write 10 nodes: : 20773ms; 20.773s; 1888.455 ms/node; errors: 0.
Read 10 nodes: : 17342ms; 17.342s; 1576.545 ms/node; errors: 0.
Update 10 nodes: : 22796ms; 22.796s; 2072.364 ms/node; errors: 0.
Update single field on 10 nodes: : 1919ms; 1.919s; 174.455 ms/node; errors: 0.


(sqlite, vfs)
__ Small Nodes: 10 Properties Each __
Write 10000 nodes: : 11667ms; 11.667s; 1.167 ms/node; errors: 0.
Read 10000 nodes: : 7086ms; 7.086s; 0.709 ms/node; errors: 0.
Update 10000 nodes: : 13439ms; 13.439s; 1.344 ms/node; errors: 0.
Update single field on 10000 nodes: : 2530ms; 2.53s; 0.253 ms/node; errors: 0.
__ Medium Nodes: 1000 Properties Each __
Write 100 nodes: : 12935ms; 12.935s; 128.069 ms/node; errors: 0.
Read 100 nodes: : 5792ms; 5.792s; 57.347 ms/node; errors: 0.
Update 100 nodes: : 16061ms; 16.061s; 159.020 ms/node; errors: 0.
Update single field on 100 nodes: : 200ms; 0.2s; 1.980 ms/node; errors: 0.
__ Large Nodes: 10000 Properties Each __
Write 10 nodes: : 16134ms; 16.134s; 1466.727 ms/node; errors: 0.
Read 10 nodes: : 7653ms; 7.653s; 695.727 ms/node; errors: 0.
Update 10 nodes: : 21076ms; 21.076s; 1916.000 ms/node; errors: 0.
Update single field on 10 nodes: : 1962ms; 1.962s; 178.364 ms/node; errors: 0.


(sqlite, vfs, with encryption)
__ Small Nodes: 10 Properties Each __
Write 10000 nodes: : 14899ms; 14.899s; 1.490 ms/node; errors: 0.
Read 10000 nodes: : 7301ms; 7.301s; 0.730 ms/node; errors: 0.
Update 10000 nodes: : 18248ms; 18.248s; 1.825 ms/node; errors: 0.
Update single field on 10000 nodes: : 3224ms; 3.224s; 0.322 ms/node; errors: 0.
__ Medium Nodes: 1000 Properties Each __
Write 100 nodes: : 20645ms; 20.645s; 204.406 ms/node; errors: 0.
Read 100 nodes: : 5952ms; 5.952s; 58.931 ms/node; errors: 0.
Update 100 nodes: : 27732ms; 27.732s; 274.574 ms/node; errors: 0.
Update single field on 100 nodes: : 350ms; 0.35s; 3.465 ms/node; errors: 0.
__ Large Nodes: 10000 Properties Each __
Write 10 nodes: : 28288ms; 28.288s; 2571.636 ms/node; errors: 0.
Read 10 nodes: : 9496ms; 9.496s; 863.273 ms/node; errors: 0.
Update 10 nodes: : 37709ms; 37.709s; 3428.091 ms/node; errors: 0.
Update single field on 10 nodes: : 3587ms; 3.587s; 326.091 ms/node; errors: 0.

gun-db's Issues

*.db file is empty, tables empty

Hi. I'm getting an empty SQLite file and empty tables when running default config and/or sack.vfs. Appears the data is in memory, but not writing to SQLite file.. the *.db file doesn't appear to write to disk after put events. When should this persist to disk?

Example:

var vfs = require( "sack.vfs")
var vol = vfs.Volume( "MountName", "vfsFile.dat" )

var gun = Gun({
	file: false,
	db: {
		file: "s1.db",
		exclusive : false
	},
	peers: peers
})

var data = gun.get('data').put({name: 'xyz', age: 41});
var data = gun.get('data').on((val, field)=>{console.log( field,' = ', val );setTimeout( dumpDatabase, 100 )})

function dumpDatabase() {
	var db = vol.Sqlite("s1.db")
	var tables = db.do( "select tbl_name from sqlite_master where type='table'" );
	console.log( "Tables:", tables );
	if (tables.length > 0) {
		var records = db.do("select * from record");
		console.log("records:");
		records.forEach(rec => {
			console.log(`s:${rec.soul} f:${rec.field} v:${rec.value} r:${rec.relation} s:${rec.state}`);
		});
	}
}

Env:

• node v13.8.0
• gun v0.2020.301
• gun-db v1.0.571

Server is starting but writes on the old radata files

This is the code:

teo@teo:~/gun$ cat sqlGunServer.js
var Gun = require('gun');
require('gun-db');
const http = require('http');

var server = http.createServer().listen(9191,'0.0.0.0');

var gun = Gun({
  web:server,
  file: false // turn off pesky file.js data.json default
  , db: {
    file: "sqliteGun.db",
    exclusive : false // default
  }
});

Started it with

teo@teo:~/gun$ npm version
{
  npm: '9.6.5',
  node: '14.20.1',
  v8: '8.4.371.23-node.87',
  uv: '1.42.0',
  zlib: '1.2.11',
  brotli: '1.0.9',
  ares: '1.18.1',
  modules: '83',
  nghttp2: '1.42.0',
  napi: '8',
  llhttp: '2.1.6',
  openssl: '1.1.1q',
  cldr: '40.0',
  icu: '70.1',
  tz: '2021a3',
  unicode: '14.0'
}

teo@teo:~/gun$ cat package.json
{
  "dependencies": {
    "gun": "^0.2020.1239",
    "gun-db": "^1.0.571"
  }
}

teo@teo:~/gun$ node sqlGunServer.js 
Hello wonderful person! :) Thanks for using GUN, please ask for help on http://chat.gun.eco if anything takes you longer than 5min to figure out!
Hello wonderful person! :) Thanks for using GUN, feel free to ask for help on https://gitter.im/amark/gun and ask StackOverflow questions tagged with 'gun'!
AXE relay enabled!
Multicast on 233.255.255.255:8765

The sqlite file sqliteGun.db was not even created, I created it by hand, nothing goes into it, found my new added data in the radata/ files there.

sqlite db does not syncing

i tried to sync sqlite db to another sqlite db but it was not syncing.

this is my code.
********************* sqlite_client1.js ***********************
var vfs = require( "sack.vfs" );
var vol = vfs.Volume( "MountName", "vfsFile.dat" );

var Gun = require('gun');
require('gun-db');

var peers = [
'http://localhost:8081/gun',
'http://localhost:8082/gun',
]
// Create a new gun instance
var gun = Gun(peers);

var gun = Gun({
file: false // turn off pesky file.js data.json default
, db: {
file: "gun.db",
exclusive : false // default
}
});
var data = gun.get('data').put({name: 'abc', age: 41});

function dumpDatabase() {
var db = vfs.Sqlite( "gun.db" );
//var db = vfs.Sqlite( "$sack@Mount$gun.db" );
var tables = db.do( "select tbl_name from sqlite_master where type='table'" );
console.log( "Tables:", tables );
var records = db.do( "select * from record" );
console.log( "records:" );
records.forEach( rec=>{
console.log( s:${rec.soul} f:${rec.field} v:${rec.value} r:${rec.relation} s:${rec.state} );
} );
}

dumpDatabase()

---

********************* sqlite_client2.js ***********************
var vfs = require( "sack.vfs" );
var vol = vfs.Volume( "MountName", "vfsFile.dat" );

var Gun = require('gun');
require('gun-db');

var peers = [
'http://localhost:8081/gun',
'http://localhost:8082/gun',
]
// Create a new gun instance
var gun = Gun(peers);

var gun = Gun({
file: false // turn off pesky file.js data.json default
, db: {
file: "gun2.db",
exclusive : false // default
}
});
var data = gun.get('data').put({name: 'xyz', age: 28});

function dumpDatabase() {
var db = vfs.Sqlite( "gun2.db" );
//var db = vfs.Sqlite( "$sack@Mount$gun.db" );
var tables = db.do( "select tbl_name from sqlite_master where type='table'" );
console.log( "Tables:", tables );
var records = db.do( "select * from record" );
console.log( "records:" );
records.forEach( rec=>{
console.log( s:${rec.soul} f:${rec.field} v:${rec.value} r:${rec.relation} s:${rec.state} );
} );
}

dumpDatabase()

---

server1.js

// http is a standard library
// built into node.
var http = require('http');
//var express = require('express');
// Create a new server instance.
var server = http.createServer();

// Our GUN setup from the last example.
var Gun = require('gun');
//var app = express();
//app.use(Gun.serve).use(express.static(__dirname));
var gun = Gun({web: server});

// Start the server on port 8080.
server.listen(8081, function () {
console.log('Server listening on http://localhost:8081/gun')
})

---

server2.js

// http is a standard library
// built into node.
var http = require('http');
//var express = require('express');
// Create a new server instance.
var server = http.createServer();

// Our GUN setup from the last example.
var Gun = require('gun');
//var app = express();
//app.use(Gun.serve).use(express.static(__dirname));
var gun = Gun({web: server});

// Start the server on port 8082.
server.listen(8082, function () {
console.log('Server listening on http://localhost:8082/gun')
})

---

i am sharing the four files, which i used to sync sqlite db using GUN DB. i ran all files using node command i.e node sqlite_client1.js and node server1.js etc. so am i making any mistake while writing a code. kindly check above code and please tell me.

"npm install gun-db" fails with incorrect SemVer# for gun dependency

The latest version of gun is 0.9.96.

In the package.json file the gun version ^0.9.98 needs to be changed to ^0.9.96

installation fails

$ npm i gun-db

> [email protected] install /Users/davem/Auctionologies/repository/AuctionSoftware/au-2016/experiments/reactive/gun/gun-db-1/node_modules/sack.vfs
> node-gyp configure build

xcode-select: error: tool 'xcodebuild' requires Xcode, but active developer directory '/Library/Developer/CommandLineTools' is a command line tools instance

xcode-select: error: tool 'xcodebuild' requires Xcode, but active developer directory '/Library/Developer/CommandLineTools' is a command line tools instance

  CXX(target) Release/obj.target/sack_vfs/src/vfs_module.o
  CXX(target) Release/obj.target/sack_vfs/src/sack/sack.o
In file included from ../src/sack/sack.cc:21126:
In file included from /Library/Developer/CommandLineTools/usr/include/c++/v1/math.h:310:
In file included from /Library/Developer/CommandLineTools/usr/include/c++/v1/limits:112:
/Library/Developer/CommandLineTools/usr/include/c++/v1/__undef_min_max:17:2: warning: : macro min is
      incompatible with C++. #undefing min [-W#warnings]
#warning: macro min is incompatible with C++.  #undefing min
 ^
/Library/Developer/CommandLineTools/usr/include/c++/v1/__undef_min_max:29:2: warning: : macro max is
      incompatible with C++. #undefing max [-W#warnings]
#warning: macro max is incompatible with C++.  #undefing max
 ^
../src/sack/sack.cc:34964:15: warning: unknown pragma ignored [-Wunknown-pragmas]
#  pragma GCC push_options
              ^
../src/sack/sack.cc:34965:15: warning: unknown pragma ignored [-Wunknown-pragmas]
#  pragma GCC optimize ("O0")
              ^
../src/sack/sack.cc:35032:15: warning: unknown pragma ignored [-Wunknown-pragmas]
#  pragma GCC pop_options
              ^
../src/sack/sack.cc:59227:75: error: too few arguments provided to function-like macro invocation
                        EV_SET64( &ev, pc->Socket, EVFILT_READ, EV_DELETE, 0, 0, (uintptr_t)pc );
                                                                                               ^
/usr/include/sys/event.h:119:9: note: macro 'EV_SET64' defined here
#define EV_SET64(kevp, a, b, c, d, e, f, g, h) do {     \
        ^
../src/sack/sack.cc:59227:4: error: use of undeclared identifier 'EV_SET64'
                        EV_SET64( &ev, pc->Socket, EVFILT_READ, EV_DELETE, 0, 0, (uintptr_t)pc );
                        ^
../src/sack/sack.cc:59229:76: error: too few arguments provided to function-like macro invocation
                        EV_SET64( &ev, pc->Socket, EVFILT_WRITE, EV_DELETE, 0, 0, (uintptr_t)pc );
                                                                                                ^
/usr/include/sys/event.h:119:9: note: macro 'EV_SET64' defined here
#define EV_SET64(kevp, a, b, c, d, e, f, g, h) do {     \
        ^
../src/sack/sack.cc:59228:14: error: use of undeclared identifier 'peer'
                        kevent64( peer->kqueue, &ev, 1, 0, 0, 0, 0 );
                                  ^
../src/sack/sack.cc:59229:4: error: use of undeclared identifier 'EV_SET64'
                        EV_SET64( &ev, pc->Socket, EVFILT_WRITE, EV_DELETE, 0, 0, (uintptr_t)pc );
                        ^
../src/sack/sack.cc:59230:14: error: use of undeclared identifier 'peer'
                        kevent64( peer->kqueue, &ev, 1, 0, 0, 0, 0 );
                                  ^
../src/sack/sack.cc:59426:9: error: no matching function for call to 'kevent64'
                cnt = kevent64( thread->kqueue, NULL, 0, &events, 10, 0, NULL );
                      ^~~~~~~~
/usr/include/sys/event.h:386:9: note: candidate function not viable: no known conversion from
      'kevent64_s (*)[10]' to 'struct kevent64_s *' for 4th argument; remove &
int     kevent64(int kq,
        ^
../src/sack/sack.cc:59457:5: error: use of undeclared identifier 'pc'
                                pc = (PCLIENT)events[n].udata;
                                ^
../src/sack/sack.cc:59732:15: error: no member named 'kevents' in 'sack::network::peer_thread_info'
                this_thread.kevents = CreateDataList( sizeof( ev ) );
                ~~~~~~~~~~~ ^
../src/sack/sack.cc:84672:3: warning: variable 'n' is incremented both in the loop header and in the loop
      body [-Wfor-loop-analysis]
                n++;
                ^
../src/sack/sack.cc:84656:23: note: incremented here
        for( n = 0; blob[n]; n++ )
                             ^
6 warnings and 9 errors generated.
make: *** [Release/obj.target/sack_vfs/src/sack/sack.o] Error 1
gyp ERR! build error
gyp ERR! stack Error: `make` failed with exit code: 2
gyp ERR! stack     at ChildProcess.onExit (/Users/davem/.nvm/versions/node/v8.9.1/lib/node_modules/npm/node_modules/node-gyp/lib/build.js:258:23)
gyp ERR! stack     at emitTwo (events.js:126:13)
gyp ERR! stack     at ChildProcess.emit (events.js:214:7)
gyp ERR! stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:198:12)
gyp ERR! System Darwin 17.3.0
gyp ERR! command "/Users/davem/.nvm/versions/node/v8.9.1/bin/node" "/Users/davem/.nvm/versions/node/v8.9.1/lib/node_modules/npm/node_modules/node-gyp/bin/node-gyp.js" "configure" "build"
gyp ERR! cwd /Users/davem/Auctionologies/repository/AuctionSoftware/au-2016/experiments/reactive/gun/gun-db-1/node_modules/sack.vfs
gyp ERR! node -v v8.9.1
gyp ERR! node-gyp -v v3.6.2
gyp ERR! not ok
npm WARN [email protected] No description
npm WARN [email protected] No repository field.

npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] install: `node-gyp configure build`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] install script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in: