Single Row Tables + Writing Support

[mhayes853]

It's nice to see that these tools have been standardized, and I even prefer to use SQLite as my go-to persistence strategy in many projects.

At least for me, single-row tables have been a suitable alternative to UserDefaults, as the data is integrated with the app's primary persistence mechanism instead of being in some separate key value store. It's also nice to store related properties in a single value type, as opposed to having separate UserDefaults values for each property or relying on Codable UserDefaults extensions that lack the migration support that SQLite and GRDB provide. GRDB even has a dedicated article specifically for single row tables which is worth looking at.

In addition to the tools now standardized by this library, I've also been using a shared key helper I created specifically for single row tables. Having a separate SharedKey conformance to the one the library provides is preferable here because querying a single-row table is trivial, and only having a single row in the table means that it's easy to know what row to update when save is called on the SharedKey thus making writing possible.

This latter idea could possibly even be extracted to some kind of RowKey that works on a single row of any kind of table given a primary key as input to the SharedKey. Since the SharedKey would only work with a primary key to a row instead of an arbitrary query, it would be possible to add write support as yet again we would know which row to update when save is called on the SharedKey.

Eg.

struct RowKey<Record: FetchableRecord & PersistableRecord, PrimaryKey: DatabaseValueConvertible>: SharedKey {
  let primaryKey: PrimaryKey
  let database: any DatabaseWriter

  // Other SharedKey Requirements...

  func load(context: LoadContext<Record>, continuation: LoadContinuation<Record>) {
    continuation.resume(with: Result { try database.read { try Record.find($0, key: primaryKey) } })
  }

  func save(_ value: Record, context: SaveContext, continuation: SaveContinuation) {
    continuation.resume(with: Result { try database.write { db in try value.save(db) } })
  }
}

I haven't tried using this idea for arbitrary rows of non-single row tables, but I've certainly found that the overall approach works well when it comes to single row tables at least.

In any case, it would be cool if some kind of writing support were possible, and I think it makes sense when focusing on the single row level at least.

[rcarver]

Nice! I've been planning to implement exactly this. My app uses SQLite as the document format, so there's not just a single database. I want something as simple as appStorage / UserDefaults but it needs to be persisted for each document.

I also just opened #18 as a more general purpose approach to any mutable data.