Video #311: Sharing with SQLite: Advanced Queries

Episode: Video #311 Date: Jan 27, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep311-sharing-with-sqlite-advanced-queries

Description

Let’s leverage our new @Shared SQLite strategy by adding a brand new feature: archiving. We will see how easy it is to incorporate queries directly into a SwiftUI view, and we will expand our tools to support even more kinds of queries.

Video

Cloudflare Stream video ID: 697869f12079b8cb85fafc7883f42d78 Local file: video_311_sharing-with-sqlite-advanced-queries.mp4 *(download with --video 311)*

References

Transcript

— 0:05

Things are looking really good now. By moving the database connection to our dependencies system we have massively improved the ergonomics of using the @SharedReader property wrapper to fetch results from a SQLite database. We can now simply specify the query when declaring the shared state, and as long as we set up the database connection in the entry point everything will just work. And if we forget to set up the database connection we will get a helpful warning letting us know what needs to be done.

— 0:31

There are still more ergonomics improvements to make to how @SharedReader interacts with SQLite. For one thing, we have not yet dealt with dynamic queries. That is, queries that depend on state that the user can change, such as ordering the facts by the date they were saved or the number the fact is for. And another ergonomic problem is that we are specifying the query to power @SharedReader as a raw SQL string. It might be better to use a kind of query builder, like the one that comes with GRDB, in order get some type safety on the queries we write. Stephen

— 1:03

These are all things we can massively improve, and it’s amazing to see, but let’s have a little fun first. We now have a powerful tool for querying a database to fetch data, but we are barely taking advantage of these powers. Let’s add a few more features to this app so that we can see just how easy it is to incorporate all new queries for fetching data.

— 1:21

The feature we will add is the ability to archive facts that are no longer interesting to us, but that we don’t want to delete just yet. Archiving will remove the fact from the main list, but we will also add the ability to navigate to a screen for viewing all archived facts, as well as the ability to un archive facts in order to bring them back to the main list.

— 1:38

Let’s get started. Archived facts

— 1:41

Let’s start with some schema changes. The Fact model will now have a boolean field that determines if it has been archived or not: struct Fact: Codable, Equatable, Identifiable, FetchableRecord, MutablePersistableRecord { … var isArchived = false … }

— 1:54

And we also need to add a migration to our database in order to add this column to the “facts” table: migrator.registerMigration( "Add 'isArchived' column to 'facts'" ) { db in try db.alter(table: Fact.databaseTableName) { table in table.add(column: "isArchived", .boolean).defaults(to: false) } }

— 2:42

We are using defaults(to: false) so that all of our existing facts in the database will start out as not archived.

— 2:54

