Recommended way to update a Single-Row Table with migrations

[DamienPetrilli]

Hello,

I am trying to move away from NSUserDefaults and was giving a try to Single-Row Tables.

Everything is nice until you realize that you will need to add more settings as the App features involve. Then you are stuck with the fact that :

1. Each column is mandatory (.notNull())
2. When you mutate the table to add a new column, you have to perform an initial insert (which is not recommended in the guide) otherwise the migration is going to crash because of the missing value.

What would be the recommended way to add new settings then?

Cheers

[groue]

Hi @DamienPetrilli,

You are correct. The guide contains an inconsistency. And I'm sorry.

I met the same issue, just from a different perspective, while wondering how default settings should be handled. By "default" I mean the value of a setting until the user has made an explicit choice.

In the standard UserDefaults api, a setting has its default value until the user has explicitly modified it:

// Default value is true
defaults.register(defaults: ["mySetting": true])

// If there is no stored value, return default value
defaults.bool(forKey: "mySetting") // true

// User makes an explicit choice: store a value
defaults.set(false, forKey: "mySetting")

// Returns stored value
defaults.bool(forKey: "mySetting") // false

// Destroy stored value
defaults.removeObject(forKey: "mySetting")

// Return default value again
defaults.bool(forKey: "mySetting") // true

The UserDefaults api has quite a few advantages:

• Adding a new key has no impact on previously defined keys.
• If the user has not expressed any opinion on a particular setting, the app can make the default value change over releases, and this can be handy sometimes.

This is not at all what the single-row table guide describes 😅 In the guide, values always come from the database (with NOT NULL columns). There is no such thing as "use the default value if none is persisted".

Such a setup requires a different SQLite storage, where all columns are nullable (NULL is the sentinel value for "use the default"). Since the rest of the app does not want to see optional values, we can wrap and hide the persisted record with optional properties in another record that exposes non-optional values:

/// The persisted record, with nullable columns
/// and optional properties.
fileprivate struct AppConfigurationStorage: Codable {
    // Support for the single row guarantee
    private var id = 1

    var mySetting: Bool?
}

/// The record exposed to the rest of the application, with
/// non-optional properties.
struct AppConfiguration {
    private var storage: AppConfigurationStorage
    
    var mySetting: Bool {
        get { storage.mySetting ?? true /* the "registered default" */ }
        set { storage.mySetting = newValue }
    }

    // New feature: ability to restore a setting to its default value.
    mutating func resetMySetting() {
        storage.mySetting = nil
    }
}

// Database Access
extension AppConfigurationStorage: FetchableRecord, PersistableRecord {
    static let databaseTableName = "appConfiguration"
}

extension AppConfiguration: FetchableRecord, PersistableRecord {
    /// The default configuration
    private static let `default` = AppConfiguration(storage: AppConfigurationStorage())

    init(row: Row) throws {
        self.storage = try MyDefaultsRecord(row: row)
    }

    func encode(to container: inout PersistenceContainer) {
        storage.encode(to: container)
    }
    
    func willUpdate(_ db: Database, columns: Set<String>) throws {
        // Insert the default configuration (nils only) if it does not exist yet.
        if try !exists(db) {
            try AppConfiguration.default.insert(db)
        }
    }
    
    /// Returns the persisted configuration, or the default one if the
    /// database table is empty.
    static func fetch(_ db: Database) throws -> AppConfiguration {
        try fetchOne(db) ?? .default
    }
}

The problem you correctly spotted with migrations is resolved, because we just add a NULL column when a new setting is added.

I think I did not use such a setup in the guide because it looks much more complex. I have been too shy.

I understand that this is completely different from your current setup, and that... the guide has put you in a corner 😬😭 Yeah, the guide should quite probably have a section dedicated to UserDefaults-like situations.

If this different setup looks like a better fit for your app, I can help write the migration that transforms the current table in a new one with NULLABLE values. After all, I wrote the misleading guide in the first place!

[DamienPetrilli]

Hello!

Thanks for your reply. No need to apologize it's fine 🙂

I was just wondering if I had missed anything. I will switched to your proposed system, it seems indeed closer to User Defaults.

If this different setup looks like a better fit for your app, I can help write the migration that transforms the current table in a new one with NULLABLE values. After all, I wrote the misleading guide in the first place!

Thanks for your offer, however it won't be necessary the App isn't in production yet 👍🏻. I will just add a migration to drop and recreate the table for my existing testing devices.

You do a lot of work for all of us for free already, I would be ashamed to ask for more! You don't have to feel responsible for anything I did 😉

[groue]

Thanks Damien!

I'll think about amending the guide, though.

[yqiang]

Hi - As luck would have it I've been thinking about doing the same thing in my app. I was curious if either of you had considered following the single record pattern but storing the user settings as a JSON object, and what the pros/cons would be to that approach.

Thanks again for the amazing work on GRDB!

[groue]

Hi @yqiang. Storing values in a JSON object is totally possible. I my exploration of the topic, I wanted to be able to support foreign keys, which require dedicated columns.