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.

Right now the 4 queries we have built with our library look roughly like this:

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

And I think you will immediately notice that there’s a bit of overlap with all of these queries. In each one we are specifying whether we are fetching just archived facts or just unarchived facts.

There is a way to bundle up this concept into a reusable unit that will allow us to make these queries quite a bit clearer. We can add static query expressions to the Fact type that represent the query fragment of specifying archived or unarchived facts:

extension Fact {
  static let archived = Self.where(\.isArchived)
  static let unarchived = Self.where { !$0.isArchived }
}

With just that little bit of code we can rewrite our four queries like so:

Fact.archived
Fact.archived.count()
Fact.unarchived.count()
Fact.unarchived.order { 
  switch ordering {
  case .number: ($0.number, $0.savedAt.descending())
  case .savedAt: $0.savedAt.descending()
  }
}

These are all the same query, but we get to say at a top level when we are starting with archived or unarchived facts.

And this is only the beginning. It is possible to define lots of little helper that express much more complex and nuanced queries, and then piece them together in powerful ways. It’s difficult to demonstrate this with the Fact table because its schema is quite simple.

To give a small preview of what is possible, let’s play around with a new schema. We aren’t going to actually create these tables in a database, but we can still apply the @Table macro to some types and play around with the tools. It’s even kinda cool to see just how easy it is to define new schemas with the macro.

For example, suppose we had a SyncUp type that represents a recurring meeting:

@Table
struct SyncUp {
  let id: Int
  var duration: Int
  var title: String
}

And further, suppose you had an Attendee type that represents someone who attends a sync up meeting, and so their relationship to the sync up is expressed as having a column with the sync up’s ID:

@Table
struct Attendee {
  let id: Int
  var name: String
  var syncUpID: Int
}

With this relationship defined between tables we can query for all attendees, along with the sync up they are associated with. The way one does this in SQL is via joins. This is a topic we are going to get deep into as we build the query builder from scratch, but suffice it to say that we can perform the join like so with our builder:

Attendee.join(
  <#some SelectProtocol#>,
  on: <#((Attendee.Columns, Sendable)) -> QueryExpression#>
)

You first tell it which table you are joining to, in this case the SyncUp table:

Attendee.join(
  SyncUp.all(),
  on: <#((Attendee.Columns, Sendable)) -> QueryExpression#>
)

Then you give it a trailing closure that is given the columns for the two tables we are joining:

Attendee
  .join(SyncUp.all()) { attendees, syncUps in
  }

In this trailing closure we can form any kind of predicate we want that describes how the tables are joined together. In this case, we will join two rows when the attendee’s syncUpID matches the sync up’s id:

Attendee
  .join(SyncUp.all()) { attendee, syncUp in
    attendee.syncUpID == syncUp.id
  }

And some might find it nicer to write this closure without specifying the argument name:

Attendee
  .join(SyncUp.all()) { $0.syncUpID == $1.id }

Let’s print this query in the initializer of the app entry point:

let query = Attendee
  .join(SyncUp.all()) { $0.syncUpID == $1.id }
print(query.queryFragment)

Running the app we see the following query printed:

SELECT
  "attendees"."id",
  "attendees"."isDeleted",
  "attendees"."name",
  "attendees"."syncUpID",
  "syncUps"."id",
  "syncUps"."duration",
  "syncUps"."isDeleted",
  "syncUps"."title" 
FROM "attendees" 
JOIN "syncUps" ON ("attendees"."syncUpID" = "syncUps"."id")

This essentially says that we want to select all of the attendees and sync ups from the database, but we will glue together rows for which an attendee’s syncUpID matches a sync up’s id. You don’t have to understand everything about this query right now. We will be discussing joins soon enough.

Another kind of join that is very common is to select all rows from a table along with a count of of associated rows. For example, to select all sync ups along with a count of all attendees we can simply do the following:

let query2 = SyncUp
  .join(Attendee.all()) { $0.id == $1.syncUpID }
  .select { ($0, $1.id.count()) }
print(query2.queryFragment)

Running the app now prints this query:

SELECT
  "syncUps"."id",
  "syncUps"."duration",
  "syncUps"."isDeleted",
  "syncUps"."title",
  count("attendees"."id")
FROM "syncUps" 
JOIN "attendees" ON ("syncUps"."id" = "attendees"."syncUpID")

We also need to add a group:

