Nodejs Sqlite PDF
Nodejs Sqlite PDF
Nodejs Sqlite PDF
Node.jsSQLite3w3resource
w3resource
Node.jsSQLite3
LastupdateonJanuary28201520:08:50(UTC/GMT+8hours)
Introduction
Thisisanode.jsdriver(Asynchronous,nonblockingSQLite3bindings)forsqlite3.Itis
writteninJavaScript,doesnotrequirecompiling.Itprovidesallmostall
connection/queryfromSQLit3.Thisisprobablyoneofthebestmodulesusedfor
workingwithSQLite3databaseandthemoduleisactivelymaintained.
WeassumethatyouhavealreadyinstalledSQLite3andnode.jsonWindowsorLinux
environment.
Hereisanexampletoretrievethenames(firstname,lastname)from'employees'
tablebelongsto'hr'database:
Note:themodulemustbeinstalledbeforeuse.
viewplain
01.
02.
03.
04.
05.
06.
07.
08.
09.
copytoclipboard
varsqlite3=require('sqlite3').verbose();
varfile="hr";
vardb=newsqlite3.Database(file);
db.all("SELECTfirst_name,last_nameFROMemployees",function(err,rows){
rows.forEach(function(row){
console.log(row.first_name,row.last_name);
})
});
db.close();
Output:
E:\nodejs>nodetest.js
StevenKing
NeenaKochhar
LexDeHaan
AlexanderHunold
BruceErnst
DavidAustin
http://www.w3resource.com/node.js/nodejssqlite.php
1/9
12/8/2015
Node.jsSQLite3w3resource
ValliPataballa
DianaLorentz
NancyGreenberg
DanielFaviet
Anotherexampletocreateanewdatabase,table,insertandretrieverecords:
viewplain
01.
02.
03.
04.
05.
06.
07.
08.
09.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
copytoclipboard
varsqlite3=require('sqlite3').verbose();
vardb=newsqlite3.Database('abcd');
db.serialize(function(){
db.run("CREATETABLEuser(idINT,dtTEXT)");
varstmt=db.prepare("INSERTINTOuserVALUES(?,?)");
for(vari=0;i<10;i++){
vard=newDate();
varn=d.toLocaleTimeString();
stmt.run(i,n);
}
stmt.finalize();
db.each("SELECTid,dtFROMuser",function(err,row){
console.log("Userid:"+row.id,row.dt);
});
});
db.close();
Output:
E:\nodejs>nodetest.js
Userid:017:14:51
Userid:117:14:51
Userid:217:14:51
Userid:317:14:51
Userid:417:14:51
Userid:517:14:51
Userid:617:14:51
Userid:717:14:51
Userid:817:14:51
Userid:917:14:51
Features
http://www.w3resource.com/node.js/nodejssqlite.php
2/9
12/8/2015
Node.jsSQLite3w3resource
Straightforwardqueryandparameterbindinginterface
FullBuffer/Blobsupport
Extensivedebuggingsupport
QueryserializationAPI
Extensionsupport
WritteninmodernC++andtestedformemoryleaks
Installing
Youcanusenpmtodownloadandinstall:
Thelatestsqlite3package:npminstallsqlite3
GitHub'smasterbranch:npminstallhttps://github.com/mapbox/node
sqlite3/tarball/master
Inbothcasesthemoduleisautomaticallybuiltwithnpm'sinternalversionofnodegyp,
andthusyoursystemmustmeetnodegyp'srequirements.
Supportedplatforms
Thesqlite3moduleworkswithNode.jsv0.10.xorv0.11.x(thoughonlyv0.11.13and
above).BinariesformostNodeversionsandplatformsareprovidedbydefaultvia
nodepregyp.
Nodesqlite3hasbuiltinfunctioncallserializationandautomaticallywaitsbefore
executingablockingactionuntilnootheractionispending.Thismeansthatit'ssafeto
startcallingfunctionsonthedatabaseobjectevenifitisnotyetfullyopened.The
Database#close()functionwillwaituntilallpendingqueriesarecompletedbefore
closingthedatabase.
API
newsqlite3.Database(filename,[mode],[callback])
ReturnsanewDatabaseobjectandautomaticallyopensthedatabase.Thereisno
separatemethodtoopenthedatabase.
Name
Description
filename
Validvaluesarefilenames,":memory:"forananonymousinmemorydatabaseandan
http://www.w3resource.com/node.js/nodejssqlite.php
3/9
12/8/2015
Node.jsSQLite3w3resource
emptystringforananonymousdiskbaseddatabase.Anonymousdatabasesarenot
persistedandwhenclosingthedatabasehandle,theircontentsarelost.
mode
(optional)
Oneormoreofsqlite3.OPEN_READONLY,sqlite3.OPEN_READWRITEand
sqlite3.OPEN_CREATE.ThedefaultvalueisOPEN_READWRITE|OPEN_CREATE.
callback
(optional)
Ifprovided,thisfunctionwillbecalledwhenthedatabasewasopenedsuccessfullyor
whenanerroroccurred.Thefirstargumentisanerrorobject.Whenitisnull,opening
succeeded.Ifnocallbackisprovidedandanerroroccurred,anerroreventwiththe
errorobjectastheonlyparameterwillbeemittedonthedatabaseobject.Ifopening
succeeded,anopeneventwithnoparametersisemitted,regardlessofwhethera
callbackwasprovidedornot.
sqlite3.verbose()
Setstheexecutionmodetoverbosetoproducelongstacktraces.Thereisnowayto
resetthis.Seethewikipageondebuggingformoreinformation.
CloseDatabase
Database#close([callback])
Closesthedatabase.
callback(optional):Ifprovided,thisfunctionwillbecalledwhenthedatabasewas
closedsuccessfullyorwhenanerroroccurred.
RunSQLQuery
Database#run(sql,[param,...],[callback])
RunstheSQLquerywiththespecifiedparametersandcallsthecallbackafterwards.It
doesnotretrieveanyresultdata.ThefunctionreturnstheDatabaseobjectforwhichit
wascalledtoallowforfunctionchaining.
Name
Description
sql
TheSQLquerytorun.IftheSQLqueryisinvalidandacallbackwaspassedtothe
function,itiscalledwithanerrorobjectcontainingtheerrormessagefromSQLite.Ifno
callbackwaspassedandpreparingfails,anerroreventwillbeemittedontheunderlying
Statementobject.
param,
...
(optional)
WhentheSQLstatementcontainsplaceholders,youcanpasstheminhere.Theywill
beboundtothestatementbeforeitisexecuted.Therearethreewaysofpassingbind
parameters:directlyinthefunction'sarguments,asanarray,andasanobjectfor
namedparameters.
http://www.w3resource.com/node.js/nodejssqlite.php
4/9
12/8/2015
Node.jsSQLite3w3resource
RunsSQLquerywithspecifiedparameters
Database#get(sql,[param,...],[callback])
RunstheSQLquerywiththespecifiedparametersandcallsthecallbackwiththefirst
resultrowafterwards.ThefunctionreturnstheDatabaseobjecttoallowforfunction
chaining.
Database#all(sql,[param,...],[callback])
RunstheSQLquerywiththespecifiedparametersandcallsthecallbackwithallresult
rowsafterwards.ThefunctionreturnstheDatabaseobjecttoallowforfunction
chaining.
Database#each(sql,[param,...],[callback],[complete])
RunstheSQLquerywiththespecifiedparametersandcallsthecallbackwithforeach
resultrow.ThefunctionreturnstheDatabaseobjecttoallowforfunctionchaining.
RunsSQLqueriesinthesuppliedstring
Database#exec(sql,[callback])
RunsallSQLqueriesinthesuppliedstring.Noresultrowsareretrieved.Thefunction
returnstheDatabaseobjecttoallowforfunctionchaining.Ifaqueryfails,no
subsequentstatementswillbeexecuted.
PreparingSQLstatement
Database#prepare(sql,[param,...],[callback])
PreparestheSQLstatementandoptionallybindsthespecifiedparametersandcalls
thecallbackwhendone.ThefunctionreturnsaStatementobject.
Statement#bind([param,...],[callback])
Bindsparameterstothepreparedstatementandcallsthecallbackwhendoneorwhen
anerroroccurs.ThefunctionreturnstheStatementobjecttoallowforfunction
chaining.Thefirstandonlyargumenttothecallbackisnullwhenbindingwas
successful,otherwiseitistheerrorobject.
Statement#reset([callback])
Resetstherowcursorofthestatementandpreservestheparameterbindings.Use
thisfunctiontoreexecutethesamequerywiththesamebindings.Thefunction
returnstheStatementobjecttoallowforfunctionchaining.Thecallbackwillbecalled
aftertheresetiscomplete.
Statement#finalize([callback])
http://www.w3resource.com/node.js/nodejssqlite.php
5/9
12/8/2015
Node.jsSQLite3w3resource
Finalizesthestatement.Thisistypicallyoptional,butifyouexperiencelongdelays
beforethenextqueryisexecuted,explicitlyfinalizingyourstatementmightbe
necessary.Thismightbethecasewhenyourunanexclusivequery(seesection
Serialization).
Statement#run([param,...],[callback])
Bindsparametersandexecutesthestatement.ThefunctionreturnstheStatement
objecttoallowforfunctionchaining.
Statement#get([param,...],[callback])
Bindsparameters,executesthestatementandretrievesthefirstresultrow.The
functionreturnstheStatementobjecttoallowforfunctionchaining.
Statement#all([param,...],[callback])
Bindsparameters,executesthestatementandcallsthecallbackwithallresultrows.
ThefunctionreturnstheStatementobjecttoallowforfunctionchaining.
Statement#each([param,...],[callback],[complete])
Bindsparameters,executesthestatementandcallsthecallbackforeachresultrow.
ThefunctionreturnstheStatementobjecttoallowforfunctionchaining.
Debugging
Writingasynchronousfunctionsusingthethreadpoolunfortunatelyalsoremovesall
stacktraceinformation,makingdebuggingveryhardsinceyouonlyseetheerror
message,notwhichstatementcausedit.Tomitigatethisproblem,nodesqlite3hasa
verbosemodewhichcapturesstacktraceswhenenqueuingqueries.Toenablethis
mode,callthesqlite3.verbose(),orcallitdirectlywhenrequiring:varsqlite3=
require('sqlite3').verbose().
Whenyouthrowanerrorfromacallbackpassedtoanyofthedatabasefunctions,
nodesqlite3willappendthestacktraceinformationfromtheoriginalcall,likethis:
http://www.w3resource.com/node.js/nodejssqlite.php
6/9
12/8/2015
Node.jsSQLite3w3resource
vardb=newsqlite3.Database('abcd)
^^^^^^^
SyntaxError:UnexpectedtokenILLEGAL
atModule._compile(module.js:439:25)
atObject.Module._extensions..js(module.js:474:10)
atModule.load(module.js:356:32)
atFunction.Module._load(module.js:312:12)
atFunction.Module.runMain(module.js:497:10)
atstartup(node.js:119:16)
atnode.js:902:3
Notethatyoushouldn'tenabletheverbosemodeinaproductionsettingasthe
performancepenaltyforcollectingstacktracesisquitehigh.Verbosemodecurrently
doesnotaddstacktraceinformationtoerrorobjectsemittedonStatementor
Databaseobjects.
Database#on('trace',[callback])
Thetraceeventisemittedwheneveraqueryisrun.Thefirstandonlyparametertothe
callbackistheSQLstringthatwassenttothedatabase.Theeventisemittedassoon
asthequeryisexecuted(e.g.with.run()or.get()).Asinglestatementmaybeemitted
moreonce.EXPLAINstatementswillnottriggeranevent,soit'ssafetopipeallSQL
queriesyoureceivefromthiseventbackintothedatabaseprefixedwithaEXPLAIN
QUERYPLAN.
Ifyouexecutestatementsfromthiscallback,makesurethatyoudon'tenteraninfinite
loop!
Database#on('profile',[callback])
Theprofileeventisemittedwheneveraqueryisfinished.Thefirstparameteristhe
SQLstringthatwassenttothedatabase,thesecondparameteristhetime
approximatetimeittooktoruninmilliseconds.Theeventisemittedafterthequery
completed.
Ifyouexecutestatementsfromthiscallback,makesurethatyoudon'tenteraninfinite
loop!
Structureof'hr'database:
http://www.w3resource.com/node.js/nodejssqlite.php
7/9
12/8/2015
Node.jsSQLite3w3resource
<<Previous
Next>>
Lookingforsomeothertutorial?
Searchw3resourcetutorials
Search
http://www.w3resource.com/node.js/nodejssqlite.php
8/9
12/8/2015
Node.jsSQLite3w3resource
0Comments
Recommend
w3resource
Share
Login
SortbyNewest
Startthediscussion
Bethefirsttocomment.
Subscribe
AddDisqustoyoursite
Privacy
w3resource201115
Privacypolicy
About
Contact
Feedback
Advertise
http://www.w3resource.com/node.js/nodejssqlite.php
9/9