Video #304: SQLite: Observation

Episode: Video #304 Date: Nov 25, 2024 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep304-sqlite-observation

Description

We conclude our introductory series on SQLite by showing how to live update SwiftUI views powered by database using GRDB’s “value observation.” Along the way we will tackle quite a few Swift 6 concurrency issues, and we will tie things in a bow by showing how the SwiftUI environment can vastly simplify how our application is powered by SQLite.

Video

Cloudflare Stream video ID: f78b50e7424055e9af137cf2d4595f5d Local file: video_304_sqlite-observation.mp4 *(download with --video 304)*

References

Transcript

— 0:05

With just a little bit of work we have modeled a new domain type for our application, created a relationship between domain types, and created a new view to display the data for this new type, as well as navigation to the view. And this sets the stage for how one can build a simple SQLite-based application. In the onAppear of a feature’s view you can execute some queries to populate the data in the view, and then SwiftUI takes care of the rest. Stephen

— 0:27

But also most apps cannot be so simple. Typically you will have lots of features accessing the database, and then also lots of features writing to the database. And when one features makes a write you will want to make sure that all other views update in order to show the freshest data.

— 0:41

This brings us to a powerful feature that SQLite has called “change notifications”. We didn’t explore this when dealing with the C library directly because it can be quite cumbersome due to how one handles callback functions in C. But luckily GRDB has a nice interface to this functionality, so let’s take a look. Database observation

— 1:00

Let’s first take a look at how things can go wrong when we naively fetch data the way we are currently. Right now no part of our app makes writes to the database, so let’s do that. We will make it so that you can edit the name and injured status of a player from the detail screen.

— 1:15

One way we might try to approach this is by upgrading our player value in the detail view, which is currently just a let : let player: Player …to a binding: @Binding var player: Player

— 1:24

After all, that does allow this view to make changes to the player that will then be visible to the parent view that passes in the binding, and that sounds like what we want here.

— 1:32

However, this does not compile for 2 reasons. The first is the preview since we now need to pass a binding to the view instead of a raw Player . It can be surprisingly tricky to fix this, but we are going to look at the other compiler error.

— 1:42

Back in the parent view, when presenting the sheet we now need to pass a binding, and the sheet(item:) API does not give us a binding to the unwrapped player value: .sheet(item: $playerDetail) { player in … } Cannot convert value of type ‘Player’ to expected argument type ’Binding ’

— 1:52

This is an unfortunate deficiency in SwiftUI’s navigation APIs. When you drive navigation from an optional you cannot allow the child view presented to make edits to that state so that the parent can see those changes.

— 2:03

We maintain a powerful SwiftUI Navigation library that does give one access to tools that solve this problem. It allows you to get a binding to the unwrapped player in the trailing closure: .sheet(item: $presentedPlayer) { $player in … }

— 2:13

…which can then be handed to the PlayerDetailView : PlayerDetailView(databaseQueue: databaseQueue, player: $player)

— 2:17

And in a world in which this is possible, we could listen for mutations to the playerDetail data in order to save the changes to the database using the onChange view modifier: .onChange(of: playerDetail) { player in }

— 2:27

It’s awkward, but it could work. But of course this is all moot because SwiftUI does not provide the tools to do this, and we we’re not going to bring in our SwiftUI Navigation library just to pursue this approach.

— 2:38

A different approach is to use local @State for the player in the detail view instead of a binding: struct PlayerDetailView: View { @State var player: Player … }

— 2:47

This state can still be seeded from the parent just like when it was a let , and even when it was a @Binding , but after the initial seeding of data the detail view will be in complete control of this state. The parent view will not be able to make changes to the state, and parent views won’t even be able to see the current value of the state.

— 3:01

With that change we can update the Text view that displays the player name to be a TextField so that the user can make changes to the name: TextField("Name", text: $player.name)

— 3:19