let query2 = SyncUp
  .join(Attendee.all()) { $0.id == $1.syncUpID }
  .select { ($1.id.count(), $0) }
  .group { syncUps, _ in syncUps.id }
print(query2.queryFragment)

SELECT
  "syncUps"."id",
  "syncUps"."duration",
  "syncUps"."isDeleted",
  "syncUps"."title",
  count("attendees"."id")
FROM "syncUps" 
JOIN "attendees" ON ("syncUps"."id" = "attendees"."syncUpID")
GROUP BY "syncUps"."id"

Which we can shorten to:

let query2 = SyncUp
  .group(by: \.id)
  .join(Attendee.all()) { $0.id == $1.syncUpID }
  .select { ($1.id.count(), $0) }
print(query2.queryFragment)

It’s just really incredible how infinitely flexible the builder is in constructing these complex queries.

So, that’s joins, but we can also bake this join into a helper so that we have easy access to it in other contexts. For example, a withSync helper could be defined on Attendee to be a shortcut for getting attendees with associated their sync up:

extension Attendee {
  static let withSyncUp = join(SyncUp.all()) { $0.syncUpID == $1.id }
}

We can check the type to see that indeed it is a query expression of an array of tuples.

It is also possible to bundle this tuple up into a custom data type, which is typically friendlier than dealing with tuples in Swift code:

struct AttendeeAndSyncUp {
  let attendee: Attendee
  let syncUp: SyncUp
}

But to do this we need to annotate this type with another macro called @Selection:

@Selection
struct AttendeeAndSyncUp {
  …
}

And once that’s done we have the ability to select the data we want from our tables and package it up in this data type like so:

extension Attendee {
  static let withSyncUp = Attendee
    .join(SyncUp.all()) { $0.syncUpID == $1.id }
    .select { AttendeeAndSyncUp.Columns(attendee: $0, syncUp: $1) }
}

We can even just pass the initializer of AttendeeAndSyncUp to select:

extension Attendee {
  static let withSyncUp = Attendee
    .join(SyncUp.all()) { $0.syncUpID == $1.id }
    .select(AttendeeAndSyncUp.Columns.init)
}

This technique can be super powerful. It allows you to be very precise with exactly what data you want to select from the database. For example, if we wanted to be able to select all sync ups along with the count of their attendees we could start by defining a type for that:

@Selection
struct SyncUpWithAttendeeCount {
  let attendeeCount: Int
  let syncUp: SyncUp
}

And then we can define a helper on SyncUp that makes it easy to query for all sync ups along with the count of their attendees:

extension SyncUp {
  static let withAttendeeCount = SyncUp
    .group(by: \.id)
    .join(Attendee.all()) { $0.id == $1.syncUpID }
    .select { 
      SyncUpWithAttendeeCount.Columns(
        attendeeCount: $1.id.count(), 
        syncUp: $0
      )
    }
  }
}

And we can employ these helpers where our queries were previously built inline:

let query1 = Attendee.withSyncUp
let query2 = SyncUp.withAttendeeCount

And we can further chain onto these helpers to further build upon the base queries:

let query1 = Attendee.withSyncUp
  .where { attendees, _ in
    attendees.name.collate(.nocase).contains("blob")
  }

SELECT
  "attendees"."id",
  "attendees"."isDeleted",
  "attendees"."name",
  "attendees"."syncUpID",
  "syncUps"."id",
  "syncUps"."duration",
  "syncUps"."isDeleted",
  "syncUps"."title" 
FROM "attendees" 
JOIN "syncUps" ON ("attendees"."syncUpID" = "syncUps"."id")
WHERE (("attendees"."name" COLLATE NOCASE) LIKE '%blob%')

And similarly for our other query:

let query2 = SyncUp.withAttendeeCount
  .where { syncUps, _ in
    syncUps.title.collate(.nocase).contains("morning")
  }

SELECT
  "syncUps"."id",
  "syncUps"."duration",
  "syncUps"."isDeleted",
  "syncUps"."title",
  count("attendees"."id")
FROM "syncUps" 
JOIN "attendees" ON ("syncUps"."id" = "attendees"."syncUpID")
WHERE (("syncUps"."title" COLLATE NOCASE) LIKE '%morning%')
GROUP BY "syncUps"."id"

