So far in this series all of our database accesses have been reads. Let's turn our attention to writing things to a database from GraphQL.

A write in GraphQL terminology is referred to as a mutation. A mutation in GraphQL is defined by its name, its input parameters, and by the type of object it returns. A GraphQL mutation could touch a single table, multiple tables, or a combination of SQL tables and other non-SQL. The syntax doesn't specify at all what gets changed, just that there are a number of parameters and what you want back from the server after the mutation has succeeded.

Example: Upgrading a seat

In the previous parts of this series we introduced a demo database related to air travel. One obvious example mutation would be making a booking but that's a bit complicated for a tutorial. Instead we'll show how to do something much simpler: upgrade a seat for a specific passenger on a specific flight.

Of course if you want to change seats the new seat has to be available. So first we need a query that shows the open seats on a given flight, and maybe by class of service. Since we've already covered queries in the earlier parts of this series we'll just go ahead and run the new availableSeats query without going through it in detail:

As in the prior parts, if you've cloned the repo and have postgres loaded with the data, you can follow along with:

$ git checkout firstMutation
$ npm start

I noticed that the 3 people traveling on booking 3B54BB had a flight (PG0224) where two of them had seats in business class but poor Tatyana Sorokina was stuck in coach. Let's run the following query and look for available seats in Business class:

query fas(
  $flightNo: String!
  $departureDate: String!
  $departureAirport: String!
  $fareConditions: FareConditions
) {
  availableSeats(
    flightNo: $flightNo
    departureDate: $departureDate
    departureAirport: $departureAirport
    fareConditions: $fareConditions
  ) {
    seatNo
    fareConditions
  }
}

We can see that there are many business class seats available:

Darya is sitting in 3G so let's put Tatyana next to her in 3H.

At this point we need to define a mutation. We'll need to specify:

• the flight details
• the passenger name
• the new seat (we know it's still available but we'll still validate that before committing)

Note that the passenger may or may not already have a boarding pass. If they do, we want to replace it and if they don't we just want to create the new, upgraded boarding pass.

We add the following to our typeDefs to define the semantics of the mutation:

    type Mutation {
      changeSeat(
        flightNo: String!
        departureDate: String!
        departureAirport: String!
        passengerName: String!
        newSeat: String!
      ): BoardingPass
    }

The first 3 parameters are the same ones we used for the availableSeats query - they allow us to look up an individual flight using human-friendly parameters as opposed to the internal flightId reference. Then we need a passengerName to identify the passenger and the string representing the new seat that is being requested.

The javascript that performs the actual mutation is the longest piece of code we've shown in this series of posts. This is primarily due to all the error checking required.

export const changeSeat = async (
  _: object,
  { flightNo, departureDate, departureAirport, passengerName, newSeat }: Props
) => {
  let boardingPass;
  const flight = await findFlight({
    flightNo,
    departureDate,
    departureAirport,
  });
  if (flight) {
    const { flightId } = flight;
    const ticket = await database('ticketFlights')
      .join('tickets', 'ticketFlights.ticketNo', 'tickets.ticketNo')
      .where({ flightId, passengerName })
      .first();

    if (ticket) {
      const { ticketNo } = ticket;
      const oneSeat = await database('boarding_passes')
        .where({ flightId, ticketNo })
        .first();

      // get the list of seats that are available on this flight
      const openSeats = await availableSeats(null, {
        flightNo,
        departureDate,
        departureAirport,
      });

      // verify that the new seat is available
      const seatIsAvailable =
        openSeats.findIndex(
          ({ seatNo }: { seatNo: string }) => seatNo === newSeat
        ) >= 0;

      if (seatIsAvailable) {
        const trx = await database.transaction();
        try {
          if (oneSeat) {
            // already has a boarding pass, change it to the new seat
            [boardingPass] = await trx('boardingPasses')
              .update({ seatNo: newSeat })
              .where({ flightId, seatNo: oneSeat.seatNo })
              .returning(['*']);
          } else {
            //create a brand new boarding pass
            // the combination of flightId and boardingNo needs to be unique, pick the next highest number
            // ideally we'd pick the lowest unused integer but the max will do for now
            const max = await database('boardingPasses')
              .where({ flightId })
              .max('boardingNo')
              .first();
            const boardingNo = max ? max + 1 : 0;
            [boardingPass] = await trx('boardingPasses')
              .insert({ seatNo: newSeat, flightId, ticketNo, boardingNo })
              .returning(['*']);
          }
          await trx.commit();
          return boardingPass;
        } catch (error) {
          await trx.rollback();
          throw new ApolloError(
            `Error creating pass: ${error.message}`,
            'BOARDING_PASS_ERROR'
          );
        }
      } else
        throw new ApolloError(
          `Seat ${newSeat} is already assigned`,
          'SEAT_TAKEN'
        );
    } else
      throw new ApolloError(
        `Passenger ${passengerName} does not have a ticket on flight ${flightNo} leaving ${departureAirport} on ${departureDate}!`,
        'TICKET_NOT_FOUND'
      );
  } else throw new ApolloError(`No matching flight found!`, 'FLIGHT_NOT_FOUND');
};

This code illustrates several important concepts in GraphQL when interfacing with SQL:

• Using database transactions, ex: const trx = await database.transaction(); In this example the transaction seems a bit superfluous since it only surrounds a single write but it's critical when a mutation touches multiple records.
• Throwing ApolloError() when records don't exist, seat is already taken, etc…
• Reusing a previous query to, in this case, ensure that the seat being requested is actually available.

If we invoke the mutation with the following parameters:

We get the result:

{
  "data": {
    "changeSeat": {
      "seatNo": "3H",
      "boardingNo": 167,
      "ticket": {
        "passenger": {
          "name": "TATYANA SOROKINA"
        }
      }
    }
  }
}

Confirming that Tatyana has her new seat in business class next to her friend.

Now an SQL "purist" might see the above use case as a good place to use a stored procedure. There are several SELECT queries that need to be run to validate the seat change before doing either an INSERT or an UPDATE query and these could all be performed inside a stored procedure. I'll leave the decision about where your business logic needs to live up to you.

Summary

In this part we've introduced the concept of mutations, the CUD in CRUD applications with a fairly simple example. At the bottom of our GraphQL server code here we end up with either an INSERT or an UPDATE. Around that we have a lot of error checking since writes generally carry a much higher risk than reads. GraphQL helps a little bit here since it performs some basic type checking of inputs before they ever get to actual code. We could push some more work onto GraphQL here if we defined custom types for our flightNo and seatNo variables.

What else might a SQL developer want to know about GraphQL? Please let me know in the comments if you want to see a part 5.