sqlite N-API port - resolve mac issue

[mhdawson]

TryGhost/node-sqlite3#830

[aruneshchandra]

@anisha-rohra investigated and fixed the original problem reported, now working on additional reported problems and also to investigate if we can remove the C++ 11 requirement.

[mhdawson]

mapbox, found issue, we fixed in our repo.

Anisha is working on PR.

[mhdawson]

Jim has been working on this for a while, see TryGhost/node-sqlite3#830 for current state.

[jschlight]

There is now a PR under active work. TryGhost/node-sqlite3#1304

[jschlight]

@NickNaso requested some benchmarks. Here are some rough numbers from the Mocha tests.

Mocha Test	NAN	N-API
should return the correct lastID (insert 5000 rows)	102	111
fuss-free incremental backups work	193	183
retrieve 100,000 rows with Statement#each	270	350
should insert rows with lots of null values	97	81
should insert in parallel	635	805
should prepare a statement and run it 1005 times		47
should have inserted all the rows after synchronizing with serialize()		85
serialize(fn) - should call the callback		129
unicode - should insert all values		107

The NAN and N-API columns show those tests for which Mocha is reporting a "slow" time. The Mocha default "slow" threshold is 75ms. A timer is displayed for those tests exceeding ~37ms.

Both Mocha tests run in about two seconds of wall-clock time.

[jschlight]

The PR has landed.

[jschlight]

Below is the results of the benchmarks I've run as we discussed at the most recent weekly meeting.

Two tests were run, one for NAN and one for N-API, both using Node.js v12.16.2. The NAN test was run using a pre-built, downloadable binary for the currently shipping node-sqlite3 v4.2.0. The N-API test was run using a binary locally built from the current node-sqlite3 master branch that will eventually ship as node-sqlite3 v5.0.0. Both tests were run on a MacBook Pro (Late 2016).

node-sqlite3 has a Mocha suite that runs 159 tests. For this benchmark, the complete suite was run sequentially 20 times. All 20 iterations were run in the same process. The wall-clock time for the NAN tests was 33 seconds, and the wall-clock time for the N-API tests was 38 seconds. This means the wall-clock time for the N-API version is about 15% greater than the NAN version.

Here's what the table headings mean:

Mocha Test The name of the Mocha test.
NAN Mean The mean run time, in ms, of the 20 NAN tests.
NAPI Mean The mean run time, in ms, of the 20 N-API tests.
Diff The difference between the N-API mean run time and NAN mean run time.
% The N-API mean run time expressed as a percentage of the NAN mean run time.
NAN s The standard deviation of the NAN mean run time.
NAPI s The standard deviation of the N-API mean run time.

