7. CRUD Database Operations

Written by Tim Condon

Chapter 5, “Fluent & Persisting Models”, explained the concept of models and how to store them in a database using Fluent. This chapter concentrates on how to interact with models in the database. You’ll learn about CRUD operations and how they relate to REST APIs. You’ll also see how to leverage Fluent to perform complex queries on your models.

Note: This chapter requires you to use PostgreSQL. Follow the steps in Chapter 5, “Fluent & Persisting Models”, to set up PostgreSQL in Docker and configure your Vapor application.

CRUD and REST

CRUD operations — Create, Retrieve, Update, Delete — form the four basic functions of persistent storage. With these, you can perform most actions required for your application. You actually implemented the first function, create, in Chapter 5.

RESTful APIs provide a way for clients to call the CRUD functions in your application. Typically you have a resource URL for your models. For the TIL application, this is the acronym resource: http://localhost:8080/api/acronyms. You then define routes on this resource, paired with appropriate HTTP request methods, to perform the CRUD operations. For example:

• GET http://localhost:8080/api/acronyms/: get all the acronyms.
• POST http://localhost:8080/api/acronyms: create a new acronym.
• GET http://localhost:8080/api/acronyms/1: get the acronym with ID 1.
• PUT http://localhost:8080/api/acronyms/1: update the acronym with ID 1.
• DELETE http://localhost:8080/api/acronyms/1: delete the acronym with ID 1.

Create

In Chapter 5, “Fluent & Persisting Models”, you implemented the create route for an Acronym. You can either continue with your project or open the TILApp in the starter folder for this chapter. To recap, you created a new route handler in routes.swift:

// 1
app.post("api", "acronyms") { 
  req -> EventLoopFuture<Acronym> in
  // 2
  let acronym = try req.content.decode(Acronym.self)
  // 3
  return acronym.save(on: req.db).map { acronym }
}

Here’s what this does:

1. Register a new route at /api/acronyms/ that accepts a POST request and returns EventLoopFuture<Acronym>.
2. Decode the request’s JSON into an Acronym. This is simple because Acronym conforms to Content.
3. Save the model using Fluent. When the save completes, you return the model inside the completion handler for map(_:). This returns an EventLoopFuture — in this case, EventLoopFuture<Acronym>.

Build and run the application, then open RESTed. Configure the request as follows:

• URL: http://localhost:8080/api/acronyms/
• method: POST
• Parameter encoding: JSON-encoded

Add two parameters with names and values:

• short: OMG
• long: Oh My God

Send the request and you’ll see the response containing the created acronym:

Retrieve

For TILApp, retrieve consists of two separate operations: retrieve all the acronyms and retrieve a single, specific acronym. Fluent makes both of these tasks easy.

Retrieve all acronyms

To retrieve all acronyms, create a route handler for GET requests to /api/acronyms/. Open routes.swift and add the following at the end of routes(_:):

// 1
app.get("api", "acronyms") { 
  req -> EventLoopFuture<[Acronym]> in
  // 2
  Acronym.query(on: req.db).all()
}

Here’s what this does:

1. Register a new route handler that accepts a GET request which returns EventLoopFuture<[Acronym]>, a future array of Acronyms.
2. Perform a query to get all the acronyms.

Fluent adds functions to models to be able to perform queries on them. You must give the query a Database. This is almost always the database from the request and provides a connection for the query. all() returns all the models of that type in the database. This is equivalent to the SQL query SELECT * FROM Acronyms;.

Build and run your application, then create a new request in RESTed. Configure the request as follows:

• URL: http://localhost:8080/api/acronyms/
• method: GET

Send the request to see the acronyms already in the database:

Retrieve a single acronym

Vapor’s parameters integrate with Fluent’s querying functions to make it easy to get acronyms by IDs. To get a single acronym, you need a new route handler. Open routes.swift and add the following at the end of routes(_:):

// 1
app.get("api", "acronyms", ":acronymID") { 
  req -> EventLoopFuture<Acronym> in
  // 2
  Acronym.find(req.parameters.get("acronymID"), on: req.db)
    // 3
    .unwrap(or: Abort(.notFound))
}

Here’s what this does:

1. Register a route at /api/acronyms/<ID> to handle a GET request. The route takes the acronym’s id property as the final path segment. This returns EventLoopFuture<Acronym>.
2. Get the parameter passed in with the name acronymID. Use find(_:on:) to query the database for an Acronym with that ID. Note that because find(_:on:) takes a UUID as the first parameter (because Acronym’s id type is UUID), get(_:) infers the return type as UUID. By default, it returns String. You can specify the type with get(_:as:).
3. find(_:on:) returns EventLoopFuture<Acronym?> because an acronym with that ID might not exist in the database. Use unwrap(or:) to ensure that you return an acronym. If no acronym is found, unwrap(or:) returns a failed future with the error provided. In this case, it returns a 404 Not Found error.

