Video #340: CloudKit Sync: Preparing an Existing App

Episode: Video #340 Date: Oct 6, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep340-cloudkit-sync-preparing-an-existing-app

Description

We show how to add iCloud synchronization to the persistence layer of an existing SQLite application by using SQLiteData. While SQLiteData’s CloudKit tools can be configured with a single line of code, one must still prepare their database schema to be compatible and durable when it comes to synchronizing across multiple devices and versions.

Video

Cloudflare Stream video ID: ee79b0c69c63dbaea02a3781272ec593 Local file: video_340_cloudkit-sync-preparing-an-existing-app.mp4 *(download with --video 340)*

References

Transcript

— 0:05

We have now released 15 videos in our “ Modern Persistence ” series. Along the way we have built a large, complex app that is based off the Reminders app from Apple. We have multiple SQLite tables representing the various domain models of our app. There are relationships between those models, such as one-to-many relationships and many-to-many relationships. We use a type-safe and schema-safe query builder to write very complex queries to pull data from the database and populate our features’ views. And each feature automatically subscribes to the database so that any changes to the data will cause the view to automatically re-render. Brandon

— 0:39

And we really do think that all of the techniques and tools we have shown off so far do bring one closer to having “Modern Persistence” in their apps. But there is one huge topic that we have yet to touch in this series, and some might say it is a lie to call any of this “Modern Persistence” without discussing this topic, and that’s synchronization.

— 1:00

Modern apps almost always need the ability to have the user’s data distributed across all of their devices. If you are working on a reminders app, it is almost certainly a requirement that the reminders that a user creates on their phone is also synchronized to their iPad. Stephen

— 1:15

But distributing the schema of your database across many devices comes with many significant problems that you have to deal with. For just a taste of these problems, consider the following:

— 1:24

What happens when the user edits a record on each of their devices before there has been time to synchronize changes? How are we supposed to deal with record conflicts? Brandon

— 1:32

What happens when a user’s iPhone and iPad are running different versions of the app with different schemas? Special care needs to be taken so that records created with the older device can be synced to the new device, and so that records created by the new device do not lose data when synced to the old device. Stephen

— 1:52

SQLite has an important data integrity tool called “foreign key constraints.” This makes it so that if table B depends on table A, then you are not allowed to construct rows of B unless it has a corresponding A in the database. But what happens if during synchronization you receive child records from another device before you receive the parent record? How are we supposed to handle that in a world of foreign key constraints? Brandon

— 2:13

And if that already sounds complicated, let me tell you…that ain’t nothing.

— 2:18

There is a laundry list of synchronization nuances that one has to think about when distributing their schema across multiple devices. And luckily for everyone watching this episode right now, we spent the last 5 months and many hundreds of hours building a tool that allows you to synchronize your SQLite databases across multiple devices that automatically handles these edge cases for you. Stephen

— 2:43

It’s called SQLiteData , and the 1.0 of the library was released just a few weeks ago . It supersedes the SharingGRDB library we released many months ago, and we are going to use it now to add synchronization to the reminders app we have been building during our “Modern Persistence” series.

— 2:59

And this is going to be really informative for everyone because this will give us an opportunity to update an existing app to work with synchronization rather than starting a brand new app from scratch, which can be a little easier.

— 3:10

So, let’s get started by showing the steps we need to take in order to make our current SQLite schema more friendly to synchronization. Preparing our app for synchronization

— 3:19

I have the Reminders project open that we have been building for the past many weeks, and I also have it running in the simulator right here. This app is quite complex. It shows off various forms of navigation, has forms for creating reminders lists and reminders, has advanced filtering and sorting functionality, and advanced full-text search with highlighting, snippets and tokenization.

— 4:11

The first thing we are going to do with our project is migrate it from the SharingGRDB dependency to the SQLiteData dependency. Our SQLiteData library is largely just a rename and evolution of the SharingGRDB library we released at the beginning of this year, but our past episodes covering “Modern Persistence” topics was started before we released SQLiteData.

— 4:35

Luckily the migration is super quick. We are going remove the SharingGRDB dependency, and replace it with SQLiteData 1.0…

— 4:57

Next we are going to find all instances of import SharingGRDB in this project and replace them with import SQLiteData …

— 5:09

And after trying to build we will find that we do have one error, and that is that our project no longer seems to have access to some symbols it used to, such as @Shared . This is happening because SharingGRDB used to re-export our [swift-sharing] library since it was fundamental to its usage, but now that we have generalized the package a bit under the name SQLiteData, we no longer re-export Sharing. swift-sharing

— 5:32

But that’s OK. This just means we have to be more explicit with our dependence on Sharing by adding it explicitly and importing it where needed…

— 5:37

And believe it or not, that is all it takes to update our project to use SQLiteData 1.0. SQLiteData is for the most part backwards compatible with SharingGRDB, but there may be a few small things you have to do to move over to SQLiteData 1.0.

— 5:51

Now the question is: how do we prepare this app for use with CloudKit synchronization? Well, we know that our documentation can never be as good as SQLite’s, which is some of the best in the business, but we try our hardest. And so let’s visit the docs for SQLiteData to see what it takes to start implementing CloudKit synchronization:

— 6:06

In these docs we have a bunch of articles on various things, and the one we are interested in right now is “Getting started with CloudKit”. It starts with a section on setting up the project. All of these steps are things that need to be done in any CloudKit project, even if you are not using SQLiteData.

— 6:29

First we need to enable the iCloud entitlements in our project, which we can do in the project settings…

— 6:39

Then, within the iCloud entitlement we will enable CloudKit…

— 6:43

