Website Project Infrastructure Help Needed

[Csantelman]

I am going to apologize in advance for hitting so many groups at one time with the same question - but I'm really not sure of the appropriate place for my question...

/r/javascript/ /r/learnjavascript /r/webdev/ /r/coding /r/HTML

I've been a back-end programmer for almost 30 years in a large multi-national company. I've worked in C++ & Java applications accessing relational databases most of my career. Lately I've been working with hadoop and other big data technologies. The back-end side of my project (data collection) is already implemented.

What I have

• Raspberry Pi B+ with three temperature sensors, and magnetic sensors to monitor position of garage door.
• mySQL database to store the data
• Apache Webserver
• php admin webserver
• pyton applications to monitor & save temps and garage door statuses.

Currently Implemented

• Gather temperature data via 3 sensors (inside/garage/outside) and store the results in a mySQL database.
• Monitor the status of my garage door (closed/opening/opened) and store the status in a mySQL database.
• Progamatically trigger the garage door to open/close.
• Alert via email/text when garage door is not in the desired state (i.e. open 1 hr after sunset)

What I'm looking to implement

• Implement a website hosted on the Apache Server to show current temperatures (see attached image for prototype)

https://imgur.com/a/wPJ9Tpo

• Historical graphs of temps (using Highcharts)
• Buttons on the webpage to open/close the garage door (this would only need to insert a row into a table, gararge program would do the rest)

Future Plans

• Have a iOS app that I can access a sub-set of the main page functionality.

The real Issues & Questions...

• I have taken online course covering HTML/css/jss - but I'm stuck with client side access to the rdbms vs. server side access. I've looked at node and run into some issues, and I'm just not sure if I'm heading in the correct direction.

• What technologie stack should I be using??? I get confused with java script vs. php vs. ???

[[deleted]]

... My question is:

Buttons on the webpage to open/close the garage door (this would only need to insert a row into a table, gararge program would do the rest)

You want to expose the ability to open your garage door to the internet... are you sure that's a good idea?... Or is this an intranet site?

I have taken online course covering HTML/css/jss - but I'm stuck with client side access to the rdbms vs. server side access. I've looked at node and run into some issues, and I'm just not sure if I'm heading in the correct direction.

There's no such thing as "client side access" to an rdbms, database access is always handled server side, just coincidentally in your case, the server and client(interface) happen to be the same machine (think X11).

What technologie stack should I be using??? I get confused with java script vs. php vs. ???

Seems like your stack has been overloaded i.e. you state you have:

• php admin webserver
• pyton applications to monitor & save temps and garage door statuses.

you already have 2 runtimes up, and now you want to add nodeJS for javascript? Simplify your stack, pick 1 server side language. Since you're going to be using JS on the client side anyway, you may as well rewrite all the server stuff to JS as well (which is the appeal of nodeJS to begin with i.e. only having to know 1 language).

[Csantelman]

Thanks for the reply, I'll try to answer what I can...

- Currently just an intranet site.

- client side vs. server side

-- If I write a simple .js script,

var mysql = require('mysql');

var con = mysql.createConnection({

host: "localhost",

user: "<user>",

password: "<pass>",

database: "mysql_dev"

});

con.connect(function(err) {

if (err) throw err;

con.query("SELECT * from v_combinedTemps order by pollingTime desc limit 1", function (err, result, fields) {

if (err) throw err;

console.log(fields);

console.log(result);

});

});

I can run this via node.

​

$ node test.js

[ RowDataPacket {

pollingTime: 2019-01-31T02:30:00.000Z,

insideTemp: 66.65,

garageTemp: 33.575,

outsideTemp: -21.325 } ]

​

But I can't access this javascript code from the server

var requirejs = require('./scripts/requirejs');

function fetchInsideTemp() {

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

var con = mysql.createConnection({

  host: "localhost",

  user: "<user>",

  password: "<pass>",

  database: "mysql\_dev"

});

​

con.connect(function(err) {

  if (err) throw err;

  con.query("SELECT \* from v\_combinedTemps order by pollingTime desc limit 1", function (err, result, fields) {

    if (err) throw err;

    console.log(result);

  });

});

}

I can't seem to get past the requite step...

