Moving / modifying data in migrations

[zchira]

Simple use case:
I had two tables
Authors { id, name }
Post { id, text, author_id }

Now, I want to allow more than one author for the posts (many-to-many relation).
I created one migration which creates new table:
PostAuthor { author_id, post_id }

    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager.create_table(
            Table::create().table(PostAuthor::Table)
                .if_not_exists()
                .col(ColumnDef::new(PostAuthor::AuthorId)
                    .integer()
                    .not_null()
                )
                .col(ColumnDef::new(PostAuthor::PostId).integer().not_null())
                .primary_key(Index::create().col(PostAuthor::PostId).col(PostAuthor::AuthorId))
                .foreign_key(
                    ForeignKey::create()
                        .from(PostAuthor::Table, PostAuthor::AuthorId)
                        .to(Author::Table, Author::Id)
                        .on_update(ForeignKeyAction::Cascade)
                        .on_delete(ForeignKeyAction::Cascade)
                )
                .foreign_key(
                    ForeignKey::create()
                        .from(PostAuthor::Table, PostAuthor::PostId)
                        .to(Post::Table, Post::Id)
                        .on_update(ForeignKeyAction::Cascade)
                        .on_delete(ForeignKeyAction::Cascade)
                )
                .to_owned()
        ).await
    }

in the next migration I want to migrate data to new table:
to take all posts from the Posts table and move Post(id) and Post(author_id) to the new table as PostAuthor(post_id, author_id).

and, after the migration is over, to (eventually in the new migration?) remove the author_id column from Post table,

What would be the cleanest way to do that (dirty way is also acceptable if there is no clean :) )?

[zchira]

OK. I've got it (though I'm not sure if that is the best way):

    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        use entity::post::Entity as PostEntity;
        let results: Vec<entity::post::Model> = PostEntity::find().all(manager.get_connection()).await?;

        for p in results.iter() {
            use entity::post_author::ActiveModel as PostAuthorActiveModel;
            let p = PostAuthorActiveModel {
                post_id: ActiveValue::set(p.id),
                author_id: ActiveValue::set(p.author_id)
            };
            entity::post_author::Entity::insert(p).exec(manager.get_connection()).await?;
        };

        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
            entity::post_author::Entity::delete_many().exec(manager.get_connection()).await?;
            Ok(())
    }

Next thing, removing author_id from Post table, which will probably break the building step if I remove AuthorId from Post struct 🤔

#[derive(DeriveIden)]
pub enum Post {
    Table,
    Id,
    Text,
    AuthorId     <<<<<<<<<<--------- remove?
}