CODEWITHSUNDEEP
javascriptmysqlsql-servernode.jsrest
Jack47
Jack47

Reputation: 49

Issue connecting to my sql database using a REST api server with Node.js

I'm very new to coding servers and javascript in general but I'm currently trying to set up a REST api server and connect it to my sql database, for the moment I am doing everything locally. I am running ubuntu 18.04 while using NODE js. I have been able to successfully create a REST api and connect to it through an url of a webpage or with Postman. I have created a sql server database through my cmd terminal and have created test data on it. I've been looking at guides to connect the REST api to the database but I think the info I'm giving the api to connect is where my issue is occurring. I am starting with this below as my server.js where i have a folder Controller and a ProductController.js file where I'm handling the route /api/products .

var http = require('http');
var express = require('express');
var app = express();
var port = process.env.port || 3000;
var productController = require('./Controller/ProductController')();
app.use("/api/products", productController);
app.listen(port, function(){
    var datetime = new Date();
    var message = "Server running on Port:- " + port + " Started at :- " + 
datetime;
    console.log(message);   
});

Below is my ProductController.js file. The issue might be here but I believe it is my next file called connect.js the table in my sql database is called 'data' hence the "SELECT * FROM data" part. when I try to GET this data in postman it displays the error i set up "Error while inserting data". so I believe when running I'm not getting data from sql so conn.close() is not being reached.

var express = require('express');
var router = express.Router();
var sql = require("mssql");
var conn = require("../connection/connect")();

var routes = function()
{
    router.route('/')
        .get(function(req, res)
        {
            conn.connect().then(function()
            {
                var sqlQuery = "SELECT * FROM data";
                var req = new sql.Request(conn);
                req.query(sqlQuery).then(function (recordset)
                {
                    res.json(recordset.recordset);
                    conn.close();
                })
                    .catch(function (err) {
                         conn.close();
                         res.status(400).send("Error while inserting data");
                    });
            })
                .catch(function (err) {
                    conn.close();
                    res.status(400).send("Error while inserting data");
                });
        });
    return router;
};
module.exports = routes;

This is my connect.js file below. I have a password for root which is not *** but is correct on my machine. I have changed root's plug in to mysql_native_password in the mysql terminal. I think the server: part is wrong, I've tried commenting it out but still no connection. I do not have SQL Server Management Studio and have not found a way to get my sql server's name through the terminal. I've seen examples that seem to range of what info you need to give the api to connect. If someone has insight on that too that would be appreciated as well. My end goal is to eventually create GET and POST routes for the database and a function to manipulate the POST data but for now I'm just trying to get things connected so I can play around with the data being GET'ed. Thanks for any insight you can give, it is much appreciated.

var sql = require("mssql");
var connect = function()
{
    var conn = new sql.ConnectionPool({
        host: 'localhost'
        user: 'root',
        password: '********',
        server: 'mysql',
        database: 'test'
    });

    return conn;
};

Upvotes: 2

Views: 1467

Answers (2)

Jim
Jim

Reputation: 3684

To diagnose the errors you should add some logging to your catch blocks:

.catch(function (err) {
    console.log('connection error', err); //or Bunyan, Winston, Morgan, etc. logging library
    conn.close();
    let message = "Error while inserting data"
    if (process.env.NODE_ENV === 'development') { //conditionally add error to result message
        message += "\n"+err.toString());
    } 
    res.status(500).send(message);  //use 5xx for server problems, 4xx for things a user could potentially fix
});

And set NODE_ENV in your environment, for example in package.json:

"scripts": {
    "start": "NODE_ENV=production node app.js"
    "start-dev": "NODE_ENV=development node app.js"
}

Upvotes: 0

jkchu
jkchu

Reputation: 98

Looks like you may have some errors in your connect.js file:

var conn = new sql.ConnectionPool({
    host: 'localhost'
    user: 'root',
    password: '********',
    server: 'mysql',
    database: 'test'
});

should be in the format of:

const pool = new sql.ConnectionPool({
    user: '...',
    password: '...',
    server: 'localhost',
    database: '...'
})

Note that you currently have both host and server, looks like only server is needed. Also, server: 'mysql' doesn't make sense if you are connecting to a MSSQL database.

Source: node-mssql documentation

Upvotes: 1

Related Questions