Not only have we just showed that we can define lots of little helpers that allow us to build complex queries from simpler pieces, but we’ve even given a glimpse into some really powerful queries. We are performing joins between tables in a type safe way that even allows us to use autocomplete to aid us in building the join predicate, and at the end of a complex query we can always bundle up our tuple of data into a data type that better expresses our domain.

Let’s amp things up even more. It is quite common in applications to have the concept of “soft” deleting rows. That is, rather than deleting the data from the database immediately, you have a boolean column that flags whether or not the row is deleted. And then maybe after some amount of time you actually delete the rows.

Let’s show how we can introduce this concept to our schema, and make it possible to bake the notion of “not deleted” directly into all of our query helpers. And along the way we are going to get to show off a fun trick that will be unlocked in the next version of Swift, which is version 6.1 and at the time of this recording has not yet been released.

We can emulate this by adding an isDeleted boolean to our schema:

@Table
struct SyncUp {
  var id: Int
  var isDeleted = false
  var duration: Int
  var title: String
}
@Table
struct Attendee {
  var id: Int
  var isDeleted = false
  var name: String
  var syncUpID: Int
}

And again, we aren’t actually creating these tables in a database, and so we don’t need to perform a migration to add these columns, but typically you would need to do that.

With those columns added we can define helpers for selecting all rows that are not deleted:

extension SyncUp {
  static let notDeleted = Self.where { !$0.isDeleted }
}
extension Attendee {
  static let notDeleted = Self.where { !$0.isDeleted }
}

And then we can update our withSyncUp helper to make sure to only grab non-deleted attendees and sync ups:

extension Attendee {
  static let withSyncUp = Attendee.notDeleted
    .join(SyncUp.notDeleted) { $0.syncUpID == $1.id }
    .select { AttendeeAndSyncUp.Columns(attendee: $0, syncUp: $1) }
}

extension SyncUp {
  static let withAttendeeCount = SyncUp.notDeleted
    .group(by: \.id)
    .join(Attendee.notDeleted) { $0.id == $1.syncUpID }
    .select { 
      SyncUpWithAttendeeCount.Columns(
        attendeeCount: $1.id.count(), 
        syncUp: $0
      )
    }
  }
}

And things are going to get even better in Swift 6.1. That version of Swift will bring meta type key paths, which will allow the notDeleted and withSyncUp helpers to be used anywhere in a query expression, not just directly on a meta type.

For example, something as simple as this:

SyncUp.select { $0.name }.notDeleted

…does not work because notDeleted is not a property on of the select. It’s a static property on SyncUp.

And this will be solved in Swift 6.1. In fact, I’ve got a Swift 6.1 snapshot installed right. If I flip to that snapshot…

And these queries compile! And now we are really starting to see some power here. It is so easy to create a suite of query helpers that we can be sure that we always query for our data in the most correct way possible. Not only in terms of generating valid SQL statements with data bound properly, but also that the predicates used to fetch certain rows is correct and consistent across the entire app.

So, this is all looking pretty amazing, but so far we aren’t even using any of these queries in our app. We of course want to be able to use these queries with our @SharedReader property wrapper instead of the raw SQL that is being used now.

Currently we are still executing queries with raw SQL strings:

