Home iOS & Swift Books Server-Side Swift with Vapor

30
Advanced Fluent Written by Tim Condon

Note: This update is an early-access release. This chapter has not yet been updated to Vapor 4.

In the previous sections of this book, you learned how to use Fluent to perform queries against a database. You also learned how to perform CRUD operations on models. In this chapter, you’ll learn about some of Fluent’s more advanced features. You’ll see how to save models with enums and use Fluent’s soft delete and timestamp features. You’ll also learn how to use raw SQL and joins, as well as seeing how to return nested models.

Getting started

The starter project for this chapter is based on the TIL application from the end of chapter 21. You can either use your code from that project or use the starter project included in the book materials for this chapter. This project relies on a PostgreSQL database running locally.

Clearing the existing database

If you’ve followed along from the previous chapters, you need to delete the existing database. This chapter contains model changes which require either reverting your database or deleting it. In Terminal, type:

docker stop postgres
docker rm postgres

Creating a new database

Create a new database in Docker for the TIL application to use. In Terminal, type:

docker run --name postgres -e POSTGRES_DB=vapor \
  -e POSTGRES_USER=vapor -e POSTGRES_PASSWORD=password \
  -p 5432:5432 -d postgres

Soft delete

In Chapter 7, “CRUD Database Operations”, you learned how to delete models from the database. However, while you may want models to appear deleted to users, you might not want to actually delete them. You could also have legal or company requirements which enforce retention of data. Fluent provides soft delete functionality to allow you to do this. Open the TIL app in Xcode and go to User.swift. Below var profilePicture: String?, add the following:

var deletedAt: Date?
static let deletedAtKey: TimestampKey? = \.deletedAt
func deleteHandler(_ req: Request)
  throws -> Future<HTTPStatus> {
    return try req.parameters
      .next(User.self)
      .delete(on: req)
      .transform(to: .noContent)
}
tokenAuthGroup.delete(User.parameter, use: deleteHandler)

Restoring Users

Even though the application now allows you to soft delete users, you may want to restore them at a future date. First, add the following below import Crypto at the top of UsersController.swift:

import Fluent
func restoreHandler(_ req: Request)
  throws -> Future<HTTPStatus> {
    // 1
    let userID = try req.parameters.next(UUID.self)
    // 2
    return User.query(on: req, withSoftDeleted: true)
      .filter(\.id == userID)
      .first().flatMap(to: HTTPStatus.self) { user in
        // 3
        guard let user = user else {
          throw Abort(.notFound)
        }
        // 4
        return user.restore(on: req).transform(to: .ok)
    }
}
tokenAuthGroup.post(
  UUID.parameter, 
  "restore",
  use: restoreHandler)

docker exec -it postgres psql -U vapor
select id from "User" where username = '<your username>';
\q

Force delete

Now you can soft delete and restore users, you may want to add the ability properly delete a user. You use force delete for this. Back in Xcode, create a new route to do this, below restoreHandler(_:):

func forceDeleteHandler(_ req: Request)
  throws -> Future<HTTPStatus> {
    // 1
    return try req.parameters
      .next(User.self)
      .flatMap(to: HTTPStatus.self) { user in
        // 2
        user.delete(force: true, on: req)
          .transform(to: .noContent)
    }
}
tokenAuthGroup.delete(
  User.parameter, 
  "force",
  use: forceDeleteHandler)

Timestamps

Fluent has built-in functionality for timestamps for a model’s creation time and update time. If you configure these, Fluent automatically sets and updates the times. To enable this, open Acronym.swift in Xcode. Below var userID: User.ID add two new properties for the dates:

var createdAt: Date?
var updatedAt: Date?
static let createdAtKey: TimestampKey? = \.createdAt
static let updatedAtKey: TimestampKey? = \.updatedAt
func getMostRecentAcronyms(_ req: Request)
  throws -> Future<[Acronym]> {
    return Acronym.query(on: req)
      .sort(\.updatedAt, .descending)
      .all()
}
acronymsRoutes.get("mostRecent", use: getMostRecentAcronyms)
docker stop postgres
docker rm postgres
docker run --name postgres -e POSTGRES_DB=vapor \
  -e POSTGRES_USER=vapor -e POSTGRES_PASSWORD=password \
  -p 5432:5432 -d postgres

Enums

A common requirement for database columns is to restrict the values to a pre-defined set. Both FluentPostgreSQL and FluentMySQL support enums for this. To demonstrate this, you’ll add a type to the user to define basic user access levels. Close your project in Xcode. Then, in Terminal, enter the following:

touch Sources/App/Models/UserType.swift
vapor xcode -y
// 1
import FluentPostgreSQL