And we can turn the injured row into a button for toggling the player’s status: Button { player.isInjured.toggle() } label: { if player.isInjured { Text("\(Image(systemName: "stethoscope")) Injured") .foregroundStyle(.red) } else { Text("Not injured") } }

— 3:31

And now everything compiles, but of course the edits we are making to this local @State has no bearing on the state in the parent view, let alone the data held in the database.

— 3:40

We can see this by running the preview of the players list, tapping a player, making a few edits, and then dismissing the sheet. Those changes were not persisted to the parent view, and it definitely wasn’t persisted to the database since there is nowhere in our code that we are writing to the database. We can even run the app in the simulator, make some edits to a player, and then re-run the simulator to see that those changes did not persist.

— 3:58

But now that the detail view owns the player state, we can listen for changes to it in order to save to the database: .onChange(of: player) { }

— 4:12

But we need to make Player equatable: struct Player: Codable, Equatable, FetchableRecord, Identifiable, MutablePersistableRecord { … }

— 4:22

And now we can save the player whenever the it changes: .onChange(of: player) { do { try databaseQueue.write { db in try player.save(db) } } catch { reportIssue(error) } }

— 4:48

Unfortunately when we run the preview we will see that it still doesn’t seem to work. The edits in a presented sheet do not seem to update the root list of players.

— 5:01

However, the changes are being persisted to the database. But to really see this we need to actually run the app in the simulator so that we use a database stored on disk instead of one just stored in memory. Let’s run the app, make some edits, and then re-run the app. Those changes now appear in the player list, and so we did indeed persist the changes to disk.

— 5:42

But how can we take this all the way and dynamically update the root list of players anytime a change is made to the player? We can do this with a tool in GRDB called “value observation.” This allows you to execute a SQL query to obtain data from your database and simultaneously fetch the freshest data if the database ever changes in the future.

— 5:58

