Insert Endpoint

Testing your service is hard without the ability to add new memories, so you next add an endpoint that will insert a new memory. Adding new records is done via a POST request sent to /memories:

service.post('/memories', (request, response) => {
  // issue insert statement...
});

There are no parameters in the URL. With PATCH requests, the resource ID is usually included in the URL, as in /memories/:id. With POST requests, the memory is not yet in the database. It has no ID.

Since the memory may be large, you expect it to be sent along as a JSON payload rather than through URL parameters. That means you need to add the middleware for unpacking JSON bodies before defining any of the endpoints:

service.use(express.json());

From there, you flesh out the callback. A prepared INSERT statement will take the fields from the JSON payload and form a new memory. Here's your first draft of the endpoint:

service.post('/memories', (request, response) => {
  const parameters = [
    request.body.year,
    request.body.month,
    request.body.day,
    request.body.entry,
  ];

  const query = 'INSERT INTO memory(year, month, day, entry) VALUES (?, ?, ?, ?)';
  connection.query(query, parameters, (error, result) => {
    if (error) {
      response.status(500);
      response.json({
        ok: false,
        results: error.message,
      });
    } else {
      response.json({
        ok: true,
        results: 'It worked!',
      });
    }
  });
});

MySQL will automatically assign the memory an ID. Sending this ID back in the response is a common practice so that the client knows how to identify the resource in future requests.

This ID arrives in your callback through its second parameter. In the SELECT statement, you called this second parameter rows to reflect that multiple records were returned. That name doesn't make much sense for an INSERT statement, which doesn't yield any rows. You generically call it result and wonder if there's a better name. Until you think of a better name, the ID is referenced as result.insertId. Here's your second draft of the endpoint:

service.post('/memories', (request, response) => {
  const parameters = [
    request.body.year,
    request.body.month,
    request.body.day,
    request.body.entry,
  ];

  const query = 'INSERT INTO memory(year, month, day, entry) VALUES (?, ?, ?, ?)';
  connection.query(query, parameters, (error, result) => {
    if (error) {
      response.status(500);
      response.json({
        ok: false,
        results: error.message,
      });
    } else {
      response.json({
        ok: true,
        results: result.insertId,
      });
    }
  });
});

This endpoint works as long as the JSON body includes all four fields. You can write the client so that it always sends a valid payload, but you recognize that this is a web service exposed to the broader world through URLs. Anyone can issue a POST request to your service, and they might not get the payload right. Therefore, you add a minimal check to ensure that all four fields are available before trying to insert the memory:

service.post('/memories', (request, response) => {
  if (request.body.hasOwnProperty('year') &&
      request.body.hasOwnProperty('month') &&
      request.body.hasOwnProperty('day') &&
      request.body.hasOwnProperty('year')) {

    const parameters = [
      request.body.year,
      request.body.month,
      request.body.day,
      request.body.entry,
    ];

    const query = 'INSERT INTO memory(year, month, day, entry) VALUES (?, ?, ?, ?)';
    connection.query(query, parameters, (error, result) => {
      if (error) {
        response.status(500);
        response.json({
          ok: false,
          results: error.message,
        });
      } else {
        response.json({
          ok: true,
          results: result.insertId,
        });
      }
    });

  } else {
    response.status(400);
    response.json({
      ok: false,
      results: 'Incomplete memory.',
    });
  }
});

Try adding this endpoint to your service and restarting it. Because this is a POST request, you can't test it with your browser. Instead, run a command like this in your terminal:

curl --header 'Content-Type: application/json' \
  --data '{"year": 2013, "month": 2, "day": 3, "entry": "Got my first bike!"}' \
  http://example.com:5001/memories

Try querying for all the memories on this month and day. Does the inserted record appear in the results?