Advanced PostgreSQL With Vapor

Using Views for Frequently Accessed Queries

Views are simple yet powerful. Using Views, you can write a complex query and name it so you can call it by name the next time you need it.

In your case, you have two tables called person and company, which will be joined to form employees. This makes a good candidate for a view. You can make a new view named employee and set it to only return persons who are already employed. You can also exclude a person‘s hobby because it’s irrelevant to a person‘s employment status and you won’t need it.

For that, you need to make a normal model for employee, then instruct PostgreSQL with your new employee view’s properties.

Making an Employee Model

Make a new file called Employee in Models and replace its contents with the following:

import Fluent
import Vapor

final class Employee: Model, Content {
  
  static let schema = "employee"
  
  @ID
  var id: UUID?
  
  @Field(key: FieldKeys.firstName)
  var firstName: String
  
  @Field(key: FieldKeys.lastName)
  var lastName: String
  
  @Field(key: FieldKeys.job)
  var job: String

  @Field(key: FieldKeys.email)
  var email: String
  
  @Field(key: FieldKeys.companyName)
  var companyName: String
  
  @Field(key: FieldKeys.companyLocation)
  var companyLocation: String
  
  init() { }
}

extension Employee {
  enum FieldKeys {
    static let firstName: FieldKey = "first_name"
    static let lastName: FieldKey = "last_name"
    static let job: FieldKey = "job"
    static let email: FieldKey = "email"
    static let companyName: FieldKey = "company_name"
    static let companyLocation: FieldKey = "company_location"
  }
}

It seems pretty standard, doesn’t it? You’re treating employee as if it’s just a new table.

Creating Views Using SQLKit

Now, make a file called CreateEmployee in your Migrations folder. You need a new migration to tell PostgreSQL how does an Employee look like:

import Fluent
import SQLKit

struct CreateEmployee: AsyncMigration {
    
  private typealias CompanyKeys = Company.FieldKeys
  private typealias PersonKeys = Person.FieldKeys
  private typealias EmployeeKeys = Employee.FieldKeys
    
  func prepare(on database: Database) async throws {
    // 1
    let sqlDatabase = database as! SQLDatabase

    // 2
    let select = sqlDatabase
      .select()
      .column(SQLColumn(FieldKey.id.description, table: Person.schema))
      .column(
         SQLColumn(PersonKeys.firstName.description, table: Person.schema))
      .column(
         SQLColumn(PersonKeys.lastName.description, table: Person.schema))
      .column(SQLColumn(PersonKeys.job.description, table: Person.schema))
      .column(SQLColumn(PersonKeys.email.description, table: Person.schema))
      .column(
        SQLColumn(CompanyKeys.name.description, table: Company.schema),
        as: EmployeeKeys.companyName.description
      )
      .column(
        SQLColumn(CompanyKeys.location.description, table: Company.schema),
        as: EmployeeKeys.companyLocation.description
      )
      .column(
        SQLColumn(PersonKeys.companyId.description, table: Person.schema))
      .from(Person.schema)
      .join(
        SQLIdentifier(Company.schema),
        method: SQLJoinMethod.inner,
        on: SQLBinaryExpression(
          left: SQLColumn(
            PersonKeys.companyId.description, table: Person.schema),
          op: SQLBinaryOperator.equal,
          right: SQLColumn(FieldKey.id.description, table: Company.schema)
        )
      )
    
    // 3
    try await sqlDatabase.raw("""
    CREATE VIEW "\(raw: Employee.schema)" AS
    \(select.query)
    """)
    // 4
    .run()
  }
  
  func revert(on database: Database) async throws {
    let sqlDatabase = database as! SQLDatabase
    try await sqlDatabase.raw("DROP VIEW \(raw: Employee.schema)").run()
  }
}

Look at prepare(on:). This function seems slightly different from the usual Fluent code you write. That’s because it’s not Fluent! To write a migration for a new view, you need to drop down to SQLKit. SQLKit is what Fluent uses under the hood. It provides lower-level tools to communicate with your SQL database.

Go slowly through prepare(on:) to see how SQLKit works:

Using SQLKit is basically writing raw SQL queries but with Swift syntax! This is possible thanks to SQLExpressions, which know how to turn their own values from what you write to something acceptable by your SQL database. All types used in a SQLKit query conform to SQLExpression, including SQLColumn, SQLIdentifier and SQLJoinMethod.

Raw queries are not simple Strings. They use a SQLQueryString type, which is expressible by String. This enables interpolating different value types to a raw query. As you can see, you use \(raw: Employee.schema) to interpolate a plain String to the query, but you don’t need any raw labels when interpolating a SQLExpression like \(select.query). You can also bind values using \(bind:) for protection against injection attacks, but that’s not handy for this query.

1. First things first, you see let sqlDatabase = database as! SQLDatabase. Every database in Vapor is an abstraction over a lower-level database object, which handles the actual communication. Because you know you’re using PostgreSQL, you can safely force-cast your database to a SQLDatabase, because that’s the protocol all Vapor’s PostgreSQL databases conform to.
2. Creating a view requires a Select query, which will be used to query a new View. Here, you make your Select query to select rows from the person table, then specify the columns you need, optionally give the columns a new name and, at the end, perform an inner join of each person with their company.

   Using SQLKit is basically writing raw SQL queries but with Swift syntax! This is possible thanks to SQLExpressions, which know how to turn their own values from what you write to something acceptable by your SQL database. All types used in a SQLKit query conform to SQLExpression, including SQLColumn, SQLIdentifier and SQLJoinMethod.

3. SQLKit doesn’t yet fully support making views, so you need to use a bit of raw SQL. The syntax for making any views is as CREATE VIEW [view-name] AS [select-query], and this is what’s happening right here.

   Raw queries are not simple Strings. They use a SQLQueryString type, which is expressible by String. This enables interpolating different value types to a raw query. As you can see, you use \(raw: Employee.schema) to interpolate a plain String to the query, but you don’t need any raw labels when interpolating a SQLExpression like \(select.query). You can also bind values using \(bind:) for protection against injection attacks, but that’s not handy for this query.

4. At the end, you execute the query to make a new view.

What happens in revert(on:) is much simpler. It’s just removing the view using the DROP VIEW command, in case you don’t want the employee view anymore.

Remember to add CreateEmployee to the list of migrations in your configure file:

app.migrations.add([
  CreateCompany(),
  SeedCompany(),
  CreatePerson(),
  SeedPerson(),
  CreateEmployee(),
])

Now, go back to getAllEmployees(_:) in MainController. You have some changes to make.

Replace getAllEmployees(_:) with the following code:

func getAllEmployees(_ req: Request) async throws -> [Employee] {
  try await Employee
    .query(on: req.db)
    .all()
}

This returns the same information as the previous code but uses your new Employee. That makes it simpler and nicer to reuse.

Using Materialized Views for Frequently Accessed Data

Materialized views are another useful feature of PostgreSQL.

The view you created in the last section is merely a saved query in the database, which will be executed when it’s called by its name. Materialized views, on the other hand, are different. They save the result of the Select query into another table upon creation and will only access that dedicated table when you query the materialized view. This has the advantage of having the data ready for faster queries while consuming disk space and needing to be updated manually or using another mechanism.

In summary, materialized views have three differences from normal views. For materialized views, you need to use:

• CREATE MATERIALIZED VIEW to create.
• DROP MATERIALIZED VIEW to delete.
• REFRESH MATERIALIZED VIEW to refresh.

Now, you have the employees under control. But there are around 70,000 different employees and 30,000 unemployed people, and you need to be able to filter them on demand.