A Node API Example For Node/Angular MVC

//nasty SQL value handling, but Named parameters don't seem to work, ERROR:25 ??


/*
 *   A model class, creates table if needed (no database init needed)
 *   Stores all data in memory, async node/sqlite3 keeps data save
 */
var Model = function( name, model ) {
    this.name = name;
    this.model = model;
    this.data = [];

    //Returns a create table sql statement
    this.toSQLcreate = function( ) {
        var sql = "CREATE TABLE '" + this.name + "' ("
        for(var prop in this.model){
            sql += "'" + prop + "' " + this.model[prop] + ", "
        }
        sql = sql.slice(0,-2);
        sql += ")";

      return sql;
    }

    //Check if table exists in database, of not, create
    this.check = function( ) {
        var t = this;
        db.all("SELECT id FROM '" + t.name + "' LIMIT 0,1", function(err, rows) {
            if( err && err.toString().indexOf("no such table: " + t.name) > -1  ) {
                db.run( t.toSQLcreate() );
            }
        });
    }

    //Check if table exists in database, of not, create
    this.create = function( data ) {
        var sql = "INSERT INTO '" + this.name + "' ("

        //Todo : check if data is valid for model, but should always be as it is given by Angular

        for(var prop in data){
            sql += "" + prop + ", "
        }
        sql = sql.slice(0,-2);
        sql += ") VALUES (";
        for(var prop in data){
            if( this.model[prop].indexOf("INTEGER" > -1 )
                sql += data[prop] + ", "
            else
                sql += "'" + data[prop] + "', "
        }
        sql = sql.slice(0,-2);
        sql += ")";

        db.run(sql);
    }

    this.update = function( data ) {
        var sql = "UPDATE '" + this.name + "' SET"

        //Todo : check if data is valid for model, but should always be as it is given by Angular

        for(var prop in data){
            if( this.model[prop].indexOf("INTEGER" > -1 )
                sql += prop + " = " + data[prop] + ", "
            else
                sql += prop + " = '" + data[prop] + "', "
        }
        sql = sql.slice(0,-2);
        sql += " WHERE id = " + data.id;

        db.run(sql);
    }

    this.delete = function( schedule_id ) {
        db.run("DELETE FROM '" + this.name + "' WHERE id = " + schedule_id);
        var cnt = 0;
        for( s in this.data ) {
            if( s.id == schedule_id ) {
                this.data.splice(cnt,1);
                break;
            }
            cnt++;
        }
    }

    //Put all database data in data array
    this.all = function( ) {
        var t = this;
        db.all("SELECT * FROM '" + t.name + "'", function(err, rows) {
            t.data = rows;
        });
    }

    this.find = function( what ) {
        var sql = "SELECT * FROM '" + this.name + "'"

        if( what.length ) {
            sql += " WHERE"
            for(var prop in what){
                if( this.model[prop].indexOf("INTEGER" > -1 )
                    sql += prop + " = " + data[prop] + " AND "
                else
                    sql += prop + " = '" + data[prop] + "' AND "
            }
            sql = sql.slice(0,-5);
        }

        var found = [];
        db.all(sql, function(err, rows) {
            found = rows;
        });

        return found;
    }


    this.check();
    this.all();


}



// classes ========================
var Schedule = function( model ) {
    this.model = model;
}




// set up ========================
var express        = require('express');
var app            = express();
var sqlite3        = require('sqlite3').verbose();
var morgan         = require('morgan');
var bodyParser     = require('body-parser');
var methodOverride = require('method-override');



// configuration =================
var db = new sqlite3.Database('newton.db');

app.use(express.static(__dirname + '/public'));                 // set the static files location /public/img will be /img for users
app.use(morgan('dev'));                                         // log every request to the console
app.use(bodyParser.urlencoded({'extended':'true'}));            // parse application/x-www-form-urlencoded
app.use(bodyParser.json());                                     // parse application/json
app.use(bodyParser.json({ type: 'application/vnd.api+json' })); // parse application/vnd.api+json as json
app.use(methodOverride());


// define model =================
var Schedule = new Schedule(
        new Model('Schedule',
            {
            id : 'INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL',
            description : 'TEXT NOT NULL',
            planned_on : 'DATETIME NOT NULL',
            time_estimated : 'INTEGER NOT NULL',
            }
        )
);



// listen (start app with node server.js) ======================================
app.listen(8080);
console.log("App listening on port 8080");



// routes ======================================================================

/*
 *  API Calls
 */

//Get all schedules
app.get('/api/schedule', function(req, res) {
    res.json( Schedule.model.find() );
});

//Createa a schedule with JSON POST
app.post('/api/schedule', function(req, res) {
    // create a todo, information comes from AJAX request from Angular
    Schedule.create( JSON.parse( req.body.text ) , function() {
        res.json(Schedule.model.find());
    });

});


// delete a todo
app.delete('/api/schedule/:id', function(req, res) {
    Schedule.delete( req.params.schedule_id );
});


app.get('*', function(req, res) {
    res.sendfile('./public/index.html'); // load the single view file (angular will handle the page changes on the front-end)
});