In our last series of episodes we showed how to use our Swift Sharing library to model state in your features that is powered by a SQLite database under the hood. We were able to describe the state as a query that is run in a database persisted to the file system, and when the data in that database changed, like when a row is inserted or deleted, then the state in our feature would automatically update and any views using that state would re-render. It was amazing to see.

We were even able to open source the tools we built in that series as a dedicated library called SharingGRDB. It allows you to use our Swift Sharing tools with SQLite, via the very popular GRDB library, and we consider it an alternative to SwiftData for many types of applications.

However, there was one thing we weren’t quite happy with in the tools we built. In order to build a custom persistence strategy to be used with the Swift Sharing library, one must conform the strategy to the Hashable protocol. This is because a global dictionary of shared state is maintained so that state can be shared across the entire app, instantly.

And we found that it was difficult to describe SQL queries in a manner that was compatible with Hashable. We ended up needing to maintain a new layer of abstraction in order to enforce hashability. We would like to fix this annoyance now.

We are going to build a brand new mini-library for constructing SQL queries that does play nicely with Hashable. This will allow us to use queries directly with our Swift Sharing library without an intermediate layer of abstraction. But that is only the beginning. It will also give us the opportunity to enforce static correctness in building our queries, so that the compiler can catch errors for us, and we will be able to unlock a whole new world of composition that makes it possible to break down complex queries into simpler ones, and reuse queries in interesting ways.

We’ve got a lot to cover, but let’s first start by reminding ourselves of what is not quite ideal about the tools we just released.

Let’s take a look.

In order to remember the problems we encountered last time when trying to shoehorn SQL queries into our Swift Sharing library, let’s go back to the project we built during the last series. Let’s run it in the simulator…

And we’ll see that we can increment and decrement a counter. Then when settled on a count we can ask for a fact about that number.

If we like that fact we can save it, and then the fact appears in a list below. We can save a few facts and notice that not only does the list grow, but also the header of the list correctly describes how many facts are in the list.

Further, if there are some facts in this list that we no longer like we can archive them…

The moment we archive a fact an “Archived facts (1)” button appears in the top-right of the UI. This lets us know that there are archived facts that we can view in another screen, and lets us know how many archived facts there are. We can archive a few facts to see the count increase.

If we tap the “Archive facts” button we are presented with a sheet that shows all of those facts. We can even unarchive a fact from this screen, and we will see it instantly removed from the archived sheet and inserted into the root list of facts. And further all of the numbers displayed in the UI also immediately updated. Animations are even working when we add/remove facts or re-sort them.

And then on top of all of this, this data is persisted to a SQLite database stored on disk so that if we relaunch the app, all of our data is restored.

This app may seem simple, but it is deceptively complex. A lot of work needs to be done to make sure that all of these data sources are kept in sync. In fact, there are 4 data sources: there’s the list of facts in the root list, there’s the count in the header of the list, the count in the toolbar button, and the list in the sheet. As soon as a change is made to the database, all 4 of these data sources observe the change, update their state accordingly, and force the view to re-render.

But the best part is that all of this complexity is hidden from us thanks to our Swift Sharing library and a small suite of tools that integrate it with the GRDB library. The codebase we have open right now is mostly what we built during the last series, but we have replaced the tools that we built during that series with the official tools we released last week with the SharingGRDB library.

We can search the project for @SharedReader to find all the places our Swift Sharing library is being used to power a piece of state that is actually run off the database under the hood. We will find 4 places it is being used.

First, in the ArchivedFactsView it is used directly in the view to load all of the archived facts from the database:

