Migration using raw sql

[thaarrtt]

Hey guys

is there a function or if not can we use raw sql for writing migration????
or is there a tool or function that can generate from rust model/raw sql to migration

I know there are function that can generate rust entities from raw sql, I just ask if there are some from rust model to migration

[bendo01]

i'm using raw sql in my migration, here an example

use sea_orm_migration::prelude::*;
use sea_orm_migration::sea_orm::ConnectionTrait;

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        // Replace the sample below with your own migration scripts
        let db = manager.get_connection();
        db.execute_unprepared(
            "
            CREATE SCHEMA IF NOT EXISTS person_reference;
            ",
        )
        .await?;
        db.execute_unprepared(
            "
            CREATE TABLE IF NOT EXISTS person_reference.age_classifications
            (
                id uuid DEFAULT gen_random_uuid(),
                created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
                deleted_at timestamp(0) with time zone,
                created_by uuid DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
                updated_by uuid DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
                CONSTRAINT person_reference_age_classifications_pkey PRIMARY KEY (id)
            )
            ",
        )
        .await?;
        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .get_connection()
            .execute_unprepared("DROP TABLE IF EXISTS `person_reference.age_classifications`")
            .await?;

        Ok(())
    }
}

i'm using Postgresql 17

[thaarrtt]

Yeah, I just found the docs few days ago (they hidden, dont blame me)

using raw sql is much more easier than having to learn sea query shenanigan

[jondot]

The recommended way is to use what @bendo01 wrote. To load from an actual sql file you can also include_str or open a file while running. This ensures you get the full migrator system (which tracks of which migrations were applied, resets in testing, etc.)