Next we will update the query that powers the favoriteFacts variable, which is responsible for displaying the facts on the screen. We want this list to only display unarchived facts, and we can do so by adding a “WHERE” clause to our query: @ObservationIgnored @SharedReader( .fetchAll(#"SELECT * FROM "facts" WHERE NOT "isArchived"#) ) var favoriteFacts: [Fact]

— 3:09

That’s all it takes. This will make it so that we never show archived facts in the main list.

— 3:14

Next we will update the UI to expose an “Archive” button. We will do this by overriding the “swipe actions” on each row of the ForEach : Text(fact.value) .swipeActions { Button("Archive") { } .tint(.blue) Button("Delete", role: .destructive) { } }

— 4:21

If we run the preview and swipe on a row we will see that we have an “Archive” and “Delete” action exposed.

— 4:29

For the “Archive” button we will invoke a new method on the model: Button("Archive") { model.archive(fact: fact) } .tint(.blue) …and we will implement this method in a moment.

— 4:40

Also the “Delete” button will invoke a method on the model, but it will be a little different from how we do things currently. Right now the onDelete view modifier has a trailing closure that is handed an entire IndexSet of indices that are to be deleted: .onDelete { indexSet in … }

— 4:53

It is designed this way because you can put the list into a mode where you are allowed to select multiple rows and delete them all at once. But with swipe actions it is only possible to perform the action on a particular row. So we will change the method defined on the model to take the fact that is to be deleted rather than an index set of facts to delete: Button("Delete", role: .destructive) { model.delete(fact: fact) }

— 5:20

Implementing these methods is quite straightforward. For archive(fact:) we can simply make a copy of the fact, change its isArchived property to be true , and then invoke the update method on the value that is given to us by the MutablePersistableRecord protocol: func archive(fact: Fact) { do { try database.write { db in var fact = fact fact.isArchived = true try fact.update(db) } } catch { reportIssue(error) } }

— 6:14

And the delete(fact:) method is even simpler. We just need to invoke the delete method on the value: func delete(fact: Fact) { do { try database.write { db in _ = try fact.delete(db) } } catch { reportIssue(error) } }

— 6:28

That is all it takes and we already have a basic version of the archiving functionality. We can swipe on a fact, archive it, and that fact is removed from the list. The fact has not been deleted, but it is being filtered out from the list thanks to the “

WHERE 6:53

Now you may be wondering, why not just perform the filtering logic in the view rather than altering the query? That is, why not go back to fetching all of the facts: .fetchAll(#"SELECT * FROM "facts""#)

WHERE 7:01

And then perform the filtering logic inside the ForEach to not render a row for the archived facts: ForEach(model.favoriteFacts) { fact in if !fact.isArchived { Text(fact.value) … } }

WHERE 7:15

Well, there are multiple problems with this.

WHERE 7:17

First, and most obvious, this has actually introduced a bug in our feature. We are currently showing the number of facts in the list as part of the header of the section: Text("Favorites (\(model.favoriteFacts.count))") But now this is counting all facts, not just the ones being displayed. And so the count shows we have 4 facts, but in the list there’s only 3. So we technically need to perform a calculation here to compute the number of unarchived facts.

WHERE 7:47

Further, there are a few performance problems with doing this. We are fetching facts that we don’t plan on ever displaying to the user, and there could potentially be hundreds or thousands of archived facts. And we are constructing an intermediate array of facts that will need to be computed every time the view’s body is re-computed, even if nothing about the facts array changed.

WHERE 7:57

Further, if statements inside ForEach s are a well-known performance killer in SwiftUI: ForEach(model.favoriteFacts) { fact in if !fact.isArchived { … } }

WHERE 8:01

So it is far better for us to perform the filtering logic in SQL: .fetchAll(#"SELECT * FROM "facts" WHERE NOT "isArchived""#) \…and not in our application code: ForEach(model.favoriteFacts) { fact in Text(fact.value) … } And so performing this kind of filtering logic in the app code is not the correct thing to do. The archived facts view

WHERE 8:18

SQLite’s greatest power is the clean separation of the storage of our data from the “views” into the data. You can create lots of lightweight queries for tailoring the data specifically for the features the user is interacting with, and so we should keep as much of this kind of logic in SQL as possible and out of our app’s code.

WHERE 8:35

Back when we were using the file system for this data we only had one view into the data, which is exactly how it was stored on disk. The only way to provide alternative views into the data was to compute it from scratch each time we needed the data, which can lead to excessive computations and inefficient code. SQLite allows us to avoid all of those downsides. Brandon

WHERE 8:53

OK, we now have the basics of the archived facts features implemented, but we don’t yet have a way to view our archived facts. Let’s create a new view that can be presented to display just those facts, and we will further give the user the option of unarchiving a fact, or completely deleting it.

WHERE 9:11

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

WHERE 9:18

And let’s add a new view to this file: import SwiftUI struct ArchivedFactsView: View { }

WHERE 9:25

I’m going to approach this view in a slightly different way than we approached the FactsFeatureView . I don’t anticipate this view getting very complicated over time. It will just display a list of facts where each row has swipe actions for unarchiving and deleting, and it will probably stay that way forever, or at least for a long time.

WHERE 9:44

In contrast, the facts feature has gotten quite complex over time. It started as just a simple feature for incrementing and decrementing a counter, as well as loading a fact for the current count. Then we addd the ability to save our favorite facts. Then the ability to delete those facts. Then the ability to archive facts. And soon we will add navigation to a new screen for viewing archived facts. That was just enough complexity to warrant moving all of that logic into a dedicated observable model so that it would be extracted out of the view and even testable one day.

WHERE 10:23

But for this new archived facts feature, I am going to approach it in the simplest way possible by just cramming all of the logic directly in the view. That’s not something we do often here on Point-Free, but it is totally fine to do for simple views.

WHERE 10:36

So, this view is going to hold onto an array of facts that are the archived facts: var archivedFacts: [Fact]

WHERE 10:42

But the way we are going to fetch these facts is by performing a query to our SQLite database. And the easiest way to do this is with our @SharedReader property wrapper: @SharedReader(.fetchAll(#"SELECT * FROM "facts" WHERE "isArchived""#)) var archivedFacts: [Fact]

WHERE 11:22

That’s right, this property wrapper works just fine in views, as well as observable models. Where ever it makes more sense for you to put your app’s logic you are free to use @SharedReader , as well as @Shared . This is of course in stark contrast to the @Query macro from Swift Data, whose magic only works when it is used directly in a view.

WHERE 11:52

Next we will implement the basics of the body of the view by displaying the archived facts in a form and adding swipe actions for “Unarchive” and “Delete”: var body: some View { Form { ForEach(archivedFacts) { fact in Text(fact.value) .swipeActions { Button("Unarchive") { } .tint(.blue) Button("Delete", role: .destructive) { } } } } .navigationTitle("Archived facts") }

WHERE 12:07

In order to implement the action closures for these buttons we need access to the database, which we can do by adding a @Dependency to the view: @Dependency(\.defaultDatabase) var database

WHERE 12:23

And now we can implement the action closures in much the same way we did back in the FactsFeatureModel : Button("Unarchive") { do { try database.write { db in var fact = fact fact.isArchived = false try fact.update(db) } } catch { reportIssue(error) } } .tint(.blue) Button("Delete", role: .destructive) { do { try database.write { db in _ = try fact.delete(db) } } catch { reportIssue(error) } }

WHERE 13:14

It’s of course a bit of a bummer to have so much logic crammed in the view. It’s really distracting when trying to understand how the view hierarchy is composed together. But, you can always extract out these action closures into little helper methods on the view if you want. And you can of course extract out all of the state and logic into a dedicated @Observable model if you really want.

WHERE 13:48

But, we don’t really expect this view to change much in the future, so it’s probably OK to keep things simple and not introduce an observable model just for this little bit of logic.

WHERE 14:08

We can also get a basic preview into place for this screen. We will just paste in the full preview since we’ve done this kind of thing a few times already: #Preview("Archived facts") { let _ = prepareDependencies { $0.defaultDatabase = DatabaseQueue.appDatabase let _ = try! $0.defaultDatabase.write { db in for index in 1...10 { _ = try Fact( isArchived: index.isMultiple(of: 2), number: index, savedAt: Date(), value: "\(index) was a good number." ) .inserted(db) } } } NavigationStack { ArchivedFactsView() } }

WHERE 14:14

We are using the prepareDependencies tool in order to set the defaultDatabase to be one with all migrations applied, and then we insert facts for the numbers 1 through 10, but only the facts about even numbers are archived. And then finally we display the ArchivedFactsView wrapped in a navigation stack.

WHERE 14:29

We can see right in the preview that only facts for even numbers are being displayed, and so that gives us confidence that our query is set up correctly. If our query was wrong, like say we accidentally negated isArchived : @SharedReader( .fetchAll(#"SELECT * FROM "facts" WHERE NOT "isArchived""#) )

WHERE 14:46

…then we can immediately see in the preview that non-archived facts are being displayed.

WHERE 15:03

And further, if we had a typo in our query: @SharedReader( .fetchAll(#"SELECT * FROM "facts" WHERE NOT "isArchive""#) )

WHERE 15:14

…the preview shows that there are no facts being displayed, and in the logs we have a very helpful message letting us know of our syntax error: SharingGRDB.swift:140: Caught error: SQLite error 1: no such column isArchive - while executing SELECT * FROM "facts" WHERE "isArchive"

WHERE 15:28

Let’s fix this bug real quick: @SharedReader(.fetchAll(#"SELECT * FROM "facts" WHERE "isArchived""#)) …to see that again our preview only shows facts for even numbers.

WHERE 15:34

And not only is the correct data showing, but even the behavior of the view works in the preview. We can delete and archive any fact to see that it is removed from the list. We can even inspect the logs of the preview to see what queries are being executed. Of course, the changes are only happening to an in-memory database, and so next time we run the preview all of the deleted facts come back. Navigating to archived facts

WHERE 16:44

And so it is incredibly powerful for us to exercise the full logic of our features in previews because it helps us catch small bugs in our app very quickly. You are still going to want a test suite to automate these kinds of checks, because you certainly aren’t going to fire up every single one of your previews to make sure they work correctly after every little change you make to a query. And of course we are all human and are prone to mistakes, and so after loading a preview for the 10th time to check for its correctness you may miss small, subtle bugs. Stephen

WHERE 17:14

OK, we now have a whole new view implemented for displaying the archived facts. We just need to navigate to this screen from the main facts feature.

WHERE 17:22

Let’s give that a shot.

WHERE 17:24

To keep things simple we will represent this navigation in state as a simple boolean: @Observable @MainActor class FactFeatureModel { … var isArchivedFactsPresented = false … }

WHERE 17:44

And we’ll add a toolbar button to the view for flipping this boolean to true : .toolbar { ToolbarItem { Button("Archived facts") { model.isArchivedFactsPresented = true } } }

WHERE 18:07

Next we will use the sheet(isPresented:) view modifier to drive a sheet from the new boolean state, and we will use presentation detents to make it possible to see the main list of facts from the list of archived facts: .sheet(isPresented: $model.isArchivedFactsPresented) { NavigationStack { ArchivedFactsView() } .presentationDetents([.fraction(0.4), .fraction(0.9)]) }

WHERE 18:54

And with that done we can run the app in the simulator to see that everything works as expected. We can tap the “Archived facts” button in the top-left to see all of our archived facts. And even cooler, if we unarchive a fact we will live see the fact removed from the sheet and inserted into the main list of facts. This is happening because the @SharedReader used in the root list is observing changes to the database, so when the sheet makes a change to the data it is immediately seen by the parent feature. This guarantees that our views are always in sync with the freshest data in the database, and it comes for free by virtue of the fact that we are using @SharedReader .

WHERE 19:36

One thing that is weird right now is that we are showing the “Archived facts” button even if there aren’t any facts. What if we could detect if there are any archived facts, and if there are not we omit that button entirely? Well, luckily for us, SQL excels at computing aggregate totals such as the number of rows that satisfy a condition by using the count function: SELECT count(*) FROM "facts" WHERE "isArchived" That is all it takes. And we might hope we could use it with our existing SQL tools built for @SharedReader : @ObservationIgnored @SharedReader( .fetchAll(#"SELECT count(*) FROM "facts" WHERE "isArchived""#) ) var archivedFactsCount = 0

WHERE 20:35

But this does not compile because fetchAll expects that we are fetching a collection of records: Cannot convert value of type ‘Int’ to expected argument type ’[Record]’

WHERE 20:42

…but we just want to fetch a single number, which is the count of archived facts.

WHERE 20:44

We need a new tool that works for just fetching a single value from a query, which we can call fetchOne : @ObservationIgnored @SharedReader( .fetchOne(#"SELECT count(*) FROM "facts" WHERE "isArchived""#) ) var archivedFactsCount = 0

WHERE 20:52

Its implementation will be very similar to what we did for fetchAll , and so we will not go through all of the details of its implementation. Instead, we will hop over to the SharingGRDB.swift file, where all of our library code currently exists, and paste in a new SharedReaderKey conformance that is capable of executing a SQL query for fetching a single value: struct FetchOneKey< Value: DatabaseValueConvertible & Sendable >: SharedReaderKey { let database: any DatabaseReader let sql: String init(sql: String) { @Dependency(\.defaultDatabase) var database self.database = database self.sql = sql } var id: ID { ID( databaseObjectIdentifier: ObjectIdentifier(database), sql: sql ) } struct ID: Hashable { let databaseObjectIdentifier: ObjectIdentifier let sql: String } struct NotFound: Error {} func load( context: LoadContext<Value>, continuation: LoadContinuation<Value> ) { do { let value = try database.read { db in try Value.fetchOne(db, sql: sql) } if let value { continuation.resume(returning: value) } else { continuation.resume(throwing: NotFound()) } } catch { continuation.resume(throwing: error) } } func subscribe( context: LoadContext<Value>, subscriber: SharedSubscriber<Value> ) -> SharedSubscription { let cancellable = ValueObservation.tracking { db in if let value = try Value.fetchOne(db, sql: sql) { return value } else { throw NotFound() } } .start( in: database, scheduling: .async(onQueue: .main) ) { error in subscriber.yield(throwing: error) } onChange: { elements in subscriber.yield(elements) } return SharedSubscription { cancellable.cancel() } } }

WHERE 21:10

This type is nearly identical to FetchAllQueryKey , except it uses the fetchOne method from GRDB for fetching a value. The other difference is that this type is generic over a Value type that conforms to the DatabaseValueConvertible protocol. This protocol expresses types that can be converted into types that SQLite can handle, such as integers, doubles strings and data blobs.

WHERE 21:47

With this type defined we will also define a fetchOne static helper in order to make the call site of this tool look nice: extension SharedReaderKey { static func fetchOne<Value>( _ sql: String ) -> Self where Self == FetchOneKey<Value> { Self(sql: sql) } }

WHERE 22:14

And with that done our theoretical syntax is now compiling: @ObservationIgnored @SharedReader( .fetchOne(#"SELECT count(*) FROM "facts" WHERE "isArchived""#) ) var archivedFactsCount = 0

WHERE 22:24

This allows us to hold a simple integer that represents the number of archived facts in the database. And amazingly it will always be kept up-to-date with any changes to the database since it uses SQLite’s observation tools.

WHERE 22:36

We can now use this state to determine if we want to show the “Archived facts” button in the toolbar, or not: .toolbar { if model.archivedFactsCount > 0 { … } }

WHERE 22:52

And further, we can also display the number of archived facts to the user in case that is interesting to them: Button("Archived facts (\(model.archivedFactsCount))") { … }

WHERE 23:04

With that done the app now works exactly as we expect. Running the app in the simulator shows that the “Archived facts” button is hidden because we don’t have any archived facts. But, the moment we archive a fact it instantly appears, and shows the count. If we archive another fact, the count display will go up to 2.

WHERE 23:38

Further, if we open up the archived facts sheet, and unarchive all of the facts, we will see that the “Archived facts” button in the parent view immediately hides. This is an amazing amount of behavior and functionality being executed in this view, but most of the complexity is hidden from us and for the most part we get to just deal with regular data. And when the the database changes, the shared state is automatically updated, and the view re-renders.

WHERE 23:52

It is now so easy to use @SharedReader to represent state driven off of database queries that we can improve something else in our app. Currently in the view we display the number of facts in the list by counting how many facts are in the favoriteFacts array: Text("Favorites (\(model.favoriteFacts.count))")

WHERE 24:08

This works for now because we are simply display all unarchived facts. However, in the future we may start limiting the number of facts that are shown in this view with a button for expanding all facts. Or maybe in the future we will add infinite scrolling pagination. When we do that this count will no longer be accurate because it will only be showing how many facts are being displayed, not how many facts are in the database.

WHERE 24:30

So, let’s fix this. We will add another property to the model that counts the number of unarchived facts: @ObservationIgnored @SharedReader( .fetchOne(#"SELECT count(*) FROM "facts" WHERE NOT "isArchived""#) ) var unarchivedFactsCount = 0

WHERE 24:43

And then we will use that data: Text("Favorites (\(model.unarchivedFactsCount))")

WHERE 24:50

That’s all it takes. We now have an accurate count of all facts, regardless of how many facts we are showing in the list. For example, just for a moment let’s limit the number of facts displayed to 1: @ObservationIgnored @SharedReader( .fetchAll(#"SELECT * FROM "facts" WHERE NOT "isArchived" LIMIT 1"#) ) var favoriteFacts: [Fact]

WHERE 25:16

Running the app in the simulator shows only the first fact, but the count in the header is still correct because it is running a separate query to compute that count.

WHERE 25:44

And you may think that our model is getting a little messy with all of these queries defined inline with the property declaration they are attached to. I personally don’t think it’s the worst thing, especially for one off queries that aren’t repeated elsewhere in the app. But if you want you can always extract out this key to a type-safe helper.

WHERE 26:11

That’s certainly less noisy, but it will be up to you and your team to determine where you want to draw the line between putting some shared keys directly inline versus extracting them out to helpers. Next time: Dynamic queries

WHERE 26:25

I think this is all looking pretty fantastic. We are now using our new SQLite tools with the @SharedReader property wrapper for 4 different pieces of state: the collection of unarchived facts for the root view, the count of archived and unarchived facts for displaying that information in the view, and a collection of archived facts that are displayed in a sheet. Brandon

WHERE 26:44

Some of this state is held in an observable model because that feature has a decent amount of logic that we may want to test some day. And an other piece of this state is held directly in the view because that feature is very simple and we don’t anticipate it becoming complex in the future, and therefore a full observable model may be overkill. Stephen

WHERE 27:03

But most importantly, it doesn’t matter where this property is used. The @SharedReader variables always hold the most current data as determined by the database, and if some other part of the app makes a change to the database directly, the @SharedReader variables will automatically update. And the view will also automatically re-render. Brandon

WHERE 27:19

Let’s now move onto to fixing some of the last remaining ergonomic quirks in the tools we have built so far. The tools are very powerful, but often it is not appropriate to write raw SQL strings from scratch when there are tools available to us to make these queries a little more type safe.

WHERE 27:36

To motivate this we will see what it takes to make the query powering a @SharedReader state dynamic so that it can change when the user changes certain settings.

WHERE 27:50

Let’s dig in. References Sharing Point-Free Instantly share state among your app’s features and external persistence layers, including user defaults, the file system, and more. https://github.com/pointfreeco/swift-sharing SQLite The SQLite home page https://www.sqlite.org