What is the best place to auto-update fields in models? The example of automatic timestamps.

[abournier]

Hello,

Supposing I have a model like this:

struct Drink: Identifiable, Codable, TableRecord, FetchableRecord, MutablePersistableRecord {

	var id: String? // UUID
	var name: String

	var createdAt:Date?
	var updatedAt:Date?
}

GRDB has now callbacks, so I can populate 1 field such id with its UUID in willInsert():

	mutating func willInsert(_ db: Database) throws {
             if id == nil {
			id = UUID().uuidString.lowercased()
		}
	}

I checked GRDB code and willInsert() and didInsert() are the uniq callback to be "mutating".
Now, if I want to auto-update the field updatedAt, I can't do this in callbacks as other callback methods are non mutating. updatedAt should be updated after any insert/update.

I know I can create a custom method to update it before the save, and call it "manually", for example in:

func saveDrink(_ drink: inout Drink) throws {		
    // special stuff
    drink.updatedAt = Date()
    // save now
    try dbWriter.write { db in
        try drink.save(db)
    }
}

I don't like it at all.

Is-there a way to update this kind of field in aroundSave() or willSave() ? If I used these methods with "mutating" keyword, the compiler warns about it. I can remove the warning with the keyword "private". I'm quite sure it's not a good idea too...

Thanks for any suggestion.

[groue]

Hello @abournier,

Thanks for having checked the library possibilities before opening an issue 😊 You are totally right, the PersistableRecord protocol only allow mutation on inserts, but not on updates or deletions. There's no escape in the current state of the library.

So you're correct when you modify the updatedAt property before saving 👍

The rest of the answer explores the topic, but you won't find any better option there.

---

First, you have another option which is an SQL trigger, but I really not recommend using it:

Trigger (not advised)

You can instruct SQLite to touch the updateAt column of every updates in the drink table:

// Do it only once!
try db.execute(sql: """
    CREATE TRIGGER UpdateDrinkTimestamp UPDATE ON drink BEGIN
        UPDATE drink SET updatedAt = CURRENT_TIMESTAMP WHERE id=old.id;
    END;
    """)

What's cool with a trigger is that you can't forget to update the timestamp 👍 Even batch updates are covered.

What's NOT COOL with the trigger is that you can't prevent the timestamp from being updated (not today, and not in the future when you eventually discover that you need this). 😅

With a trigger, you'd need to call saveAndFetch instead of save, so that the drink Swift struct gets updated with the new timestamp:

func saveDrink(_ drink: inout Drink) throws {
    try dbWriter.write { db in
        drink = drink.saveAndFetch(db)
    }
}

Also, you can make Drink a class instead of a struct. A Drink class could update its updatedAt timestamp in willSave - but you'd lose the immutable safety of Swift structs, so this is also an option I do not recommend.

I don't like it at all.

You didn't tell why, so I will guess. We're talking about this one single extra line drink.updatedAt = Date(), aren't we? Maybe you've used other ORMs in the past, and you're used to built-in support for timestamps? Maybe you worry that it is easy to forget to update the timestamps in other saving methods? I don't know.

What I can tell is that I'm much aware of the feature. I've used ActiveRecord the Ruby ORM for a long time. It ships with built-in support for timestamps.

So... the feature was not ported to GRDB. The main reason is that users of this feature eventually want to perform updates without touching the timestamps. That's how people are. They think they always want to update timestamps, and probably they do, until they discover they don't, because things have changed.

So ActiveRecord supports explicit disabling of timestamp updates:

# Explicit
drink.save(touch: false)

Then other users discover that they want to disable timestamp updates at a distance. So ActiveRecord supports implicit disabling of timestamp updates:

# Implicit (with a 😳global😱)
Drink.record_timestamps = false
drink.a_method_that_calls_save_eventually
Drink.record_timestamps = true

Needless to say, a global is not quite concurrency-safe in a multi-threaded application.

Oh, and I did not mention developers who need to inject an altered clock in, say, tests.

It's a never-ending game between convenience and exceptions. Meh.

