How do I declare an update_at and create_at column using sea_orm_migration？

[fu050409]

I uses the latest Sea-ORM on crates.io, and uses Sqlite3 as my database backend.

---

Hello! I tried to look for information about my problem in the issues and discussions of sea-orm, but may due to the limitations of my English, I really didn't find enough information about it. Please forgive me if there is already a duplicate question.

I saw an issue where it was mentioned that I could implement create_time by declaring a default time with a .extra implementation, e.g. .extra("DEFAULT CURRENT_TIMESTAMP".to_string()), and that's exactly how I implemented it. But many of the solutions I've tried don't implement update_time, which is very confusing to me.

I want to build a column that stores the creation time and update time, here is my code:

// In up function
manager.create_table(
        Table::create()
            .table(Account::Table)
            .if_not_exists()
            .col(
                ColumnDef::new(Account::Id)
                    .integer()
                    .not_null()
                    .auto_increment()
                    .primary_key(),
            )
            .col(
                ColumnDef::new(Account::Username)
                    .string()
                    .not_null()
                    .unique_key(),
            )
            .col(ColumnDef::new(Account::Password).string().not_null())
            .col(ColumnDef::new(Account::Nickname).string().null())
            .col(
                ColumnDef::new(Account::CreateTime)
                    .timestamp()
                    .not_null()
                    .extra("DEFAULT CURRENT_TIMESTAMP".to_string()),
            )
            .col(
                ColumnDef::new(Account::UpdateTime)
                    .timestamp()
                    .extra("DEFAULT CURRENT_TIMESTAMP".to_string()) // Now it's the same as the CreateTime field, but I need it to update automatically when the data gets updated
                    .not_null()
            )
            .to_owned(),
    )
    .await

And this is the model code:

#[derive(DeriveIden)]
enum Account {
    Table,
    Id,
    Username,
    Password,
    Nickname,
    CreateTime,
    UpdateTime,
}

I deeply suspect that this is due to me missing my database class. :(

Thank you very much !

[fu050409]

After doing more searching, I realized that the ON UPDATE method I had tried didn't look like it would work in Sqlite, and it seemed like I needed a method that would work in Sqlite

[fu050409]

I'm not quite sure if the latest version of SeaORM provides a built-in way to implement automatic data updates? I wasn't able to find it.

I think it could be a better solution to implement this via SeaORM.

[fu050409]

I'm migrating from Django for Python, which offers a wealth of methods, so there are often some areas that are difficult to understand quickly, so forgive me if I'm asking a stupid question.

[fu050409]

I found a another solution to implement create_at in the source code of Sea-ORM:

.col(
    ColumnDef::new(Account::CreateTime)
        .timestamp_with_time_zone()
        .default(Expr::current_timestamp())
        .not_null(),
)

[fu050409]

It works the same as .extra("DEFAULT CURRENT_TIMESTAMP".to_string())

[fu050409]

https://github.com/SeaQL/sea-orm/pull/854/files#diff-50b187da806a981ede479097b8adc4cc78b5dfbe6d7d42495e914b7133b632e9
#854
#1803
#1431
#819
#827

These are the links that mentioned my issue, but I found it seems that Sea-ORM haven't supported updated_at.

May be I should update the updated_at manually?

[fu050409]

Not these are my solutions now:

.col(
    ColumnDef::new(Account::CreatedAt)
        .timestamp_with_time_zone()
        .default(Expr::current_timestamp())
        .not_null(),
)
.col(
    ColumnDef::new(Account::CreatedAt)
        .timestamp_with_time_zone()
        .default(Expr::current_timestamp())
        .not_null(),
)

and updated_at will work with:

let db = manager.get_connection();

db.execute_unprepared(
    "CREATE TRIGGER updated_at
    AFTER UPDATE ON account
    FOR EACH ROW
    BEGIN
        UPDATE account
        SET updated_at = (datetime('now','localtime'))
        WHERE id = NEW.id;
    END;",
).await?;

If you have the same problem, you may should replace the plain sql command with your database's.

[alvissraghnall]

Sorry I have to ask, but where exactly in your setup did you place the dB execute_unprepared code ?

[alvissraghnall]

Sorry I have to ask, but where exactly in your setup did you place the dB execute_unprepared code ?

Okay nvm I included it in my migration. However, I was quite unsure about the table name. Upon inspecting my sqlite file, I observed that table names were wrapped in quotes (""), hence I included quotes in my trigger SQL command.