And after that we will add a new container to iCloud, and this must start with “iCloud.” and then can be anything after that, but we will just use the bundle identifier of the app: iCloud.co.pointfree.ModernPersistence.Reminders

— 7:01

Going back to the docs we see the next step is to configure background execution modes, which we can do by adding another entitlement…

— 7:12

And enabling “Remote notifications”…

— 7:15

Going back to the docs we see that there are additional steps to be taken once we are ready to allow sharing, but we will get to that later. And there are also additional steps to be taken once we are ready to deploy our app to production, but again those steps are not needed right now.

— 7:27

Ok, with the iCloud entitlements and container configured, we are ready to start embarking on the steps to enable CloudKit synchronization that are specific to SQLiteData. The first step is setting up what is known as a SyncEngine . This is the type that is responsible for listening for changes in the user’s database and replaying those changes back to CloudKit. And conversely, it receives updates from CloudKit and replays those changes back to the user’s database.

— 7:47

The docs recommend that we set up the sync engine as early as possible in the application’s lifecycle, and we should even do it in prepareDependencies so that we can set the defaultSyncEngine that is used by the entire app: prepareDependencies { $0.defaultDatabase = try! appDatabase() $0.defaultSyncEngine = SyncEngine( for: <#any DatabaseWriter#>, tables: <#repeat each T1#>, privateTables: <#repeat each T2#>, containerIdentifier: <#String?#>, defaultZone: <#CKRecordZone#>, startImmediately: <#Bool#>, logger: <#Logger#> ) }

— 8:20

There are a number of options we can provide, but many are optional. The first argument, for , is required and it is a reference to the user’s database that we want to synchronize to CloudKit. And that happens to be the database we just configured: $0.defaultSyncEngine = SyncEngine( for: $0.defaultDatabase, tables: <#repeat each T1#>, privateTables: <#repeat each T2#>, containerIdentifier: <#String?#>, defaultZone: <#CKRecordZone#>, startImmediately: <#Bool#>, logger: <#Logger#> )

— 8:33

Next is a variadic list of the tables that we want to synchronize to CloudKit. This must be manually provided and cannot be automatically inferred from the user’s database because there may be tables the user does not want sync’d.

— 8:44

So, let’s specify all of the domain models that make up our schema: $0.defaultSyncEngine = SyncEngine( for: $0.defaultDatabase, tables: RemindersList.self, Reminder.self, Tag.self, ReminderTag.self, privateTables: <#repeat each T2#>, containerIdentifier: <#String?#>, defaultZone: <#CKRecordZone#>, startImmediately: <#Bool#>, logger: <#Logger#> )

— 8:59

Next is a list of private tables, which are tables that are synchronized to CloudKit but are not shared with other users when records are shared. An example of this would be if you wanted to hold onto preferences for a record, such as the default sort and filters to be used in a reminders list. You would want each user to be able to have their own preferences and for them to not be synced to all users when someone makes a change.

— 9:19

This argument is optional and so we can omit it…

— 9:23

Next is the containerIdentifier , which is optional because typically we can determine that automatically from the configuration of your project, so let’s delete that…

— 9:31

Next is defaultZone , which is the zone that all records will be put into. If no zone is specified we provide one automatically, and so let’s remove that…

— 9:43

Next is startImmediately , which tells the sync engine to start syncing records right away. This option can be useful if you want sync’ing capabilities to be something that the user can purchase to enable. In such a case you can create the engine so that it does not start immediately, and then once they purchase the sync’ing functionality you can start the sync engine. We are not going to use that right now, so let’s remove it…

— 10:04

And lastly there is an argument for a logger that can be used to logging various events in the sync engine. That too has a default, and so let’s remove it…

— 10:11

The initializer for SyncEngine can also throw, and so just like when provisioning the database we will try! this: $0.defaultSyncEngine = try SyncEngine( for: $0.defaultDatabase, tables: Reminder.self, RemindersList.self, Tag.self, ReminderTag.self )

— 10:20

Now right off the bat we get an error: Initializer ‘init(for:tables:privateTables:containerIdentifier:defaultZone:startImmediately:logger:)’ requires that ‘ReminderTag’ conform to ‘PrimaryKeyedTable’

— 10:25

This tells us that the ReminderTag table, which is the join table that allows us to associate any number of reminders to any number of tags, must be a PrimaryKeyedTable . What does that mean?

— 10:38

Well, we’ve got docs on that. If we scroll down a little more we will see a section of the docs called “Designing your schema with synchronization in mind”, in it we learn that: Note Distributing your app’s schema across many devices is a big decision to make for your app , and care must be taken . It is not true that you can simply take any existing schema, add a SyncEngine to it, and have it magically synchronize data across all devices and across all versions of your app. There are a number of principles to keep in mind while designing and evolving your schema to make sure every device can synchronize changes to every other device , no matter the version.

— 11:05

And a sub-section of this section lets us know what is going on with this error: Note Primary keys on every table Each synchronized table must have a single, non-compound primary key to aid in synchronization, even if it is not used by your app.

— 11:12

When we created the ReminderTag table we decided not to put a primary key on it because the reminder ID and tag ID serve as the main way to identify the joining of the tables.

— 11:21

Well, in a world in which we need to synchronize rows across many devices that no longer works. Every single table synchronized needs a primary key because we need a way to distinguish all rows from each other, regardless of which device created them.

— 11:33

So, we will need to add a primary key to ReminderTag , which will take a bit of work, so for a moment let’s just comment out that table when initializing the sync engine just so that we can see what other work needs to be performed: $0.defaultSyncEngine = try SyncEngine( for: $0.defaultDatabase, tables: Reminder.self, RemindersList.self, Tag.self, // ReminderTag.self )

— 11:44

Well, unfortunately we still have an error, and this one is a little different: Initializer ‘init(for:tables:privateTables:containerIdentifier:defaultZone:startImmediately:logger:)’ requires that ‘Int’ conform to ‘IdentifierStringConvertible’

— 11:57

The problem here is that the primary key for all of our tables is an integer: CREATE TABLE "remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, … ) STRICT

— 12:09

This is by far the easiest way to get started with primary keys in SQLite because the ID is represented by a simple integer, and when inserting a new row into a table SQLite takes care of automatically assigning a new, unused ID.

— 12:20

However, such a simplistic mechanism for choosing IDs for rows in a database does not play nicely with distributing your schema across multiple devices. It leaves open the possibility, and really almost a certainty, that two different devices can create a record with the same ID. At that point our library has no choice but to try to merge the data from the two records, which means that data was lost since two independent records became one.

— 12:42

And the docs for the library call this out under the section “Globally unique primary keys”: Note Primary keys must be globally unique identifiers, such as UUID, and cannot be an auto-incrementing integer. Further, a NOT NULL constraint must be specified with an ON CONFLICT REPLACE action.

— 12:49

So we further have to update all of our existing tables to convert their integer primary keys into UUID primary keys.

— 12:54

So let’s comment out the SyncEngine we are trying to construct since that will take a bit more time…

— 13:05

And let’s go through each data struct we have defined that represents a SQLite table and update its id to be a UUID: @Table struct RemindersList: Equatable, Identifiable { let id: UUID … } @Table struct Tag: Identifiable { let id: UUID … } @Table struct Reminder: Identifiable { let id: UUID … }

— 13:21

And further, the ReminderTag table doesn’t have a primary key at all, so let’s add one: @Table struct ReminderTag { let id: UUID … }

— 13:25

One nice thing about what we have done so far is that we did not have to update any of the foreign keys. Because all of our types are Identifiable , they automatically have a nested

ID 13:49

This of course causes some problems where we have assumed that we have an integer ID. So let’s fix all of those compiler errors…

ID 15:14

And it would be pretty amazing if everything just magically worked, but of course that can’t possible be. We have lots of data in our database already that have integer IDs and those cannot be magically turned into UUIDs. In fact, if we run the app we will see no reminders lists are showing, and we have a purple runtime warning letting us know that something is not right: Caught error: InvalidUUID()

ID 15:40

This is happening because the SQLiteData cannot decode the integer fetched from the database into its expected type, which is a UUID. We have to further migrate our tables to change all of their primary keys to be UUIDs. Migrating primary keys

ID 15:51

And we’re not gonna lie. We have a bit of a slog ahead of us. In order to take this existing application and prepare it for synchronization we are finding that we now need to add a primary key to a table that didn’t have one, and we have to update 3 existing tables that have an integer primary key and turn them into UUID primary keys.

ID 16:08

And of course it would have been a lot better if we had known about these rules before we created our schema, so that we could just use UUIDs from the very beginning. But often things don’t work out so nicely, and so we have to be prepared to do the hard work to migrate an existing schema. Brandon

ID 16:21

And even though it’s a slog, we are going to do it right now in this episode. On Point-Free we don’t like to only show our users the pristine, perfect, happy path for building applications. Real world applications are filled with messiness, uncertainty, and mistakes that take time to correct. And by doing the hard work to fix our schema to make it friendlier towards synchronization, we hope to make it more possible for all of our viewers to update their existing apps rather than just starting from scratch.

ID 16:50

We’ve got a lot of work ahead of us, so let’s begin.

ID 16:54

While we do have a slog ahead of us, the docs luckily have some good information for us under the “Preparing an existing schema for synchronization” section. And in particular there is a section on “Convert Int primary keys to UUID”.

ID 17:10

In this section we will learn that sadly SQLite does not support changing the definition of a column after it has been created. So we cannot simple alter the table to change the integer ID to a UUID. But SQLite’s docs do give details on how to perform these kinds of alterations to a table in their “ Making Other Kinds Of Table Schema Changes ” section of the ALTER TABLE docs:

ID 17:55

And we will find an intimidating 12 step plan. Luckily we do not have to explicitly follow all of these steps. For example the first two steps turn off foreign key constraints and start a transaction, and the last two steps reverse those actions. Those steps are already taken care of by GRDB’s migration tools. And a few other steps in here are more nice-to-haves than required.

ID 18:40

For the purposes of this episode, we will really just focus in on 4 key steps, which are steps 4 through 7:

ID 18:45

First we create a whole new table with the version of the schema we ultimately want.

ID 18:58

Then we copy over the data from the new table to the old and convert the IDs in the process

ID 19:19

Then we drop the old table

ID 19:22

And finally we rename the new table to have the same name as the old.

ID 19:26

It’s a lot, but the docs for SQLiteData also give some sample code for us to take some inspiration from. So, let’s get started.

ID 19:39

From the docs we get a bit of a template to follow for this. For example, the first step: // Step 1: Create new table with updated schema try #sql(""" CREATE TABLE "new_remindersLists" ( "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()), -- all other columns from 'remindersLists' table ) STRICT """) .execute(db)

ID 21:08

However, even this step has some nuance. It is not enough to go to where we initially created the “remindersLists” table and copy it: try #sql( """ CREATE TABLE "remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "color" INTEGER NOT NULL DEFAULT \(raw: 0x4a99ef_ff), "title" TEXT NOT NULL DEFAULT '' ) STRICT """ ) .execute(db)

ID 21:26

This is because we have other migrations that altered this table definition. For example, we added a position column at one point in the development of our app: migrator.registerMigration("Add 'position' to 'remindersLists'") { db in try #sql( """ ALTER TABLE "remindersLists" ADD COLUMN "position" INTEGER NOT NULL DEFAULT 0 """ ) .execute(db) }

ID 21:50

And so we need to be sure to take that column into account too.

ID 21:54

The most surefire way to get the up-to-date schema of your tables it to access it directly in the database. So, let’s open up a connection to the database from terminal…

ID 22:09

ID 22:36

We can see there is info about all of our tables, even the virtual table for full-text search. And the sql column holds the full definition of the table as a SQL string.

ID 22:45

So, let’s select just that sql column and just for the remindersLists table: sqlite> select sql from sqlite_schema where tbl_name = 'remindersLists'; CREATE TABLE "remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "color" INTEGER NOT NULL DEFAULT 1251602431, "title" TEXT NOT NULL DEFAULT '' , "position" INTEGER NOT NULL DEFAULT 0) STRICT

ID 22:57

That right there is the most current version of the schema for our table. So let’s register a new migration at the very end of all of our existing migrations: migrator.registerMigration("Convert primary keys") { db in } In this migration we are going to carry out the multi-step process of creating a new table, copying data, dropping the old table, and renaming.

ID 23:33

And let’s start with a copy-and-paste of our up-to-date schema into our migration and give it a new table name: try #sql( """ CREATE TABLE "new_remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "color" INTEGER NOT NULL DEFAULT 1251602431, "title" TEXT NOT NULL DEFAULT '', "position" INTEGER NOT NULL DEFAULT 0 ) STRICT """ ) .execute(db)

ID 23:57

OK, that right there has created a brand new table with the exact schema as what is already used for remindersLists . But now we can update it so that it’s more appropriate for use in CloudKit synchronization.

ID 24:04

In particular we want to store the id of the table as a UUID, but SQLite does not actually offer a native UUID type. But that’s OK, we can just use

TEXT 24:12

Further, the

AUTOINCREMENT 24:17

But we do want it to be the case that one is allowed to insert rows into this table without specifying the ID and have the database choose the ID for us. So we can add a

DEFAULT 24:45

This is getting close, but there is more to do. Integer primary keys are automatically implied to be NOT NULL in SQLite, but non-integer IDs are not given that affordance. We have to provide that constraint explicitly: "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),

DEFAULT 25:02

Auto-incrementing integer primary keys have another special behavior imparted on them that other type of primary keys do not have. One is allowed to insert

NULL 25:18

To see this, let’s create a quick temporary table with an integer primary key: sqlite> create temporary table foos (id integer primary key);

NULL 25:38

Then insert a bunch of

NULL 25:46

And see that SQLite executes that with no errors, and 3 rows were inserted into the table: sqlite> select * from foos; ┌────┐ │ id │ ├────┤ │ 1 │ │ 2 │ │ 3 │ └────┘

NULL 25:56

However, if we did the same with a table that has a text primary key with a default value of UUID: sqlite> create temporary table bars (id text primary key not null default (uuid()));

NULL 26:13

Then inserting

NULL 26:22

Luckily there’s an additional clause we can add to the NOT NULL constraint called ON CONFLICT REPLACE : sqlite> drop table bars; sqlite> create temporary table bars (id text primary key not null on conflict replace default (uuid())); This makes it so that when the NOT NULL constraint failure is raised it will be suppressed and will instead just compute the ID from the

DEFAULT 26:38

We can see this by inserting some

NULL 26:42

That did not error, and 3 rows were indeed inserted: sqlite> select * from bars; ┌──────────────────────────────────────┐ │ id │ ├──────────────────────────────────────┤ │ a0d4b9b0-260c-4f8a-a2dc-260b0a66eeef │ │ fe25494b-eedc-4156-b17e-815214e19738 │ │ 6f74f5e4-0a4a-41e0-8f4f-820a79714bb7 │ └──────────────────────────────────────┘

NULL 26:48

So we will add that clause to the NOT NULL constraint of our ID, and it is very important that it come immediately after NOT NULL : "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()),

NULL 26:51

OK, that is all that is needed to create the new reminders list table.

NULL 26:59

Now we have to copy all of the data from the old table to this new table, and in the process of doing so we will need to convert the old integer-based IDs to UUIDs. And we will do so in the most naive way possible, which is to literally prefix the integer number with enough zeros to make it look like a UUID. And the docs give a template for how this can be done: // Step 2: Copy data from 'remindersLists' to 'new_remindersLists' and convert integer // IDs to UUIDs try #sql(""" INSERT INTO "new_remindersLists"

SELECT 27:32

The printf function allows us to turn an integer into a hex value padded to up to 12 digits. That means we can represent integer as large as: 0xffffffffffff

SELECT 27:44

…which is over 280 trillion. So, this conversion should be more than enough for any app out there.

SELECT 27:50

We can basically copy-and-paste this template and make just a few small changes to suite it for our needs: try #sql( """ INSERT INTO "new_remindersLists"

SELECT 28:18

And then the last two steps are the easiest: we drop the old table and rename the new table to the original table name: try #sql( """ DROP TABLE "remindersLists" """ ) .execute(db) try #sql( """ ALTER TABLE "new_remindersLists" RENAME TO "remindersLists" """ ) .execute(db)

OK 29:04

Let’s do another table just so that we can get the hang of it. Next we will migrate the reminders table. We start by getting the existing schema, which we can do from our database connection that we already have up: sqlite> select sql from sqlite_schema where tbl_name = 'reminders'; CREATE TABLE "reminders" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "dueDate" TEXT, "isFlagged" INTEGER NOT NULL DEFAULT 0, "notes" TEXT NOT NULL DEFAULT '', "priority" INTEGER, "remindersListID" INTEGER NOT NULL REFERENCES "remindersLists"("id") ON DELETE CASCADE, "title" TEXT NOT NULL DEFAULT '', "createdAt" TEXT, "updatedAt" TEXT, "status" INTEGER NOT NULL DEFAULT 0 ) STRICT

OK 29:29

We can immediately turn this into a new CREATE TABLE statement where we use the name new_reminders for the table, and we swap out the integer auto-incrementing primary key for a text primary key that is not null, has the ON CONFLICT REPLACE clause, and a default of a fresh UUID: try #sql( """ CREATE TABLE "new_reminders" ( "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()), "dueDate" TEXT, "isFlagged" INTEGER NOT NULL DEFAULT 0, "notes" TEXT NOT NULL DEFAULT '', "priority" INTEGER, "remindersListID" INTEGER NOT NULL REFERENCES "remindersLists"("id") ON DELETE CASCADE, "title" TEXT NOT NULL DEFAULT '', "createdAt" TEXT, "updatedAt" TEXT, "status" INTEGER NOT NULL DEFAULT 0 ) STRICT """ ) .execute(db)

OK 30:21

But, there is an additional step we have to take with this table that we didn’t have to take with the reminders list table. The reminders table has a foreign key that points to the reminders lists table: "remindersListID" INTEGER NOT NULL REFERENCES "remindersLists"("id") ON DELETE CASCADE,

OK 30:38

This too needs to be updated, but it’s a much simpler update of just flipping the

TEXT 30:53

OK, that is step one. The next step is to copy over all of the data from the existing reminders table to this new one. But this time we will need to perform two integer-to-UUID transformations: try #sql( """ INSERT INTO "new_reminders"

SELECT 32:01

And then finally we can perform the last two, much simpler steps, which is to drop the old table and rename the new table: try #sql( """ DROP TABLE "reminders" """ ) .execute(db) try #sql( """ ALTER TABLE "new_reminders" RENAME TO "reminders" """ ) .execute(db)

SELECT 32:18

OK, and that’s what it takes to migrate the reminders table.

SELECT 32:21

I think everyone is understanding how this is all going now so we can go a little faster with our reminder tables. I’m going to just copy-and-paste the migration for tags because it is identical to what we did for reminders lists and reminders: try #sql( """ CREATE TABLE "new_tags" ( "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()), "title" TEXT NOT NULL DEFAULT '' CHECK(instr("title", ' ') = 0) ) STRICT """ ) .execute(db) try #sql(""" INSERT INTO "new_tags"

SELECT 32:58

And lastly we have the reminderTags table, which is the intermediate join table that allows us to associate any number of reminders to any number of tags. This table is different from all the others in that we never added a primary key to it in the first place. We opted to not do that because the reminder ID and tag ID fully define the join, and there was no need for an extra ID.

SELECT 33:26

Well, now CloudKit synchronization demands we have a primary key, and so we must add one. So, let’s get the schema for the reminderTags table and define a new CREATE TABLE for the new schema with a primary key and converting the foreign keys to be text: try #sql( """ CREATE TABLE "new_reminderTags" ( "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()), "reminderID" TEXT NOT NULL REFERENCES "reminders"("id") ON DELETE CASCADE, "tagID" TEXT NOT NULL REFERENCES "tags"("id") ON DELETE CASCADE ) STRICT """ ) .execute(db)

SELECT 34:05

And then we need to copy over the data from the old table to the new one by transforming the IDs to UUIDs: try #sql(""" INSERT INTO "new_reminderTags" ( "reminderID", "tagID" )

SELECT 34:14

Note that we are leaving off the id column in this insert, and so SQLite will use the default for that column, which is a fresh UUID computed in SQLite.

SELECT 34:35

And then we can finish this off by dropping the old table and renaming the new table: try #sql( """ DROP TABLE "reminderTags" """ ) .execute(db) try #sql( """ ALTER TABLE "new_reminderTags" RENAME TO "reminderTags" """ ) .execute(db)

SELECT 34:45

And, well, that is all it takes!

SELECT 34:48

Of course I’m being a little facetious because this is a lot of work. It took us over 130 lines of code to migrate just 4 tables, and so this would become quite large if we had dozens of tables.

SELECT 35:10

But, we still feel this is a relatively small cost to pay to get the benefits we are about to unlock with CloudKit synchronization. And we’ll get to that soon, but let’s first see that our migration runs successfully. We can run the app, and all of the reminders that were present earlier are still there. In particular we are no longer getting that purple runtime warning that we previously saw.

SELECT 35:57

And we can open a connection to the database to see that indeed all IDs have been converted to UUIDs. For example, in the reminders lists: sqlite> select * from remindersLists; ┌──────────────────────────────────────┬────────────┬──────────┬──────────┐ │ id │ color │ title │ position │ ├──────────────────────────────────────┼────────────┼──────────┼──────────┤ │ 00000000-0000-0000-0000-000000000001 │ 1251602431 │ Personal │ 0 │ │ 00000000-0000-0000-0000-000000000002 │ 4018031359 │ Family │ 1 │ │ 00000000-0000-0000-0000-000000000003 │ 2128628479 │ Business │ 2 │ └──────────────────────────────────────┴────────────┴──────────┴──────────┘

SELECT 36:16

And for the reminderTags table we will see that it has a fresh primary key, and all foreign keys have been converted: sqlite> select * from reminderTags; ┌──────────────────────────────────────┬──────────────────────────────────────┬──────────────────────────────────────┐ │ id │ reminderID │ tagID │ ├──────────────────────────────────────┼──────────────────────────────────────┼──────────────────────────────────────┤ │ 65b128e1-0e42-4970-9ad4-d9b81e11ecfd │ 00000000-0000-0000-0000-000000000001 │ 00000000-0000-0000-0000-000000000001 │ │ a6cf9855-0c6b-49b4-9cc9-a57268402d92 │ 00000000-0000-0000-0000-000000000002 │ 00000000-0000-0000-0000-000000000001 │ │ 95cf88aa-9b48-45a5-bc1f-f48412cd70b6 │ 00000000-0000-0000-0000-000000000004 │ 00000000-0000-0000-0000-000000000001 │ │ df7e297c-70a6-4126-95f0-24a6f2e84c1b │ 00000000-0000-0000-0000-000000000004 │ 00000000-0000-0000-0000-000000000002 │ │ 4e9600d1-c316-4a26-ab70-9e2bd6694c64 │ 00000000-0000-0000-0000-000000000005 │ 00000000-0000-0000-0000-000000000002 │ │ 8c38d246-05c6-42a3-b28c-46c965c9db1d │ 00000000-0000-0000-0000-000000000002 │ 00000000-0000-0000-0000-000000000003 │ │ ed1aac07-dc12-44b5-9fde-e054d27e0f4d │ 00000000-0000-0000-0000-000000000006 │ 00000000-0000-0000-0000-000000000003 │ │ 896c674b-35a7-4cbf-ba5e-0004e7e1a7ae │ 00000000-0000-0000-0000-000000000007 │ 00000000-0000-0000-0000-000000000003 │ │ 7b2dcd10-d4c4-44c3-8baa-96dcc15e0ca1 │ 00000000-0000-0000-0000-000000000008 │ 00000000-0000-0000-0000-000000000003 │ └──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘

SELECT 36:37

And note that we did not need to update any of our triggers because they are all temporary and so created the moment the connection to the database is created. That’s a huge win for us because we would technically have to create all of our triggers after migrating these tables since the act of dropping a table also drops its associated triggers. A better way

SELECT 37:13

And I think it’s worth mentioning that this involved process we are performing here is one of the many reasons we do not offer automatic migrations like SwiftData does. It just isn’t clear to me at all how an automatic migration system could possibly infer what we are trying to do here and perform this multi-step process of creating a scratch table, copying data over, deleting and renaming. It’s a very precise set of steps and care should be taken, especially since this is your user’s data and you wouldn’t want to accidentally lose it all.

SELECT 37:47

And further, everything we are seeing here also highlights why we prefer SQL strings for migrations over a more “Swifty” DSL for writing table definitions. Since we fully embraced SQL strings we had the ability to simply copy over the existing schema extracted from the database, and then make the tweaks to it necessary to change integer IDs to UUIDs. If we had written our table definition using a Swifty DSL we would have had to write all of that from scratch again using the DSL, hoping we did faithfully recreate the current version of the schema, or we would have had to abandon the DSL and write a SQL string. But, then at that point, is the DSL really pulling its weight if we have to abandon it for complex migrations like this? Stephen

SELECT 38:33

And none of this is to say that someday we won’t explore and offer some kind of automatic migration tool. But we just want to make it crystal clear why it is so complex and very difficult to get right.

SELECT 38:43

OK, so our tables are now migrated and ready to be used in a CloudKit syncing environment, but before jumping to that, we just want to show that the library actually provides a tool that helps automate everything we just did. You may be upset with us that we put you through that tedious migration, but we still feel that understanding all of the idiosyncrasies is extremely important. There are value lessons to be learned, and being familiar with those details only makes it more possible for you to appreciate what we are about to show off now.

SELECT 39:11

Let’s take a look.

SELECT 39:15

First let’s travel back to a time before we performed our UUID migration. I will comment out the migration that you just wrote, since I haven’t yet run it locally, and we can run the app in the simulator and see the purple warning from before.

SELECT 39:45

And now we are left to perform the difficult work of migrating each table to use UUIDs for its primary key, which for each table was a laborious four step process.

SELECT 39:56

Well, what if we told you that the library provides a tool that can perform this migration for you, all in just a few lines of code? We start just as before by defining a new migration: migrator.registerMigration("Convert primary keys") { db in }

SELECT 40:02

And in this migration we can make use of a powerful tool defined in SQLiteData called migratePrimaryKeys , and it’s defined as a static on the SyncEngine type: migrator.registerMigration("Convert primary keys") { db in try SyncEngine.migratePrimaryKeys( <#Database#>, tables: <#repeat each T#>, uuidFunction: <#(any ScalarDatabaseFunction<(), UUID>)?#> ) }

SELECT 40:12

It takes three arguments. The first is the database handle to execute with, and so we can pass along the db from the migration trailing closure: try SyncEngine.migratePrimaryKeys( db, tables: <#repeat each T#>, uuidFunction: <#(any ScalarDatabaseFunction<(), UUID>)?#> )

SELECT 40:21

The second is a variadic list of primary keyed tables. We just need to provide the types of all the tables that we want to migrate: try SyncEngine.migratePrimaryKeys( db, tables: RemindersList.self, Reminder.self, Tag.self, ReminderTag.self, uuidFunction: <#(any ScalarDatabaseFunction<(), UUID>)?#> )

SELECT 40:36

And the third argument is a UUID database function, and is optional. If we do not provide this argument the tool will just use the default uuid() function that comes with SQLite. However, some people out there may want to provide their own UUID functions, such as if they want one that can be controlled for easier testing, or if they want to provide niche UUID formats, such as UUIDv7 which encodes a timestamp into the value so that IDs are sortable.

SELECT 41:00

We don’t need any of that complex functionality right now and so we will just leave it off: try SyncEngine.migratePrimaryKeys( db, tables: RemindersList.self, Reminder.self, Tag.self, ReminderTag.self )

SELECT 41:05

And amazingly, that is all it takes. This will do all of the hard work to analyze each table to find its primary key and any foreign keys, and then perform the 4 step process of creating a new schema with the columns updated, copying data from the old table to the new, dropping the old table, and renaming the new table. It even takes special care to recreate any indices and triggers that may have been removed when dropping tables. In essence, this tools handle the complicated 12 step program that is outline in the SQLite docs.

SELECT 41:37

We can run the app and see that all of our data is still intact, even though behind the scenes all of its IDs have been updated. We can also connect to the database and browse the data to see the updated IDs. You will notice that the IDs are not as simple as they were when we migrated our tables by hand a moment ago. And that’s because in the final version of the tool we take care to generate unique IDs for each table by hashing the table name and integer ID. That makes it so that you don’t have multiple tables with a UUID of 00000000-0000-0000-0000-000000000001, which honestly is not ideal since usually we assume that UUIDs are globally unique.

SELECT 42:41

And if we look at the logs we will see everything that little function did: 0.000s BEGIN IMMEDIATE TRANSACTION 0.000s SELECT "sqlite_schema"."sql" FROM "sqlite_schema" WHERE ((("sqlite_schema"."tbl_name" IN ('remindersLists', 'reminders', 'tags', 'reminderTags')) AND ("sqlite_schema"."type" IN ('index', 'trigger'))) AND ("sqlite_schema"."sql" IS NOT NULL)) 0.000s SELECT "sqlite_schema"."sql" FROM "sqlite_schema" WHERE (("sqlite_schema"."type" = 'table') AND ("sqlite_schema"."tbl_name" = 'remindersLists')) 0.000s PRAGMA table_info='remindersLists' 0.000s SELECT "remindersListsTableInfo"."cid", "remindersListsTableInfo"."name", "remindersListsTableInfo"."type", "remindersListsTableInfo"."notnull", "remindersListsTableInfo"."dflt_value", "remindersListsTableInfo"."pk" FROM pragma_table_info('remindersLists') AS "remindersListsTableInfo" 0.000s PRAGMA foreign_key_list='remindersLists' 0.000s SELECT "remindersListsForeignKeys"."id", "remindersListsForeignKeys"."seq", "remindersListsForeignKeys"."table", "remindersListsForeignKeys"."from", "remindersListsForeignKeys"."to", "remindersListsForeignKeys"."on_update", "remindersListsForeignKeys"."on_delete", "remindersListsForeignKeys"."match" FROM pragma_foreign_key_list('remindersLists') AS "remindersListsForeignKeys" 0.000s CREATE TABLE "new_remindersLists" ( "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT ("uuid"()), "color" INTEGER NOT NULL DEFAULT 1251602431, "title" TEXT NOT NULL DEFAULT '' , "position" INTEGER NOT NULL DEFAULT 0) STRICT 0.000s INSERT INTO "new_remindersLists" SELECT '00000000-0000-0000-0000-' || printf('%012x', "id"), "color", "title", "position" FROM "remindersLists" 0.000s DROP TABLE "remindersLists" 0.000s ALTER TABLE "new_remindersLists" RENAME TO "remindersLists" 0.000s SELECT "sqlite_schema"."sql" FROM "sqlite_schema" WHERE (("sqlite_schema"."type" = 'table') AND ("sqlite_schema"."tbl_name" = 'reminders')) 0.000s PRAGMA table_info='reminders' 0.000s SELECT "remindersTableInfo"."cid", "remindersTableInfo"."name", "remindersTableInfo"."type", "remindersTableInfo"."notnull", "remindersTableInfo"."dflt_value", "remindersTableInfo"."pk" FROM pragma_table_info('reminders') AS "remindersTableInfo" 0.000s PRAGMA foreign_key_list='reminders' 0.000s SELECT "remindersForeignKeys"."id", "remindersForeignKeys"."seq", "remindersForeignKeys"."table", "remindersForeignKeys"."from", "remindersForeignKeys"."to", "remindersForeignKeys"."on_update", "remindersForeignKeys"."on_delete", "remindersForeignKeys"."match" FROM pragma_foreign_key_list('reminders') AS "remindersForeignKeys" 0.000s CREATE TABLE "new_reminders" ( "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT ("uuid"()), "dueDate" TEXT, "isFlagged" INTEGER NOT NULL DEFAULT 0, "notes" TEXT NOT NULL DEFAULT '', "priority" INTEGER, "remindersListID" TEXT NOT NULL REFERENCES "remindersLists"("id") ON DELETE CASCADE, "title" TEXT NOT NULL DEFAULT '' , "createdAt" TEXT, "updatedAt" TEXT, "status" INTEGER NOT NULL DEFAULT 0) STRICT 0.000s INSERT INTO "new_reminders" SELECT '00000000-0000-0000-0000-' || printf('%012x', "id"), "dueDate", "isFlagged", "notes", "priority", '00000000-0000-0000-0000-' || printf('%012x', "remindersListID"), "title", "createdAt", "updatedAt", "status" FROM "reminders" 0.000s DROP TABLE "reminders" 0.000s ALTER TABLE "new_reminders" RENAME TO "reminders" 0.000s SELECT "sqlite_schema"."sql" FROM "sqlite_schema" WHERE (("sqlite_schema"."type" = 'table') AND ("sqlite_schema"."tbl_name" = 'tags')) 0.000s PRAGMA table_info='tags' 0.000s SELECT "tagsTableInfo"."cid", "tagsTableInfo"."name", "tagsTableInfo"."type", "tagsTableInfo"."notnull", "tagsTableInfo"."dflt_value", "tagsTableInfo"."pk" FROM pragma_table_info('tags') AS "tagsTableInfo" 0.000s PRAGMA foreign_key_list='tags' 0.000s SELECT "tagsForeignKeys"."id", "tagsForeignKeys"."seq", "tagsForeignKeys"."table", "tagsForeignKeys"."from", "tagsForeignKeys"."to", "tagsForeignKeys"."on_update", "tagsForeignKeys"."on_delete", "tagsForeignKeys"."match" FROM pragma_foreign_key_list('tags') AS "tagsForeignKeys" 0.000s CREATE TABLE "new_tags" ( "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT ("uuid"()), "title" TEXT NOT NULL DEFAULT '' CHECK(instr("title", ' ') = 0) ) STRICT 0.000s INSERT INTO "new_tags" SELECT '00000000-0000-0000-0000-' || printf('%012x', "id"), "title" FROM "tags" 0.000s DROP TABLE "tags" 0.001s ALTER TABLE "new_tags" RENAME TO "tags" 0.000s SELECT "sqlite_schema"."sql" FROM "sqlite_schema" WHERE (("sqlite_schema"."type" = 'table') AND ("sqlite_schema"."tbl_name" = 'reminderTags')) 0.000s PRAGMA table_info='reminderTags' 0.000s SELECT "reminderTagsTableInfo"."cid", "reminderTagsTableInfo"."name", "reminderTagsTableInfo"."type", "reminderTagsTableInfo"."notnull", "reminderTagsTableInfo"."dflt_value", "reminderTagsTableInfo"."pk" FROM pragma_table_info('reminderTags') AS "reminderTagsTableInfo" 0.000s PRAGMA foreign_key_list='reminderTags' 0.000s SELECT "reminderTagsForeignKeys"."id", "reminderTagsForeignKeys"."seq", "reminderTagsForeignKeys"."table", "reminderTagsForeignKeys"."from", "reminderTagsForeignKeys"."to", "reminderTagsForeignKeys"."on_update", "reminderTagsForeignKeys"."on_delete", "reminderTagsForeignKeys"."match" FROM pragma_foreign_key_list('reminderTags') AS "reminderTagsForeignKeys" 0.000s CREATE TABLE "new_reminderTags" ( "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT ("uuid"()), "reminderID" TEXT NOT NULL REFERENCES "reminders"("id") ON DELETE CASCADE, "tagID" TEXT NOT NULL REFERENCES "tags"("id") ON DELETE CASCADE ) STRICT 0.001s INSERT INTO "new_reminderTags" SELECT NULL, '00000000-0000-0000-0000-' || printf('%012x', "reminderID"), '00000000-0000-0000-0000-' || printf('%012x', "tagID") FROM "reminderTags" 0.000s DROP TABLE "reminderTags" 0.000s ALTER TABLE "new_reminderTags" RENAME TO "reminderTags" 0.000s INSERT INTO grdb_migrations (identifier) VALUES ('Convert primary keys') 0.000s PRAGMA foreign_key_check 0.000s COMMIT TRANSACTION After querying the sqlite_schema and gather pragma table info and foreign keys for each table, it sets about recreating the schemas from scratch, but with the integer IDs upgraded to UUID, it copies the data over to the new tables, and then drops and renames the tables. All of this information can be inferred from the information that SQLite gives us, but it’s also not a 100% full proof SQL table definition parser. There is a very slight chance that a sufficiently pathological schema out there could break some assumptions we made in building this tool, and if we ever detect that something doesn’t look right, we throw an error which will completely abort the migration. That has the benefit of making sure we never accidentally mess up your data, but if that does happen to your schema it means you are in charge of performing this migration yourself and cannot use your tool. But rest assured that our dozens of tests on this tool make us confident that it works in nearly all schemas. Next time: The Sync Engine

SELECT 44:19

OK, we have now seen the real world troubles associated with trying to take an existing application and preparing it to be synchronized to CloudKit. In short, it takes some work. If your app is using simple integer primary keys then you have to perform a multi-step process to update all of those IDs to be globally unique identifiers, such as a UUID. And if you have any tables that don’t have a primary key at all, which can be common with join tables, then you have to migrate them to add a primary key.

SELECT 44:44

It’s honestly a lot of work to do these kinds of migrations, and a bit scary since your user’s data is at risk. One small typo and you run the risk of corrupting their data. And so that’s why we also designed a tool that specifically aids in this kind of migration. You can simply provide a list of your tables that you want to migrate, and the tool takes care of analyzing the current schema to perform the multi-step migration process of changing primary keys to UUIDs and adding primary keys to tables that don’t have them. It even takes care to restore any indices or triggers after the migration is done. Brandon

SELECT 45:15

But we’ve done all of this work to prepare our app for synchronization and we haven’t actually gotten to sync our data yet! But that’s ok, because on Point-Free we don’t shy away from showing our views the gritty, dark underbelly of app development. These are the kinds of problems we are faced every day, and we don’t all have the benefit of constantly starting with beautiful greenfield projects.

SELECT 45:34

And with our bit of prep work done, we are now ready to actually install a sync engine into our application and unleash the true powers of SQLiteData.

SELECT 45:46

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 0340-sync-pt1 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 .