OK, we have now gotten our feet wet with querying the database and making changes to the database using our SQLiteData library. For the most part it really isn’t that different from SwiftData. You use the @FetchAll property wrapper instead of the @Query macro, and make mutations by executing SQL queries instead of mutating reference types and calling methods on a ModelContext. And we personally think it’s actually a superpower that we have direct access to SQL, not a downside, because SQL offers incredible features that are hidden from us in SwiftData, which we will soon see more of.

But for now let’s move onto the 2nd feature of our app, which is the ability to tap on a game, drill down to a new screen, and manage a list of players and their scores for that particular game. This screen is going to have a decent amount of logic because you will be able to add and remove players, increment and decrement their scores, and even sort the players by the score in increasing or decreasing fashion.

And because of this we are going to build this feature so that it uses an observable model to encapsulate its logic and behavior, and that will allow us to show that all of the tools we have been demonstrating work just as well outside of SwiftUI views as they do in SwiftUI views. This is in stark contrast to SwiftData, which practically forces you to put everything in the view, even if that’s not appropriate to do. Our tools work in SwiftUI views, observable models, and even in UIKit view controllers.

So, let’s get started.

Let’s create a new file called GameFeature.swift…

And we are going to start by pasting in a bunch of scaffolding for the basics of a game view:

import SQLiteData
import SwiftUI

struct GameView: View {
  init(game: Game) {
  }