All in all, I did not spend the time to design the feature correctly, with the necessary amount of flexibility. And it would really not frequently be used on mobile/desktop dev. I admit servers are another story, but GRDB targets apps. Maybe someone will come with a workable design one day?

[abournier]

As you can see, records are not responsible for their preparation, but the database manager is.

I completely agreed with this.
So to be organized with multiple models, I have created AppDatabase extensions, each has 1 file. We can consider these files quite as the Rails controllers (CRUD).

CrudDrink.swift:

extension AppDatabase {
    
    func saveDrink(_ drink: inout Drink) throws {}
    func removeDrink(_ drink: inout Drink) throws {}

}

CrudBook.swift:

extension AppDatabase {
    
    func saveBook(_ book: inout Book) throws {}
    func removeBook(_ book: inout Book) throws {}

}

So all my models have their CRUD interface, and of course, the models are not responsible of their creation/update/deletion. Well, its not the initial subject, but I willl call the model timestamps methods from there as shown before.

[groue]

All right :-)

So... Did you consider using a base class for your 15 models? After all, there's nothing like ActiveSupport modules or ActiveSupport concerns within the Swift language. All we have in order to share some behavior and allow overrides in specific types are Swift classes.

[abournier]

So... Did you consider using a base class for your 15 models?

Yes, it's an option. My "old" Objective-C/FMDatabase app uses classes.
But, I want the new app to be more "modern" and to use the structs and its benefits. It should be an advantage in term of concurrency (and also for my future sync engine I think).

However, I'm talking about 10/15 lines to repeat per CrudModel. It's not a big issue after all.
In all cases, thanks for your suggestions and help.

[DamienPetrilli]

And it would really not frequently be used on mobile/desktop dev. I admit servers are another story, but GRDB targets apps.

Hello,

I am just jumping in because I am working on data syncing across devices. I think this statement is not accurate as the majority of users now expect their data to sync across devices. And knowing when a record was last updated is an important feature to do that: grabbing data since last update, handling conflict when the same record has been updated on two different devices, etc.

So... the feature was not ported to GRDB. The main reason is that users of this feature eventually want to perform updates without touching the timestamps. That's how people are. They think they always want to update timestamps, and probably they do, until they discover they don't, because things have changed.

Well I would argue that those who use timestamps but do not want it to update all the time didn't design their data correctly. It should be up to them to handle everything manually with a regular date field. None of those cases should be supported.

I am going to look into the above solution, thanks for your help 👍

[groue]

I am just jumping in because I am working on data syncing across devices. I think this statement is not accurate as the majority of users now expect their data to sync across devices.

This is true. I stand corrected, thank you 👍

And I've just started working on an app that requires sync timestamps ;-)

Well I would argue that those who use timestamps but do not want it to update all the time didn't design their data correctly. It should be up to them to handle everything manually with a regular date field. None of those cases should be supported.

I'm not sure I agree (assuming I understood what you said). If there would exist an automatic timestamping feature that is all or nothing, then consider the use case of adding a transient (non-synced) property to a timestamped record.

All updates to the transient property would touch the timestamp, against the user's will.

The user would have to move the transient property in a separate record/table.

This is bad. One ought not force a fellow developer into denormalization of the database schema against his will. You never know the problems this will create (refactoring time, migrations, SQL requests to rewrite, performance impacts, extra file size on disk, stress, broken schedules, you name it). My rule of thumb is: a database schema and the data inside has more value than the code that processes it. If something has to be fixed, it is the code, not the schema.

Finally, the user would open an issue in this repository, and rightfully so. I have better purposes for my free time than apologizing for troubles that I was able to foresee. Discussing points of views on technical topics and looking for good solutions with fellow developers is ok, I quite like that 😉

[groue]

Having record instances responsible for their timestamps considered harmful

Several answers in this discussion make individual record instances responsible for their own timestamps.

This idea has merits, but is it acceptable or meaningful that within a single write, not all records have the same timestamp? This is what happens when each individual record accesses "now" in a naive way (Date()). Update thousands of records and you can expect significant differences in the value returned by Date(), even though all those records are modified in a single shot.

Consider for example this piece of SQLite documentation:

The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call.