Build and run your application, then create a new request in RESTed. Configure the request as follows:

• URL: http://localhost:8080/api/acronyms/\ (replace the ID with the ID of the acronym you created earlier)
• method: GET

Send the request and you’ll receive the first acronym as the response:

Update

In RESTful APIs, updates to single resources use a PUT request with the request data containing the new information.

Add the following at the end of routes(_:) to register a new route handler:

// 1
app.put("api", "acronyms", ":acronymID") { 
  req -> EventLoopFuture<Acronym> in
  // 2
  let updatedAcronym = try req.content.decode(Acronym.self)
  return Acronym.find(
  	req.parameters.get("acronymID"),
  	on: req.db)
    .unwrap(or: Abort(.notFound)).flatMap { acronym in
      acronym.short = updatedAcronym.short
      acronym.long = updatedAcronym.long
      return acronym.save(on: req.db).map {
        acronym
      }
  }
}

Here’s the play-by-play:

1. Register a route for a PUT request to /api/acronyms/<ID> that returns EventLoopFuture<Acronym>.
2. Decode the request body to Acronym to get the new details.
3. Get the acronym using the ID from the request URL. Use unwrap(or:) to return a 404 Not Found if no acronym with the ID provided is found. This returns EventLoopFuture<Acronym> so use flatMap(_:) to wait for the future to complete.
4. Update the acronym’s properties with the new values.
5. Save the acronym and wait for it to complete with map(_:). Once the save has returned, return the updated acronym.

Build and run the application, then create a new acronym using RESTed. Configure the request as follows:

• URL: http://localhost:8080/api/acronyms/
• method: POST
• Parameter encoding: JSON-encoded

Add two parameters with names and values:

• short: WTF
• long: What The Flip

Send the request and you’ll see the response containing the created acronym:

It turns out the meaning of WTF is not in fact “What The Flip”, so it needs updating. Change the request in RESTed as follows:

• URL: http://localhost:8080/api/acronyms/\

Note: Use the ID from the returned create request.

• method: PUT
• long: What The Fudge

Send the request. You’ll receive the updated acronym in the response:

To ensure this has worked, send a request in RESTed to get all the acronyms. You’ll see the updated acronym returned:

Delete

To delete a model in a RESTful API, you send a DELETE request to the resource. Add the following to the end of routes(_:) to create a new route handler:

// 1
app.delete("api", "acronyms", ":acronymID") { 
  req -> EventLoopFuture<HTTPStatus> in
  // 2
  Acronym.find(req.parameters.get("acronymID"), on: req.db)
    .unwrap(or: Abort(.notFound))
    // 3
    .flatMap { acronym in
      // 4
      acronym.delete(on: req.db)
        // 5
        .transform(to: .noContent)
  }
}

Here’s what this does:

1. Register a route for a DELETE request to /api/acronyms/<ID> that returns EventLoopFuture<HTTPStatus>.
2. Extract the acronym to delete from the request’s parameters as before.
3. Use flatMap(_:) to wait for the acronym to return from the database.
4. Delete the acronym using delete(on:).
5. Transform the result into a 204 No Content response. This tells the client the request has successfully completed but there’s no content to return.

Build and run the application. The “WTF” acronym is a little risqué so delete it. Configure a new request in RESTed as follows:

• URL: http://localhost:8080/api/acronyms/\

Note: Use ID of the WTF acronym from the previous request

• method: DELETE

Send the request; you’ll receive a 204 No Content response.

Send a request to get all the acronyms and you’ll see the WTF acronym is no longer in the database.

Fluent queries

You’ve seen how easy Fluent makes basic CRUD operations. It can perform more powerful queries just as easily.

Filter

Search functionality is a common feature in applications. If you want to search all the acronyms in the database, Fluent makes this easy. Ensure the following line of code is at the top of routes.swift:

import Fluent

Next, add a new route handler for searching at the end of routes(_:):

// 1
app.get("api", "acronyms", "search") { 
  req -> EventLoopFuture<[Acronym]> in
  // 2
  guard let searchTerm = 
    req.query[String.self, at: "term"] else {
    throw Abort(.badRequest)
  }
  // 3
  return Acronym.query(on: req.db)
    .filter(\.$short == searchTerm)
    .all()
}

Here’s what’s going on to search the acronyms:

1. Register a new route handler that accepts a GET request for /api/acronyms/search and returns EventLoopFuture<[Acronym]>.
2. Retrieve the search term from the URL query string. If this fails, throw a 400 Bad Request error.

Note: Query strings in URLs allow clients to pass information to the server that doesn’t fit sensibly in the path. For example, they are commonly used for defining the page number of a search result.

