Video #310: Sharing with SQLite: The Solution

Episode: Video #310 Date: Jan 20, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep310-sharing-with-sqlite-the-solution

Description

SQLite offers a lot of power and flexibility over a simple JSON file, but it also requires a lot of boilerplate to get working. But we can hide away all that boilerplate using the @Shared property wrapper and end up with something that is arguably nicer than Swift Data’s @Query macro!

Video

Cloudflare Stream video ID: 96b2c4081f0b32567b0b4e65cdaeaed7 Local file: video_310_sharing-with-sqlite-the-solution.mp4 *(download with --video 310)*

References

Transcript

— 0:05

We now have a fully functioning app, and everything is powered by SQLite. We can insert new facts into the database and the view will immediately refresh with the newest data. And the same happens when removing a fact. Brandon

— 0:15

However, there are a few things to not like about the code we have right now. First, we had to put some of our feature’s logic in the view. In particular, we are leveraging the task(id:) view modifier to listen for changes to state that cause the SQL query to change, and then at that moment we go tell the model to re-fetch the data from the database. That’s a very roundabout and indirect way to do things. Stephen

— 0:37

Further, we have a mutable array of facts right in our model, but we aren’t meant to actually mutate it directly. In fact, doing so will not save the data in the database, and so will definitely lead to bugs in your code. You have to instead remember to make any mutations through the database connection, and then the observation we set up will take care of updating the array of facts. It kinda seems like the array of facts should be read-only if possible. Brandon

— 1:02

And finally, there is a lot of boilerplate needed to get all of this set up correctly. Every feature that wants access to data in the database will need to: …use the task(id:) view modifier to listen for state changes that change the SQL query, then inside that modifier invoke a method on the model to re-execute the query, and then inside that method set up SQL observation so that changes to the database will update the state held in the model.

— 1:26

That is quite a bit, and it will be easy to get wrong.

— 1:26

Well, what if we told you that it is possible to hide all of this boilerplate behind a custom persistence strategy that can be used with the @Shared property wrapper. That will allow you to hold onto the collection of facts in your feature as if it’s a regular array, but secretly it is being powered by SQLite.

— 1:48

Let’s take a look. Bridging SQLite to @Shared

— 1:50

Let’s start by theorizing how we might want our call site to look like and then work towards making it a reality.

— 1:58

Back when we were using the @Shared property wrapper with fileStorage to manage our favorite facts, the property declaration looked like this: @ObservationIgnored @Shared(.favoriteFacts) var favoriteFacts

— 2:17

This is short and succinct because we defined a type-safe key, favoriteFacts , that allows us to describe how this state is persisted, and even describes the default used when there is no value previously persisted.

— 2:32

And before we had this type-safe key we used the longer form fileStorage key to describe all of this: @ObservationIgnored @Shared( .fileStorage( .documentsDirectory.appending(component: "favorite-facts.json") ) ) var favoriteFacts: [Fact] = []

— 3:09

This makes it clear we are persisting this state to the file system, as well as the URL on disk we are using, and even the default value.

— 3:20