  var body: some View {
    Form {
      if <#No players?#> {
        ContentUnavailableView {
          Label("No players", systemImage: "person.3.fill")
        } description: {
          Button("Add player") { <#Action#> }
        }
      } else {
        Section {
          ForEach(<#Players#>) { player in
            HStack {
              Button {
                <#Action#>
              } label: {
                if let image = UIImage(data: <#Image data#>) {
                  Image(uiImage: image)
                    .resizable()
                    .scaledToFill()
                } else {
                  Rectangle()
                }
              }
              .foregroundStyle(Color.gray)
              .frame(width: 44, height: 44)
              .clipShape(Circle())
              .transaction { $0.animation = nil }
              
              Text(player.name)
              Spacer()
              Button {
                <#Action#>
              } label: {
                Image(systemName: "minus")
              }
              Text("\(player.score)")
              Button {
                <#Action#>
              } label: {
                Image(systemName: "plus")
              }
            }
            .buttonStyle(.borderless)
          }
          .onDelete { indexSet in <#Action#> }
        } header: {
          HStack {
            Text("Players")
            Spacer()
            Button {
              <#Action#>
            } label: {
              Image(systemName: "arrow.down")
            }
          }
        }
      }
    }
    .navigationTitle(<#Title#>)
    .toolbar {
      ToolbarItem {
        Button {
          <#Action#>
        } label: {
          Image(systemName: "square.and.arrow.up")
        }
      }
      ToolbarItem {
        Button {
          <#Action#>
        } label: {
          Image(systemName: "plus")
        }
        .alert("New player", isPresented: <#Is alert presented?#>) {
          TextField("Player name", text: <#New game title#>)
          Button("Save") { <#Action#> }
          Button("Cancel", role: .cancel) { }
        }
      }
    }
  }
}

#Preview {
  let game = prepareDependencies {
    $0.defaultDatabase = try! appDatabase()
    try! $0.defaultDatabase.seed()
    return try! $0.defaultDatabase.read { db in
      try Game.fetchOne(db)!
    }
  }

  NavigationStack {
    GameView(game: game)
  }
}

There’s a lot of view hierarchy here, and a lot of placeholders we’ve gotta fill out, but we don’t feel it’s worth your time for us to build all of this from scratch. We’re not here to teach you SwiftUI, but rather how to use SQLiteData. But we can see from the preview that there is a view with a title, a list of players, the players’ scores, buttons that will eventually allow us to increment and decrement the scores, a button for adding a new player, and a share icon that will be used for sharing games with other iCloud users.

And browsing through the actual view code we will not see anything too surprising:

• The GameView has an initializer that takes a game.

• If the game has no players we display a ContentUnavailableView to prompt the user to add a player.

• Otherwise we display the players in a ForEach where we display each image, name, score, with decrement and increment buttons.

• And we support deleting players via the swipe action.

• Further, in the header of this list of players we will display a button that allows flipping the sort of players by their score in either an ascending or descending fashion.

• And finally in the toolbar of the view we have a button for sharing this game with another iCloud user, as well as a button for adding a player, and we have gone ahead and stubbed out an alert like we did over in the games list view.

So, as we can see, this view has quite a bit more behavior than the games list view did. And we could of course start cramming a bunch of state into this view and implementing all of these action closures. Our tools work just fine for that. But some people like to take complex views like this and extract their logic and behavior into an observable model so that it can be easier to understand in isolation and even test.

At the end of the day we have our personal preference on how we like to handle these things, just as you do, and we want to build tools that can be used in a variety of styles of building apps. And so to demonstrate this we are going to define an @Observable model that holds all the logic and behavior of this feature, and then the view can simply read state from the model and invoke methods on the model:

@Observable class GameModel {
}

Let’s start by modeling all of the state this will need to hold. We will certainly need the game that is passed to the view:

let game: Game

And we’ll want state to represent whether the new player alert is presented as well as the name of the new player, similar to what we did in the games feature:

var isNewPlayerAlertPresented = false
var newPlayerName = ""

We’ll want a boolean that determines if we are sorting the scores in an ascending or descending fashion:

var sortAscending = false

And we’ll want to fetch all of the players associated with the game we are viewing:

@FetchAll var players: [Player]

A few things to note about this. First, the @Observable macro does not play nicely with property wrappers, and so we have to explicitly @ObservationIgnored it:

@ObservationIgnored @FetchAll var players: [Player]

The next thing to note is that this @FetchAll tool is being used in an observable model, and this is in stark contrast to SwiftData’s tools. The @Query macro from SwiftData can only be used in views, and so that means if you find yourself in a situation where your view has gotten so complex that you’d like to extract some of the logic to an observable model to make it possible to test, well you are out of luck. You will have to forgo all of the niceties of the @Query macro and try to recreate that tool’s features yourself, which can be quite difficult to get right. But our tool naturally works in an observable model just fine.

And the final thing to note is that this @FetchAll is different from our previous one in the games list view. That one wanted to unconditionally load all games for display, and this one wants to only load players for a specific game. It is a dynamic query that depends on outside information, whereas the other one was static and did not need to change.

For such queries what we do is start the query as a none query:

@ObservationIgnored @FetchAll(Player.none) var players

This represents a query that simply does not fetch any data and hence players will be an empty array. And then later in the model once we have all the information available to us, we can dynamically load a new query into the players value.

That is the basics of the state we will hold in this model, and we can add an initializer for the one piece of state that does not have a default:

init(game: Game) {
  self.game = game
}

And while we have not implemented any logic yet we can start filling in some of the view placeholders and that will help guide us in adding methods to the model for the logic. First we will start holding onto a model in the view, and we will do so as a @State variable so that this view owns its model:

struct GameView: View {
  @State var model: GameModel
  init(game: Game) {
    _model = State(wrappedValue: GameModel(game: game))
  }
  …
}

Now that the view had access to the model we can start reading state from it, like when we check if the players array is empty:

if model.players.isEmpty {
  …
} else {
  …
}

The action of the “Add player” button will just call out to the observable model since that is where all of the logic of this feature will live:

Button("Add player") { model.addPlayerButtonTapped() }

And we can go ahead and implement that method:

func addPlayerButtonTapped() {
  newPlayerName = ""
  isNewPlayerAlertPresented = true
}

Next we can update the ForEach to use the players in the model:

ForEach(model.players) { player in
  …
}

We will skip over the player photo button right now, but that will later be used for changing the photo of each player.

Next we have the minus and plus buttons, and for each of the buttons we will invoke methods on the model again:

Button {
  model.decrementButtonTapped(for: player)
} label: {
  Image(systemName: "minus")
}
Text("\(player.score)")
Button {
  model.incrementButtonTapped(for: player)
} label: {
  Image(systemName: "plus")
}

And it is straightforward to implement these methods. Let’s start with decrementButtonTapped:

func decrementButtonTapped(for player: Player) {
}

We need to write to the database in here, and so we will need to open a write transaction. But to do that we do need access to the database in this model, which is easy enough to do with the @Dependency property wrapper:

@ObservationIgnored @Dependency(\.defaultDatabase) var database

And again, this tool works just fine in observable models, which is in stark contrast to the SwiftUI version of this tool, the @Environment property wrapper. It can only be used in views.

Now we can start a write transaction:

try database.write { db in
}

We can construct a query that updates the player handed to this method. That can begin with using find to select a player whose primary key is a certain value:

Player.find(player.id)

Then we can construct an update query using the update method:

Player.find(player.id)
  .update { }

This trailing closure is handed a representation of the schema of the Player table that gives us a type-safe and schema-safe way to represent queries on the table. We can even make very simple mutations to this value that get automatically translated into the equivalent SQL. For example, we can literally decrement the score field:

Player.find(player.id)
  .update { $0.score -= 1 }

It’s important to remember that we are only constructing a SQL query, not actually executing anything here. This value right represents SQL like this:

UPDATE "players"
SET "score" = "score" - 1
WHERE "players"."id" = ?

…except we don’t have to write this SQL string by hand, which risks typos or type mismatches.

Finally we can execute the query:

Player.find(player.id)
  .update { $0.score -= 1 }
  .execute(db)

And again there are no user facing errors that can be thrown here so we will just catch all errors and report them as purple runtime warnings:

func decrementButtonTapped(for player: Player) {
  withErrorReporting {
    try database.write { db in
      try Player.find(player.id)
        .update { $0.score -= 1 }
        .execute(db)
    }
  }
}

And the increment method is basically the same:

func incrementButtonTapped(for player: Player) {
  withErrorReporting {
    try database.write { db in
      try Player.find(player.id)
        .update { $0.score += 1 }
        .execute(db)
    }
  }
}

Now it may seem a little strange to you to so fully separate the description of what we want to do in the database from the actual doing of the thing in the database. Many frameworks out there prioritize a syntax such as:

player.score += 1
try player.save()

But we feel this syntax is not ideal and is really just limiting in the long run:

• First off all, such a save method has no choice but to save all fields of the player even though we are only updating the score. If there is a slight chance that the value we are holding in memory right at this moment is stale, then we risk writing old data back to the database. What we really want to do is truly take the current score in the database and increment it, and by fully embracing the query language that is super easy to do.

• And second of all, hiding database queries inside this save() method makes it all too easy to not think about transactions when executing queries. That can lead you to executing multiple queries, each in their own transactions, which can make it possible for data races to wiggle their way in between the transactions. We feel the more explicit database.write syntax is better because it makes the transaction upfront and center.

Next we can address the onDelete action, which will again just call a method on the model so that the model can be in charge of that logic:

.onDelete { offsets in model.deletePlayers(at: offsets) }

And the implementation of this method looks similar to what we did over in the games list view:

func deletePlayers(at offsets: IndexSet) {
  withErrorReporting {
    try database.write { db in
      try Player.find(offsets.map { players[$0].id })
        .delete()
        .execute(db)
    }
  }
}

Next we have the action for the sort button, which we will again just invoke a method. And we can even use the sortAscending state in the model to determine if we should show an up or down arrow:

Button {
  model.toggleSortButtonTapped()
} label: {
  Image(systemName: model.sortAscending ? "arrow.down" : "arrow.up")
}

And then in this method we will just toggle the sortAscending state:

func toggleSortButtonTapped() {
  sortAscending.toggle()
}

We are going to of course have to do more work in this method to actually sort the players, but let’s just keep going for now.

The navigation title can be taken from the game in the model:

.navigationTitle(model.game.title)

The action for the share button will be handled later, once we’ve integrated iCloud synchronization into this app.

Next we have the action for the “+” button in the toolbar, and we can just call out to the existing addPlayerButtonTapped method:

Button {
  model.addPlayerButtonTapped()
} label: {
  Image(systemName: "plus")
}

And now at the very end of the view we can fill in the place holders with bindings and state from the model, as well as call a method when the “Save” button is tapped:

.alert("New player", isPresented: $model.isNewPlayerAlertPresented) {
  TextField("Player name", text: $model.newPlayerName)
  Button("Save") { model.saveNewPlayerButtonTapped() }
  Button("Cancel", role: .cancel) { }
}

To implement this method we need to start a write transaction and construct an insert SQL statement:

func saveNewPlayerButtonTapped() {
  withErrorReporting {
    try database.write { db in
      Player.insert { }
    }
  }
}

Inside this trailing closure we can construct a draft of a player that belongs to the current game and has the new player name, and execute the query:

try Player.insert {
  Player.Draft(gameID: game.id, name: newPlayerName)
}
.execute(db)

We would hope that’s it takes to implement this feature, but we do have a bit more work to do. If we run the preview we will see it has a nice empty state, which seems good. But then if we add a player we will see that they do not appear in the list.

Well, that is just because the players state is still being only powered by a none query:

@ObservationIgnored @FetchAll(Player.none) var players

We need to actually update the query that powers this state. There are two moments when we should do this: first when the view appears and then again if the sort is ever changed.

To make it so that we can load a new query into the players state from each of these events we will define a single method that can be called from multiple places:

private func reloadData() async {
}

The players state has a projected value:

$players

And that projected value gives you access to a load method that can load a new query to power the state:

$players.load()

We can construct a SQL select statement that selects all players belonging to the current game:

$players.load(
  Player
    .where { $0.gameID.eq(game.id) }
)

And we can further sort these players by their score, either ascending or descending depending on the sortAscending state:

$players.load(
  Player
    .where { $0.gameID.eq(game.id) }
    .order {
      if sortAscending {
        $0.score.asc()
      } else {
        $0.score.desc()
      }
    }
)

And we can have it so that whenever the database changes we refresh the view with animation:

$players.load(
  Player
    .where { $0.gameID.eq(game.id) }
    .order {
      if sortAscending {
        $0.score.asc()
      } else {
        $0.score.desc()
      }
    },
  animation: .default
)

This load method is throwing and async:

try await $players.load(
  …
)

We are not in a throwing context and this should query should not throw any errors unless we did something wrong, so let’s wrap everything in withErrorReporting:

await withErrorReporting {
  …
}

Now we can invoke this from a few places. For example, when the sortAscending state is changed we should update the query:

var sortAscending = false {
  didSet { Task { await updateQuery() } }
}

And when the view appears we should update the query. It would not be appropriate to do this in the init of the GameModel. It’s best to keep the init of features lightweight because they can often be created long before they are needed, such as in the destinations of navigation links.

The best way to do this is by using the task view modifier to invoke a method on the model:

.task { await model.task() }

And then that method can update the query:

func task() async {
  await updateQuery()
}

And with that done everything is now working in the preview. We see the seeded players, we can sort the players with animation, we can even add and delete players, and we can of course increment and decrement scores.

We can even go over to the Schema.swift file to update the seed function so that we seed the “Family gin rummy” game with 3 players:

try db.seed {
  Game.Draft(id: UUID(1), title: "Family gin rummy")
  Game.Draft(id: UUID(2), title: "Weekly poker night")
  Game.Draft(id: UUID(3), title: "Mahjong with grandma")

  Player.Draft(gameID: UUID(1), name: "Blob", score: 20)
  Player.Draft(gameID: UUID(1), name: "Blob Jr", score: 100)
  Player.Draft(gameID: UUID(1), name: "Blob Sr", score: 60)
}

To tie the bow we will now make it possible to navigate to the game view from the games list view by adding a navigation link:

ForEach(games) { game in
  NavigationLink {
    GameView(game: game)
  } label: {
    Text(game.title)
      .font(.headline)
  }
}

Now let’s run the app in the simulator, and we will see all of the games we previously created, and we can tap on one to drill down to the details of the game, and from there we can add new players, sort players, and change scores.

Now one quick thing to note is that if you look very, very closely when drilling into a game, you may for a brief moment see the empty state of the players list while the query to fetch players is being executed. This is actually something that can happen in SwiftData too, and to fix it we will make it so that the empty state only appears when there are no players, and when the query is not currently executing. We can do this by using the isLoading property defined on the $players projected value:

if !model.$players.isLoading, model.players.isEmpty {
  ContentUnavailableView {
    …
  }
} else {
  …
}

And that instantly fixes that little animation glitch.

Now our project is starting to feel like a real app. We’ve got navigation, we’ve got multiple screens that can be changes to a central database, we’ve got querying, filtering and sorting, and all the data is being persisted across runs of the app.

The next big project to tackle would be to get all of this data synchronizing to all of our devices, so that we can have our games and players on both our phone and iPad, but before doing that let’s add just a few more niceties to the app to show off more features of SQLiteData, and this will give us an opportunity to show off a performance improvement.

Let’s take a look.

We want to update the UI of the list so that we can show the number of players in each game:

HStack {
  Text(game.title)
    .font(.headline)
  Spacer()
  Text("2")  // Need to compute number of players in each game
  Image(systemName: "person.2.fill")
    .foregroundStyle(.gray)
}

That sounds like a very reasonable thing to want to display, but there are unreasonable ways to accomplish this. One unreasonable, but very easy thing we could do, is define a helper method that queries for all of the players belonging to a game:

func players(for game: Game) -> [Player] {
  (try? database.read { db in
    try Player.where { $0.gameID.eq(game.id) }
      .fetchAll(db)
  })
  ?? []
}

And then we could compute that array of players so that we can count them in the view:

Text("\(players(for: game).count)")

That does certainly get the feature working with very little work, but it is also secretly firing off a ton of queries. For every time the body of the view re-computes, for whatever reason, and for each row in the list of games, we are going to execute a SQL query to load all players for each game, decode their data into memory, and then just simply count the number of elements in the array.

To see this concretely, we are going to enable a debug feature in our database connection that will help make it painfully obvious that what we have here is not correct. If we go back to the code that our editor wrote for us when we prompted it to create a database connection and migrate our database, we can add just a few lines of code to make the connection print every single query submitted to SQLite:

var configuration = Configuration()
configuration.prepareDatabase {
  #if DEBUG
    $0.trace { print($0.expandedDescription) }
  #endif
}
let database = try SQLiteData.defaultDatabase(
  configuration: configuration
)

With that, if we run the app in the simulator we will see a whole bunch of queries spewed to the logs:

BEGIN DEFERRED TRANSACTION
PRAGMA schema_version
SELECT "players"."id", "players"."gameID", "players"."name", "players"."score" FROM "players" WHERE ("players"."gameID") = ('51a3247d-3005-43ea-a2ec-d6806bc0ffa4')
COMMIT TRANSACTION
BEGIN DEFERRED TRANSACTION
PRAGMA schema_version
SELECT "players"."id", "players"."gameID", "players"."name", "players"."score" FROM "players" WHERE ("players"."gameID") = ('65dd286a-0b05-4847-9375-fe3f7473d521')
COMMIT TRANSACTION
BEGIN DEFERRED TRANSACTION
PRAGMA schema_version
SELECT "players"."id", "players"."gameID", "players"."name", "players"."score" FROM "players" WHERE ("players"."gameID") = ('ab705452-3710-4d9d-b133-bb7fb0cb517f')
COMMIT TRANSACTION
BEGIN DEFERRED TRANSACTION
PRAGMA schema_version
SELECT "players"."id", "players"."gameID", "players"."name", "players"."score" FROM "players" WHERE ("players"."gameID") = ('e2709530-9a98-49a9-8589-e68ac1d8a37d')
COMMIT TRANSACTION

We get a query executed for each game in the database, multiplied by the number of times the body is re-computed. And over the lifecycle of this app this body could re-computed hundreds or thousands of times, and all of this work is happening on the main thread. We are just performing a lot of work that is not necessary at all because all we want is the count of players in each game.

And further, because our query is being performed in an ad hoc way instead of using the @FetchAll property wrapper, changes to this count are not being observed. We can drill into a game, add a player, and when we drill out the count does not reflect the addition.

Well, instead of fetching all players into memory just to count them, we could do a slightly more reasonable thing by computing the count in SQLite itself:

func playerCount(for game: Game) -> Int {
  (try? database.read { db in
    try Player.where { $0.gameID.eq(game.id) }
      .fetchCount(db)
  })
  ?? 0
}

And then in the view we invoke this function like so:

Text("\(playerCount(for: game))")

That’s a little better since we are no longer loading database rows into memory, but we are still performing a bunch of queries:

BEGIN DEFERRED TRANSACTION
PRAGMA schema_version
SELECT count(*) FROM "players" WHERE ("players"."gameID") = ('65dd286a-0b05-4847-9375-fe3f7473d521')
COMMIT TRANSACTION
BEGIN DEFERRED TRANSACTION
PRAGMA schema_version
SELECT count(*) FROM "players" WHERE ("players"."gameID") = ('ab705452-3710-4d9d-b133-bb7fb0cb517f')
COMMIT TRANSACTION
BEGIN DEFERRED TRANSACTION
PRAGMA schema_version
SELECT count(*) FROM "players" WHERE ("players"."gameID") = ('e2709530-9a98-49a9-8589-e68ac1d8a37d')
COMMIT TRANSACTION
BEGIN DEFERRED TRANSACTION
PRAGMA schema_version
SELECT count(*) FROM "players" WHERE ("players"."gameID") = ('51a3247d-3005-43ea-a2ec-d6806bc0ffa4')
COMMIT TRANSACTION

And this is all happening inside the body of the view, which really should be as fast as possible since it’s all on the main thread.

And things we could worse if we wanted to perform additional work with these counts. For example, want if we wanted to sort our games by the number of players in each game? If we naively performed a sort on the games array by calling out to the playerCount(for:) function we could potentially incur dozens or hundreds more database requests:

let sortedGames = games.sorted {
  playerCount(for: $0) < playerCount(for: $1)
}

Well, luckily for us there is a much better way, and the trick is to fully embrace the powers of SQLite. Relational databases like SQLite allow you to join tables together and aggregate information across the pairings of the rows between the tables. For example, we can join the “games” table to the “players” table and then aggregate the count of players for each game. And this all happens in a single super efficient query that even runs off the main queue and just tells the view to re-compute the body once it is finished executing.

Even more amazing, we can construct this more complex query directly in line where we are currently fetching all games:

@FetchAll var games: [Game]

First we need to define a new type that will hold all of the information we are querying for. This includes the game and the count of players for each game:

struct Row {
  let game: Game
  let playerCount: Int
}

And further we need to annotate this type with @Selection so that our library knows how to decode data from the database into the type:

@Selection struct Row {
  let game: Game
  let playerCount: Int
}

Now we can start writing our query. We start by selecting all the rows from the “games” table:

Game.all

Then we left join to the “players” table:

Game
  .leftJoin(Player.all) { }

We are performing a “left” join because there may be some games that don’t have any players, and we wouldn’t want to exclude those games, which is what would happen if we used a regular join.

Then we provide the constraint for the join, which is that the id of the game should match the gameID of the player. This trailing closures takes a value representing the schema of the “games” table and a value representing the schema of the “players” table, and so we can write this constraint like so:

Game
  .leftJoin(Player.all) { $0.id.eq($1.gameID) }

OK, we have now joined our tables together. Now we can perform a select so that we can describe how to pull this data into the Row data type:

Game
  .leftJoin(Player.all) { $0.id.eq($1.gameID) }
  .select {

  }

This trailing closure is also handed values that represent the schemas of the “games” and “players” tables, and we can use them to construct the Row.Columns value:

Game
  .leftJoin(Player.all) { $0.id.eq($1.gameID) }
  .select {
    Row.Columns(
      game: $0,
      playerCount: $1.count()
    )
  }

This is very nearly done, but currently we are performing a count() aggregation and not telling SQLite how to group the rows when counting them. And so what it does by default is simply collapse all rows into a single one, and then you just get a total count of all players.

What we want to do is perform this count on a per-game basis, and so we use a group clause for that:

Game
  .group(by: \.id)
  .leftJoin(Player.all) { $0.id.eq($1.gameID) }
  .select {
    Row.Columns(
      game: $0,
      playerCount: $1.count()
    )
  }

And this is the actual query we want to execute, and we can do so in a @FetchAll:

@FetchAll(
  Game
    .group(by: \.id)
    .leftJoin(Player.all) { $0.id.eq($1.gameID) }
    .select {
      Row.Columns(
        game: $0,
        playerCount: $1.count()
      )
    },
  animation: .default
)
var rows

Note that we don’t even need to specify the type for rows because it’s already understood to be [Row] by the type system.

And I know this may seem like a lot, but rest assured that everything here is very standard, vanilla SQL, and the more you write queries like this the more you will get used to it. SQL gives you infinite flexibility in crafting very precise queries to grab just what you need from the database, and do so in a super efficient manner.

OK, now that we have fundamentally changed the collection of data that powers the view, we do have to update a few places in the view to go through this Row type to get at the game, so let’s do that real quick…

And further we can now properly display the count of players for each game:

Text("\(row.playerCount)")

And our feature is now complete. The games list view behaves just as it did before, but now everything is computed in a single query. We can even look in the logs to confirm that indeed far fewer queries are being executed. And the one we write using our type-safe and schema-safe query builder was translated into the following valid SQL string:

SELECT 
  "games"."id" AS "id", 
  "games"."title" AS "title", 
  count("players"."id") AS "playerCount" 
FROM "games" 
LEFT JOIN "players" ON ("games"."id") = ("players"."gameID") 
GROUP BY "games"."id"

And want to see something really amazing? If we wanted to sort these games by the number of players in a descending fashion, we just need to tack on the following to our query:

.order { $1.count().desc() }

And now our games are sorted, and done so efficiently by SQL. We can even see that the query in the logs updated to the following:

SELECT 
  "games"."id" AS "id", 
  "games"."title" AS "title", 
  count("players"."id") AS "playerCount" 
FROM "games" 
LEFT JOIN "players" ON ("games"."id") = ("players"."gameID") 
GROUP BY "games"."id" 
ORDER BY count("players"."id") DESC

It’s really amazing.

And while we’re on this kick of showing off how SQLiteData allows your views to be efficient, let’s show off another trick. Currently the @FetchAll stored in our view will observe changes to the database so that it can refresh the view when necessary. And that’s great, but this happens even if this view is no longer being displayed, such as when we drill down to a game detail view. And this is in fact how SwiftData works too.

Typically this is not really a problem. If the data in your database isn’t changing at a frequent rate you probably do not need to worry about it. But, if you do have high frequency changes, you may find that your app gets sluggish as SwiftUI is constantly re-computing its views, even when they are not visible.

Well, SQLiteData comes with the perform tool to help with this. To use this tool we are going to move where we execute the query from being inline at the declaration of the variable to inside of the task modifier. So, I am going to cut everything attached to the @FetchAll, including the parentheses, and only specify the type:

@FetchAll var rows: [Row]

And then down at the bottom of the view I will add the task view modifier, which allows us to perform async work when a view appears, and that async work is cancelled when the view disappears:

.task {
}

And in here we can load a new query into the $rows projected value:

await withErrorReporting {
  try await $rows.load
}

And I will paste in what I cut earlier:

.task {
  await withErrorReporting {
    try await $rows.load(
      Game
        .group(by: \.id)
        .leftJoin(Player.all) { $0.id.eq($1.gameID) }
        .order { $1.count().desc() }
        .select {
          Row.Columns(
            game: $0,
            playerCount: $1.count()
          )
        },
      animation: .default
    )
  }
}

If we stopped just here it would not work exactly how we want. See, the await we are performing when we do $rows.load only suspends for the amount of time it takes the execute that query a single time and get data loaded into the rows variable. That does not take much time, probably just a millisecond or so, and so it’s not like when the view disappears and this task context is cancelled is going to somehow magically cancel the subscription the @FetchAll has to the database.

But, the load method secretly returns a value that can be used to suspend for a lot longer, and in fact it will suspend for as long as the subscription to the database, and cancelling the surrounding async context will also cancel the subscription. To get access to this handle we just have to access the .task property of the value returned from load:

.task {
  await withErrorReporting {
    try await $games.load(
      …
    )
    .task
  }
}

Now we we run in the simulator again, navigate to a game, clear the logs, and add a player, we see a much smaller set of logs. It’s only the INSERT statement, followed by another SELECT statement for refreshing the list of players. And only if we navigate back to the previous screen do we re-compute this query:

SELECT 
  "games"."id" AS "id", 
  "games"."title" AS "title", 
  count("players"."id") AS "playerCount" 
FROM "games" 
LEFT JOIN "players" ON ("games"."id") = ("players"."gameID") 
GROUP BY "games"."id" 
ORDER BY count("players"."id") DESC

Our view is now more efficient, it only took a few small changes to our code, and as far as we know this kind of optimization is just not possible in SwiftData.

OK, we have now employed a very advanced technique in our application. We have constructed a query that is capable of simultaneously selecting all games from our database, as well as a count of players in each game, and we are displaying that information in the view. Any change made to the database will cause the view to re-render, and we even made this a little more efficient by cutting off that subscription to the database when the view is not displayed on screen.

Both of these things are just not really possible with SwiftData. SwiftData does not expose a powerful enough query language to compute what we want in a single query, and it does not give us a tool to stop the view from rendering when it’s not visible.

Let’s move onto the next big feature that our SQLiteData library supports, which is seamless assets. For many kinds of apps we can store assets directly in the database, and SQLiteData takes care of packaging up that data into a CKAsset to send off to iCloud. Let’s explore this by implementing a feature that allows us to choose images for each of our players…next time!