Mocha Test	NAN Mean	NAPI Mean	Diff	%	NAN s	NAPI s
query properties should return the correct lastID	93	136	43	146	14	8
query properties should return the correct changes count	1	1	1	153	0	0
backup output db created once step is called	4	6	1	134	3	2
backup copies source fully with step(-1)	4	4	0	102	1	1
backup backup db not created if finished immediately	1	0	0	73	0	0
backup error closing db if backup not finished	0	0	0	63	0	0
backup using the backup after finished is an error	1	1	0	110	1	0
backup remaining/pageCount are available after call to step	1	1	0	100	0	0
backup backup works if database is modified half-way through	6	7	1	112	1	1
backup can backup from temp to main	2	2	0	112	1	0
backup can backup from main to temp	1	1	0	133	0	0
backup cannot backup to a locked db	1	2	0	123	0	1
backup fuss-free incremental backups work	203	204	1	100	6	5
backup setting retryErrors to empty disables automatic finishing	3	3	0	105	1	1
backup setting retryErrors enables automatic finishing	3	6	2	174	1	11
backup default retryErrors will retry on a locked/busy db	4	4	0	105	0	1
blob should insert blobs	3	4	0	111	1	1
blob should retrieve the blobs	16	16	-1	96	1	2
cache should cache Database objects while opening	0	1	0	183	0	0
cache should cache Database objects after they are open	0	0	0	133	0	0
constants should have the right OPEN_* flags	0	0	0	50	0	0
constants should have the right error flags	0	0	0	400	0	0
error handling throw when calling Database() without new†	NaN	0	NaN	NaN	NaN	0
error handling should error when calling Database#get on a missing table	0	0	0	50	0	0
error handling Database#all prepare fail	0	0	0	50	0	0
error handling Database#run prepare fail	0	1	0	500	0	1
error handling Database#each prepare fail	0	0	0	133	0	1
error handling Database#each prepare fail without completion handler	0	0	0	33	0	0
error handling Database#get prepare fail with param binding	0	0	0	150	0	0
error handling Database#all prepare fail with param binding	0	0	0	167	0	0
error handling Database#run prepare fail with param binding	0	0	0	200	0	0
error handling Database#each prepare fail with param binding	0	0	0	300	0	0
error handling Database#each prepare fail with param binding without completion handler	0	0	0	17	0	0
each retrieve 100,000 rows with Statement#each	253	355	103	141	12	17
each Statement#each with complete callback	28	39	12	142	3	3
exec Database#exec	1	1	0	145	1	0
exec retrieve database structure	0	1	0	250	0	1
fts should create a new fts4 table	1	1	0	150	1	0
interrupt should interrupt queries	2	2	0	79	4	1
interrupt should throw if interrupt is called before open	0	0	0	50	0	0
interrupt should throw if interrupt is called after close	1	0	0	70	1	0
interrupt should throw if interrupt is called during close	0	0	0	100	0	0
buffer should insert blobs	1	1	0	125	1	1
json should select JSON	0	0	0	500	0	0
map test Database#map() with two columns	1	1	0	177	0	0
map test Database#map() with three columns	1	1	0	167	0	0
named columns should create the table	0	0	0	57	0	0
named columns should insert a value	0	0	0	75	0	0
named columns should retrieve the values	0	0	0	500	0	0
named columns should be able to retrieve rowid of last inserted value	0	0	0	300	0	0
named parameters should create the table	0	0	0	167	0	0
named parameters should insert a value with $ placeholders	0	0	0	100	0	0
named parameters should insert a value with : placeholders	0	0	0	300	0	0
named parameters should insert a value with @ placeholders	0	0	0	Infinity	0	0
named parameters should insert a value with @ placeholders using an array	0	0	0	100	0	0
named parameters should insert a value with indexed placeholders	0	0	0	50	0	0
named parameters should insert a value with autoindexed placeholders	0	0	0	29	1	0
named parameters should retrieve all inserted values	0	0	0	Infinity	0	0
null error should create a table	4	6	2	142	2	2
null error should insert rows with lots of null values	84	89	5	106	35	13
null error should have created the database	0	0	0	NaN	0	0
open/close should not be unable to open an inaccessible database	0	0	0	71	0	0
open/close open and close non-existant database should open the database	0	0	0	225	0	0
open/close open and close non-existant database should close the database	0	0	0	50	0	0
open/close open and close non-existant database should have created the file	0	0	0	0	0	0
open/close open and close non-existant shared database should open the database	0	0	0	233	0	0
open/close open and close non-existant shared database should close the database	0	0	0	200	0	0
open/close open and close non-existant shared database should have created the file	0	0	0	0	0	0
open/close open and close shared memory database should open the first database	0	0	0	75	0	0
open/close open and close shared memory database should open the second database	0	0	0	120	0	0
open/close open and close shared memory database first database should set the user_version	0	0	0	150	0	0
open/close open and close shared memory database second database should get the user_version	0	0	0	200	0	0
open/close open and close shared memory database should close the first database	0	0	0	100	0	0
open/close open and close shared memory database should close the second database	0	0	0	Infinity	0	0
open/close creating database without create flag should fail to open the database	0	0	0	100	0	0
open/close creating database without create flag should not have created the file	0	0	0	100	0	0
open/close open and close memory database queuing should open the database	0	0	0	100	0	0
open/close open and close memory database queuing should close the database	0	0	0	50	0	0
open/close open and close memory database queuing shouldn't close the database again	0	0	0	33	0	0
open/close closing with unfinalized statements should create a table	0	0	0	60	0	0
open/close closing with unfinalized statements should prepare/run a statement	0	0	0	200	0	0
open/close closing with unfinalized statements should fail to close the database	0	0	0	100	0	0
open/close closing with unfinalized statements should succeed to close the database after finalizing	0	0	0	167	0	0
data types should serialize Date()	0	0	0	200	0	0
data types should serialize RegExp()	0	0	0	67	0	0
data types should serialize float 4294967296.249	0	0	0	160	0	0
data types should serialize float 3.141592653589793	0	0	0	200	0	0
data types should serialize float 3924729304762836.5	0	0	0	150	0	0
data types should serialize float 1588183026935	0	0	0	150	0	0
data types should serialize float 912667.394828365	0	0	0	400	0	0
data types should serialize float 2.3948728634826374e+83	0	0	0	33	0	0
data types should serialize float 9.293476892934982e+300	0	0	0	60	0	0
data types should serialize float Infinity	0	0	0	300	0	0
data types should serialize float -9.293476892934982e+300	0	0	0	100	0	0
data types should serialize float -2.3948728634826374e+83	0	0	0	500	0	0
data types should serialize float -Infinity	0	0	0	100	0	0
data types should serialize integer 4294967299	0	0	0	150	0	0
data types should serialize integer 3924729304762836	0	0	0	100	0	0
data types should serialize integer 1588183026935	0	0	0	Infinity	0	0
data types should serialize integer 2.3948728634826374e+83	0	0	0	100	0	0
data types should serialize integer 9.293476892934982e+300	0	0	0	NaN	0	0
data types should serialize integer Infinity	0	0	0	133	0	0
data types should serialize integer -9.293476892934982e+300	0	0	0	50	0	0
data types should serialize integer -2.3948728634826374e+83	0	0	0	100	0	0
data types should serialize integer -Infinity	0	0	0	67	0	0
parallel should create the table	12	2	-11	13	48	1
parallel should insert in parallel	651	686	36	106	40	37
parallel should close the database	0	0	0	125	0	0
parallel should verify that the database exists	0	0	0	50	0	0
prepare invalid SQL should fail preparing a statement with invalid SQL	0	0	0	40	0	0
prepare simple prepared statement should prepare, run and finalize the statement	0	0	0	129	0	0
prepare inserting and retrieving rows should create the table	0	0	0	150	0	0
prepare inserting and retrieving rows should insert 1000 rows	20	36	16	182	2	3
prepare inserting and retrieving rows should prepare a statement and run it 1005 times	22	30	8	139	3	3
prepare inserting and retrieving rows should have retrieved 1005 rows	0	0	0	Infinity	0	0
prepare inserting with accidental undefined should create the table	0	0	0	125	0	0
prepare inserting with accidental undefined should insert two rows	0	0	0	67	0	0
prepare inserting with accidental undefined should retrieve the data	0	0	0	63	0	0
prepare inserting with accidental undefined should have retrieved two rows	0	0	0	Infinity	0	0
prepare retrieving reset() function should retrieve the same row over and over again	10	23	13	224	1	2
prepare retrieving reset() function should have retrieved 10 rows	0	0	0	Infinity	0	0
prepare multiple get() parameter binding should retrieve particular rows	1	1	0	153	0	0
prepare multiple get() parameter binding should have retrieved 10 rows	0	0	0	NaN	0	0
prepare prepare() parameter binding should retrieve particular rows	0	1	0	167	0	1
prepare prepare() parameter binding should have retrieved 1 row	0	0	0	0	0	0
prepare all() should retrieve particular rows	4	8	4	183	1	1
prepare all() should have retrieved all rows	0	0	0	100	0	0
prepare all() should retrieve particular rows	1	1	0	173	0	0
prepare high concurrency should create the table	0	0	0	150	0	0
prepare high concurrency should insert all values	16	24	8	146	7	12
prepare high concurrency should retrieve all values	6	6	0	104	2	3
prepare test Database#get() should get a row	0	0	0	180	0	1
prepare test Database#get() should have retrieved all rows	0	0	0	NaN	0	0
prepare Database#run() and Database#all() should create the table	0	0	0	700	0	0
prepare Database#run() and Database#all() should insert 1000 rows	19	34	15	177	2	3
prepare Database#run() and Database#all() should retrieve all rows	4	8	3	176	0	1
profiling should profile a create table	0	1	0	200	0	1
profiling should profile a select: profile select‡	NaN	NaN	NaN	NaN	NaN	NaN
profiling should profile a select	0	0	0	225	0	0
rerunning statements should create the table	0	0	0	600	0	0
rerunning statements should insert repeatedly, reusing the same statement	0	0	0	100	0	0
rerunning statements should retrieve repeatedly, resuing the same statement	1	1	0	100	0	0
rerunning statements should have inserted and retrieved the right amount	0	0	0	NaN	0	0
scheduling scheduling after the database was closed	0	0	0	140	0	0
scheduling scheduling a query with callback after the database was closed	0	0	0	100	0	0
scheduling running a query after the database was closed	0	0	0	100	0	0
serialize() and parallelize() should toggle	0	0	0	0	0	0
serialize() and parallelize() should insert rows	1	5	4	375	0	1
serialize() and parallelize() should have inserted all the rows after synchronizing with serialize()	24	33	9	135	4	4
serialize(fn) should call the callback	24	39	15	165	2	5
serialize(fn) should have inserted and retrieved all rows	0	0	0	0	0	0
tracing Database tracing	1	1	0	136	0	0
tracing test disabling tracing #1	1	0	0	73	1	0
tracing test disabling tracing #2	0	0	0	150	0	0
unicode should create the table	0	0	0	150	0	0
unicode should insert all values	19	20	1	103	10	10
unicode should retrieve all values	4	4	0	99	2	2
unicode should have inserted and retrieved the correct amount	0	0	0	NaN	0	0
query properties should upsert	0	1	0	122	0	0

† This test succeeds on N-API but fails on NAN.
‡ This is an artifact from the Mocha tests.

[jschlight]

This is the raw data, in JSON format, used to create the table above .

rawdata.txt

[mhdawson]

PR has landed in sqlite repo so closing this.

[jschlight]

node-sqlite3 v5.0.0 with N-API support has been published to npm.

https://github.com/mapbox/node-sqlite3/releases/tag/v5.0.0