Hello! Out of curiosity, do people have a pattern ...

# exposed

[Romain]

Hello! Out of curiosity, do people have a pattern they like to have a created_at/updated_at column? This seems to come back every few months so I figured it was my turn to ask 😄
The only way that does exactly what I want is to do a select, then either an update or an insert, but that seems super wasteful

Untitled.kt

[Up]

You could just do it at the DB level, then you're guaranteed to never forget it.

My current alternative is to do it on the DAO but that ofc means it only works on DAO, not regular DB operations:

@OptIn(ExperimentalTime::class)
internal fun nowUTC() = Clock.System.now().toLocalDateTime(TimeZone.UTC)

internal fun EntityClass<*, *>.withAuditLogColumns(vararg exclude: Column<*> = arrayOf()) {
    EntityHook.subscribe { change ->
        val changedEntity = change.toEntity(this)
        if(change.changeType == EntityChangeType.Updated) {
            val now = nowUTC()
            changedEntity?.let { entity ->
                if(exclude.toList().containsAll(entity.writeValues.keys)) {
                    return@let
                }

                @Suppress("UNCHECKED_CAST") val modifiedColumn: Column<Any?> = table.columns.find { it.name == "updated_at" && it.columnType is KotlinLocalDateTimeColumnType } as Column<Any?>
                if(entity.writeValues[modifiedColumn] == null) {
                    entity.writeValues[modifiedColumn] = now
                }
            }
        }
    }
}

[Up]

(and then use like this)

[Romain]

Thank you! I don't use the DAO but maybe I should look into it 🤔

[simon.vergauwen]

I have them be automatically initialised:

val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp)
val updatedAt = timestamp("updated_at").defaultExpression(CurrentTimestamp)

This uses CURRENT_TIMESTAMP, but then it still requires to properly track updatedAt yourself. Alternatively combined with triggers on postgres you can mange this entirely automatically but needs to be setup out of of Exposed.

[Romain]

Yeah that's probably the better approach indeed, I could create triggers when I run the migration maybe?