There are a few APIs one can use to do this, but the simplest one to start with is the tracking static method on ValueObservation , which we will do in the onAppear of the root list view: .onAppear { ValueObservation.tracking(<#(Database) throws -> Value#>) } The trailing closure is handed a Database object just like the read and write methods on a database queue: ValueObservation.tracking { db in }

— 6:19

Inside this trailing closure we can perform the fetch for all players: ValueObservation.tracking { db in try Player.fetchAll(db) } …and this constructs a ValueObservation value.

— 6:27

There are several ways observation can be “started” from this value, but the most convenient to us if we want to use Swift concurrency is its values(in:) method, which takes a single argument: the database connection we will use for observation. ValueObservation.tracking { db in try Player.fetchAll(db) } .values(in: databaseQueue)

— 6:41

And it returns an async sequence of values that are emitted whenever a database change is observed.

— 6:46

So we can update our onAppear to the task view modifier, which is given an async context to work with: .task { ValueObservation.tracking { db in try Player.fetchAll(db) } .values(in: databaseQueue) … }

— 6:53

And then we can loop over the values and update the players accordingly. .task { let values = ValueObservation.tracking { db in try Player.fetchAll(db) } .values(in: databaseQueue) do { for await players in values { self.players = players } } catch { reportIssue(error) } }

— 7:04

And that is all it takes. Now when we run the app in the preview we will see that when making edits to a player the list of players immediately updates.

— 7:22

But also this quite an inefficient way to do this. Do we really need to execute a whole database write for every little key stroke in the detail view? What if instead we had a dedicated “Save” button that wrote to the database and dismissed the sheet. And I guess we will also need a “Cancel” button for when you want to discard the edits.

— 7:38

We’ll start by getting rid of the onChange view modifier since we no longer want to save for each change…

— 7:45

Next we will add a toolbar to our view for the “Save” and “Cancel” buttons: .toolbar { ToolbarItem(placement: .cancellationAction) { Button("Cancel") { } } ToolbarItem(placement: .primaryAction) { Button("Save") { } .bold() } }

— 8:06

In this “Cancel” button action we just want to dismiss the sheet, which we can do with the \.dismiss environment value: @Environment(\.dismiss) var dismiss

— 8:16

And then we can invoke it: Button("Cancel") { dismiss() }

— 8:19

Next, in the “Save” button action we can perform the database write and then save once that’s done: Button("Save") { do { try databaseQueue.write { db in _ = try player.saved(db) } dismiss() } catch { reportIssue(error) } } .bold()

— 8:28

And that is all it takes. We can run the preview for the root list of players to see that it behaves exactly as we expect. No database writes are made until we tap the “Save” button. And we can run the app in the simulator to see the same.

— 8:49

Again it’s really amazing to see how easy it is for us to preview such complex functionality without running the full app in the simulator. And the code we are previewing is actually using a real life SQLite database, but it just happens to be stored in memory and never actually persisted to disk.

— 9:04

However, the preview for our detail no longer really represents real life. We know the detail is always presented in a sheet, and so ideally the preview would emulate this. Luckily it’s quite easy to do with the @Previewable macro: #Preview { @Previewable @State var isPresented = true let databaseQueue = try! DatabaseQueue() try! databaseQueue.migrate() let player = try! databaseQueue.read { db in try Player.fetchOne(db, id: 1)! } return Button("Present") { isPresented = true } .sheet(isPresented: $isPresented) { NavigationStack { PlayerDetailView( databaseQueue: databaseQueue, player: player ) } .presentationDetents([.medium]) } }

— 10:02

Now this preview is more realistic in emulating how this view is interacted with in the real app, and the sheet is immediately presented so that we don’t even have to tap on anything, which is just a waste of time that breaks our development cycle. Sorting players

— 10:11

We have now explored a very advanced topic of SQLite which is database change notifications. GRDB gives us a very simple API to access this functionality, in which we can simultaneously execute a SQL statement to fetch data from the database, and subscribe to any changes to the database that affect our query. Brandon

— 10:27

But let’s take things a step further. Right now our query is quite simple. It’s just fetch all players with no additional logic, and then displays those players in one big list. In more real world apps you will typically deal with more complex queries, in which you dynamically apply filters or sorts to the results fetched.

— 10:44

Let’s add a sorting mechanism to our players list so that we can sort by when the player was created, or sort by their name, or sort by their injured status.

— 10:56

We’ll start by modeling an enum to represent the sorting options we want to expose in the UI: enum Order: String, CaseIterable { case created = "Created" case isInjured = "Injured" case name = "Name" }

— 11:26

And we will hold onto some new local state in the view that represents the current order we are showing the players: @State var order: Order = .created

— 11:35

Next we will add a toolbar item that allows one to change the order of the list of players. And to keep things easy, we will use a picker view for this: .toolbar { ToolbarItem(placement: .primaryItem) { Picker(order.rawValue, selection: $order) { Section { ForEach(Order.allCases, id: \.self) { order in Text(order.rawValue) .tag(order) } } header: { Text("Sort by") } } } }

— 13:13

With just that we already have a button in the top-right of the UI, and when we tap the button we get a popover with all the choices we can sort by.

— 13:25

But now we need to use the current order state in order to customize the query we are observing.

— 13:36

To customize the order of the results we fetch we can use some other helpers from GRDB. The fetchAll we are currently using eagerly fetches all the rows right away. Instead of doing that we can build up a query and then execute it.

— 13:42

Then we start with the order method to specify the columns we want to order by: try Player .order(<#orderings: any SQLOrderingTerm...#>)

— 13:53

A SQLOrderingTerm is a protocol in GRDB that describes how to sort results, such as the column to sort by and whether or not we are sorting by increasing or decreasing order.

— 14:01

So if we specify the name column in descending order: .order(Column("name").desc)

— 14:07

The preview immediately reflects this order. And if we flip desc to asc we see the table flip.

— 14:22

We can add a computed property to the Order enum for coming up with a term: enum Order: String, CaseIterable { … var orderingTerm: any SQLOrderingTerm { switch self { case .created: case .isInjured: case .name: } } }

— 14:50

For each of these cases we simply want to sort by a particular column, and we can describe this using the Column type from

GRDB 15:00

Further, it let’s also sort the injured column descending so that the injured players are put at the top: switch self { case .created: Column("id") case .isInjured: Column("isInjured").desc case .name: Column("name") }

GRDB 15:09

Now we can use this ordering term in our query: try Player .order(order.orderingTerm) Main actor-isolated property ‘order’ can not be referenced from a Sendable closure

GRDB 15:21

But now we get an error about main actor isolation, but we can side-step this entirely by eagerly capturing the computed ordering term: let values = ValueObservation.tracking { [orderingTerm = order.orderingTerm] db in try Player .order(orderingTerm) .fetchAll(db } Capture of ‘orderingTerm’ with non-sendable type ‘any SQLOrderingTerm’ in a @Sendable closure

GRDB 15:47

And now we have another error because the SQLOrderingTerm protocol does not imply sendability, but luckily we can again fix this quite quickly by changing what we return from the computed property to also be sendable: var orderingTerm: any SQLOrderingTerm & Sendable { … }

GRDB 16:07

Now we have a compiling query that does some ordering. If we had other customizations we wanted to make to the query we could do so here. For example, we could filter if we wanted to select only a subset of players: .filter(<#some SQLSpecificExpressible#>)

GRDB 16:32

Or we could limit the number of results we get back, even with an optional offset: .limit(<#Int#>, offset: <#Int?#>)

GRDB 16:40

But we don’t need any of that right now, and so we can finish off the query by executing the fetchAll method which executes the actual query: try Player .order(order.orderingTerm) .fetchAll(db)

GRDB 16:44

And we would hope this would all just work, but unfortunately changing the sort in our preview does nothing. This is because our task view modifier is executing just a single time when the view first appears. We need to re-execute it whenever the ordering changes, and luckily for us this is easy to do with an overload of task that takes an id , and if that id ever changes the task will start all over again. So we can use the order as this id : .task(id: order) { … }

GRDB 17:16

And now our feature is working exactly how we might hope. We can use the picker in the top-right to change the order of the players. We can even perform these sorts with animation if we want: ) { players in withAnimation { self.players = players } }

GRDB 17:39

And further, if we make an edit to a player that causes the ordering to change, we will see that the list updates and animates as soon as we tap “Save” in the sheet. Database environment

GRDB 18:03

This is all looking pretty great. We can now let the user dynamically sort the players in the list, which means we are dynamically changing the query that powers the list. And further, and writes to the database will cause the list to refresh and the players will be re-sorted.

GRDB 18:21

There is one last topic we want to cover before ending this introductory series on SQLite. Currently we are holding onto a database queue in 3 different places and we are explicitly passing it around to each view. We did this because it makes most sense to only have a single database connection for our app right now, but it’s also a bit of a pain.

GRDB 18:56

Let’s quickly see what it looks like to move the database queue into the SwiftUI environment so that we can immediately get access to it from any view in the app.

GRDB 19:07

We can start by extending the EnvironmentValues type to add a custom property that will represent the database connection: extension EnvironmentValues { var databaseQueue: DatabaseQueue }

GRDB 19:33

And we can take advantage of the fancy new @Entry macro from Xcode 16 so that we can register this environment value with just a single line of code: extension EnvironmentValues { @Entry var databaseQueue = try! DatabaseQueue.appDatabase() }

GRDB 20:04

With that done the @Entry macro will take care of the work of creating a type safe key that represents the environment value, and then turning this stored property into a computed property that subscripts into the environment values with the key.

GRDB 20:27

Now we can start making use of this environment value. First of all, the entry point no longer even needs to hold onto a database queue or create a database connection: @main struct SQLiteExplorations: App { var body: some Scene { WindowGroup { NavigationStack { ContentView() } } } } The only reason the entry point was doing this work is because it needed to pass it down to the ContentView , but ideally the content view would no longer need that object explicitly. It should just come from the environment.

GRDB 20:43

So, let’s hop over to the ContentView and change it’s let databaseQueue to an environment property: struct ContentView: View { @Environment(\.databaseQueue) var databaseQueue … }

GRDB 20:55

And further we will update the PlayerDetailView and update its let databaseQueue to be an environment value too: struct PlayerDetailView: View { @Environment(\.databaseQueue) var databaseQueue … }

GRDB 21:05

This makes our previews much simpler. We no longer need to construct a database when constructing views, like the content view: #Preview { … NavigationStack { ContentView() } }

GRDB 21:17

However we do have a problem with how this preview is seeding the database: #Preview { let databaseQueue = try! DatabaseQueue.appDatabase() try! databaseQueue.write { db in for index in 1...10 { _ = try! Player(…).inserted(db) } } NavigationStack { ContentView() } }

GRDB 21:21

Which we can see by running the preview: none of the players created in the loop appear on the screen. And this makes sense because the content view is now using a different database from the environment and not the one created in the preview.

GRDB 21:44

Well we can use the @Previewable macro to fix this, as well! It will give us a handle on the environment’s database so that we can seed things: #Preview { @Previewable @Environment(\.databaseQueue) var databaseQueue try! databaseQueue.write { db in for index in 1...10 { _ = try! Player(…).inserted(db) } } NavigationStack { ContentView() } }

GRDB 22:07

However the preview is still missing this seed data. We’d expect that the database queue we pluck from the environment would be seeded with some player data, and then the content view would be able to render this data, but that does not seem to be the case. We think this might be a bug in Xcode previews and previewable environment variables: for whatever reason, the environment variable we get a handle on in the preview is not the same as the value used by ContentView . To work around this, we can explicitly pass it along to the content view using the environment view modifier: ContentView() .environment(\.databaseQueue, databaseQueue) We think this shouldn’t be necessary, but it does get the preview rendering as we expect.

GRDB 22:04

That’s all it takes and now the preview runs just as it did before, and the entire app is working without us having to pass around a database queue everywhere. Conclusion

GRDB 23:02

Ok, that concludes this mini introduction to SQLite in Swift. We have covered the foundations, starting with how to interface directly with the SQLite C library directly. This involved invoking a lot of cryptic sounding C functions, along with a bunch of unsafe pointers, and pointers of pointers, and more scary looking things. We were able to accomplish the basics, such as creating tables, inserting data and querying for data, but it wasn’t pretty. Stephen

GRDB 23:27

So then we looked at a very popular open source library in the Swift community that puts a nicer interface to SQLite called GRDB. With that library we showed off the basics of creating tables, inserting data and querying data, but then we did a lot more. We also showed:

GRDB 23:42

…how to create a Swift data type that represents the data stored in the database, and how to perform incremental migrations to the database when the app first starts.

GRDB 23:50

How to power a SwiftUI view from the data fetched from the database.

GRDB 23:54

How to create relationships between tables so that we can represent when a row from one table belongs to a row of another table.

GRDB 24:02

How to observe changes to the database so that when a write is made to a row that is being displayed on screen the view will automatically update with the freshest data.

GRDB 24:10

And then finally, how to store the database connection in the SwiftUI environment so that every view gets immediate access.

GRDB 24:17

With all of these lessons you can already accomplish quite a bit in your app. But there is still so much more we want to show off. Brandon

GRDB 24:23

Next week we will begin a brand new series that we are calling “Modern Persistence” where we will build a complex, data-heavy app from scratch using SQLite and GRDB. Along the way we are going to show off some powerful techniques for modernizing and revolutionizing how you approach data persistence in your apps. It’s some really mind blowing stuff, and we will see a surprise cameo from the @Shared property wrapper we introduced to the Composable Architecture.

GRDB 24:55

Until next time! References SQLite The SQLite home page https://www.sqlite.org