// 2
enum UserType: String, PostgreSQLEnum, PostgreSQLMigration {
  // 3
  case admin
  case standard
  case restricted
}
migrations.add(migration: UserType.self, database: .psql)
var userType: UserType
init(name: String,
     username: String,
     password: String,
     email: String,
     profilePicture: String? = nil,
     userType: UserType = .standard) {
  self.name = name
  self.username = username
  self.password = password
  self.email = email
  self.profilePicture = profilePicture
  self.userType = userType
}
let user = User(
  name: "Admin",
  username: "admin",
  password: hashedPassword,
  email: "admin@localhost.local",
  userType: .admin)
// 1
let requestUser = try req.requireAuthenticated(User.self)
// 2
guard requestUser.userType == .admin else {
  throw Abort(.forbidden)
}
// 3
return try req.parameters
  .next(User.self)
  .delete(on: req)
  .transform(to: .noContent)

Lifecycle hooks

Fluent provides hooks for various aspects of a model’s lifecycle. Fluent exposes the following hooks:

// 1
func willCreate(on conn: PostgreSQLConnection) 
  throws -> Future<User> {
    // 2
    return User.query(on: conn)
  	  .filter(\.username == self.username)
  	  .count()
  	  .map(to: User.self) { count in
        // 3
        guard count == 0 else {
          throw BasicValidationError("Username already exists")
        }
        return self
    }
}

Nested models

If you follow a strict REST API, you should retrieve a model’s children in a separate request. However, this isn’t alway ideal, and you may want the ability to send a single request to get all models with all their children. For example, in the TIL application, you may want a route that returns all users with all their acronyms. This is commonly referred to as the N+1 problem and, at the time of writing, Fluent provides no easy way to achieve this. You must implement it manually. Open UsersController.swift and add the following at the bottom of the file:

struct UserWithAcronyms: Content {
  let id: UUID?
  let name: String
  let username: String
  let acronyms: [Acronym]
}
func getAllUsersWithAcronyms(_ req: Request)
  throws -> Future<[UserWithAcronyms]> {
    // 1
    return User.query(on: req)
      .all()
      .flatMap(to: [UserWithAcronyms].self) { users in
        // 2
        try users.map { user in
          // 3
          try user.acronyms.query(on: req)
          .all()
          .map { acronyms in
            // 4
            UserWithAcronyms(
             id: user.id,
             name: user.name,
             username: user.username,
             acronyms: acronyms)
          }
        // 5
        }.flatten(on: req)
    }
}
usersRoute.get("acronyms", use: getAllUsersWithAcronyms)

Joins

The above scenario isn’t very efficient. For a database with a hundred users, you need to make a hundred database queries to get all their acronyms, just for a single request. When getting all acronyms with their users, you can do this more efficiently with a join. Joins allow you to combine columns from one table with columns from another table by specifying the common values. Such as combining the acronyms table with the users table using the user’s ID.

struct AcronymWithUser: Content {
  let id: Int?
  let short: String
  let long: String
  let user: User.Public
}
func getAcronymsWithUser(_ req: Request)
  throws -> Future<[AcronymWithUser]> {
    // 1
    return Acronym.query(on: req)
      // 2
      .join(\User.id, to: \Acronym.userID)
      // 3
      .alsoDecode(User.self).all()
      // 4
      .map(to: [AcronymWithUser].self) { acronymUserPairs in
        // 5
        acronymUserPairs
          .map { acronym, user -> AcronymWithUser in
            // 6
            AcronymWithUser(
              id: acronym.id,
              short: acronym.short,
              long: acronym.long,
              user: user.convertToPublic())
        }
    }
}
acronymsRoutes.get("users", use: getAcronymsWithUser)

Raw SQL

In Fluent, there’s currently no solution for solving the N+1 problem efficiently. You can manually get all users and all acronyms and combine them server-side, but Fluent doesn’t yet provide a way to do this. In a complex application, you may find that there are scenarios where Fluent doesn’t provide the functionality you need. In these cases, you can use raw SQL queries to interact with the database directly. This allows you to perform any type of query the database supports.

func getAllAcronymsRaw(_ req: Request)
  throws -> Future<[Acronym]> {
    // 1
    return req.withPooledConnection(to: .psql) { conn in
      // 2
      conn.raw("SELECT * from \"Acronym\"")
      // 3
      .all(decoding: Acronym.self)
    }
}
acronymsRoutes.get("raw", use: getAllAcronymsRaw)

Where to go from here?

In this chapter, you learned how to use some of the advanced features Fluent provides to perform complex queries. You also saw how to send raw SQL queries if Fluent can’t do what you need.

Have a technical question? Want to report a bug? You can ask questions and report bugs to the book authors in our official book forum here.

Have feedback to share about the online reading experience? If you have feedback about the UI, UX, highlighting, or other features of our online readers, you can send them to the design team with the form below:

© 2021 Razeware LLC

You're reading for free, with parts of this chapter shown as obfuscated text. Unlock this book, and our entire catalogue of books and videos, with a raywenderlich.com Professional subscription.

Unlock Now

To highlight or take notes, you’ll need to own this book in a subscription or purchased by itself.