Adventures of a Hobbyist ~ Part four
Tags: node, learning, opensource
Published: 2018-08-16 11:45:24

---
title: Adventures of a Hobbyist ~ Part four
published: true
description: Playing with MySQL and HTTP
tags: NodeJS, learning, opensource
cover_image: https://thepracticaldev.s3.amazonaws.com/i/vhwf7aff57qnvanormc8.png
series: Adventures of a Hobbyist
---

# Playing with `MySQL` and `HTTP`

![database](https://upload.wikimedia.org/wikipedia/en/7/78/DB-database-icon.png)

## What is this?

Hi, you're reading the fourth part of this series if you're interested in reading what I've written so far you can find the posts here:

- [Preamble](https://dev.to/link2twenty/adventures-of-a-hobbyist--part-one-2e0n)
- [First week of Learning](https://dev.to/link2twenty/adventures-of-a-hobbyist--part-two-2g5a)
- [Thinking about `conf` files](https://dev.to/link2twenty/adventures-of-a-hobbyist--part-three-l1m)

That being said, here's a *very* quick run down for if you're just jumping in here; I work in IT Support, the job requires interaction with the terminal directly but people make mistakes so I wrote some software to do user creation through Samba. Unfortunately, the software felt a little clunky, though it was a hit in the office, and wanted to re-write it properly.  I decided I'd opensource the project and document my journey.

## MySQL

I haven't decided to which database software to use yet (there is an open [issue](https://github.com/ignis-pwa/ideas/issues/4) on GitHub, if you want to have your say), I've had `MongoDB` suggested to me but as I'm used to MySQL I'm look into that too. I think it's important for me to learn new things during this process even if I don't end up using them in the final project.

### Code

As with most things Node, I found a [module](https://www.npmjs.com/package/mysql) I could use and set to work writing a very simple test program.

```javascript
const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'hostname',
  user: 'hostuser',
  password: 'password',
  database: 'database'
});

connection.connect();

let table = "user_details";

connection.query(`SELECT * FROM ${table}`, (err, res) => {
  if (err) throw err;
  for (let result of res) {
    console.log(`${JSON.stringify(result)}`)
  }
});

connection.end();
```
As I said, very simple. It simply outputs a line of data to the console for each user in the table.

## HTTP

I will, in the long run, want to use HTTPS or perhaps even HTTP/2 but I need to get the basics down first, I have a habit of running ahead in a project, getting bogged down/confused and then losing interest. Slow and steady truly does win the race.

### Code

I'm sure many, if not all, of you, have seen a simple `hello world` HTTP example but here it is again, just in case.

```javascript
const http = require('http');

http.createServer((req, res) => {
  res.write('Hello World!');
  res.end();
}).listen(80);
```

Now simply browse to `localhost/` and there we have it a white page with `Hello World!` written at the top. Another very simple, boring, example.

## Putting it together

As both of these examples were straightforward I decided to go off-piste and mix the two together, I haven't quite reached routing yet so my code was in the server script which, I guess, is really bad practice.

I decided I wanted to make a simple script that could look in a MySQL table and output the results to an HTML table. My main thoughts were, it's simple enough to throw together and I'll have something to look at by the end.

### Code
```javascript
const http = require('http');
const mysql = require('mysql');

const database = mysql.createConnection({
  host: 'hostname',
  user: 'hostuser',
  password: 'password',
  database: 'database'
});

let table = "user_details";

// Variable for storing HTML
let outputTable = "";

// Database connection
database.connect();
database.query(`SELECT * FROM ${table}`, (err, res) => {
  if (err) throw err;
  // Push results to `outputTable`
  outputTable += "<div class='table-container'><table>"
  for (let key of Object.keys(res[0])) {
    outputTable += `<th>${key}</th>`
  }
  for (let result of res) {
    outputTable += `<tr>`
    for (let key of Object.keys(result)) {
      outputTable += `<td>${result[key]}</td>`
    }
    outputTable += `</tr>`
  }
  outputTable += "</table>"
});

database.end();

// CSS
let styles = `<style>
  .table-container {
    max-width: 85%;
    width: 1024px;
    border-radius: 4px;
    overflow: hidden;
    margin: 0 auto;
    box-shadow: 0 3px 6px rgba(0,0,0,0.16), 0 3px 6px rgba(0,0,0,0.23);
  }
  table {
    border-collapse: collapse;
    width: 100%;
  }
  th {
    font-size: 18px;
    color: #fff;
    line-height: 1.4;
    padding: 2px 5px;
    background-color: #6c7ae0
  }
  td {
    font-size: 15px;
    color: #808080;
    line-height: 1.4;
    padding: 0px 5px;
  }
  tr:nth-child(even) {
    background-color: #f8f6ff;
  }
</style>`

http.createServer((req, res) => {
  res.write(styles);
  res.write(outputTable);
  res.end();
}).listen(80);
```
I'm sure many of you are cringing at my code but, as I said, this was just a way to put together what I'd learnt as time goes on I'll learn the correct way to do things.

If you do want to set me straight you can leave a comment here and I'll read it and try and incorporate it in my coding practices from now on, anything you contribute will really help.

### The output

Just in case you're curious, here's the table:

![HTML Table](https://thepracticaldev.s3.amazonaws.com/i/ilyisgio31hrd108jmak.png)

## That's another one done.

Woah, this was a long one. Thank you for reading. I was wondering if the titles of these posts needed changing `Adventures of a Hobbyist ~ Part ${n}` is fine but feels like it might be a bit bland, what do you lot think? As always if there is anything I'm doing that's blindingly wrong or you just have something to add feel free to leave a comment or pop over to my [GitHub](https://github.com/ignis-pwa) and leave an issue.

I'm on vacation next week but have actually done a little more this week I could write about, mostly regarding more config stuff continuing on from the last post. I may pencil a post and then publish it next week. If not I won't post until I'm back (08/28).

Thanks again for reading ❤️