migration is broken because of sqlx compile time query checking

[chujungeng]

I was following the readme file and trying to set up the development server for the first time. I got postgres running in a docker container after running docker-compose -f integrations/docker-compose.yml up -d. However, the scheduler won't compile when I do cargo run. Here's an example of the compiler errors:

error: error returned from database: relation "user_integrations" does not exist --> crates/infra/src/repos/user_integrations/postgres.rs:44:9 | 44 | / sqlx::query!( 45 | | r#" 46 | | INSERT INTO user_integrations(account_uid, user_uid, provider, refresh_token, access_token, access_token_expires_ts) 47 | | VALUES($1, $2, $3, $4, $5, $6) ... | 54 | | integration.access_token_expirests 55 | | ) | |____^

Turns out, these errors were thrown by sqlx's compile time query checking feature, and it won't compile unless we have everything in the database set up. However, we can't run the migrations to set up the database unless the code compiles and runs.

One workaround is to run the .sql files manually before compiling the code for the first time. But that renders automated migration useless, right? Can we turn off the compile time query checking in the code to prevent the deadlock from happening?

[fmeringdal]

You are right that the current migration binary in scheduler/src/bin/migrate.rs is broken because of the cyclic dependency. I don't really think the migration tool and app should be the same binary, which unfortunately is the case in this repo. The migration should be run with a separate tool before starting the application. The existing run_migrations function in the code should probably be removed to avoid confusion.

Compile time checked sql queries are extremely useful for catching very common bugs, so that will not be disabled.

I use sqlx-cli when running the migrations for this codebase, here are the commands:

export DATABASE_URL="TODO"
cargo install sqlx-cli
sqlx migrate run --source scheduler/crates/infra/migrations

[chujungeng]

That makes sense. Thanks for the clarification!