This means that the following SQL query assigns the same date to all players, even if the update statement takes a lot of time to execute:

-- Identical timestamps for all players
UPDATE player SET score = score + 1, updateDate = datetime('now')

PostgreSQL is even stricter:

Since these functions [(CURRENT_DATE, ...)] return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

Now, would we want the equivalent Swift code to generate distinct timestamps?

// Think about what you expect from timestamps here:
for player in players {
    try player.incrementScore(db)
}

To me, the clear answer is that we should strive for identical timestamps. I won't assume I'm smarter than SQLite + PostgresQL designers.

A quick Google search for a detailed description of ActiveRecord behavior on this topic did not yield significant answer - but I'm sure Rails was doing the wrong thing a few years ago : https://groups.google.com/g/rubyonrails-talk/c/TtqeqnP9JGI/m/OnZcfJxxQjYJ.

Let's not make the same mistake: record instances must not be responsible for the value of their timestamps.

Possible solution: database access locals

So, the GRDB user can make sure that several related statements or a whole transaction use the same timestamp.

I'm not sure yet how this could be done.

I'm considering taking inspiration from Swift TaskLocal and introduce "database access locals". A database access local is a value that can be bound and read in the context of a database access. It is available at any point during a database access.

Like TaskLocal (I'm lazy), database access locals must be declared as static properties, like this:

enum DatabaseTimestamp {
    @DatabaseAccessLocal static var currentDate: Date?
}

One can access the above database access local with DatabaseTimestamp.currentDate:

extension Player {
    mutating func incrementStore(_ db: Database) {
        try updateChanges(db) {
            $0.score += 1
            // It is a programmer error to forget to set the current date,
            // so shamelessly crash if not set.
            $0.modificationDate = DatabaseTimestamp.currentDate!
        }
    }
}

One can set the database access local from within a database access:

try dbQueue.write { db in
    DatabaseTimestamp.$currentDate.withValue(Date()) {
        for var player in players {
            try player.incrementScore(db)
        }
    }
}

Database access hook

The above implementation of Player.incrementScore force unwraps DatabaseTimestamp.currentDate! in order to make sure the timestamp has been set before Player.incrementScore is called.

I know that the whole concept of "programmer error", and the force unwrap operator, give the shivers to many developers 🤷‍♂️. So we should also make it possible to prevent such programmer error, which means make sure that all database accesses set currentDate. Let's introduce the "database access hook", a way to execute some code around all database accesses:

enum DatabaseTimestamp {
    // We can use a non-optional Date now, because
    // we remove the opportunity for programmer errors below
    @DatabaseAccessLocal var currentDate = Date()
}

extension Player {
    mutating func incrementStore(_ db: Database) {
        try updateChanges(db) {
            $0.score += 1
            $0.modificationDate = DatabaseTimestamp.currentDate // 😎
        }
    }
}

// Make sure all database accesses set the current date
var config = Configuration()
config.prepareDatabaseAccess { dbAccess in
    DatabaseTimestamp.$currentDate.withValue(Date(), execute: dbAccess)
}
let dbQueue = try DatabaseQueue("/path/to/db", configuration: config)

// Run!
try dbQueue.write { db in
    for var player in players {
        try player.incrementScore(db)
    }
}

I wonder how this prepareDatabaseAccess technique works w.r.t dependency injection. Tests, for example, must be able to inject a custom clock.

---

Those are raw thoughts on the topic! What do you think?

[DamienPetrilli]

Looks good! Can't wait to use it!

[groue]

v6.7.0 has shipped. See Record Timestamps and Transaction Date. I'll spend more time thinking about my own synchronisation needs before I suggest future enhancements - if needed.

[abournier]

Thanks @groue for your work ! I will try these new tools soon.

[groue]

The DocC article linked above has gained more details about the modification timestamps, but those are not deployed yet. You can read it on GitHub, though: https://github.com/groue/GRDB.swift/blob/master/GRDB/Documentation.docc/RecordTimestamps.md#modification-timestamp

[groue]

Since v6.8.0, Record Timestamps and Transaction Date provides a sample protocol TimestampedRecord that applications may reuse or take inspiration from.