Creating a RESTful API bridge to MySQL using NodeJS/AngularJS

Question

I'm new to RESTful APIs and my application has a problem with it. I want set up the HTTP GET method to retrieve data from mysql, and I think the file app.js has already connected to database (but I don't quite know how the router works). I know that I need to create a router, but I just don't know how to do it like post and get etc, and I am not sure what I am missing. Please help! I appreciate your time and respond!

========================================================================= I think I didn't describe my question, let me rephrase it.

Now I have a model

@models/Users.js
And I have router files

@routers/index.js & user.js`

(they are all default file generated by express)

Also I have a semi-setting file(I am not sure)

@app.js

My goal is when type in /whatever in url can get a respond from http, like a json data or whatever from the database. I know there are a lot of problems in my file(Maybe all of them are not correctly wrote), I just wanna get a help to make it right so that I understand the structure. Thank you for helping!

app.js:

var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var routes = require('./routes/index');
var users = require('./routes/users');

var app = express();

var mysql = require('mysql');
require('./models/Users');



// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', routes);
app.use('/users', users);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  var err = new Error('Not Found');
  err.status = 404;
  next(err);
});

// error handlers

// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
  app.use(function(err, req, res, next) {
    res.status(err.status || 500);
    res.render('error', {
      message: err.message,
      error: err
    });
  });
}

// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
  res.status(err.status || 500);
  res.render('error', {
    message: err.message,
    error: {}
  });
});


module.exports = app;

/models/User.js

var mysql      = require('mysql');
var connection = mysql.createConnection({
    connectionLimit : 100,
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'ajs02'
});

connection.connect();

connection.query('SELECT * from post', function(err, rows, fields) {
  if (!err)
    console.log('The solution is: ', rows);
  else
    console.log('Error while performing Query.');
});

connection.end();

/routers/index.js

var express = require('express');
var router = express.Router();

var mysql = require('mysql');


/* GET home page. */

router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});

router.get('/match', function(req, res) {
    mysql.query('SELECT * FROM ' + req.params.table + ' ORDER BY id DESC LIMIT 20', req.params.id, function(err, rows, fields) {
        var user = new User(req.postId);


        res.send(user)
        connection.release();
    });
});
router.get('/hw', function(req, res) {
  res.send('hello world');
});

module.exports = router;

brandonscript · Accepted Answer · 2015-07-24 03:28:42Z

4

RESTful APIs work on the same principles as CRUD (create, read, update, delete) operations which your database already supports. You just need to translate these SQL operations into RESTful (HTTP request) verbs like GET, PUT, POST, and DELETE.

Consider:

// a GET request = a database READ or (a.k.a SELECT)
app.get('/path', function(req, res) {
    connection.query('SELECT * FROM ' + req.params.table + ' ORDER BY id DESC LIMIT 20', req.params.id, function(err, rows, fields) {
        res.json('.. assume you translated your database response a javascript object .. ')
        connection.release();
    });
});

// a POST request = a database CREATE (a.k.a INSERT)
app.post('/path', function(req, res) {
    connection.query('INSERT INTO ' + req.params.table + ' SOME OTHER PARTS OF YOUR SQL QUERY', req.params.id, function(err, rows, fields) {
        res.json('.. assume you translated your database response a javascript object again .. ')
        connection.release();
    });
});

// a PUT request = a database UPDATE
app.put('/path', function(req, res) {
    connection.query('UPDATE ' + req.params.id + ' SOME OTHER PARTS OF YOUR SQL QUERY', req.params.id, function(err, rows, fields) {
        res.json('.. assume you translated your database response a javascript object yet again .. ')
        connection.release();
    });
});

// a DELETE request = a database DELETE
app.delete('/path', function(req, res) {
    connection.query('DELETE FROM ' + req.params.table + ' SOME OTHER PARTS OF YOUR SQL QUERY', req.params.id, function(err, rows, fields) {
        res.json('.. assume you translated your database response a javascript object once again .. ')
        connection.release();
    });
});

As for routes, start with the Express Routing Guide. At the basic level, a route defined (e.g. /path) corresponds to the request URI: http://yourserver.com/path). It also supports wildcards, named parameters, and regular expressions like: /path/:id, /path/*, and /path/id_.*$ respectively.

edited Jul 24, 2015 at 3:28

answered Jul 24, 2015 at 3:14

brandonscript

73.7k35 gold badges178 silver badges240 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Call me Newbie Over a year ago

I just updated my question, sorry I didn't explain it well.

brandonscript Over a year ago

Ok, well my answer stands. I'm not going to write out your database queries for you, but this is a good starting point.

Anand Over a year ago

@remus I like the link which you have added +1 from my end

Collectives™ on Stack Overflow

Creating a RESTful API bridge to MySQL using NodeJS/AngularJS

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related