struct ArchivedFactsView: View {
  @SharedReader(.fetchAll(sql: #"SELECT * FROM "facts" WHERE "isArchived""#))
  var archivedFacts: [Fact]
  …
}

That one single line populates the archivedFacts variable with all of the data from the database, and also observes changes in the database to automatically update the state if it ever detects a change. And the act of that state updating will also automatically cause the view to re-render too.

Next we use the @SharedReader in the observable model that powers the facts feature:

@ObservationIgnored @SharedReader(.fetchOne(sql: #"SELECT count(*) FROM "facts" WHERE "isArchived""#))
var archivedFactsCount = 0

This computes the number of archived facts in the database, and stores the result as a simple integer in the model. Again, as soon as any change is made to the database, this count will be recomputed and the model’s state will be updated accordingly.

A few lines below we will find another query that is quite similar:

@ObservationIgnored @SharedReader(.fetchOne(sql: #"SELECT count(*) FROM "facts" WHERE NOT "isArchived""#))
var unarchivedFactsCount = 0

This computes the number of unarchived facts.

And finally, we have the following @SharedReader state for holding onto all of the unarchived facts in the model:

@ObservationIgnored @SharedReader(value: []) var favoriteFacts: [Fact]

However, this time things are a little different. We are referencing @SharedState without a fetch and instead using this value initializer in order to default the array of facts to a constant, which is an empty array.

The reason we are doing this is because the SQL query that powers this state is dynamic. It depends on the ordering state that determines how to sort the facts, and that state can be changed by the user. And because of this we can’t instantiate the query right at the declaration of the variable, but instead need to observe changes to the ordering state so that we can reconfigure the query powering the state:

init() {
  $ordering.publisher.sink { [weak self] ordering in
    guard let self else { return }
    Task {
      try await $favoriteFacts.load(.fetch(Facts(ordering: ordering), animation: .default))
    }
  }
  .store(in: &cancellables)
}

Here we are loading a new key into the $favoriteFacts projected value that describes the new query we are using.

And the query is specified as this additional type, called Facts, that describes how to execute the query:

struct Facts: FetchKeyRequest {
  let ordering: Ordering
  func fetch(_ db: Database) throws -> [Fact] {
    try Fact
      .filter(!Column("isArchived"))
      .order(ordering.orderingTerm)
      .fetchAll(db)
  }
}

And it’s inside this Facts type that we get to use GRDB’s query builder for describing the query we want to execute, and then using the database connection to execute it.

And that is the basics of how we were able to integrate a SQLite database into the @SharedReader tool so that pieces of state in our models and views could be powered by the database. It’s incredible powerful, but there is something to not like about these lines as they are written right now.

Three of the queries are written as raw SQLite strings:

@SharedReader(.fetchAll(#"SELECT * FROM "facts" WHERE "isArchived""#))
…
@SharedReader(.fetchOne(#"SELECT count(*) FROM "facts" WHERE "isArchived""#))
…
@SharedReader(.fetchOne(#"SELECT count(*) FROM "facts" WHERE NOT "isArchived""#))

This is super convenient to do, but it does leave us open to malformed SQL syntax or typos.

For example, in a moment of distraction we could forget that the table is named “facts” and not “fact”:

SELECT count(*) FROM "fact" WHERE "isArchived"

Or we could migrate our database so that the “isArchived” column changes from a simple boolean to a “status” column that could be expressed as a raw representable integer:

SELECT count(*) FROM "fact" WHERE "status" = 2

However, it would be our responsibility to audit our entire codebase for anywhere we are using raw SQL strings to make sure that we updated any “isArchived” columns to “status”. And if we forget any we will have subtly broken queries that may not be caught during testing. We are all so used to a compiler having our back when we rename variables or change their type that it would be great to get that kind of safety with our SQL queries too.

These are the kinds of problems that encouraged us to use GRDB’s query builder for writing our queries instead of raw SQL strings. However, in order to satisfy the Hashable requirements of @SharedReader we needed to introduce an intermediate layer of abstraction, the FetchKeyRequest, so that we could recover hashability:

struct Facts: FetchKeyRequest {
  let ordering: Ordering
  func fetch(_ db: Database) throws -> [Fact] {
    try Fact
      .filter(!Column("isArchived"))
      .order(ordering.orderingTerm)
      .fetchAll(db)
  }
}

That means when we look at a line of code like this in our application:

$favoriteFacts = SharedReader(.fetch(Facts(ordering: ordering)))

…we are not seeing the true query that is going to be executed. We are only seeing a description of what will be executed, and we will always have to jump down to the Facts type to then see what the query being executed really is.

And even with the query builder things are not as statically checked as we might hope. Constructing a query like this:

Fact
  .filter(!Column("isArchived"))
  .order(ordering.orderingTerm)

…does help make sure that we do not have any syntax errors in our SQL. Such as accidentally typing “ORDER” instead of “ORDER BY”:

SELECT * FROM "facts" WHERE NOT "isArchived" ORDER "number" ASC

Or accidentally putting the “ORDER BY” before the “WHERE”:

SELECT * FROM "facts" ORDER BY "number" ASC WHERE NOT "isArchived"

…which is not correct SQL syntax.

However, this query builder doesn’t know anything about the table schema that underlies the Fact type, and so in the future if we change the name of the “isArchived” column to “status”, it is still on us to make sure to update our query:

Fact
  .filter(Column("status") == 2)
  .order(ordering.orderingTerm)

The compiler will not be able to help us flag this potential problem.

So, these are the problems that we witnessed when diving into these topics last series. In summary, the hashability requirements of @SharedReader make it so that we can’t use a query builder directly. We either need to use a raw SQL string, or we need an intermediate abstraction layer that is Hashable and uses the query builder under the hood. And then even when we use the query builder, we are still leaving some potential type safety on the floor. It does not have our backs in order to verify that the columns we are referencing on the table do indeed exist.

And so the point of this new series will be to write a mini query builder library that addresses all of these problems. It will be Hashable right from the beginning, it will have knowledge of the schema of our types to enhance type safety, and we will get a few extra benefits too such as composability.

But, before diving into that, let’s give a sneak peek into what the final form of the tools will look like. We already gave a tiny peek at these tools during our livestream 2 weeks ago, but we want to show off a bit more before embarking on making these tools from scratch. And we have not yet released these tools yet, and we are still actively working on them, but we think they are close enough to done that we can demonstrate roughly what they will look like.

So, let’s dig in.

We are going to add a dependency to our new library to this project, though at the time of recording this episode the library is not public. We can hop over to the Xcode project settings and add a dependency on Structured Queries…

And we will link the library to our app target.

With that done we can already import the library in our Schema.swift file:

import StructuredQueries

The first tool we can use from the library is the @Table macro. It is a macro that you attach to your Swift data type that represents a table in your SQLite database. The macro uses the structure of your data type to figure out the columns from the table and introduces new static information that can be used for building type safe queries.

So, let’s apply it to our Fact data type:

@Table
struct Fact: Codable, Equatable, Identifiable, FetchableRecord,
  MutablePersistableRecord
{
  …
}

This unfortunately does not compile right away:

The problem is that we have a Date field in our type and dates are a little complicated in SQLite. If you don’t already know, SQLite is a bit different from most other databases in that it is dynamically typed. It has a loose set of types it understands, such as integers, doubles, text, and data blobs, but at the end of the day SQLite will let you store any kind of data in any column regardless of the type specified for the column, and it will implicitly cast between types.

On top of that, of the few types it understands, a dedicated “date” type is not one of them. There are three ways to model dates in SQLite. You can either hold a string of an ISO8601 formatted date, or you can hold an integer that represents the number of seconds since the Unix epoch, or you can also use a double to represent a date, but then SQLite interprets that as “Julian day” numbers, which is the number of days since November 24, 4714 B.C., which is probably not a measurement of dates most of us have worked with before.

So, dates are definitely quite unique in SQLite, and we have hopes of making our StructuredQueries library work with other databases, such as Postgres and MySQL, so we do not want to bake in any opinions of date into the @Table macro. And for that reason there is another macro, called the @Column macro, that can be used to customize how the date should be stored in SQLite.

In fact, we are even getting a helpful fix-it from the macro that will guide us to specifying this:

@Column(as: .<#strategy#>)
var savedAt: Date

And in the as argument we can decide how we want to encode and decode the date. We will choose an ISO8601 formatted string:

@Column(as: .iso8601)
var savedAt: Date

And if you want to store it as an integer number of seconds since the Unix epoch you can do this:

@Column(as: .unixTime)
var savedAt: Date

Or if you want to store it as a double number of Julian days since the Unix epoch you can do this:

@Column(as: .julianDay)
var savedAt: Date

Currently our database represents dates as a string formatted with ISO8601 since that is what GRDB does by default, and so that is the strategy we can use for now:

@Column(as: .iso8601)
var savedAt: Date

And with that change everything is now compiling. The application of the @Table macro has unlocked a whole suite of tools for building SQL queries in a type safe manner.

If we expand the macro we will see all the code written for us:

extension Fact: StructuredQueries.Table, StructuredQueries.PrimaryKeyedTable {
    public struct Columns: StructuredQueries.PrimaryKeyedSchema {
        public typealias QueryOutput = Fact
        public let id = StructuredQueries.Column<QueryOutput, Int64?>("id", keyPath: \.id)
        public let isArchived = StructuredQueries.Column<QueryOutput, Swift.Bool>("isArchived", keyPath: \.isArchived, default: false)
        public let number = StructuredQueries.Column<QueryOutput, Int>("number", keyPath: \.number)
        public let savedAt = StructuredQueries.Column<QueryOutput, _>("savedAt", keyPath: \.savedAt, as: .iso8601)
        public let value = StructuredQueries.Column<QueryOutput, String>("value", keyPath: \.value)
        public var primaryKey: some StructuredQueries.ColumnExpression<QueryOutput> & StructuredQueries.QueryExpression<Int64?> {
            self.id
        }
        public var allColumns: [any StructuredQueries.ColumnExpression<QueryOutput>] {
            [self.id, self.isArchived, self.number, self.savedAt, self.value]
        }
    }
    public struct Draft: StructuredQueries.Draft {
        var isArchived = false
        var number: Int
        var savedAt: Date
        var value: String
        public struct Columns: StructuredQueries.DraftSchema {
            public typealias QueryOutput = Draft
            public let isArchived = StructuredQueries.DraftColumn<QueryOutput, Swift.Bool>("isArchived", keyPath: \.isArchived, default: false)
            public let number = StructuredQueries.DraftColumn<QueryOutput, Int>("number", keyPath: \.number)
            public let savedAt = StructuredQueries.DraftColumn<QueryOutput, _>("savedAt", keyPath: \.savedAt, as: .iso8601)
            public let value = StructuredQueries.DraftColumn<QueryOutput, String>("value", keyPath: \.value)
            public var allColumns: [any StructuredQueries.ColumnExpression<QueryOutput>] {
                [self.isArchived, self.number, self.savedAt, self.value]
            }
        }
        public static let columns = Columns()
        public var queryFragment: QueryFragment {
            var sql: QueryFragment = "("
            sql.append(
                [
                    Self.columns.isArchived.encode(self.isArchived),
                    Self.columns.number.encode(self.number),
                    Self.columns.savedAt.encode(self.savedAt),
                    Self.columns.value.encode(self.value)
                ]
                .joined(separator: ", ")
            )
            sql.append(")")
            return sql
        }
    }
    public static let columns = Columns()
    public static let name = "facts"
    public init(decoder: some StructuredQueries.QueryDecoder) throws {
        self.id = try Self.columns.id.decode(decoder: decoder)
        self.isArchived = try Self.columns.isArchived.decode(decoder: decoder)
        self.number = try Self.columns.number.decode(decoder: decoder)
        self.savedAt = try Self.columns.savedAt.decode(decoder: decoder)
        self.value = try Self.columns.value.decode(decoder: decoder)
    }
    public var queryFragment: QueryFragment {
        var sql: QueryFragment = "("
        sql.append(
            [
                Self.columns.id.encode(self.id),
                Self.columns.isArchived.encode(self.isArchived),
                Self.columns.number.encode(self.number),
                Self.columns.savedAt.encode(self.savedAt),
                Self.columns.value.encode(self.value)
            ]
            .joined(separator: ", ")
        )
        sql.append(")")
        return sql
    }
}

There’s a lot here, but we don’t need to be familiar with all of this. The only thing we want to call out is that the Columns type gives us a static description of the columns of the table, including the type of data they store, the name of the column, and any transformations that need to be done on the data as is the case with dates:

public let savedAt = StructuredQueries.Column<Value, _>("savedAt", keyPath: \.savedAt, as: .iso8601)

And the init(decoder:) initializer makes it possible to create instances of Fact from SQLite row data.

This macro generates enough information for us that we can drop the FetchableRecord and MutablePersistableRecord conformances from the Fact type:

@Table
struct Fact: Codable, Equatable, Identifiable
{
  …
}

However, this will create a bunch of compiler errors and so we will hold off on this until later…

Let’s now take a look at the suite of tools that are unlocked by using the @Table macro and having all of this code generated for us. We will show how to rewrite all of our queries using the query builder, and how it improves all of the problems we showed a moment ago.

Let’s start with the query that fetches all of the archived facts in the ArchivedFactsView:

@SharedReader(.fetchAll(sql: #"SELECT * FROM "facts" WHERE "isArchived""#))
var archivedFacts: [Fact]

To play with building this query with our query builder we will construct some queries in the init of the view:

init() {
}

The entry point to the query builder is the all() static method defined on any table type:

Fact.all()

This returns an opaque type that conforms to the QueryExpression protocol:

let query: some QueryExpression = Fact.all()

The QueryExpression protocol represents any fully or partially formed SQL statement. We can even render it out to the SQL string:

print(query.queryFragment)

There is quite a bit of noise in the console since we have query tracing on. Let’s turn that off for the time being:

configuration.prepareDatabase { db in
  // db.trace(options: .profile) {
  //   #if DEBUG
  //     print($0.expandedDescription)
  //   #else
  //     print($0)
  //   #endif
  // }
}

OK, that is much better.

And already with this we can run the preview to see the query statement printed to the console:

SELECT "facts"."id", "facts"."isArchived", "facts"."number", "facts"."savedAt", "facts"."value" FROM "facts"

And this may be a bit more than you expected to be printed. After all, since we’ve only specified Fact.all(), you may expect that that implies selecting all columns, for which a simple:

SELECT * FROM "facts"

…would suffice.

However, while using SELECT * in SQL queries is convenient, and totally fine to do while debugging things, you typically want to shy away from that in queries you use in your app’s logic. Being explicit with your selected columns leads to more efficient queries, especially when joins are involved that can really balloon the number of columns selected.

But, you don’t have to worry about that when using our query builder library, because it will be as explicit as possible each step of the way.

The QueryExpression protocol also has a primary associated type that represents the type of data that would be returned from the database if we were to execute this query. In this case it is an array of facts:

let query: some QueryExpression<[Fact]> = Fact.all()

Where can further chain onto the query to customize it more, such as adding a WHERE clause:

let query: some QueryExpression<[Fact]> = Fact.all()
  .where(<#(Fact.Columns) -> QueryExpression#>)

We can even get into the WHERE clause without even specifying all() first if we want:

let query: some QueryExpression<[Fact]> = Fact
  .where(<#(Fact.Columns) -> QueryExpression#>)

…and here is where things get interesting.

We can see that where accepts a closure as an argument, and that closure takes Facts.Columns as an argument and must return another QueryExpression.

Remember that Fact.Columns is a static description of the columns of the table, which means we can use autocomplete to inspect all the fields available to us:

let query = Fact
  .where { $0.<#⎋#> }

We will immediately see all of the columns in the table that we can choose from, in particular the isArchived field:

let query = Fact
  .where { $0.isArchived }

And when the preview runs again we will see the exact SQL statement this generates:

SELECT
  "facts"."id",
  "facts"."isArchived",
  "facts"."number",
  "facts"."savedAt",
  "facts"."value
FROM "facts"
WHERE "facts"."isArchived"

In particular, a “WHERE” clause was added to the end of the statement.

This is all statically checked. It would not be possible to have a typo in the column name, such as “archived” instead of “isArchived”:

let query = Fact
  .where { $0.archived }

…because that static symbol does not exist.

And further, if we had a computed property on the type, such as adding an isNotArchived property that simply delegates to isArchived:

extension Fact {
  var isNotArchived: Bool { !isArchived }
}

Even this is not allowed to be used in a query:

let query = Fact.all()
  .where { $0.isNotArchived }

And this is a good thing! If we allowed generating SQL for this property we would accidentally generate an invalid statement that refers to a non-existent column called “isNotArchived”.

And if we are so inclined we can even use a key path instead of a closure:

let query = Fact.all()
  .where(\.isArchived)

Though for more complex queries you will need to use the closure-based syntax because that allows you to form compound predicates. For example, if you wanted to select all facts that are archived and whose number is greater than 100 you can do the following:

let query = Fact.all()
  .where { $0.isArchived && $0.number >= 100 }

That represents the SQL statement:

SELECT
  "facts"."id",
  "facts"."isArchived",
  "facts"."number",
  "facts"."savedAt",
  "facts"."value" 
FROM "facts" 
WHERE ("facts"."isArchived" AND ("facts"."number" >= 100))

And based on what we are seeing in the console it may seem like we are naively interpolating data into these queries, but that is not the case. This output in the console is only a debugging affordance. In reality a proper prepared statement can be created that allows SQLite to bind the data to the query, making it safe from injection attacks.

And again, this is all statically type checked, including the types used for the columns. It would not be correct to compare the number column to a string like this:

.where { $0.isArchived && $0.number == "Blob" }

…and the compiler immediately tells you what is wrong:

A column for an Int cannot be compared to a String.

So this is looking pretty amazing. By simply applying the @Table macro we unlock a whole world of query building APIs that provides type safety, autocomplete, and can significantly shorten code that needs to build complex queries.

But so far we have only rewritten a single query using the query builder. Let’s convert the rest of the queries so that we can get more exposure to what the query building library has to offer.

Let’s look at the other queries we considered. All of them are back in the FactsFeatureModel, where we will again first play around with the query builder syntax

init() {
  …
}

The first query we will look at is the one that computed the count of archived facts:

@ObservationIgnored @SharedReader(.fetchOne(#"SELECT count(*) FROM "facts" WHERE "isArchived""#))
var archivedFactsCount = 0

To write this with our query builder you start off similarly to how we did for the last query. We can use the where static method in order to select only the archived facts:

Fact.where(\.isArchived)

In order to compute the aggregate count across this query we can use the select method:

.select(<#(Fact.Columns) -> (repeat QueryExpression)#>)

This method imitates what one does in the “SELECT” of a real SQL statement, such as choosing which columns to select, or even expressing a count of rows by a column:

let query1 = Fact
  .where(\.isArchived)
  .select { $0.id.count() }

If we print this query:

print(query.queryFragment)

…we will see that we have constructed the following query:

SELECT count("facts"."id") FROM "facts" WHERE "facts"."isArchived"

Which is exactly what we expect. And further you can count distinct non-NULL values of a column like so:

let query1 = Fact
  .where(\.isArchived)
  .select { $0.id.count(distinct: true) }

That now prints the following to the console:

SELECT count(DISTINCT "facts"."id") FROM "facts" WHERE "facts"."isArchived"

But there’s also a small shortcut we can take here. If all you want to do is select the number of rows for the query, you can use the count() method directly on the query expression:

let query1 = Fact.all()
  .where(\.isArchived)
  .count()

Now this prints the following to the console:

SELECT count(*) FROM "facts" WHERE "facts"."isArchived"

It’s subtle, but there is a difference between count(*) and count("facts"."id") in general, though in this case they are equivalent.

The count(*) syntax just means literally count every row returned by the query. But when a column is specified in the count it means count all of the non-NULL values of that column.

This is all good information to know, and as we’ve stated before, we think people first and foremost should be familiar with SQLite before using our libraries. SQLite is one of the most powerful pieces of software out there, and it will only help your career if you are intimately familiar with how to wield it effectively.

Let’s move onto the next SQL query, which is selecting a count of unarchived facts:

@ObservationIgnored @SharedReader(.fetchOne(#"SELECT count(*) FROM "facts" WHERE NOT "isArchived""#))
var unarchivedFactsCount = 0

This time we do need to open up the closure argument of where so that we can express that we do not want archived facts:

let query2 = Fact.where { !$0.isArchived }.count()

This now represents the SQL statement:

SELECT count(*) FROM "facts" WHERE NOT ("facts"."isArchived")

The final query we have in the app is a dynamic one. It changes based on the ordering that is selected, which is something the user can change at anytime. We handle this by sinking on the shared $ordering publisher, and when it changes we reassign the $favoriteFacts projected value with a new query:

$favoriteFacts = SharedReader(.fetch(Facts(ordering: ordering)))

However, there is a bit of indirection here. This is just a description of the query, not the query itself. The query it self is hidden away in the Facts type, which we can jump down to see it:

func fetch(_ db: Database) throws -> [Fact] {
  try Fact
    .filter(!Column("isArchived"))
    .order(ordering.orderingTerm)
    .fetchAll(db)
}

And now we finally see that the query grabs all non-archived facts, and orders them based on the ordering state. There is an extra layer of indirection here because we further derive an “ordering term” from the ordering state by using a computed property:

enum Ordering: String, CaseIterable {
  case number = "Number", savedAt = "Saved at"

  var orderingTerm: any SQLOrderingTerm {
    switch self {
    case .number:
      Column("number")
    case .savedAt:
      Column("savedAt").desc
    }
  }
}

Let’s rewrite this query using our query builder library. We can start by querying for all facts that are not archived:

let query3 = Fact
  .where { !$0.isArchived }

Next we can use the order method:

let query3 = Fact
  .where { !$0.isArchived }
  .order(<#(Fact.Columns) -> (repeat _OrderingTerm)#>)

This takes a trailing closure just like where, and it is handed Fact.Columns so that we get static access to the schema of the Fact table. And we can do all types of fun things in this closure. For example, if we just want to sort by the fact’s number we can simply do:

let query3 = Fact
  .where { !$0.isArchived }
  .order { $0.number }

This corresponds to the SQL statement:

SELECT
  "facts"."id",
  "facts"."isArchived",
  "facts"."number",
  "facts"."savedAt",
  "facts"."value" 
FROM "facts" 
WHERE NOT "facts"."isArchived"
ORDER BY "facts"."number"

And if you want to order by “number” in a descending order you can simply do:

let query3 = Fact.all()
  .where { !$0.isArchived }
  .order { $0.number.descending() }

Now this corresponds to the SQL statement:

SELECT
  "facts"."id",
  "facts"."isArchived",
  "facts"."number",
  "facts"."savedAt",
  "facts"."value" 
FROM "facts" 
WHERE NOT "facts"."isArchived"
ORDER BY "facts"."number" DESC

And you can even sort by multiple columns by simply returning a tuple. For example, if you wanted to sort by “number” in a descending order, and then for any facts with the same number you can further sort by their “savedAt” column:

let query3 = Fact
  .where { !$0.isArchived }
  .order { ($0.number.descending(), $0.savedAt) }

This now corresponds to the SQL statement:

SELECT
  "facts"."id",
  "facts"."isArchived",
  "facts"."number",
  "facts"."savedAt",
  "facts"."value" 
FROM "facts" 
WHERE NOT "facts"."isArchived"
ORDER BY "facts"."number" DESC, "facts"."savedAt"

And so this is incredibly powerful, but it doesn’t seem to directly help with our situation. We want to be able to dynamically control which order is used based on the ordering state.

Well, secretly behind the scenes of the order trailing closure is a result builder. That allows us to perform a subset of logical operations to determine which order to use. So, when ordering is number we will order by the “number” column in an ascending fashion, and when it is “savedAt” we will order by the “savedAt” column in a descending fashion:

let query3 = Fact
  .where { !$0.isArchived }
  .order {
    switch ordering {
    case .number:  $0.number
    case .savedAt: $0.savedAt.descending()
    }
  }

There’s no need for a computed property because we can perform the logic directly in the trailing closure. We can even amp up the logic a bit by further making that when sorting by “number” we will also sort by “savedAt” for any facts with the same number:

let query3 = Fact
  .where { !$0.isArchived }
  .order {
    switch ordering {
    case .number: ($0.number, $0.savedAt.descending())
    case .savedAt: $0.savedAt.descending()
    }
  }

The query builder is so flexible that it makes it very easy to construct complex queries. You don’t have to be afraid of crafting the perfect query that suits your feature best.

This is pretty amazing. We have now rewritten all 4 queries of our app using our new, but unreleased, query builder library. In each case it allowed us to write queries in a more concise and safer manner, and we leveraged Swift and autocomplete to help us along the way. Further, for the last query we were even able to express an advanced query in a single line without being forced to split things up across separate types and properties.

So clearly our query builder library is powerful, and there is a lot more power lurking in the shadows. We want to show off a few more tricks before we get into building the library from scratch in our episodes. First we are going to demonstrate its ability to compose and reuse queries.

Right now we have repeated certain parts of our queries over and over. In particular, we’ve needed to specify a where clause to specify “is archived” and “is not archived” multiple times. It is possible to bundle up fragments of these queries into helpers that can then be composed together at a high level.

It’s amazing to see, so let’s give it a shot…next time!