@SharedReader(
  .fetchAll(sql: #"SELECT * FROM "facts" WHERE "isArchived""#)
)

It is not possible to use a query expression here from the Structured Queries library because that functionality is not built into the StructuredQueries library. Its intent is to be only a library for building SQL statements. It shouldn’t have anything to do with our Swift Sharing library.

But, our new SharingGRDB library is an appropriate place to put this kind of integration code between our new query builder library and the Swift Sharing tools that integrate with GRDB. To get access to those tools we will depend on a special branch that has not yet been released…

Now we can import SharingGRDB into our file:

import SharingGRDB

Which instantly gives us access to a fetchAll shared reader key that knows how to interface with our query builder syntax. This…

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

…can now be shortened to just this:

@SharedReader(.fetchOne(Fact.archived.count()))
var archivedFactsCount = 0

Quite amazing! We get to use this lightweight query builder syntax directly in fetchAll because it is made up of Hashable data. And we now have a static description of the query so that the compiler has our backs when it comes to accidental typos or other kinds of mistakes that could lead to invalid SQL. And this feature should work exactly as it did before.

Next we compute the count of unarchived facts, which can be shorted to:

@SharedReader(.fetchOne(Fact.unarchived.count()))
var unarchivedFactsCount = 0

And we have the dynamic query for fetching unarchived facts with a specific order, which now becomes:

$favoriteFacts = SharedReader(
  .fetch(
    Fact.unarchived.order {
      switch ordering {
      case .number: ($0.number, $0.savedAt.descending())
      case .savedAt: $0.savedAt.descending()
      }
    }
  )
)

OK, technically this code has become longer, in some sense. But really what we have done is removed two layers of indirection. Previously we had to hop down to the Facts type to understand how the query was constructed. And further, in that type we also had to hop over to the orderingTerm computed property to understand how the order was constructed. Now we get to delete all of that code and we can now see our query directly in line where our shared state resides. That is absolutely incredible.

And we have our final select query for archived facts, which can be simplified to:

@SharedReader(.fetchAll(Fact.archived))
var archivedFacts: [Fact]

In fact, because the type of Fact is encoded into the query itself, which was not possible when using a raw SQL string, we can even leave off the type annotation entirely:

@SharedReader(.fetchAll(Fact.archived)) var archivedFacts

The query builder is short and safer and is reusing logic across the entire app! Win-win-win!

Absolutely incredible.

We have now rewritten all of the queries used in the app with our query builder, and integrated them into all of the @SharedReader state held in our features. Each step of the way we were able to shorten code, reduce noise, improve safety and just overall simplify.

But there are other kinds of SQL statements that the app executes that aren’t queries for fetching data. There are insertions, updates and deletions. And it turns out that the Structured Queries library has some really amazing tricks up its sleeve when it comes to these kinds of queries.

In the FactFeatureModel we do the following to insert a new fact into the database:

try database.write { db in
  _ = try Fact(number: count, savedAt: now, value: fact)
    .inserted(db)
}

There’s something a little subtle about this code. The id field has been left off when initializing the Fact. We are allowed to do that because that field is optional:

@Table
struct Fact:
  Codable,
  Equatable,
  Identifiable,
  FetchableRecord,
  MutablePersistableRecord
{
  var id: Int64?
  …
}

And that is a little strange. A Fact should not have an optional ID. The only time it needs to be unspecified is during the transient moment of constructing a brand new value to insert into the database. But once the Fact is inserted into the database it will always have an ID and it should never be nil.

This is a very tricky thing to get right, and GRDB has dedicated documentation to describe all of the nuances. We can’t make id non-optional because then it would be impossible to insert a Fact into the database and let SQLite choose a unique ID for us. And we don’t want to make it optional because then we have handle nil all of the place in our code, and further an optional ID for an Identifiable conformance is quite strange. All models with nil for their ID are identified as the same value.

Well, our @Table macro handily solves this problem for us. It detects that id column and interprets that to mean the primary key on the table. And in that case it is able to introduce a few new tools. First, it automatically creates a Draft inner type that has all of the same fields as Fact, but without the primary key:

Fact.Draft(number: count, savedAt: now, value: fact)

This is an appropriate type to use when you are collecting all of the data for a fact and before it has been persisted to the database.

Next, there is a special insert static method on Fact that takes a draft to be inserted into the database:

try Fact.insert(
  Fact.Draft(number: count, savedAt: now, value: fact)
)

This is now a QueryExpression that corresponds to the following SQL statement:

INSERT INTO "facts" ("isArchived", "number", "savedAt", "value")
VALUES (0, 3, '2025-02-19 23:23:49', '3 is a good number!')

Note that it is important for us to not have to specify the “id” column since SQLite is going to generate that for us.

All that is left to do is execute this query. The tool to accomplish this comes from another library of ours that provides integration tools between our Structured Queries library and GRDB, but that library comes for free with SharingGRDB and so can be executed right away:

try database.write { db in
  try Fact.insert(
    Fact.Draft(number: count, savedAt: now, value: fact)
  )
  .execute(db)
}

That is all it takes. And right now we don’t need the data returned from the insert, but suppose we did want the actual Fact back after the insertion, with its ID set up and everything. Well, SQL already has a concept for this called RETURNING. It allows you to insert a row into a database and then return certain columns that were inserted.

Like maybe we want to use the newly-created ID, or suppose the database was responsible for setting the savedAt:

let (id, savedAt) = try Fact.insert(
  Fact.Draft(number: count, savedAt: now, value: fact)
)
.returning { ($0.id, $0.savedAt) }
.fetchOne(db)

Or we can representing returning all columns from our insertion like so:

let fact = try Fact.insert(
  Fact.Draft(number: count, savedAt: now, value: fact)
)
.returning(\.self)
.fetchOne(db)

And thanks to all of these changes we can now make the id field of Fact non-optional:

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

Even better, we can even make it a let now, giving us even stronger guarantees that once the ID is set it can’t be mutated:

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

And we can get rid of the didInsert logic for mutating the previously optional ID.

There are a few more SQL statements in our app that we can improve. For example, currently this is what we do to archive a fact:

func archive(fact: Fact) {
  do {
    try database.write { db in
      var fact = fact
      fact.isArchived = true
      try fact.update(db)
    }
  } catch {
    reportIssue(error)
  }
}

This works, but also it isn’t ideal. Technically it is going to execute an update statement that updates all fields of the fact, even though all we want to update is the “isArchived” column. If somehow the data in the database has changed since we last captured this Fact value, then we could accidentally be updating the database with stale data.

It would be far better if we could just update the “isArchived” column, and luckily the StructuredQueries library allows just that. It is possible to construct an UPDATE statement with the library using the update static function:

Fact.update(set: <#(inout Record<Fact>) -> Void#>)

This takes a trailing closure that accepts a “record” as an argument. We can mutate that argument to describe how we want to set the data in the database:

Fact
  .update { $0.isArchived = true }

UPDATE "facts" SET "isArchived" = 1

However, this is not the correct query. This is going to update every single row in the database to flip their “isArchived” columns to true. Instead we only want to do this for one particular fact, which is the one whose ID matches the ID we have locally:

Fact
  .update { $0.isArchived = true }
  .where { $0.id == fact.id }

UPDATE "facts" SET "isArchived" = 1
WHERE ("facts"."id" = 1)

But we are allowed to put the where first:

Fact
  .where { $0.id == fact.id }
  .update { $0.isArchived = true }

…which may be a little safer in general. This puts our filter on the rows we want to update front and center because it updates can be dangerous if we forget to specify which rows we want to update.

We’re even able to access the current value when making the mutation, like if we wanted to toggle the “isArchived” rather than just setting to true:

Fact
  .where { $0.id == fact.id }
  .update { $0.isArchived = !$0.isArchived }

There’s even a small subset of methods supported in this context, such as toggle():

Fact
  .where { $0.id == fact.id }
  .update { $0.toggle() }

UPDATE "facts" SET "isArchived" = NOT ("facts"."isArchived")
WHERE ("facts"."id" = 1)

But for our purposes, we do want to set the “isArchived” column to true:

Fact
  .update { $0.isArchived = true }

This is now the query we want to execute:

try database.write { db in 
  try Fact
    .where { $0.id == fact.id }
    .update { $0.isArchived = true }
    .execute(db)
}

And this is a safer query now. We can be very precise that we only want to update the “isArchived” column of our fact, and not touch any of the other data. That way we can be sure we do not accidentally update the database with state data.

And finally we have a query left for deleting a fact by its ID. Such a query can be constructed like so:

Fact
  .where { $0.id == fact.id }
  .delete()

And we can execute it like this:

try database.write { db in 
  try Fact
    .where { $0.id == fact.id }
    .delete()
    .execute(db)
}

That’s all it takes. We now see how we can select, insert, update and delete rows from a SQLite database using our powerful StructuredQueries library.

We have now properly set up the problem that we want to solve. We want a simple Swift API for constructing SQL statements that can be executed by SQLite. We would like this API to mimic what one would do when writing SQL directly, but should have the added benefit of better discoverability thanks to autocomplete, better safety thanks to static types, and the ability to reuse and compose queries together. And further we have given a preview of what this Swift API can look like by taking a peek at our Structured Queries library, which has not yet been released.

And that will come in due time, but for now let’s take a look at what it takes to build a query building library like this. It turns out to require the full power of nearly every feature available to modern Swift, including existentials, protocols with primary associated types, key paths, and even variadic generics. This library simply would not be possible to build in a version from Swift from just a year ago.

And along we the way we are also going to use this as an opportunity to dive deep into the concepts of SQL as its own language. It is a powerful language, and you will be a better engineer for being familiar with it.

We are going to start by defining a schema for some data types so that we can construct some interesting queries, and then see what it takes to build a library that can handle those queries.

Let’s begin…next time!