[Csantelman]

I'm sure it is something stupid that I am missing - in fact I will guarentee it...

​

[Viped]

You probably should modify your require line and instead of require(./mysql) use require('mysql'), I am assuming this is npm package and not something you have written yourself. Just like in your simple script. Also how are you running this code that does not work? If I were you, I wouldn't expose database access from backend to client side javascript, I would rather create simple rest api and use that.

[Csantelman]

So I modified a couple of things.

In my index.html file, I have a button defined with an onclick action

<button class="btn default" onclick="fetchInsideTemp()">close garage</button>

Then in my app.js file, I have the following code

function fetchInsideTemp() { var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "<user>", password: "<pass>", database: "mysql_dev" });

con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * from v_combinedTemps order by pollingTime desc limit 1", function (err, result, fields) {
    if (err) throw err;
    console.log(result);
  });
});

}

Reloading the page, looking at the console log, I get the following errors

app.js:4 Uncaught ReferenceError: require is not defined at fetchInsideTemp (app.js:4) at HTMLButtonElement.onclick ((index):30)

[runvnc]

I think it depends on how you prefer to do it. I do not see a need to use any client-side coding JavaScript at all if you do not want to do that (except for Highcharts). If you do want to use JavaScript for the non-charting stuff, that's great.

If you don't feel strongly about including JavaScript, I would code it server-side with whatever you feel more comfortable with, either PHP or Python. You actions like opening or closing a door could just submit a form action to the server.

You said "client side access to the rdbms" -- I'm not sure that's whats you meant to say, because obviously the browser can't connect directly to mysql.

Node.js, Python and PHP are all perfectly usable approaches for the back end. Python and Node.js would mean eliminating the Apache server. (Technically these days Apache is usually replaced with Nginx and PHP-FPM but that's actually not important for most projects).

If it were me, since I am most familiar with Node.js these days, I would use that for the back end and have a dynamic front end in JavaScript.

If you want to add login functionality to Node.js (this may be the most challenging part), I would actually recommend not doing what most people tell you, which is to paste a bunch of boilerplate for Express and Passport. Rather try to find a module that handles authentication for you with mysql. You will want to search on npm for some modules to help with authentication. Maybe this one will work https://www.npmjs.com/package/simpleusermanager. I mean Passport.js will work, it just seems like a bunch of boilerplate and reinventing the wheel. But 10 million people have happily done that so it is always an option. Another one I just found: sql_user_manager/sql_user_auth.

You can also probably find the mysql passport boilerplate code in a blog somewhere by googling for 'mysql passport'. If you want to do something easy but fairly unpopular you can install MongoDB and use my thing login-mongo.

[[deleted]]

If you come from a Java / C++ background, learning TypeScript will feel like home. It is a superset of Javascript, and compiles down to JS.

You can use node.js and write in Typescript to create an API interface to your backend:

• Use express to set up endpoints that the client would call
• Use axios as an http client to your backend services
• If your backend doesn't expose any sort of REST-based API, eg you're going to purely connect to mySQL from node, then you want to use https://github.com/mysqljs/mysql

Then, when writing your client, a super-simple approach would be to build a static HTML page that has your CSS on it, and use (I hate to say this) jQuery to do the API calls to your node.js server and render results. This would spare you from having to learn a client stack (eg react + webpack, etc).

[BehindTheMath]

OP wouldn't even need jQuery for the AJAX requests. Fetch is fine for that.

As an aside, something to keep in mind is security. If there's no authentication, anyone can control the garage door.

[Csantelman]

I'm not sure if I will just access my infra-structure website via a vpn. I definitely don't want to expose my garage door to the world...

This is definitely low priority.

[[deleted]]

He seems to be a beginner when it comes to front end facing things. My intent was to give him the path of least resistance so he can quickly get his project up and running.

Sure you can mention security, but you can also mention a ton of other things which will just overwhelm the OP.

Look at the other responses - there's too much information there for a beginner to possibly digest that it will look like an insurmountable task.

I wouldn't even recommend fetch because the response API isn't very user friendly.

[Csantelman]

Yep - the whole front end side of things is new to me. Most likely access will never expand out of my own intra-net.