Video #349: Tour of SQLiteData: Assets

Episode: Video #349 Date: Jan 5, 2026 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep349-tour-of-sqlitedata-assets

Description

We explore how SQLiteData gives you precise control over your data model, including larger blobs of data, by adding a photo avatar feature to our scorekeeping app. Along the way we will explore a new iOS 26 style confirmation dialogs and a SwiftUI binding trick.

Video

Cloudflare Stream video ID: 079dccd4034f4e28cc5f39a719042a17 Local file: video_349_tour-of-sqlitedata-assets.mp4 *(download with --video 349)*

References

Transcript

— 0:05

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

— 0:27

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

— 0:39

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

— 1:05

The first thing we need to do is figure out how we are going to store image data for each player. An image can be represented by a collection of bytes, such as Swift’s Data type, and SQLite can store those bytes in a

BLOB 1:35

And this certainly can be fine for many kinds of apps. However, if your table is expected to grow to tens or hundreds of thousands of rows, and if you need to execute complex queries as quickly as possible on the table, then there is a better way. By putting the data blobs directly in this table we are hurting SQLite’s ability to scan through the rows quickly and efficiently. And further, we may find ourselves loading this data into memory when it’s not actually needed.

BLOB 2:07

A better approach is to design a new table that holds the image data and is associated with a player in a 1-to-1 fashion. That keeps queries on the players table efficient and allows us to load image data only when we explicitly need it.

BLOB 2:31

And in fact, thanks to our collection of editor helper files in the-point-free-way directory we can even ask Xcode to create this data type for us and create the corresponding migration: Prompt @SQLiteData.md I would like to associate an image to each player. show me how to do that with my current swift data types as well as create a migration to make the corresponding changes in my SQLite database.

BLOB 3:41

Xcode suggests that we define a new struct data type to represent the player asset: @Table struct PlayerAsset: Identifiable { @Column(primaryKey: true) let playerID: Player.ID let imageData: Data var id: Player.ID { playerID } }

BLOB 3:55

It even correctly defined a foreign key pointing to the player the asset belongs to, and marked that column as being a primary key.

BLOB 4:24

