My MySQL Process

Run your code every two lines.

Or something like that anyway. That's the instruction we get from our tech mentor, and it resonates, because I find that when I'm not so comfortable with a tool, I put together testable blocks of code and work from the ground-up.

It's been a while since I've used MySQL and it's the first time that I've done it in a Node environment, so that been my M.O. in these full-stack sprints. In this post I'll demonstrate that process by building a process for a client to query a database. A few more reps/projects and it'll be second nature again, but until then, here's how I've been going:

Create the database and start the server

I'm not going to break that down here, we'll just take for granted that we have a working db and server with at least one valid record.

Let's also say that all necessary imports exist.

Can I Connect to the Local Database?

Using a reference like this, establish a connection to the db.

//index.js
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: 'checkout'
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

Can I Perform a Basic Query?

All of this code builds sequentially. So picture this getting added below the previous code, and so on.

var getAll = () => {
  con.query('SELECT * FROM user_info', function(err, results){
    console.log(results)
  }
  )}

As basic as it gets. Again, I'm looking for validation of the basics here.

I should mention that what I'm doing is running index.html (node index) and verifying the results in the terminal.

If I get the result that I'm looking for, then I know that I have something that will allow me to query from anywhere. Here I'm doing it manually. The next step is to do it remotely.

Can a client connect to my Express server?

Let's make sure the server is responsive.

//server.js
app.get('/', (req, res) => {
  res.send('Hello World!')
})

I start the server with npm start and perform a get request via postman to 'localhost:3000/'.

I get 'Hello World' in response. Nice.

Can I Trigger the Query via HTTP?

This is the biggie.

I can make requests to the server. I can query the database. Now we've got to connect those two steps.

I'll note that we'll want to use a callback, as a query operation should be done asynchronously so as to not hang up our Express server if the query takes long to perform. The callback will return once the query is successful and then our 'get' handler can do whatever it wants with it.

//server.js
app.get: (req, res) => {
    getAll((err, results) => {
      console.log(results)
    })
  }

//index.js
var getAll = (callback) => {
  con.query('SELECT * FROM user_info', function(err, results){
    callback(err, results)
  }
  )}

Am I seeing the expected results in the log? Great! Now the least step is to send those results back to the client in a response. That takes just one small change.

Can I Deliver the Results Back to the Client?

//server.js
app.get: (req, res) => {
    getAll((err, results) => {
      res(results)
    })
  }

This block only amends the code block directly above.

And that's it!

I'll follow a similar process for building POST/insert functionality: building incrementally and testing/running code as I go. It's a satisfying way to build and also really practical.