Select Endpoint

You're ready to open your database up to the outside world through a web service. Earlier you wrote a web service using Express. You've just written SQL queries in scripts. Now it's time to put those together. The merger starts with imports, loading in the credentials, and starting up a service:

const express = require('express');
const fs = require('fs');
const mysql = require('mysql');

const credentials = JSON.parse(fs.readFileSync('credentials.json', 'utf8'));
const connection = mysql.createConnection(credentials);

const service = express();

connection.connect(error => {
  if (error) {
    console.error(error);
    process.exit(1);
  }
});

// define endpoints...

const port = 5001;
service.listen(port, () => {
  console.log(`We're live in port ${port}!`);
});

The connection.end call does not appear in this script. The connection will stay open indefinitely, for as long as the service is running.

You also have this utility function for converting a database record into a plain object:

function rowToMemory(row) {
  return {
    id: row.id,
    year: row.year,
    month: row.month,
    day: row.day,
    entry: row.entry,
  };
}

You want to define endpoints that will be needed by the client that you haven't written yet. Each time the user loads the client, they will see entries for the current day from years past. To grab just those memories, you add an endpoint at /memories/:month/:day that listens for GET requests:

service.get('/memories/:month/:day', (request, response) => {
  // issue select query
});

In the callback, you must grab the parameters and then issue a SELECT query. The parameters get dropped into the WHERE clause of a prepared statement. Only memories that haven't been soft-deleted are included. You let MySQL sort the memories in reverse chronological order with an ORDER BY clause. If an error occurs, you send back an error object. Otherwise, you send back an array of memory objects:

service.get('/memories/:month/:day', (request, response) => {
  const parameters = [
    parseInt(request.params.month),
    parseInt(request.params.day),
  ];

  const query = 'SELECT * FROM memory WHERE month = ? AND day = ? AND is_deleted = 0 ORDER BY year DESC';
  connection.query(query, parameters, (error, rows) => {
    if (error) {
      response.status(500);
      response.json({
        ok: false,
        results: error.message,
      });
    } else {
      const memories = rows.map(rowToMemory);
      response.json({
        ok: true,
        results: memories,
      });
    }
  });
});

Try setting up your project's index.js with all this code. Run your service. Ensure that you have memories in your database, and then visit your endpoint in your browser. Do you see the memories serialized to JSON?

Also try grabbing the memories through curl with a command like this in your terminal:

curl http://example.com:5001/memories/8/17

Use your own domain name. What happens if you query a day that has no memories?

There are other endpoints you could add. Maybe you want to grab just a single memory from /memories/:id. Maybe you want to grab them all from /memories. For the client that you have in mind, the endpoint at /memories/:month/:day is sufficient.