What if we could have a brand new, reusable persistence strategy that took a SQL query: @Shared(.fetchAll(<#SQL#>)) var favoriteFacts: [Fact] = [] Here we are using the name fetchAll to mimic what GRDB calls this operation. It fetches an array of results from the query.

— 3:49

And this query could be anything, as long as it fetches a collection of facts from our database. For example, it could be as simple as a “ SELECT * FROM facts ”: @Shared(.fetchAll(#"SELECT * FROM "facts""#)) var favoriteFacts: [Fact] = []

— 4:07

Under the hood this would execute the query to fetch the facts, and further it would even observe changes to the database so that if someone else inserts a row into the “facts” table it would immediately update our shared state.

— 4:28

And this strategy would be the backbone that other persistence strategies could be built on top of. For example, you could build a very friendly strategy that allows specifying the sort, order, limit and offset all at once: @Shared( .fetchAll(sort: \Fact.number, order: .reverse, limit: 3, offset: 3) ) var favoriteFacts

— 5:37

If this were possible then the @Shared tool used in conjunction with a SQLite persistence strategy isn’t looking that much different from the tools that Swift Data gives us.

— 6:25

This would solve all the problems we saw a moment ago because we have decoupled the state we are holding in our feature from the state held in the external storage system. SQL provides the perfect separation. The database on disk serves as the “source of truth” of the data, and the querying language is a powerful way to construct views into this data without rewriting the data on disk.

— 7:03

Let’s make this fetchAll strategy a reality. It all begins by defining a new type that conforms to the SharedKey protocol. We will do this in a new file called SharingGRDB.swift, which will hold all of the library code we are about to write.

— 7:28

We will import both GRDB and Sharing: import GRDB import Sharing

— 7:32

And we will define a new type that conforms to the SharedKey protocol: struct FetchAllKey: SharedKey { }

— 7:39

The SharedKey protocol represents a strategy for persisting the shared state to an external system. It has one requirement, save : public protocol SharedKey<Value>: SharedReaderKey { func save( _ value: Value, context: SaveContext, continuation: SaveContinuation ) }

— 7:55

…but it also inherits from another protocol called SharedReaderKey : public protocol SharedReaderKey<Value>: Sendable { associatedtype Value: Sendable associatedtype ID: Hashable = Self var id: ID { get } func load( context: LoadContext<Value>, continuation: LoadContinuation<Value> ) func subscribe( context: LoadContext<Value>, subscriber: SharedSubscriber<Value> ) -> SharedSubscription } …which is a much beefier protocol.

— 8:02

It represents the ability to load data from an external storage system, as well as subscribe to changes in the external system so that those changes can be played back to the shared state. That subscription mechanism is what we use to listen for changes in user defaults and the file system in order to make sure that shared state always stays in sync with the external system, but we can also use it to keep this shared state in sync with the data in the database.

— 8:32

Let’s explain each of these requirements:

— 8:35

First, the Value associated type is the type of value held in the shared state. In our current code Value would be an array of Fact values.

— 8:48

The

ID 9:15

Next, the load endpoint is meant to load data from the external system we are interfacing with. The work performed to load the value can be both asynchronous and throwing, and one uses the LoadContinuation to feed data back to the @Shared property wrapper.

ID 9:30

The subscribe method is meant to subscribe to changes in the external system. Whenever you detect a change in the external system you will yield the value to the SharedSubscriber so that the @Shared property wrapper can be updated.

ID 9:43

And finally, the save endpoint performs the work to save a value to the external storage system. This work can be done asynchronously and can throw an error.

ID 10:09

This seems like a lot, but it’s really not so bad in practice. Let’s take it one step at a time. First, the Value associated type is going to be an array of some other type, so we can introduce a generic for the element of the array: struct FetchAllKey<Record>: SharedKey { typealias Value = [Record] }

ID 10:54

We are going to skip the

ID 11:12

And since this signature pins the Value associated type to [Record] we can now get rid of our explicit declaration of this requirement: -typealias Value = [Record]

ID 11:18

In the load method we need to execute a SQL statement in order to fetch the array of elements. This means that somehow we are going to need a database connection in this key: let database: DatabaseQueue

ID 11:36

We are now able to execute a read query with the database connection: func load( context: LoadContext<[Record]>, continuation: LoadContinuation<[Record]> ) { try database.read { db in } }

ID 11:46

What we’d like to do in this read transaction is let GRDB fetch all of the records for our Record type: try Record.fetchAll(<#Database#>)

ID 11:54

However, in order to get these kinds of helpers provided for us we need our Record type to conform to the FetchableRecord protocol, like we did for our Fact type: struct FetchAllKey<Record: FetchableRecord>: SharedKey { … }

ID 12:11

Now we have access to a fetchAll method that takes a database and a SQL string to execute: try Record.fetchAll(db, sql: <#String#>)

ID 12:20

The SQL string is what is provided by the user of this persistence strategy, and so it is something we need to store in the key: let sql: String

ID 12:29

Now we can fully use fetchAll : try Record.fetchAll(db, sql: sql)

ID 12:31

That is the basics of performing the query, but we now need to send this information into the LoadContinuation . This is the mechanism that the library offers for performing throwing asynchronous work in the load method.

ID 12:51

You may wonder why we didn’t just use Swift’s first class support for async throws in the load method: func load(context: LoadContext<[Record]>) async throws -> [Record] { … }

ID 13:03

This definitely would have been ideal, and we tried hard to make it a reality, but unfortunately it wreaks havoc on one’s ability to test code that uses the @Shared property wrapper. In general Swift’s async machinery is very difficult to test, and we have contended with that fact many times on Point-Free, but this was one situation where we could not find a way around it.

ID 13:25

But that’s OK, it’s easy enough to use the LoadContinuation . We just need to invoke one of its resume methods once the data has been loaded. For example, once the database.read finishes we can invoke it like so: try continuation.resume( returning: database.read { db in try Record.fetchAll(db, sql: sql) } )

ID 13:56

And if database.read throws an error, we can also resume the continuation with that error: do { try continuation.resume( returning: database.read { db in try Record.fetchAll(db, sql: sql) } ) } catch { continuation.resume(throwing: error) }

ID 14:19

That’s all it takes for the load endpoint, though we can shorten it a bit using a different resume method that takes a result. try continuation.resume( with: Result { try database.read { db in try Record.fetchAll(db, sql: sql) } ) }

ID 14:50

We could even go a bit further and go async on a background thread using the database’s asyncRead method, before piping the result back to the continuation, but we’ll leave that as an exercise for the viewer

ID 15:21

Next we will implement the subscribe endpoint: func subscribe( context: LoadContext<[Record]>, subscriber: SharedSubscriber<[Record]> ) -> SharedSubscription { }

ID 15:26

Inside this method it is appropriate for us to use the ValueObservation tool from GRDB in order to listen for changes to the database and report them back to the @Shared property wrapper. We can start tracking by fetching all of the elements from the database with the SQL string provided to the key: ValueObservation.tracking { db in try Record.fetchAll(db, sql: sql) }

ID 15:57

Then we can invoke the start method on this to start observation: .start( in: <#any DatabaseReader#>, scheduling: <#ValueObservationScheduler#>, onError: <#(any Error) -> Void#>, onChange: <#([FetchableRecord & Sendable]) -> Void#> )

ID 16:17

The first argument to this method is the database connection to use: .start( in: database, scheduling: <#ValueObservationScheduler#>, onError: <#(any Error) -> Void#>, onChange: <#([FetchableRecord & Sendable]) -> Void#> )

ID 16:20

The second argument is a scheduler that is used to notify when changes are observed. We can type “.” and then use autocomplete to see that we have a number of choices available to us. We will be using the async scheduler which allows us to specify a dispatch queue to notify on, and we will use the main queue: .start(in: database, scheduling: .async(onQueue: .main)) { error in

ID 16:35

Next we will provide the two trailing closures: .start(in: database, scheduling: .async(onQueue: .main)) { error in } onChange: { records in } The first is called when an error is thrown, and the second is called when a change in the database is detected, and the most up-to-date records are newly fetched.

ID 16:46

In each of these closures we can simply yield the information to the subscriber : .start( in: database, scheduling: .async(onQueue: .main) ) { error in subscriber.yield(throwing: error) } onChange: { records in subscriber.yield(records) }

ID 17:10

That’s all it takes to start tracking changes to the database from our query. But, the start method returns a cancellable-like object that must be kept alive in order to keep the subscription to database changes alive, and so let’s assign it to a variable: let cancellable = ValueObservation.tracking { db in … } …

ID 17:20

We can keep this cancellable alive for as long as our @Shared state is alive by tying it to the lifecycle of the shared state. We do this by returning what is known as a SharedSubscription from the subscribe method, which for all intents and purposes is just our version of a cancellable: return SharedSubscription { }

ID 17:40

And when this subscription is cancelled we will cancel the GRDB cancellable: return SharedSubscription { cancellable.cancel() } And the act of simply capturing the cancellable in this subscription will keep it alive for as long as the shared state is alive.

ID 17:46

That mostly finishes the implementation of subscribe , but we now have an error letting us know that the Record type must be Sendable in order for this to work. This is required by the tracking method, so let’s add that constraint to the Record generic: struct FetchAllKey<Record: FetchableRecord & Sendable>: SharedKey {

ID 17:59

And that finishes the implementation of subscribe .

ID 18:03

Note that both load and subscribe also take a context argument, which we simply ignored. The context tells us exactly how each method was invoked, for example if it was invoked behind the scenes by initializing the @Shared property wrapper, or if it was invoked explicitly by calling $shared.load() , and some shared keys may want to react differently depending on this context. But for our purposes, we can ignore it and behave the same regardless.

ID 19:52

Finally we have the save method: func save( _ value: [Record], context: SaveContext, continuation: SaveContinuation ) { }

ID 20:04

Technically this compiles because this method does not need to return anything. However, the purpose of this method is to save the current value held by the @Shared property wrapper to the external system. It is called when a mutation is made to shared state so that the data held in memory for the app matches what is held in the external system.

ID 20:40

But it is not clear what we are supposed to do here. We are given an array of elements to persist to the database, but how we supposed to keep that data in sync with what is in the database? We can try opening up a write transaction: func save( _ value: [Record], context: SaveContext, continuation: SaveContinuation ) { try databaseQueue.write { db in } }

ID 21:03

But are we supposed to now write all of these elements to the database? What if some of the elements already exist in the database? We will need to have some way to de-dupe those. Also, what if this array holds fewer elements than what is in the database? Do we need to perform some kind of diffing between the elements we have here and the elements in the database so that we know which ones to delete? @SharedReader Brandon

ID 21:40

All of this sounds complicated enough, but we haven’t even considered the fact that these elements actually only represent a subset of all the elements in the database as defined by the SQL query. It is possible to provide a “

WHERE 22:06

So, what we are seeing here is that a save endpoint just doesn’t make any sense. We shouldn’t allow mutating shared state directly, and instead we should force you to perform mutations to your database directly via “

UPDATE 22:22

And luckily our Sharing library comes with a tool that is perfect for representing shared state that is readable, but not writable. We can just conform to only the SharedReaderKey protocol and then we no longer need to provide a save endpoint.

UPDATE 22:35

Let’s take a look.

UPDATE 22:38

Let’s swap out the SharedKey conformance for a SharedReaderKey conformance, and then we can comment out the save endpoint: struct FetchAllKey< Record: FetchableRecord & Sendable >: SharedReaderKey { … // func save( // _ value: [Record], // context: SaveContext, // continuation: SaveContinuation // ) { // } }

UPDATE 22:53

And then in our application we will use the @SharedReader property wrapper instead of the @Shared property wrapper: @SharedReader(.fetchAll(#"SELECT * FROM "facts""#)) var favoriteFacts: [Fact] = []

UPDATE 23:03

When this property wrapper is used you are not allowed to perform mutations on favoriteFacts directly. For example, if we tried mutating favoriteFacts directly instead of writing to the database: // try databaseQueue.write { db in // _ = try Fact(number: count, savedAt: now, value: fact) // .inserted(db) // } favoriteFacts.append(Fact(number: count, savedAt: now, value: fact)) …we are immediately confronted with an error: Cannot use mutating member on immutable value: ‘favoriteFacts’ is a get-only property

UPDATE 23:17

This fixes the problem we noticed earlier where favoriteFacts was mutable, but mutating it would not have actually updated the database, making it easy for your app state to get out of sync with your database state.

UPDATE 23:27

And it may seem like a bummer that we have to deal with our database in two different ways: on the one hand we use @SharedReader for querying the database and holding onto data, and on the other hand we use the databaseQueue directly for writing to the database.

UPDATE 23:41

However, that is also exactly how Swift Data works.

UPDATE 23:43

You use the @Query macro in the view for expressing a view into the data held in Core Data, and then separately you must use a model context in order to insert, update or delete records from the database. So, at the end of the day we don’t think it’s that big of a deal to treat our database the same way, and you can think of the @SharedReader as being a kind of replacement for the @Query macro from Swift Data, but it also has a lot more features that Swift Data does not.

UPDATE 24:07

We are nearly done with our conformance to the SharedReaderKey , but we do have the

ID 24:17

This needs to be some Hashable value which is used to uniquely identify this key. If two instances of @Shared somewhere in the app are using the exact same shared key, they can share the underlying state too. This means we also only have to maintain a single subscription to the database for that shared key, which will help performance.

ID 24:38

The information that uniquely identifies this shared key is the SQL string as well as the database connection. We can package all of that information up into a Hashable type: struct ID: Hashable { let databaseObjectIdentifier: ObjectIdentifier let sql: String }

ID 25:08

And then return that ID from the property: var id: ID { ID( databaseObjectIdentifier: ObjectIdentifier(database), sql: sql ) } If there are ever two different @SharedReader references in your app with the same ID as specified here, no matter how far apart, they will both be accessing the exact same copy of the facts array and share a subscription to the database: @SharedReader(.fetchAll(#"SELECT * FROM "facts""#)) var facts: [Fact] // Somewhere else in the app: @SharedReader(.fetchAll(#"SELECT * FROM "facts""#)) var facts: [Fact]

ID 25:20

And we have now finished implementing our custom SharedReaderKey . It may seem like a lot, but we only have to write this code a single time, package it up into a micro-library, and then we get to use it anywhere in our app. It will clean up a massive amount of code.

ID 25:34

However, we typically do not like to use SharedReaderKey conformances directly. It would look something like this at the call site: @SharedReader(FetchAllKey(#"SELECT * FROM "facts""#)) var favoriteFacts: [Fact] = []

ID 25:49

Typically we prefer to have a little static function helper to give us a nicer syntax like this: @SharedReader(.fetchAll(#"SELECT * FROM "facts""#)) var favoriteFacts: [Fact] = [] …which also helps with autocomplete.

ID 25:57

That can be done like so: extension SharedReaderKey { static func fetchAll<Record>( _ sql: String, database: DatabaseQueue ) -> Self where Self == FetchAllKey<Record> { FetchAllKey(database: database, sql: sql) } }

ID 26:36

Further, because this shared key deals with an array, we can bake in an empty array as its default: extension SharedReaderKey { static func fetchAll<Record: FetchableRecord>( _ sql: String, database: DatabaseQueue ) -> Self where Self == FetchAllKey<Record>.Default { Self[FetchAllKey(database: database, sql: sql), default: []] } }

ID 26:56

This means we don’t even need to specify a default value when using this shared key: @ObservationIgnored @SharedReader(.fetchAll(#"SELECT * FROM "facts""#)) var favoriteFacts: [Fact] And now this is looking really nice.

ID 27:14

However, this does not yet compile because we are not passing the database connection to the fetchAll key. Currently we are forced to supply that explicitly, which unfortunately means we can’t use @SharedReader directly in the property declaration. We instead need to assign it in the initializer: init(database: DatabaseQueue) { self.database = database _favoriteFacts = SharedReader( .fetchAll( #"SELECT * FROM "facts""#, database: database ) ) } And luckily we are already passing a database to the initializer because the model needs that object in other parts of its implementation, such as when inserting and deleting facts from the database.

ID 27:53

With that change everything is now compiling and the app mostly works as it did before. We can get a fact for a number, save the fact, and we will see that fact animate into place in the “Favorites” list. But now we are able to delete a bunch of code that is handled for us automatically by the @SharedReader property wrapper. For example, we can get rid of the onTask method that observes changes to the database: func onTask() async { … }

ID 28:05

And we can delete the task(id:) view modifier from the view.

ID 28:24

With all of that code deleted the app still works as it did before. Any changes made to the database, such as inserting a new fact or deleting an existing fact, are automatically observed by the @SharedReader property wrapper and the view is updated automatically.

ID 28:57

And secretly all of these changes are being made to a SQLite database stored on disk rather than being stored as a flat JSON file. We can even open the database real quick to see in realtime that any changes made in the app are immediately made to the SQLite database. Database dependency

ID 29:49

We now have the basics of a SQLite strategy implemented for our @SharedReader property wrapper. It allows you to describe a SQL query to run for fetching a collection of records, and it automatically observes changes to the database so that if any records are inserted, updated or deleted, the data in your model automatically updates and your views also automatically re-render. You can nearly forget that the data is even being powered by a SQLite database, and that’s really amazing. Brandon

ID 30:14

But the version of the tools we have built so far have a lot of sharp edges. For one thing we are forced to pass a database connection around everywhere, which is not ergonomic. Also we haven’t yet explored what it looks like to have a @SharedReader powered by a dynamic query. That is, a query that can change based on settings the user can tweak. And perhaps there is a nicer way to describe a query for @SharedReader that isn’t just a raw SQL string.

ID 30:40

Let’s start fixing these problems one-by-one, first starting with the database queue that is being passed around everywhere.

ID 30:48

If we search our project for database: DatabaseQueue we will find a bunch of places it is being explicitly passed around. First we are holding onto it in the FactFeatureModel : database: DatabaseQueue

ID 31:01

…which means it must be passed to its initializer: init(database: DatabaseQueue) { … } We also pass it along to the fetchAll shared key we created a moment ago: .fetchAll(#"SELECT * FROM "facts""#, database: database)

ID 31:08

And under the hood that passes it along to the FetchAllKey type: FetchAllKey(database: database, sql: sql)

ID 31:17

…which also holds onto the database: struct FetchAllKey< Record: FetchableRecord & Sendable >: SharedReaderKey { let database: DatabaseQueue … }

ID 31:20

However, there is really only one single database connection in our app right now and we would like to have any part of our app or library code have access to this connection without needing to explicitly pass it around everywhere.

ID 31:44

And this sounds like the perfect job for our Dependencies library. It allows you to define a value that is instantly accessible by the entire app, but it is done so in a way that is concurrency safe and plays nicely with tests.

ID 31:56

In fact, we are already using the Dependencies library in this app in order to control our dependence on the factClient for fetching facts about numbers, as well as date and UUID generators: @ObservationIgnored @Dependency(FactClient.self) var factClient @ObservationIgnored @Dependency(\.date.now) var now @ObservationIgnored @Dependency(\.uuid) var uuid

ID 32:14

We just need to register a new dependency that holds onto a database connection that the entire app can use, and then update our app to use that dependency rather than passing the connection around everywhere.

ID 32:43

Let’s hop over to the SharingGRDB.swift file where all of our library code has gone so far and import the Dependencies library: import Dependencies

ID 33:05

The first step in registering a dependency is defining a type that conforms to the DependencyKey protocol: private enum DefaultDatabaseKey: DependencyKey { }

ID 33:22

To conform to this protocol we have to at least provide a liveValue static, which is the version of the dependency that will be used when running the app in the simulator or on a device: private enum DefaultDatabaseKey: DependencyKey { static var liveValue: DatabaseQueue { } }

ID 33:46

We can even go a step further by generalizing this a bit. Right now we have pinned to a concrete type, the DatabaseQueue , but this type also conforms to protocols that expose the necessary functionality. They are DatabaseReader and DatabaseWriter , and there are other conformances to these protocols such as DatabasePool .

ID 34:08

If we want to leave open the possibilities of using other kinds of database connections for our default database we should use the protocol instead of a concrete type: private enum DefaultDatabaseKey: DependencyKey { static var liveValue: any DatabaseWriter { } }

ID 34:21

Now the big question here is how can we construct a database that is appropriate to use as the live value? It needs to be a database that is stored in the appropriate location, which the user of our library code may want to specify. And further, migrations need to be run on the database, which is also a domain specific operation that doesn’t make sense to perform in library code. And the user may want to apply other kinds of configuration, such as installing a query tracer.

ID 34:51

So, there really isn’t much we can do here but return a blank, un-migrated, in-memory database: private enum DefaultDatabaseKey: DependencyKey { static var liveValue: any DatabaseWriter { try! DatabaseQueue() } }

ID 35:12

But also, we don’t expect people to actually use this database. Really users of this library code should be overriding this dependency with their own database. One that is persisted to disk and migrated.

ID 35:30

We can make this explicit by reporting an issue before returning the database: static var liveValue: any DatabaseWriter { reportIssue( """ A blank, in-memory database is being used for the app. \ Override this dependency in the entry point of your app. """ ) return try! DatabaseQueue() }

ID 35:46

If a user of this library code accesses the live value of the dependency before they have overridden the dependency they will be met with a noticeable, yet unobtrusive, warning letting them know that is not correct.

ID 36:13

The next step to registering this dependency with the library is to add a computed property to DependencyValues that uses this DefaultDependencyKey type to subscript into the global blob of dependencies in the app: extension DependencyValues { var defaultDatabase: any DatabaseWriter { get { self[DefaultDatabaseKey.self] } set { self[DefaultDatabaseKey.self] = newValue } } }

ID 36:46

With that done we can now grab the default database connection from anywhere in our app like so: @Dependency(\.defaultDatabase) var database

ID 37:35

For example, the FetchAllKey type no longer needs a database passed into the initializer. Instead we will grab it from the dependencies system. We can eve weaken the type of database we hold onto be an any DatabaseReader since we do not need to ever write from the FetchAllKey : struct FetchAllKey< Record: FetchableRecord & Sendable >: SharedReaderKey { let database: any DatabaseReader let sql: String init(sql: String) { @Dependency(\.defaultDatabase) var database self.database = database self.sql = sql } … } That means our fetchAll static helper no longer needs to pass along a database to FetchAllKey , which means the helper itself no longer needs an argument for the database: extension SharedReaderKey { static func fetchAll<Record>( sql: String ) -> Self where Self == FetchAllKey<Record>.Default { Self[FetchAllKey(sql: sql), default: []] } } That then trickles up to the FactFeatureModel initializer where we no longer need to pass a database to the fetchAll static function: _favoriteFacts = SharedReader(.fetchAll(#"SELECT * FROM "facts""#))

ID 38:18

This means we don’t need to pass it into the initializer anymore, and in fact we don’t even need an initializer anymore. With that change the model is compiling, but anywhere we were constructing a model is no longer compiling because we it’s no longer necessary to pass in a database connection: FactFeatureModel()

ID 38:35

And now everything compiles. Not only have we been able to reduce passing around dependencies that should really just always be available, we now have the ability to use the fetchAll shared key directly at the declaration of our property: @ObservationIgnored @SharedReader(.fetchAll(#"SELECT * FROM "facts""#)) var favoriteFacts: [Fact]

ID 39:15

And now this line of code is truly amazing. We are able to describe a SQL query for fetching data right in our model and then we never have to think about SQL again. The data will automatically be fetched for us, and observed, but as far as the model and view are concerned we can just think of favoriteFacts as a simple array of data.

ID 39:51

However, the app does not actually work right now. And if we run it in the simulator we will see that there are no facts in the view, but we can clearly see why. We are getting two purple runtime warnings in Xcode: A blank, in-memory database is being used for the app. Override this dependency in the entry point of your app. Caught error: SQLite error 1: no such table: facts - while executing SELECT * FROM "facts"

ID 40:03

The first is the issue we report when the liveValue of the default database is accessed without overriding it. This is our reminder that we must override this dependency with a database that is persisted to disk and migrated.

ID 40:15

And the second warning is a SQL error that is happening because we are executing a query on a database that hasn’t been migrated. The database being used for this query does not even have a “facts” table, and so it is not capable of running this query.

ID 40:25

The way to fix this problem is to use the prepareDependencies function in the entry point of the app: import Dependencies import SwiftUI @main struct TourOfSharingApp: App { let model: FactFeatureModel init() { prepareDependencies { } self.model = FactFeatureModel() } … }

ID 40:41

The prepareDependencies function allows you to override dependencies one single time before they are accessed. If a dependency has already been accessed then you are no longer allowed to override it. That is why you should invoke this method as early as possible in your app’s lifecycle, such as the entry point.

ID 40:58

Inside the prepareDependencies we can override the defaultDatabase to be the appDatabase we have previously defined: init() { prepareDependencies { $0.defaultDatabase = DatabaseQueue.appDatabase } self.model = FactFeatureModel() }

ID 41:17

…which is persisted to the file system and has all migrations applied.

ID 42:27

Further, now that the FactFeatureModel doesn’t need its dependencies passed to it explicitly, we can even hoist its construction out to be a static in the entry point: @main struct TourOfSharingApp: App { static let model = FactFeatureModel() init() { prepareDependencies { $0.defaultDatabase = DatabaseQueue.appDatabase } } var body: some Scene { WindowGroup { NavigationStack { FactFeatureView(model: Self.model) } } } }

ID 42:50

With those few changes the app now does behave exactly as before. We no longer get a runtime warning letting us know that we are using a blank, in-memory database, and our previously saved facts all appear.

ID 43:36

And if we want we can even clean up how our database is prepared by restoring the “dot prefix” syntax: prepareDependencies { $0.defaultDatabase = .appDatabase } We just need to update our extension on DatabaseQueue to be on DatabaseWriter instead: extension DatabaseWriter where Self == DatabaseQueue { static var appDatabase: DatabaseQueue { … } } Next time: Archived facts

ID 44:10

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.

ID 44:36

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

ID 45:08

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.

ID 45:25

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.

ID 45:43

Let’s get started. 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