3. Use filter(_:) to find all acronyms whose short property matches the searchTerm. Because this uses key paths, the compiler can enforce type-safety on the properties and filter terms. This prevents run-time issues caused by specifying an invalid column name or invalid type to filter on. Fluent uses the property wrapper’s projected value, instead of the value itself.

Fluent makes heavy use of property wrappers for fields when creating models. As described in the Swift documentation, “a property wrapper adds a layer of separation between code that manages how a property is stored and the code that defines a property”. You can also provide a projected value to property wrappers. This allows you to expose additional functionality on the property wrapper. Fluent uses projected values to provide access to the key names and query functions for relationships.

In the above example, you provide the property wrapper’s projected value to filter on instead of the value itself. The projected value provides Fluent with information from the property wrapper that it needs. For instance, Fluent needs the column name when performing the query for the filter. If you were to provide only the property, Fluent would have no way to access this data. You’ll learn more about using property wrappers in the coming chapters.

If you require the actual value of a property, you use the property itself. For instance to read the short version of an acronym, you simply use acronym.short. In most instances, this is fine. However in some instances, this property may not have a value. You may want to reference a relation that you haven’t yet loaded from the database. Or, you may have loaded the record but only retrieved selected fields. You’ll learn about these different use cases in Chapter 9, “Parent-Child Relationships”, Chapter 10, “Sibling Relationships” and Chapter 31, “Advanced Fluent”.

Build and run your application, then create a new request in RESTed. Configure the request as follows:

• URL: http://localhost:8080/api/acronyms/search?term=OMG
• method: GET

Send the request and you’ll see the OMG acronym returned with its meaning:

If you want to search multiple fields — for example both the short and long fields — you need to change your query. You can’t chain filter(_:) functions as that would only match acronyms whose short and long properties were identical.

Instead, you must use a filter group. Replace:

return Acronym.query(on: req.db)
  .filter(\.$short == searchTerm)
  .all()

with the following:

// 1
return Acronym.query(on: req.db).group(.or) { or in
  // 2
  or.filter(\.$short == searchTerm)
  // 3
  or.filter(\.$long == searchTerm)
// 4
}.all()

Here’s what this extra code does:

1. Create a filter group using the .or relation.
2. Add a filter to the group to filter for acronyms whose short property matches the search term.
3. Add a filter to the group to filter for acronyms whose long property matches the search term.
4. Return all the results.

This returns all acronyms that match the first filter or the second filter. Build and run the application and go back to RESTed. Resend the request from above and you’ll still see the same result.

Change the URL to http://localhost:8080/api/acronyms/search?term=Oh+My+God and send the request. You’ll get the OMG acronym back as a response:

Note: Spaces in URLs must be URL-encoded as either %20 or + to be valid.

First result

Sometimes an application needs only the first result of a query. Creating a specific handler for this ensures the database only returns one result rather than loading all results into memory. Create a new route handler to return the first acronym at the end of routes(_:):

// 1
app.get("api", "acronyms", "first") { 
  req -> EventLoopFuture<Acronym> in
  // 2
  Acronym.query(on: req.db)
    .first()
    .unwrap(or: Abort(.notFound))
}

Here’s what this function does:

1. Register a new HTTP GET route for /api/acronyms/first that returns EventLoopFuture<Acronym>.
2. Perform a query to get the first acronym. first() returns an optional as there may be no acronyms in the database. Use unwrap(or:) to ensure an acronym exists or throw a 404 Not Found error.

You can also apply .first() to any query, such as the result of a filter.

Build and run the application, then open RESTed. Create new acronym with:

• short: IKR
• long: I Know Right

Now create a new RESTed request configured as:

• URL: http://localhost:8080/api/acronyms/first
• method: GET

Send the request and you’ll see the first acronym you created returned:

Sorting results

Apps commonly need to sort the results of queries before returning them. For this reason, Fluent provides a sort function.

Write a new route handler at the end of the routes(_:) function to return all the acronyms, sorted in ascending order by their short property:

// 1
app.get("api", "acronyms", "sorted") { 
  req -> EventLoopFuture<[Acronym]> in
  // 2
  Acronym.query(on: req.db)
    .sort(\.$short, .ascending)
    .all()
}

Here’s how this works:

1. Register a new HTTP GET route for /api/acronyms/sorted that returns EventLoopFuture<[Acronym]>.

2. Create a query for Acronym and use sort(_:_:) to perform the sort. This function takes the key path of the property wrapper’s projected value for that field to sort on. It also takes the direction to sort in. Finally use all() to return all the results of the query.

Build and run the application, then create a new request in RESTed:

• URL: http://localhost:8080/api/acronyms/sorted
• method: GET

Send the request and you’ll see the acronyms sorted alphabetically by their short property:

Where to go from here?

You now know how to use Fluent to perform the different CRUD operations and advanced queries. At this stage, routes.swift is getting cluttered with all the code from this chapter. The next chapter looks at how to better organize your code using controllers.