Postgres Backend: the type alias 'bigserial' and 'serial' are not valid in the context of a TableAlterStatement

[jtmorrisbytes]

Description

When writing a migration using sea-orm-migration, manager.alter_table() produces SQL with 'bigserial' and 'serial' as a data type on the postgres backend when the column is a primary key with auto-increment set to true. this happens even when attribute 'column_type' is applied to the struct definition.

Steps to Reproduce

execute a alter table statement with primary_key=true, auto_increment=true, and either i32 or i64 as rust types and 'Integer or BigInteger' as column_types respectively on a postgres backend using schema introspection

Expected Behavior

adding or modifying a column in a postgres backend should use 'integer' and 'biginteger' respectively and require the programmer to manually create the sequences

Actual Behavior

sea orm outputs 'serial' and 'bigserial' causing the migration to fail
The terminal used to run migrations displays 'Execution Error: error returned from database: type "bigserial" does not exist'

Reproduces How Often

Always (every time)

Workarounds

I prepose not to use 'bigserial' and 'serial' as data types in the context of altering a table and instead leave creating or altering sequences to the programmer

Reproducible Example

// user.rs

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq,DeriveEntityModel)]
#[sea_orm(table_name = "user"]
pub struct Model {
    #[cfg_attr(feature="sea-orm",sea_orm(primary_key,column_type="BigInteger",auto_increment=true))]
    pub id: i64,
}
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
}

impl ActiveModelBehavior for ActiveModel {}

//migration.rs
use sea_orm::{IdenStatic};
use sea_orm_migration::{
    prelude::*,
    sea_orm::{EntityTrait, Iterable, Schema},
};



#[derive(DeriveMigrationName)]
pub struct Migration;

// this function creates the table in the database if it does not exist,
// then alters the table to add the column if it does not exist
// then alters the table to modify the column if it does exist
async fn helper<E: EntityTrait + Default>(manager: &SchemaManager<'_>) -> Result<(), DbErr> {
    let schema = Schema::new(manager.get_database_backend());
    let entity = E::default();
    let stmt = schema.create_table_from_entity(entity).if_not_exists().to_owned();
    manager
        .create_table(stmt)
        .await?;
    for column in <E::Column>::iter() {
        let mut def = schema.get_column_def::<E>(column).to_owned();
        if manager.has_column(entity.table_name(), column.as_str()).await? {
            manager
                .alter_table(
                    TableAlterStatement::new()
                        .table(entity)
                        .modify_column(&mut def)
                        .to_owned(),
                )
                .await?;
        }
        else {
            let stmt = TableAlterStatement::new()
            .table(entity)
            .add_column(&mut def).to_owned();
            let sql = stmt.build(PostgresQueryBuilder);
            println!("{}",sql);
            manager
                .alter_table(
                    stmt
                )
                .await?;
        }
    }
    Ok(())
}
#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
         helper::<Entity>(manager).await
    }
    async fn down(&self,manager: &SchemaManager {}

Versions

sea-orm 0.11.3
sea-orm-migration 0.11.3
postgres version 15
windows 11 22H2

[billy1624]

Hey @jtmorrisbytes, I moved this issue to SeaQuery repository as it's more related to the query builder.

I did some digging and indeed serial is only applicable on table creation but not modification. https://dbfiddle.uk/TEnDJWeb

[billy1624]

I just drafted a PR for this, feel free to check it :)

• Fix Postgres alter primary key column statements #646