Using non-sqlx migrations for sqlx::test

[nk9]

I'm running my backend using Supabase. This handles migrations for me, and generates a migrations/ directory with timestamped sql files. Fortunately, the filenames match the format required by sqlx:

$ ls backend/supabase/migrations
20231216192815_initial_schema.sql
20231216194424_districts_geo_index.sql
etc.

I'm accessing the DB from Rust using SQLx, and that's working brilliantly. Now I want to test my business logic around the queries, so I'm trying to get sqlx::test working. Here's my test:

#[cfg(test)]
mod tests {
    use super::*;

    static TEST_DATA_DIR: &str = "tests/data";

    #[sqlx::test(migrations = "../backend/supabase/migrations")]
    async fn test_assert(pool: PgPool) {
        assert_eq!(0, 1);
    }
}

I have resolved the superuser issue, but now I am getting a different error:

thread 'insert::tests::test_insert_years' panicked at /Users/nick/.cargo/registry/src/index.crates.io-6f17d22bba15001f/sqlx-core-0.7.4/src/testing/mod.rs:248:14:
failed to apply migrations: Execute(Database(
    PgDatabaseError {
        severity: Error,
        code: "3F000",
        message: "schema \"extensions\" does not exist",
        detail: None,
        hint: None,
        position: None,
        where: None,
        schema: None,
        table: None,
        column: None,
        data_type: None,
        constraint: None,
        file: Some("namespace.c"),
        line: Some(3096),
        routine: Some("get_namespace_oid") }))

The first line of my first sql migration file is this:

create extension if not exists "postgis" with schema "extensions";

So presumably this is the problem… How can I get around this? To be clear, this migration works fine when I run it via Supabase. The DB does have access to the postgis extension, and there's an extensions schema (at least in the finished version).

To be clear, I've never run sqlx migrate because I'm not using SQLx to manage migrations. So there is no _sqlx_migrations table in my database. I'm really hoping this won't matter, as the only DB which needs to be migrated is the one-off testing DB. It does seem like sqlx has found the migration scripts and is gamely trying to execute them.

---

Update: That is definitely the problem, because when I remove that line from the sql file, the migration fails at a different place. (The first time the geography type is referenced, which is declared by postgis.)

[nk9]

I have been able to get around this error by adding this line before the create extension command:

create schema if not exists "extensions";

Because Supabase creates databases with an "extensions" schema already, this new command is a no-op for supabase db diff, but gets around the sqlx error.