`eraseDatabaseOnSchemaChange` nukes data unexpectedly albeit no changes in schema between runs

[appfrosch]

I am seeing unexpected behaviour when using the eraseDatabaseOnSchemaChange flag–data is nuked even at times when there are no changes in between runs … Might be missing something–but I could not find anything when checking the documentation …

What did you do?

I have

#if DEBUG
    migrator.eraseDatabaseOnSchemaChange = true
#endif

in place as recommended in the documentation.

On schema change, the data is nuked as expected and documented.

What did you expect to happen?

On re-runs of the app without changes to the schema, I do not expect the data to be nuked.

What happened instead?

On re-runs of the app without changes to the schema, the data is nuked.

Environment

GRDB flavor(s): GRDB GRDB version: 6.27.0 Installation method: SPM (Tuist) Xcode version: 15.4 Swift version: 5 Platform(s) running GRDB: iOS macOS version running Xcode: 14.5

[groue]

Hello @appfrosch. Please provide a minimal reproducible example.

[appfrosch]

Thanks for reaching out first of all. Because creating a minimal example seemed to be quite involved and I did not have lots of time to look into it, I did some debugging.

The needsErase flag in DatabaseMigrator.migrate(_:upTo:) is set because userSchema != tmpUserSchema. I diff'ed the two values and found out what my issue is–and it's quite obvious now that I have it in front of me.

We have a createdAt and updatedAt datetime field in all of the tables, and we are defaulting them to Date(). That means though that there will always be a difference in each run.

In a nutshell: doing

table.column("createdAt", .datetime).notNull().defaults(to: Date())

is a bad idea generally I guess :-)

[groue]

Thanks for the investigation @appfrosch 👍

You could use .defaults(sql: "CURRENT_TIMESTAMP") instead:

sqlite> CREATE TABLE t (createdAt DATETIME DEFAULT CURRENT_TIMESTAMP);
sqlite> INSERT INTO t DEFAULT VALUES;
sqlite> SELECT * FROM t;
|      createdAt      |
|---------------------|
| 2024-06-25 01:13:21 |

You might also use the techniques described in Record Timestamps and Transaction Date.