Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Node.js, Express, and SQLite to wrap a REST API around an SQL database
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('data/demodb02');
db.serialize(function() {
db.run("CREATE TABLE IF NOT EXISTS counts (key TEXT, value INTEGER)");
db.run("INSERT INTO counts (key, value) VALUES (?, ?)", "counter", 0);
});
var express = require('express');
var restapi = express();
restapi.get('/data', function(req, res){
db.get("SELECT value FROM counts", function(err, row){
res.json({ "count" : row.value });
});
});
restapi.post('/data', function(req, res){
db.run("UPDATE counts SET value = value + 1 WHERE key = ?", "counter", function(err, row){
if (err){
console.err(err);
res.status(500);
}
else {
res.status(202);
}
res.end();
});
});
restapi.listen(3000);
console.log("Submit GET or POST to http://localhost:3000/data");
@koalahamlet

This comment has been minimized.

Copy link

@koalahamlet koalahamlet commented Aug 5, 2014

@dalelane I get this error

events.js:72
throw er; // Unhandled 'error' event
^
Error: SQLITE_CANTOPEN: unable to open database file

@LuizPanariello

This comment has been minimized.

Copy link

@LuizPanariello LuizPanariello commented Aug 14, 2014

You can try to use 'fs' (file system) to read the file and pass it to sqlite3.

@seddik

This comment has been minimized.

Copy link

@seddik seddik commented Sep 14, 2016

SQLITE_CANTOPEN :

var path = require('path');
var dbPath = path.resolve(__dirname, 'mydb.db')
@jstipsBR

This comment has been minimized.

Copy link

@jstipsBR jstipsBR commented Oct 31, 2016

how i make to get and update all rows ???

@quetzaluz

This comment has been minimized.

Copy link

@quetzaluz quetzaluz commented Sep 10, 2018

how i make to get and update all rows ???

Using db.all can query all rows:
http://www.sqlitetutorial.net/sqlite-nodejs/query/

Using db.run is probably your best bet for any update statements:
http://www.sqlitetutorial.net/sqlite-nodejs/update/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment