litesql is a tiny library easing developpement with sqlite databases.
work was based upon the massive-js library. massivejs is a nodejs module and provides an intuitive interface over raw Mysql and Postgre modules.
basically, litesql intends to brings the same interface into sqlite database. undeway the module use the sqlite3 module, meaning you can access all the functions it offers.
Of course you may also use the additional facilities this module offers (otherwise, you wouldn't be here, right?)
Getting started
install it
npm install litesql
then use it
var litesql =require('litesql');
var db =litesql.db(':memory:');
db.serialize(function(){
db.createTable(
// table name
'todos',
// table defintion
{
// shortcut for id INTEGER PRIMARY KEY AUTOINCREMENT
id:'pk',
num:'int',
// column definition can be an object too; you can pass it also 'unique: true'
task:{ type:'text', required:true},
// type alias is managed internally by the library
duedate:'date',
completed:'boolean'
}
).run();
/*
you can also write
var query = db.createTable(...);
query.run( function (err) { ... } );
*/
// helper class
var todos =newlitesql.Table('todos','id', db);
for(var i =1; i <=10; i++){
todos.insert({ num: i, task:'Task #'+i, duedate:newDate(), completed:false}).run();
}
todos.find().all(function(err,tasks){
assert.equal(tasks.length,10);
});
})
So basically, it works always the way you've seen it
Construct a query via helper methods and classes
then execute the query using methods like you are used to ( run, get, all, each )
CRUD methods
We've already seen insert; following how to update an existing record given its primary key; usually (but not necessary) an 'id' column;
db.serialize(function(){
// update by pk (id = 1)
todos.update({ task:'have to finish this'},1/* pk */).run();
// and also find by pk (id = 1)
todos.find(1).get(function(err,todo){
assert.equal(todo.task,'have to finish this');
});
});
You can also update by another condition; here we update all records with num <= 5
db.serialize(function(){
// update all completed todos
// set compteted = true on all records with num <= 5