What is a good way to test local changes using the Sqlite adapter

[Sillyvan]

I would love to add better Sqlite defaults to loco. Same as the official Ruby adapter but im unsure how to test it. the examples/demo project uses postgres and just changing the config yaml to sqlite and running cargo loco db reset fails.

2024-10-13T20:46:01.619611Z INFO app: sea_orm_migration::migrator: Applying migration 'm20240416_071825_roles' environment=development thread 'main' panicked at /home/silvan/.cargo/registry/src/index.crates.io-6f17d22bba15001f/sea-orm-1.0.0/src/database/statement.rs:146:1: not implemented

I think this might be a general issue even tho migrating from postgres to sqlite is probably rather rare. Would still be nice if its possible

[kaplanelad]

You talking about this issue: #773?

[Sillyvan]

Thats the thing im playing around with. Pretty sure i figured that one out.

[Sillyvan]

This is more about how to get the exmaples/demo working with sqlite

[felipesere]

Have you figured this out?
I would very much like to run my app against multiple SQLite databases...

[Sillyvan]

From what i can tell this is more of a SeaORM “issue”. It shouldn’t be impossible because with Rails i didnt run into this issue with a way more complex db schema

[Sillyvan]

@felipesere Im not 100% sure which features dont work but rewriting enums to text seems to be the biggest one. mentioned here: https://www.sea-ql.org/SeaORM/docs/schema-statement/create-enum/#sqlite

[kaplanelad]

Have you figured this out? I would very much like to run my app against multiple SQLite databases...

we have this option. take a look here:

https://github.com/loco-rs/loco/blob/master/loco-extras/src/initializers/multi_db.rs

[Sillyvan]

I think the issue discussed here is about the migrations using Native DB enums instead of String mappings

[felipesere]

Oh I can check if I have any enums in my schema...

[Sillyvan]

I played around with the failing migration. Im not 100% sure this is a good solution but it seems to work.
https://github.com/loco-rs/loco/blob/master/examples/demo/migration/src/m20240416_071825_roles.rs

use sea_orm_migration::{prelude::*, schema::*};

use crate::sea_orm::{DbBackend, DeriveActiveEnum, EnumIter, Schema};

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        // Create a new enum type `roles_name` with the values `Admin` and `User`
        let db = SchemaManager::get_database_backend(manager);
        let schema = Schema::new(db);

        // If the database backend is Postgres, create a new enum type `roles_name` with the values `Admin` and `User`
        if let DbBackend::Postgres = db {
            manager
                .create_type(schema.create_enum_from_active_enum::<RolesName>())
                .await?;
        };
        // Create a new table `roles` with the columns `id`, `pid`, and `name`
        manager
            .create_table(
                table_auto(Roles::Table)
                    .col(pk_auto(Roles::Id))
                    .col(uuid_uniq(Roles::Pid))
                    .col(
                        ColumnDef::new(Roles::Name)
                            .custom(Alias::new("roles_name")) // Use the enum type name
                            .not_null()
                            .to_owned(),
                    )
                    .to_owned(),
            )
            .await?;

        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        // Drop the table `roles`
        manager
            .drop_table(Table::drop().table(Roles::Table).to_owned())
            .await?;
        //
        // Drop the enum type `roles_name` if db backend is Postgres
        let db = SchemaManager::get_database_backend(manager);
        if let DbBackend::Postgres = db {
            manager
                .drop_type(
                    extension::postgres::Type::drop()
                        .if_exists()
                        .name(RolesNameEnum)
                        .restrict()
                        .to_owned(),
                )
                .await?;
        }
        Ok(())
    }
}

#[derive(DeriveIden)]
enum Roles {
    Table,
    Id,
    Pid,
    Name,
}

// Create a new enum for the roles_name
#[derive(EnumIter, DeriveActiveEnum)]
#[sea_orm(rs_type = "String", db_type = "Enum", enum_name = "roles_name")]
pub enum RolesName {
    #[sea_orm(string_value = "Admin")]
    Admin,
    #[sea_orm(string_value = "User")]
    User,
}

[Sillyvan]

Sadly im not knowledgeable enough with SeaORM nor Loco to know where the issues starts. Im assuming its all SeaORM doing this. Making me wonder if this is just a bad idea or if they did not care about supporting this type of use case (postgres > sqlite)

[felipesere]

This is further than I got to. I got some nasty compile errors I couldn't explain.

[Sillyvan]

Im gonna close this discussion because its not specific enough. I opened one regarding migrations: #865

I will see if it makes sense to also test sqlite in CI/CD instead of just testing postgres