Video #312: Sharing with SQLite: Dynamic Queries

Episode: Video #312 Date: Feb 3, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep312-sharing-with-sqlite-dynamic-queries

Description

We are now driving several features using SQLite using a simple property wrapper that offers the same ergonomics as Swift Data’s @Query macro, and automatically keeps the view in sync with the database. Let’s add one more feature to leverage dynamic queries by allowing the user to change how the data is sorted.

Video

Cloudflare Stream video ID: 49d9d011a589b85c658437194d518c99 Local file: video_312_sharing-with-sqlite-dynamic-queries.mp4 *(download with --video 312)*

References

Transcript

— 0:05

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

— 0:25

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

— 0:44

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

— 0:59

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.

— 1:17

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.

— 1:30

Let’s dig in. Dynamic queries

— 1:32

Currently a major piece of functionality of our app simply does not work. If we try to change the sort of the facts in the root view, we will see that nothing happens. And this is happening because we are using a static query to describe the facts we want to fetch from the database: @ObservationIgnored @SharedReader(.fetchAll(#"SELECT * FROM "facts" WHERE NOT "isArchived""#)) var favoriteFacts: [Fact]

— 1:55

We somehow need to dynamically update this query when the user changes their preferred sort for the facts. However, that does mean that we can no longer describe the query that powers our state directly inline where the property is declared.

— 2:13

And this is true of any state that is powered by a dynamic query. From our experience is pretty rare that you can upfront declare the query that powers your shared state at the declaration of the property. The same goes for Swift Data. Even when using the @Query macro you often cannot describe the fetch descriptor directly inline, and instead need to do it somewhere else in the view. Even Apple’s big Swift Data demo, Backyard Birds, demonstrates this technique: @Query private var backyards: [Backyard] init(searchText: Binding<String>) { _searchText = searchText if searchText.wrappedValue.isEmpty { _backyards = Query(sort: \.creationDate) } else { let term = searchText.wrappedValue _backyards = Query(filter: #Predicate { backyard in backyard.name.contains(term) }, sort: \.name) } } The @Query -powered backyards property cannot be fully described at the moment of declaration of the variable, and so it must be fully specified elsewhere. In this case, the initializer.

— 3:37

We can do something similar with our @SharedReader . First start by using the @SharedReader property wrapper in a bare fashion with no key specified: @ObservationIgnored @SharedReader var favoriteFacts: [Fact]

— 3:47

That declares that the state is powered by @SharedReader , but we will set the key at a later time.

— 3:55

As soon as we make that change we get a compiler error letting us know we now need to provide an initializer since this state is no longer initialized: init() { }

— 4:06

We need to initialize this state through the underscored property: _favoriteFacts = …which is a reference to the actual property wrapper type that powers the field.

— 4:12

We can construct a SharedReader value that uses the fetchAll key and assign it: _favoriteFacts = SharedReader( .fetchAll(#"SELECT * FROM "facts" WHERE NOT "isArchived""#) ) This compiles, but this query is still not dynamic. It is not taking into account the ordering property on the model.

— 4:46

One thing we could try doing is switching on the ordering property: switch ordering { case .number: case .savedAt: }

— 4:57

…and then execute a custom query in each case of this switch: switch ordering { case .number: _favoriteFacts = SharedReader( .fetchAll( """ SELECT * FROM "facts" \ WHERE NOT "isArchived" \ ORDER BY "number" ASC """ ) ) case .savedAt: _favoriteFacts = SharedReader( .fetchAll( """ SELECT * FROM "facts" \ WHERE NOT "isArchived" \ ORDER BY "savedAt" DESC """ ) ) }

— 5:17

But this is not correct: ‘self’ used in property access ‘ordering’ before all stored properties are initialized …because ordering is a field provided by a property wrapper, which means its technically like a computed property. And computed properties on a type cannot be accessed until all stored properties are initialized.

— 5:40

We can circumvent the computed property by going straight to the underscored property’s wrappedValue : switch _ordering.wrappedValue { … }

— 5:48

And now this compiles.

— 5:49

And it’s a little verbose but it gets the job done for right now. But there is still more to do. Right now we dynamically choose the query right when the feature is presented, but we never recompute the dynamic query when the ordering changes.

— 6:59

In order to react to the ordering state changing we can put this work in a publisher.sink we like we set up in the past: $ordering.publisher.sink { [weak self] ordering in … }

— 7:19

This publisher emits once immediately with the current ordering value, and then again anytime it changes.

— 7:27

So, in this sink it is appropriate to reassign the projected value of the favoriteFacts shared reader: $ordering.publisher.sink { [weak self] ordering in guard let self else { return } switch ordering { case .number: self.$favoriteFacts = SharedReader( .fetchAll( """ SELECT * FROM "facts" \ WHERE NOT "isArchived" \ ORDER BY "number" ASC """ ) ) case .savedAt: self.$favoriteFacts = SharedReader( .fetchAll( """ SELECT * FROM "facts" \ WHERE NOT "isArchived" \ ORDER BY "savedAt" DESC """ ) ) } } .store(in: &cancellables)

— 8:15

We do still have to provide an initial value of _favoriteFacts , which we can do by using the special constant shared reader that holds onto a value and never changes: init() { _favoriteFacts = .constant([]) … }

— 8:49

That is all it takes to handle this dynamic query. When we run the app in the simulator we will see that it works exactly as we expect. I can sort by number or savedAt using the picker. Even cooler, if I archive the top row, then go into the archived facts sheet and unarchive it, we will see that it was automatically inserted back in the top of the list. We don’t have to worry about keeping our collection of facts sorted by inserting special logic all over the place in our features. We just have the one single query that describes how we want to present the data, and whenever need data is inserted into the database it will cause the view to re-render and that data will be displayed in the correct place. Using a query builder

— 10:08

So I personally think this is really incredible to see. We can dynamically change the query that is used to power the list of facts, and we can be assured that no matter where or how new rows are inserted into the database, the UI will magically update automatically. The tools we are building are really starting to feel like a Swift Data competitor for the most part.

— 10:34

However, there is of course a lot to not like about what we have done here. Because we are dealing with just raw SQL strings, there is no good way to dynamically determine the field that we want to sort on. We have no choice but to construct two completely separate SQL queries. Stephen

— 10:52

There is a concept known as “query builders” that can really help with this kind of thing. A query builder is a collection of helpers and API implemented in a programming language, such as Swift, that allow you to leverage the host languages functionality for more easily building queries.

— 11:07

And in fact, GRDB has a query builder. Let’s take a look at how it can fit into our tools:

— 11:14

As an example of query building, the query for the .number case can be written in the builder style like so: Fact.filter(!Column("isArchived")).order(Column("number").asc)

— 12:05

And similarly the query for the .savedAt case can be written like so: Fact.filter(!Column("isArchived")).order(Column("savedAt").desc)

— 12:16

And now we see very clearly that the only difference between these queries is the ordering clause, and we even have a helper defined on Ordering that can compute that for us: var orderingTerm: any SQLOrderingTerm & Sendable { switch self { case .number: Column("number") case .savedAt: Column("savedAt").desc } }

— 12:27

Which means we don’t need even need to perform a switch , and instead can compute the query in one single line like so: Fact.filter(!Column("isArchived")).order(ordering.orderingTerm)

— 12:39

This right here shows the power of a query builder. We get to dynamically decide what to order the results by using a simple method in Swift, instead of having to construct a raw SQL query from scratch.

— 12:50

The type returned by this expression is QueryInterfaceRequest<Fact> , but that is just a concrete conformance to a more general protocol for expressing a way to fetch and decode rows from the database called FetchRequest : let query: any GRDB.FetchRequest<Fact> = Fact .filter(!Column("isArchived")) .order(ordering.orderingTerm)

— 13:08

So, wouldn’t it be really cool if a piece of @SharedReader state could be powered by one of these kinds of queries so that we don’t have to manually munge together a big SQL string ourselves?

— 13:25

Perhaps the call site could even look something like this: self.$favoriteFacts = SharedReader( .fetchAll( Fact .filter(!Column("isArchived")) .order(ordering.orderingTerm) ) ) This looks very similar to our existing fetchAll key, but it allows specifying a query by this builder syntax instead of a string.

— 14:00

Let’s see if we can make this a reality. We’ll hop over to our SharingGRDB.swift file because that is where all of our library code lives right now. We are going to create a new SharedReaderKey conformance that can deal with FetchRequest queries instead of raw strings. However, this conformance is going to be very similar to our existing FetchAllKey . To keep things simple for right now, we are just going to copy-and-paste that entire conformance, rename it to FetchAllQueryBuilderKey : struct FetchAllQueryBuilderKey< Record: FetchableRecord & Sendable >: SharedReaderKey { … } …and make a few changes to try to support the query builder tools.

— 14:26

First of all, the type will no longer hold onto just a string, but rather a full fetch request that describes how to fetch a collection of records. We could do that with an existential like so: struct FetchAllQueryBuilderKey< Record: FetchableRecord & Sendable >: SharedReaderKey { let database: any DatabaseReader let request: any GRDB.FetchRequest<Record> … }

— 14:40

Then we need to update the initializer: init(request: some GRDB.FetchRequest<Record>) { @Dependency(\.defaultDatabase) var database self.database = database self.request = request }

— 14:51

As well as the id computed property: var id: ID { ID( databaseObjectIdentifier: ObjectIdentifier(database), request: request ) } Which also forces us to update the

ID 15:04

But this prevents the

ID 15:21

But because of the primary associated type this kind of composition is not possible in today’s Swift. Maybe some day, but till then, we can introduce a generic to work around the problem by encoding the type of the request directly into the FetchAllQueryBuilderKey type: struct FetchAllQueryBuilderKey< Record: FetchableRecord & Sendable, Request: FetchRequest<Record> & Hashable >: SharedReaderKey { let database: any DatabaseReader let request: Request … }

ID 15:48

And we can update every existential any with the generic type: let request: Request … init(request: Request) { … } … struct ID: Hashable { … let request: Request }

ID 15:57

And now the

ID 15:59

Next we need to update the load method so that we invoke fetchAll on the request : func load( context: LoadContext<[Record]>, continuation: LoadContinuation<[Record]> ) { do { try continuation.resume( returning: database.read { db in try request.fetchAll(db) } ) } catch { continuation.resume(throwing: error) } }

ID 16:16

And similarly for the subscribe method: let cancellable = ValueObservation.tracking { db in try request.fetchAll(db) }

ID 16:24

Now this compiles, which means we can also define a static function fetchAll that takes a fetch request: extension SharedReaderKey { static func fetchAll< Record: FetchableRecord, Request: FetchRequest<Record> >( _ request: Request ) -> Self where Self == FetchAllQueryBuilderKey<Record, Request>.Default { Self[FetchAllQueryBuilderKey(request: request), default: []] } }

ID 16:56

And that should be all that it takes to get everything working, but unfortunately we still have a problem on this line of code where we construct our query using the GRDB query builder: $favoriteFacts = SharedReader( .fetchAll( Fact .filter(!Column("isArchived")) .order(ordering.orderingTerm) ) )

ID 17:06

We have the following error: Static method ‘fetchAll’ requires that ‘ QueryInterfaceRequest<Fact> ’ conform to ‘Hashable’

ID 17:14

It turns out that QueryInterfaceRequest isn’t Hashable , and so we are not allowed to use it with our fetchAll helper.

ID 17:20

We can dive into the definition of QueryInterfaceRequest to try to figure out why it isn’t Hashable . First, it holds onto something called a SQLRelation : public struct QueryInterfaceRequest<RowDecoder> { var relation: SQLRelation }

ID 17:33

And SQLRelation holds onto a bunch of different properties: struct SQLRelation: Sendable { … var source: SQLSource var selectionPromise: DatabasePromise<[SQLSelection]> var filterPromise: DatabasePromise<SQLExpression>? var ordering = SQLRelation.Ordering() var ctes: OrderedDictionary<String, SQLCTE> = [:] var children: OrderedDictionary<String, Child> = [:] var isDistinct = false var groupPromise: DatabasePromise<[SQLExpression]>? var havingExpressionPromise: DatabasePromise<SQLExpression>? var limit: SQLLimit? } Some of this data could be made Hashable , such as SQLSource , SQLLimit and isDistinct , but unfortunately all of the other stuff cannot be made Hashable so easily. In particular, there are a lot of DatabasePromise s held in this struct, and a promise represents a lazy, unresolved value that can be computed once a database connection is available: struct DatabasePromise<T> { /// Returns the resolved value. let resolve: @Sendable (Database) throws -> T … }

ID 18:19

Such types cannot be made Hashable , or even Equatable , since in general functions cannot be made Hashable .

ID 18:25

So, it looks like we are at a bit of an impasse here. There is a bit of a hack we could try though. At the end of the day, this generic FetchRequest value does get rendered out to a SQL string that is then sent to the SQLite library for processing. So perhaps we could derive the Hashable conformance of our

ID 18:49

The way one can compute the SQL string is by using the makePreparedRequest method in GRDB. Let’s experiment with this in the entry point of our app. Right in the prepareDependencies , where we have access to a database connection, let’s construct a query to play around with: init() { prepareDependencies { $0.defaultDatabase = DatabaseQueue.appDatabase let query = Fact .filter(!Column("isArchived")) .order(Ordering.number.orderingTerm) } }

ID 19:15

This query type has a method on it that can create a fully prepared request that is ready to be sent off to SQLite: query.makePreparedRequest(<#db: Database#>)

ID 19:22

However, to invoke it we need to provide a Database object, which is the opaque pointer to the database connection. In order to get access to that object we must start a read transaction with our database: $0.defaultDatabase.read { db in query.makePreparedRequest(db) }

ID 19:36

But these are throwing methods, and so now we have to contend with error handling. We’re not in a throwable context right now, so since we are just experimenting right now we will force unwrap: try! $0.defaultDatabase.read { db in try query.makePreparedRequest(db) }

ID 19:42

Once we have the prepared statement we can grab the statement property and print its description: print("👉", try query.makePreparedRequest(db).statement.description)

ID 19:54

With that done if we launch the simulator we will see the following printed to the console: 👉 SELECT * FROM "facts" WHERE NOT "isArchived" ORDER BY "number"

ID 20:15

Just a nice simple string of the exact query that we want to execute in SQLite.

ID 20:23

So, this seems promising, but there are a bunch of reasons why this is not going to be the right thing to do. First of all, we are doing a lot of work in order to generate this string, and the whole point of Hashable is to have a cheap computation that can distill a data type down to a single number.

ID 20:38

But in the act of computing this string we not only had to invoke throwing methods, for which we can’t even handle the errors in the hash(into:) method, but we also had to start a database transaction and execute database queries. If we look at the logs around the “

SELECT 21:16

This is all work that is going to be performed every time we compute the hash value of our key. Hashable queries

SELECT 21:23

This means every time we insert a key into a dictionary, or check for the existing of a key, or update a key. This could potentially cause hundreds of SQLite queries to be executed by interacting with the shared property wrapper in very innocent ways. Brandon

SELECT 21:36

And so this is not the way to go. We cannot be doing this kind of heavy work to compute the hash value of our key, and we shouldn’t try to push this any further.

SELECT 21:44

Luckily, there is a trick we can employ that helps one turn things that are historically not Hashable into something that is Hashable . We just need to introduce one more layer of abstraction on top of the FetchRequest protocol.

SELECT 21:57

Let’s take a look.

SELECT 22:00

Let’s hop back over to the SharingGRDB.swift file, where all of our library code is, and we will sketch a new protocol that represents a SQLite query to be executed, but we will force it to be Hashable : protocol FetchKeyRequest: Hashable { }

SELECT 22:28

Types will conform to this protocol to represent that they can fetch some value from a Database connection: protocol FetchKeyRequest: Hashable { func fetch(_ db: Database) throws }

SELECT 22:46

But the type of Value needs to be encoded in the protocol itself, so we will introduce a primary associated type: protocol FetchKeyRequest<Value>: Hashable { associatedtype Value func fetch(_ db: Database) throws -> Value }

SELECT 22:54

So, how does this extra layer of abstraction help us at all?

SELECT 22:59

Well, we will create all new types that conform to this protocol, and those types will hold onto the bits of data they need in order to perform the database request, and those bits of data will define the Hashable conformance. So while we cannot have a well-defined notion of hashability for a function, such as this fetch function, we can hash all of the parameters that make up the definition of the fetch function.

SELECT 23:29

This is the same principle used to make things like animations Hashable . As we discussed in our “ Back to basics ” episodes, where we dove deep into the Equatable and Hashable protocols, animation is the kind of thing that abstractly speaking doesn’t seem to be Hashable . However, animations can be described by a system of mathematical equations, and those equations are made up of coefficients and constants, and so you can define the hashability of an animation by hashing those coefficients and constants.

SELECT 24:00

This is exactly what we are doing for queries. For example, to represent our query for fetching facts and ordering them, we will define a whole new type that conforms to this protocol: struct FactsQuery: FetchKeyRequest { }

SELECT 24:20

It will hold onto whatever data it needs for customizing the query. In this case, that is just the ordering value that determines how we want to order the facts: struct FactsQuery: FetchKeyRequest { let ordering: Ordering }

SELECT 24:34

And then we can implement the fetch method exactly as we wanted to do with the @SharedReader value: struct FactsQuery: FetchKeyRequest { let ordering: Ordering func fetch(_ db: Database) throws -> [Fact] { try Fact .filter(!Column("isArchived")) .order(ordering.orderingTerm) .fetchAll(db) } }

SELECT 25:01

This is now a Hashable type that represents the query we want to perform. Its hash value is simply a mixture of the hash of the Ordering value, along with a hash of the type FactsQuery .

SELECT 25:19

We now just need to build the tools that allow us to use this kind of query with the @SharedReader property wrapper. We’ve already done this kind of work a few times in this series, and so we are going to mostly copy-and-paste the final answer.

SELECT 25:31

It starts by defining a new SharedReaderKey conformance that is responsible for holding onto the query and using it to load results from the database and subscribe to changes in the database: struct FetchKey<Value: Sendable>: SharedReaderKey { let database: any DatabaseReader let request: any FetchKeyRequest<Value> init(request: some FetchKeyRequest<Value>) { @Dependency(\.defaultDatabase) var database self.database = database self.request = request } var id: ID { ID( databaseObjectIdentifier: ObjectIdentifier(database), request: request ) } struct ID: Hashable { let databaseObjectIdentifier: ObjectIdentifier let request: AnyHashable init( databaseObjectIdentifier: ObjectIdentifier, request: some FetchKeyRequest<Value> ) { self.databaseObjectIdentifier = databaseObjectIdentifier self.request = request } } func load( context: LoadContext<Value>, continuation: LoadContinuation<Value> ) { do { continuation.resume( returning: try database.read { db in try request.fetch(db) } ) } catch { continuation.resume(throwing: error) } } func subscribe( context: LoadContext<Value>, subscriber: SharedSubscriber<Value> ) -> SharedSubscription { let cancellable = ValueObservation.tracking { db in try request.fetch(db) } .start( in: database, scheduling: .async(onQueue: .main) ) { error in subscriber.yield(throwing: error) } onChange: { value in subscriber.yield(value) } return SharedSubscription { cancellable.cancel() } } }

SELECT 26:13

And then we further define a static method helper that allows us to fetch a bunch of records from a FetchKeyRequest : extension SharedReaderKey { static func fetch<Value>( _ request: some FetchKeyRequest<Value> ) -> Self where Self == FetchKey<Value> { FetchKey(request: request) } }

SELECT 27:02

And with that done we can try to employ this nice syntax in our observable model: $favoriteFacts = SharedReader(.fetch(FactsQuery(ordering: ordering))) ‘ init(_:) ’ is unavailable: Assign a default value

SELECT 27:38

This is almost compiling, but we lost the default value in our fetch helper. We can get it back with an overload on range replaceable collections: extension SharedReaderKey { static func fetch<Value: RangeReplaceableCollection>( _ request: some FetchKeyRequest<Value> ) -> Self where Self == FetchKey<Value>.Default { Self[FetchKey(request: request), default: Value()] } }

SELECT 28:20

And now this is compiling just fine $favoriteFacts = SharedReader(.fetch(FactsQuery(ordering: ordering)))

SELECT 28:40

And at this point the “Query” suffix of the the FactsQuery seems a little superfluous. What if we just renamed the FetchKeyRequest to be Facts : $favoriteFacts = SharedReader(.fetch(Facts(ordering: ordering)))

SELECT 28:58

Now it may seem a little worrisome to define a type with such a generic name, but because this query is quite domain specific to this feature, we could even nest it inside the FactFeatureModel : @Observable @MainActor class FactFeatureModel { … struct Facts: FetchKeyRequest { let ordering: Ordering func fetch(_ db: Database) throws -> [Fact] { try Fact .filter(!Column("isArchived")) .order(ordering.orderingTerm) .fetchAll(db) } } } That’s a bit nicer.

SELECT 29:19

When we run the app we see that everything works as it did before, but we can now finally make use of a query builder to construct complex queries rather than trying to munge together a raw SQL string.

SELECT 30:10

However, it is a bit of a bummer that we had to introduce an additional abstraction layer just to use the query builder. I still think it would have been far better if we didn’t have to introduce a whole new type to describe a query, and instead could have used the query builder right inline: self.$favoriteFacts = SharedReader( .fetchAll( Fact .filter(!Column("isArchived")) .order(ordering.orderingTerm) ) )

SELECT 30:41

But unfortunately the query builder in GRDB is not Hashable and so we are not able to do that. Multiple queries

SELECT 30:54

And so this is looking really great. We now have the ability to use a query builder for our complex SQL queries, which increases type safety, minimizes chances for typos, and makes it easier to build highly customized queries based on various parameters. Stephen

SELECT 31:09

We do think it is a bummer that we have to define a whole new type that describes our query. It’s just more code you have to write, more code you have to figure out where you want to put in your project, and one more layer of indirection between your feature code and the query being executed. Brandon

SELECT 31:24

In fact, we feel that this problem is important enough to solve, that we may dedicate episodes soon to creating a query building mini-library that preserves hashability so that you can skip the intermediate layer and describe your queries directly in your models. That is extremely powerful to do, and it’s even possible to unlock some composability in the query builder so that you can mix, match and reuse queries in interesting ways. Stephen

SELECT 31:49

But that will have to wait for another day.

SELECT 31:52

There is one last technique we want to show off before ending this series, and that is small performance improvement one can make when you have a few separate but related queries. For example, in our main feature we are executing independent queries for fetching the unarchived facts, as well as computing the number of archived and unarchived facts. The data computed from these queries tend to change together. For example, if a fact is added or removed to the database, then one of the counts will also change.

SELECT 32:18

So, rather than performing each of those 3 queries in 3 separate database transactions, what if we could execute them all in a single transaction?

SELECT 32:26

Let’s take a look.

SELECT 32:29

We are currently querying for the state of the FactFeatureModel as 3 separate @SharedReader properties: @ObservationIgnored @SharedReader( .fetchOne(#"SELECT COUNT(*) FROM "facts" WHERE "isArchived""#) ) var archivedFactsCount = 0 @ObservationIgnored @SharedReader var favoriteFacts: [Fact] @ObservationIgnored @SharedReader( .fetchOne(#"SELECT COUNT(*) FROM "facts" WHERE NOT "isArchived""#) ) var unarchivedFactsCount = 0

SELECT 32:42

This technically means we are performing 3 queries in 3 transactions and have 3 separate database observations running. We can even see this by running the app in the simulator and looking at the database traces: 0.000s PRAGMA query_only = 1 0.000s BEGIN DEFERRED TRANSACTION 0.000s SELECT COUNT(*) FROM "facts" WHERE "isArchived" = true 0.000s COMMIT TRANSACTION 0.000s PRAGMA query_only = 0 0.000s PRAGMA query_only = 1 0.000s BEGIN DEFERRED TRANSACTION 0.000s SELECT COUNT(*) FROM "facts" WHERE "isArchived" = true 0.000s COMMIT TRANSACTION 0.000s PRAGMA query_only = 0 0.000s PRAGMA database_list 0.000s SELECT type, name, tbl_name, sql FROM sqlite_master 0.000s PRAGMA query_only = 1 0.000s BEGIN DEFERRED TRANSACTION 0.000s SELECT COUNT(*) FROM "facts" WHERE "isArchived" = false 0.000s COMMIT TRANSACTION 0.000s PRAGMA query_only = 0 0.000s PRAGMA query_only = 1 0.000s BEGIN DEFERRED TRANSACTION 0.000s SELECT COUNT(*) FROM "facts" WHERE "isArchived" = false 0.000s COMMIT TRANSACTION 0.000s PRAGMA query_only = 0 0.000s PRAGMA query_only = 1 0.000s BEGIN DEFERRED TRANSACTION 0.002s SELECT * FROM "facts" WHERE NOT "isArchived" ORDER BY "number" 0.000s COMMIT TRANSACTION 0.000s PRAGMA query_only = 0 0.000s PRAGMA query_only = 1 0.000s BEGIN DEFERRED TRANSACTION 0.000s SELECT * FROM "facts" WHERE NOT "isArchived" ORDER BY "number" 0.000s COMMIT TRANSACTION 0.000s PRAGMA query_only = 0

SELECT 32:58

We will see each of our queries is run as a separate transaction, bracketed inside the “BEGIN DEFERRED TRANSACTION” and “COMMIT TRANSACTION” commands.

SELECT 33:13

It would be a bit more efficient if we had one single transaction that enclosed all 3 queries. And we already have almost all the tools to do this already. We can use the FetchKeyRequest protocol for describing the execution of multiple queries for fetching all of this data at once.

SELECT 33:30

Right now we have a FetchKeyRequest conformance that only fetches the facts: struct Facts: FetchKeyRequest { let ordering: Ordering func fetch(_ db: Database) throws -> [Fact] { try Fact .filter(!Column("isArchived")) .order(ordering.orderingTerm) .fetchAll(db) } }

SELECT 33:48

But let’s generalize this a bit. We can nest a struct directly inside this type that represents all of the data that we want to retrieve: struct Facts: FetchKeyRequest { let ordering: Ordering struct State { var archivedFactsCount = 0 var favoriteFacts: [Fact] = [] var unarchivedFactsCount = 0 } … } We want to get the counts of the archived and unarchived facts, as well as a collection of the unarchived facts.

SELECT 34:17

Then we can adapt the fetch method to query for all of this data and bundle it into the State struct: func fetch(_ db: Database) throws -> State { try State( archivedFactsCount: Fact .filter(Column("isArchived")) .fetchCount(db), favoriteFacts: Fact .filter(!Column("isArchived")) .order(ordering.orderingTerm) .fetchAll(db), unarchivedFactsCount: Fact .filter(!Column("isArchived")) .fetchCount(db) ) }

SELECT 34:55

This allows us to perform all 3 queries in a single database transaction.

SELECT 34:59

And then instead of 3 separate @SharedReader values we will hold onto a single one for the Facts.State : // @ObservationIgnored // @SharedReader( // .fetchOne(#"SELECT COUNT(*) FROM "facts" WHERE "isArchived""#) // ) // var archivedFactsCount = 0 // // @ObservationIgnored // @SharedReader var favoriteFacts: [Fact] // // @ObservationIgnored // @SharedReader( // .fetchOne(#"SELECT COUNT(*) FROM "facts" WHERE NOT "isArchived""#) // ) // var unarchivedFactsCount = 0 @ObservationIgnored @SharedReader var state: Facts.State

SELECT 35:18

This causes a few compilation errors that have to be fixed. First, when the model is first created we will initialize _state to be a constant of the Facts.State : _state = .constant(Facts.State())

SELECT 35:31

And then when the ordering changes we will need to re-set the $state projected value with the new shared key: $state = SharedReader(.fetch(Facts(ordering: ordering)))

SELECT 35:40

However, this isn’t quite right. Right now the fetch helper we have defined was specialized to work with collections and had a default of an empty collection baked in. We need to provide a default for the wrapped value instead: $state = SharedReader( wrappedValue: state, .fetch(Facts(ordering: ordering)) )

SELECT 35:58

Next we need to update the view to go through the state property in order to access the facts and counts: if !model.state.favoriteFacts.isEmpty { Section { ForEach(model.state.favoriteFacts) { fact in Text(fact.value) .swipeActions { Button("Archive") { model.archive(fact: fact) } .tint(.blue) Button("Delete", role: .destructive) { model.delete(fact: fact) } } } } header: { HStack { Text("Favorites (\(model.state.favoriteFacts.count)") Spacer() Picker("Sort", selection: Binding(model.$ordering)) { Section { ForEach(Ordering.allCases, id: \.self) { ordering in Text(ordering.rawValue) } } header: { Text("Sort by:") } } .textCase(nil) } } }

SELECT 36:16

That’s all it takes and our application is back to compiling and everything behaves exactly as it did before.

SELECT 36:37

However, we now have the improvement that multiple queries are executed in a single database transaction. We can confirm this by launching the app in the simulator and looking at the logs: 0.000s PRAGMA query_only = 1 0.000s BEGIN DEFERRED TRANSACTION 0.000s SELECT COUNT(*) FROM "facts" WHERE "isArchived" 0.002s SELECT * FROM "facts" WHERE NOT "isArchived" ORDER BY "number" ASC 0.000s SELECT COUNT(*) FROM "facts" WHERE NOT "isArchived" 0.000s COMMIT TRANSACTION 0.000s PRAGMA query_only = 0 What used to be 3 separate transactions to execute the 3 kinds of queries is now just one single transaction that encapsulates 3 queries. Conclusion

SELECT 36:55

OK, we are now at the end of our series to explore how SQLite databases can be integrated into our Swift Sharing library. We have shown that not only is it possible, but the tools we have built can rival Swift Data in its expressiveness and ease of use.

SELECT 37:07

Over the past 4 episodes we have:

SELECT 37:09

…built a variety of fetching and query APIs that can be used with the @SharedReader property wrapper that makes it possible to hold onto state in your features that is secretly powered by a SQLite database and query. Brandon

SELECT 37:20

We have also made it so that these pieces of shared state subscribe to changes in the database so that when some other part of the application makes a change to the database, the state in your features is automatically updated. Stephen

SELECT 37:33

Further, these tools can be used basically anywhere in your app. We have one feature that uses these tools in an observable model because that feature has quite complex logic. But we also have another feature that uses these tools directly in a SwiftUI view because that view was quite simple. And if we had a UIKit feature we would also be able to use these tools right in a UIViewController . We do not have the same limitations as Swift Data’s @Query macro, which only works when installed directly in a view. Brandon

SELECT 38:01

It is incredibly powerful, and it’s important to reflect for a moment on all the ways we have see the @Shared property wrapper has been used. Recall that we only open sourced this library a few weeks ago, and from the beginning it shipped with 3 persistence strategies: a user defaults strategy, a file storage strategy, and an in-memory strategy. And now we have shown how this can further be expanded to include SQLite storage, and most importantly we were able to do this outside of the library without any access to any of its internals. Stephen

SELECT 38:33

This shows that you can create your own persistence strategies for the @Shared property wrapper, and really the sky is the limit. We have lots of examples of more “exotic” forms of persistence strategies in the Swift Sharing repo, ranging from Firebase to a cross-platform SwiftWasm app. We would love to keep showing off different kinds of persistence strategies that can be used with the @Shared property wrapper, but for now we will leave it here.

SELECT 38:57

Until next time. 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