Overview

SQLiteChangesetSync is a Swift package that allows for the offline-first synchronization of SQLite databases across multiple devices with intermittent network connectivity. It works by leveraging the SQLite Session Extension to capture changesets as they are "committed" to a local database. Similar to git, these changesets can then be "pushed" to a remote repository such as a CloudKit database. Other devices can then "fetch" these changesets and apply them their local database to keep data consistent across different databases.

Note: This package is an experimental concept that I've developed and wanted to share. While it functions well on my machine, your experience may vary! 😄 I'm very interested in receiving feedback about the idea and its implementation. Insights from anyone who tries to use it would be incredibly valuable and greatly appreciated.

Advantages of SQLiteChangesetSync Approach

Offline first: Changes to the database are captured and stored locally without requiring any network connectivity.
Simple Requirements: Only requirement is that SQLite has been compiled with the SQLite Session Extension. This extension is included by default in the versions of SQLite distributed with MacOS and iOS.
Simple Integration: Works with existing SQLite databases, and requires only minimal modifications to the existing database structure or application code.
Efficient Data Synchronization: The use of changesets for recording database modifications allows for efficient data transfer when syncing, as only the changes are transmitted rather than the entire database.
Flexible Data Synchronization: An example of using a CloudKit database to sync changesets is included, but the package’s design allows for easy adaptation to different backend services (cloud services, other databases, etc.) for syncing. Like git commits, changesets are idempotent and have unique UUIDs making syncing them simple.
Flexible Sync Timing: Syncing of changeset data is independent of syncing of application data. Like git, pushing or fetching changeset data to or from the backend service does not affect the application data. Pushing and fetching could be scheduled with a timer or in response to notification events that new data is available to fetch. The application can later choose to apply the new changesets when desired.
Granular Change Tracking: Similar to version control systems, this method offers detailed tracking of each modification, enabling precise control and understanding of database evolution over time.
Flexible Conflict Resolution: The git-like functionality (merge, pull, etc.) provides a structured way to handle conflicts that may arise when different instances of the database are modified independently. Future Development: Allow applications to define their own conflict resolution logic with full access to the database at the state of conflict.

Running the Demo

Screenshot

A demo iOS app SQLiteChangesetSyncDemo is included in the package. To enable CloudKit support, edit the CloudKitConfig settings in SQLiteChangesetSyncDemoApp.swift. The app UI is basic, so please watch the app log to see the results of each operation. Setting the environment variable SQL_TRACE will log all executed SQL statements. There are two identical targets in the project SQLiteChangesetSyncDemoApp and SQLiteChangesetSyncDemoAppCopy. It is possible to run each target on a separate simulator and experiment with syncing data between the two instances.

The demo depends on the GRDB and GRDBQuery packages.

Operations

Push: Transfers unpushed changesets to a remote repository.
Fetch: Retrieves new changesets from a remote source.
Pull: Applies changesets saved in the local database to the application data synchronizing with the latest state. Note: Unlike git, the pull command does not run a fetch first. To sync new data, run fetch before pull.
Merge: Combines changes from different branches into the current branch. After the merge, to apply the merged branch to your application data, run pull.

Implementing in your own Project

Requirements

@available(iOS 14.0, *) for ChangeSetRepository
@available(iOS 15.0, *) for CloudKitManager
SQLite that has been compiled with the SQLite Session Extension. Should be the default in MacOS and iOS. To check your version, run PRAGMA compile_options and confirm that both ENABLE_SESSION and ENABLE_PREUPDATE_HOOK are included.

Package Dependencies

GRDB for accessing the SQLite database.

Integration

The best place to understand how to integrate the package is by reviewing the included demo app SQLiteChangesetSyncDemo. To get started, add the following to your APP init:

SQLiteChangesetSyncDemoApp.swift

self.changesetRepository = try ChangesetRepository(dbWriter)
self.cloudKitManager = CloudKitManager(dbWriter, config: SQLiteChangesetSyncDemo.getCloudKitManagerConfig())
self.playerRepository = try PlayerRepository(changesetRepository)

and then pass them to your views as environment objects like this:

.environment(\.changesetRepository, changesetRepository)
.environment(\.cloudKitManager, cloudKitManager)
.environment(\.playerRepository, playerRepository)

finally, modify all database.write() calls to use changesetRepository.commit() instead:

PlayerRepository.swift

return try changesetRepository.commit { db in
    try player.inserted(db)
}

Data structs

`struct Changeset`

Represents a changeset within the ChangesetRepository and backed by a changeset table in the database.

Properties:
- uuid: String - A unique identifier for the changeset.
- parent_uuid: String? - The UUID of the parent changeset, if any.
- parent_changeset: Data? - Binary changeset data.
- merge_uuid: String? - The UUID of the merge parent for merge commits.
- merge_changeset: Data? - Binary changeset data for merge commits.
- pushed: Bool - A flag indicating whether the changeset has been pushed to a remote repository.
- meta: String - A JSON string containing metadata about the changeset.
Note: Changeset objects are created internally by ChangesetRepository. Except for the pushed and meta properties, all other properties should be considered as read-only and not modified.

`struct Head`

Represents the current "checked-out" head of the ChangesetRepository.

Properties:
- uuid: String? - The UUID of the current head changeset. nil indicates the root head.
Note on Database Structure: The Head object is used internally by ChangesetRepository and should not be modified. The head table in the database backing this struct is designed to contain only a single row. This row holds the UUID value of the current head of the repository, representing the latest state of the synchronized data. It's crucial to maintain this table with only one row to ensure the integrity and correct tracking of the repository's head state.

`ChangesetRepository`

`init(_ dbWriter: some GRDB.DatabaseWriter) throws`

Initializes a new instance of ChangesetRepository with the provided database writer.

Parameters:
- dbWriter: A DatabaseWriter instance (such as DatabaseQueue or DatabasePool) to be used for all database operations within the repository.
Throws: An error if the database migration fails.

`func reset() throws`

Resets the changeset repository. This function clears all changesets from the database and sets the head UUID to nil.

Throws: An error if the reset operation fails.

`func commit<T>(meta: String = "{}", _ updates: (Database) throws -> T) throws -> T`

Commits a set of updates to the database as a new changeset. The function captures changes made during the update block and stores them as a changeset in the database.

Parameters:
- meta: A JSON string containing metadata for the changeset. Defaults to an empty JSON object.
- updates: A closure that performs the desired updates on the database.
Returns: The result of the updates closure.
Throws: An error if the commit operation fails.

`func pull() throws -> Changeset?`

Applies all the child changesets from the current HEAD in the local repository in the order they were created.

Returns: The final Changeset applied, nil if no changesets were applied.
Throws: An error if the pull operation fails.

`func mergeAll() throws`

Merges all outstanding branches in the repository. This function finds pairs of leaf nodes (branches) and merges them, continuing until no mergeable pairs are left.

Throws: An error if the merge operation fails.

CloudKitManager

`init(_ dbWriter: some GRDB.DatabaseWriter, config: CloudKitManagerConfig)`

Initializes a new instance of CloudKitManager for managing changesets in a CloudKit environment.

Parameters:
- dbWriter: A DatabaseWriter instance used for all database operations.
- config: Configuration settings for CloudKit, including the database, zone, and subscription ID.

`func setup() async throws`

Asynchronously sets up the CloudKit environment. This includes lo

SQLiteChangesetSync

Install / Use

README