And further it suggested we register a new migration to create this table in our SQLite database connection: migrator.registerMigration("Create table 'playerAssets'") { db in try #sql( """ CREATE TABLE "playerAssets" ( "playerID" TEXT PRIMARY KEY NOT NULL REFERENCES "players"("id") ON DELETE CASCADE, "imageData" BLOB NOT NULL ) STRICT """ ) .execute(db) }

BLOB 4:37

It’s also very important that this table is created in a brand new registered migration rather than editing our previous migration. Once a migration has been shipped to production and run on our user’s devices, we should consider that migration set in stone and never to be edited again. And of course that isn’t the case for this particular migration because we are still in development of the app’s features. But it’s important to understand the distinction between schema changes that happen while developing versus schema changes that happen after shipping, and our “Point-Free Way” text documents make it clear that one needs to be careful with this.

BLOB 5:37

It even knew to make the playerID column the primary key, even though the column isn’t named id like our other tables. And it knew to make it a foreign key that references the id column on the players table. And further it annotated this foreign key relationship as ON DELETE CASCADE so that when a player is deleted its associated asset will be deleted.

BLOB 6:17

Now that we have the Swift data type defined and schema migrated, let’s start updating the game feature to load this data and display it. Just as we did with counting players in games, we will design a new data type that can hold onto a player and their associated image, if it exists: @Selection struct Row { let player: Player let imageData: Data? }

BLOB 7:04

Then we can update the state in the observable model to hold onto a collection of Row values instead of players: @ObservationIgnored @FetchAll var rows: [Row]

BLOB 7:09

With that done we just need to update a few spots in this file to use the rows variable instead of the players variable…

BLOB 7:24

And then we need to update the reloadData method to execute a query that ultimately selects into this Row type. We can keep the query mostly as it is because we still want to only select players belonging to the game we are viewing and we still want to order the players. But then we will further join the PlayerAsset table and the select the Row.Columns so that we can select each player’s image: private func reloadData() async { await withErrorReporting { _ = try await $rows.load( Player .where { $0.gameID.eq(game.id) } .order { if sortAscending { $0.score.asc() } else { $0.score.desc() } } .leftJoin(PlayerAsset.all) { $0.id.eq($1.playerID) } .select { Row.Columns(player: $0, imageData: $1.imageData) }, animation: .default ) } }

BLOB 9:05

And now that we have access to each player’s image, we can start displaying it in the view: Button { } label: { if let imageData = row.imageData, let image = UIImage(data: imageData) { Image(uiImage: image) .resizable() .scaledToFill() } else { Rectangle() } }

BLOB 9:33

And that’s all it takes to start querying for player images and displaying them in the view. Selecting photos

BLOB 9:47

Alright, we’ve accomplished the first steps towards implementing the player image feature. We used our “Point-Free Way” documents to guide Xcode in defining a brand new Swift data type to represent the player asset, and it even wrote a migration for us to run in SQLite to create the table. We then altered the query that powers the game feature to load any associated player image data so that it can be displayed. However, we still don’t have a way to allow users to select images for their players. Stephen

BLOB 10:18

That’s the next thing we will do. That is going to require us to interact with SwiftUI’s photosPicker view modifier, which allows us to present a UI for choosing a photo, and then once the user selects a photo its data is transmitted back to our app code.

BLOB 10:31

Let’s take a look.

BLOB 10:34

The easiest way to allow our users to select a photo from their photo library is using the photosPicker view modifier: .photosPicker( isPresented: <#Binding<Bool>#>, selection: <#Binding<PhotosPickerItem?>#> )

BLOB 10:50

You give it a binding to a boolean to determine if the picker is displayed or not, as well as a binding of a PhotosPickerItem? , and once the user makes their selection the photo picker will write to that binding. We can then listen for changes to that state to determine when the user selects a photo and save that image to the database.

BLOB 11:07

So, we need to add some new state to our observable model: var photosPickerItem: PhotosPickerItem? var isPlayerPhotoPickerPresented = false

BLOB 11:27

…so that we can bind to that state in the view: .photosPicker( isPresented: $model.isPlayerPhotoPickerPresented, selection: $model.photosPickerItem )

BLOB 11:40

Next we can invoke a method on the observable model when the user’s avatar is tapped: Button { model.photoButtonTapped(player: row.player) } label: { … }

BLOB 12:04

And we can implement that method by simply flipping the boolean state to true : func photoButtonTapped(player: Player) { isPlayerPhotoPickerPresented = true }

BLOB 12:33

That’s all it takes to get a photo picker to slide up when tapping the user avatar. However, we still have to listen for when the user actually selects a photo, and then save that image to the database.

BLOB 12:45

To do this we will implement a didSet callback on the photosPickerItem state: var photosPickerItem: PhotosPickerItem? { didSet { } }

BLOB 12:54

This allows us to detect whenever the photo picker view writes to the binding we hand to it.

BLOB 12:58

We will invoke a method on the observable model when we detect the state changes: var photosPickerItem: PhotosPickerItem? { didSet { updatePlayerImage() } }

BLOB 13:07

And now we have to figure out how to implement this method: private func updatePlayerImage() { }

BLOB 13:18

The first thing we will do in this method is make sure we have a photoPickerItem , otherwise there’s nothing to do: guard let photosPickerItem else { return }

BLOB 13:30

And the next thing we will do is use the non-optional photosPickerItem to load the actual image data that it represents: let imageData = try await photosPickerItem.loadTransferable( type: Data.self )

BLOB 13:46

But to do this we need both a throwing and async context. For the throwing context we will explicitly catch any errors and then sometime in the future we can handle the error by displaying an alert to the user: do { … } catch { // TODO: Show error to user }

BLOB 14:11

To provide an async context we need to fire up an unstructured task. We really have no choice. But the question is where to do that.

BLOB 14:17

Typically our recommendation is to push unstructured tasks into the view as much as possible, and make the methods on the observable object async so that they are easier to test. But that principle doesn’t really help here.

BLOB 14:29

To see why, we can start by making the updatePlayerImage async: private func updatePlayerImage() async { … }

BLOB 14:34

That gets this method compiling, but now our didSet callback is not compiling because we aren’t in an async context: var photosPickerItem: PhotosPickerItem? { didSet { updatePlayerImage() } } ‘async’ call in a function that does not support concurrency

BLOB 14:53

And we can’t make this computed property have an async setter, and so we are stuck. We really have no choice but to spin up an unstructured task: var photosPickerItem: PhotosPickerItem? { didSet { Task { await updatePlayerImage() } } }

BLOB 15:07

And this is going to hurt testability too. In tests we would like to be able to assign a new PhotosPickerItem to this property and then assert how our logic executed, such as saving an image to the database. But that unfortunately will not be possible because of this unstructured task. We will need to sleep for an indeterminate amount of time to let the task execute.

BLOB 15:24

In this kind of situation there is another trick you can employ to still spin up an unstructured task in the model but also allow for testing. We will keep track of the unstructured task as an instance variable in the model: var updatePlayerImageTask: Task<Void, Never>?

BLOB 15:43

And then in the didSet callback we can cancel any existing task, and assign the newly created task: var photosPickerItem: PhotosPickerItem? { didSet { updatePlayerImageTask?.cancel() updatePlayerImageTask = Task { await updatePlayerImage() } } }

BLOB 15:55

This code still works the same as before, but now in tests we can await the updatePlayerImageTask if we want to guarantee that the method has finished executing its logic.

BLOB 16:04

OK, we’re getting closer to the implementation of this method, but there’s still a bit more work to do. The loadTransferable method returns an optional so we need to guard unwrap it: guard let imageData = try await photosPickerItem.loadTransferable( type: Data.self ) else { return }

BLOB 16:10

Now we have the image data we want to save to the database. We can open up a write transaction to the database: try await database.write { db in }

BLOB 16:19

And when saving this image data we would like it to insert if there is no image for the player already, and otherwise replace any current image data that is there. Our library comes with an upsert method that does this logic for you: try PlayerAsset.upsert { }

BLOB 16:36

Inside this trailing closure we can construct a PlayerAsset : try PlayerAsset.upsert { PlayerAsset( playerID: <#Player.ID#>, imageData: <#Data#> ) }

BLOB 16:39

…and if SQLite sees that there is already an asset for the specified playerID it will replace the data, otherwise it will insert the data. This mechanism works specifically because the table definition of PlayerAsset marked the playerID column as a primary key: try #sql( """ CREATE TABLE "playerAssets" ( "playerID" TEXT PRIMARY KEY NOT NULL REFERENCES "players"("id") ON DELETE CASCADE, "imageData" BLOB NOT NULL ) STRICT """ ) .execute(db) And remember we didn’t even write this code. This was generated for us by ChatGPT and with the aid of the context documents we used.

BLOB 17:07

We can fill in half of this PlayerAsset initializer because we now have the image data at hand: try PlayerAsset.upsert { PlayerAsset( playerID: <#Player.ID#>, imageData: imageData ) }

BLOB 17:12

But where do we get the player ID from? How do we know for which player we are upserting their image?

BLOB 17:18

Well, we don’t. We have no clue what player image the user tapped on a few moments ago. That is extra state we have to start tracking in the observable model: var playerPhotoPickerPresented: Player?

BLOB 17:43

It’s very unfortunate that we need two separate pieces of state like this: var isPlayerPhotoPickerPresented = false var playerPhotoPickerPresented: Player? This leads to invalid states being possible, such as the boolean being true but the optional being nil . Or the boolean being false but the optional being non- nil . Typically we prefer to better model our domains to avoid things like this, but unfortunately we cannot in this situation. It turns out that the photo picker writes to its binding before the image is delivered to our code, which means the state would be lost by the time we need it. So we really have no choice but to have a suboptimal domain here.

BLOB 18:09

And we will set that state when the player photo is tapped: func photoButtonTapped(player: Player) { isPlayerPhotoPickerPresented = true playerPhotoPickerPresented = player }

BLOB 18:27

And then in the updatePlayerImage method we will need to unwrap that state before proceeding: private func updatePlayerImage() async { guard let photosPickerItem, let playerPhotoPickerPresented else { return } … }

BLOB 18:37

And now we have the player that we are editing and so can finish constructing our upsert statement. But remember, this is only the description of a SQL statement and does not actually execute anything. To execute we need to invoke the execute method: try PlayerAsset.upsert { try PlayerAsset( playerID: playerPhotoPickerPresented.id, imageData: imageData ) } .execute(db)

BLOB 19:06

And that is all it takes to finish this feature. It’s even possible test this functionality directly in a preview. But we can also run the app in the simulator, set an image for a player and we will see it appear in the UI. Further, if we restart the app, we will see that the image persisted and the data was saved in the database. Deleting assets

BLOB 19:55

OK, this is pretty incredible. We have now added the ability to set images for our players, and that data is persisted across launches. It honestly didn’t take much work. Brandon

BLOB 20:03

But let’s quickly add one more feature before showing how to synchronize our app to iCloud and share data with other iCloud users. We will make it so that we can remove an image from a player that we have previously set. This will give us an opportunity to show off a small change that was made to confirmation dialogs in iOS 26.

BLOB 20:18

Let’s take a look.

BLOB 20:21

We will make it so that when tapping a player’s image that has already been set will open a confirmation dialog instead of the photo picker. And in that confirmation dialog we will give the user the option of removing the photo or selecting a new one.

BLOB 20:35

In order for us to determine if a photo is already set for a player we will make it so that the photoButtonTapped method takes the full Row value instead of just the player: func photoButtonTapped(row: Row) { … }

BLOB 20:56

And then in the implementation of this method we can determine whether a photo is already set or not: func photoButtonTapped(row: Row) { if row.imageData == nil { isPlayerPhotoPickerPresented = true playerPhotoPickerPresented = row.player } else { // TODO: show confirmation dialog } }

BLOB 21:13

And we will need to make sure to update the call site of this method to pass the row: model.photoButtonTapped(row: row)

BLOB 21:21

Now what do we do in this else condition? We need to mutate some state that the view can observe it and display the confirmation dialog. We might think we can use something simple like a boolean: var isPlayerPhotoConformationDialogPresented = false

BLOB 21:53

…but this isn’t enough. We need to know what player the user tapped on so that we can delete their image if the user decides to remove the current photo. So really we should hold onto an optional piece of state: var playerPhotoConfirmationDialog: Player?

BLOB 22:27

And then in the else branch we can simply populate this state: } else { playerPhotoConfirmationDialog = row.player }

BLOB 22:39

This is the most concise way to model this domain, but we will soon see that SwiftUI’s APIs don’t make it easy to use this kind of state. We need to employ some advanced techniques.

BLOB 22:54

To see the problem, we can autocomplete a confirmationDialog view modifier on the button that holds the player’s photo: .confirmationDialog( <#LocalizedStringKey#>, isPresented: <#Binding<Bool>#>, actions: <#() -> View#> )

BLOB 23:12

Note that we are localizing this view modifier to the button because in iOS 26 confirmation dialogs are now presented pointing to the view they are attached to. This is in contrast to iOS 18 and earlier, in which confirmation dialogs were always presented from the bottom of the screen.

BLOB 23:49

That’s certainly a better user experience, but it brings complications to our code because we have a single piece of state that is technically driving multiple confirmation dialogs. We have one for each row in the list.

BLOB 24:05

However, thanks to dynamic member lookup and subscripts we can derive a binding in each row to a boolean that powers the dialog for just that row. Let’s start by filling in the title of the confirmation dialog: .confirmationDialog( "Player photo", isPresented: <#Binding<Bool>#>, actions: <#() -> View#> )

BLOB 24:27

Next we need a binding to a boolean, but all we have is a binding to an optional piece of state: .confirmationDialog( "Player photo", isPresented: $model.playerPhotoConfirmationDialog, actions: <#() -> View#> )

BLOB 24:44

However, we can transform this binding to an optional into a binding to a boolean where true represents that the dialog for this player is presented. We can do this by subscripting into the optional: $model.playerPhotoConfirmationDialog[isPresenting: row.player.id]

BLOB 25:25

And behind the scenes this will derive a new binding. We just have to implement this subscript: extension Optional { subscript(isPresenting: <#???#>) -> Bool { get { } set { } } }

BLOB 26:00

This subscript will be constrained to only work when the wrapped value of the optional is Identifiable : extension Optional { subscript(isPresenting: <#???#>) -> Bool where Wrapped: Identifiable { … } }

BLOB 26:11

And then the argument of the subscript can be the ID of the value that we are wanting to present: extension Optional where Wrapped: Identifiable { subscript(isPresenting id: Wrapped.ID) -> Bool { … } }

BLOB 26:17

And now the get and set of this subscript are quite easy to implement. The get will just need to check that the ID of the optional matches the ID of the value passed to the subscript: get { self?.id == id }

BLOB 26:35

And the only thing SwiftUI can ever write to this binding is false , and so we will interpret that as nil ing out the optional: set { if !newValue { self = nil } }

BLOB 26:55

And that’s all it takes to derive a binding to a boolean from a binding of an optional that represents presenting a dialog for a particular user.

BLOB 27:06

We still have the actions to fill out for the confirmation dialog. We will give the user the choice of selecting a new photo or removing the existing photo, and we will invoke methods on the model to handle that logic: .confirmationDialog( "Player photo", isPresented: $model.playerPhotoConfirmationDialog[ isPresenting: row.player.id ], actions: { Button("Select new photo") { model.selectNewPhotoButtonTapped() } Button("Remove photo", role: .destructive) { model.removePhotoButtonTapped() } } )

BLOB 27:45

Note that we do not need to pass any information to these methods because the playerPhotoConfirmationDialog state is still intact by the time these action closures are called.

BLOB 27:58

So, let’s add stubs of these methods to the model: func selectNewPhotoButtonTapped() { } func removePhotoButtonTapped() { }

BLOB 28:11

The first method is the easiest. We just need to present the photo picker for the player held inside the playerPhotoConformationDialog state: func selectNewPhotoButtonTapped() { guard let player = playerPhotoConfirmationDialog else { return } isPlayerPhotoPickerPresented = true playerPhotoPickerPresented = player }

BLOB 28:47

And the second is pretty straightforward too. We just need to execute a query that finds the asset for the player, and deletes it: func removePhotoButtonTapped() { guard let player = playerPhotoConfirmationDialog else { return } withErrorReporting { try database.write { db in try PlayerAsset.find(player.id).delete().execute(db) } } }

BLOB 29:23

And we have now implemented the feature. We can run the app, find a player that we previously assigned a photo to, tap the image, and choose to remove the photo. A moment later the photo is gone. Next time: iCloud sync

BLOB 30:15

Alright, we now have a fully functional score keeper app! We can add and delete games, within each game we can add and delete players, and even sort the players. And we are able to assign photos to each player. All of the data is automatically persisted to disk so that it’s available next launch, and each step of the way we saw that our SQLiteData library gives us powerful tools for querying our data that can be used in SwiftUI views as well as observable models. Stephen

BLOB 30:42

But now let’s get on to the part that I know everyone is most interested in: synchronization! We want our users to have access to their data across all of their devices, and we want to do it in such a way that we don’t really have to think about it when making new features. We should be able to add new screens to our app that insert data into the database, delete data, query for data, all without thinking about synchronization, and it should all just work magically.

BLOB 31:05

Well, our SQLiteData makes iCloud synchronization possible, and it handles it seamlessly. Let’s take a look…next time! References SQLiteData Brandon Williams & Stephen Celis A fast, lightweight replacement for SwiftData, powered by SQL. https://github.com/pointfreeco/sqlite-data StructuredQueries A library for building SQL in a safe, expressive, and composable manner. https://github.com/pointfreeco/swift-structured-queries Downloads Sample code 0349-sqlite-data-tour-pt3 Point-Free A hub for advanced Swift programming. Brought to you by Brandon Williams and Stephen Celis . Content Become a member The Point-Free Way Beta previews Gifts Videos Collections Free clips Blog More About Us Community Slack Mastodon Twitter BlueSky GitHub Contact Us Privacy Policy © 2026 Point-Free, Inc. All rights are reserved for the videos and transcripts on this site. All other content is licensed under CC BY-NC-SA 4.0 , and the underlying source code to run this site is licensed under the MIT License .