Using node.js, express, and MySQL

Question

I am currently in the process of creating an app and I am using node.js, express, and MySQL. I feel that I am in a bit of a rut in connecting my server to my database. Here's how I see things so far

server.js

var express = require('express');
var app = express();

//These are just my static finals so this can be ignored for now
app.use(express.static(__dirname + '/public'));
app.use(express.static(__dirname + '/public/views'));
app.use(express.static(__dirname + '/public/controlers'));

app.listen(3000);

console.log("Listening at 3000")

Here I am setting up a local server on my computer that is listening on port 3000. What I am hoping to do here eventually is handle post requests. My goal is to ensure that post requests are inserted into the database. So I realize that I need to do two things create a database and a schema for my database. For now I just want to create a simple table with an ID and first_name columns. I am using this driver https://github.com/felixge/node-mysql/.

So my next step is creating a connection

dbconnection.js

var mysql = require('mysql')
var app = require('../server.js');

var connection = mysql.createConnection({
    host        : 'localhost',
    port        :  3000,
    user        : 'username',
    password    : 'password',
    database    : 'liveDatabase'
});

connection.connect()
//queries and error handling go here
connection.end()

This is where I lose touch with my program.

Here's what I don't understand:

• I don't get how a connection is being created to my localhost:3000. I see the key values for host and post being assigned to localhost and 3000 and I am requiring my server in the db.js file (var app = require('../server.js');. Is this all that is needed to create a connection? How does it find localhost:3000? I am guessing this is all happening under the hood, but I feel a little stuck here.
• I have confusion about the database as well. Where should my database be created and live in order for .createConnection to be able to find it. Do I create a separate .sql file then just create my database and table(s) there and require(/database/path) in my dbconnection.js?

Any help is appreciated. Thanks!

Answer 1

This is just a start, but this seems to be the crux of the misunderstanding:

• Your server.js is creating a web server that listens on port 3000. Your dbconnection.js is trying to connect on that server on port 3000. That's not right. You want it to connect to your MySQL server instead.

• It seems more logical for server.js to require dbconnection.js rather than the other way around. Then server.js can send/receive info with the database via the required module.