relation "_sqlx_migrations" does not exist

Bug Description

Error message from running sqlx migrate run on a fresh install postgres:

relation \"_sqlx_migrations\" does not exist

On an existing personal project (meaning I didn't need run sqlx migrate add -r <name> to generate migration files, they are already there when I pulled project), I have the following migration files:

The up file:

CREATE SCHEMA IF NOT EXISTS hello;

SET SEARCH_PATH TO hello; -- <== initially added this to save myself from prepending schema name before tables, but this seems to cause issue

CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL,
    email VARCHAR (255) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

-- many more tables, etc.

The down file:

DROP SCHEMA IF EXISTS hello CASCADE;

Experiment 1:

When I removed the offending line in the up file AND prepend the schema name to all table names, everything works as normal.

Experiment 2:

When I change the offending line to:

ALTER DATABASE hello_db SET SEARCH_PATH TO hello;

I go through the steps of reproduction below. I was able get sqlx migrate run successfully. I verify with Postgres and found all tables are there with the correct schema name, migration is inserted to the _sqlx_migrations table with 1 line of migration record. Everything looks good.

However If I run this next cmd:

sqlx migrate revert

It returns a strange regular message from terminal:

No migrations available to revert

I verify this in Postgres, and found out:

all tables are NOT deleted
migration record from _sqlx_migration table is deleted
schema hello remains

Next inside of Postgres, I copy and paste the same sqlx from my down file, and it executed successfully without problems.

This is not what I was expecting.

I'm expecting:

all tables deleted
schema hello deleted
migration record deleted from _sqlx_migration table, along with the _sqlx_migration table itself to be gone too
a success message returned to my console similar to: Applied 20240223050059/revert init (16.495667ms)

There is something strange I might not understand too well between sqlx itself or postgres, idk which is which atm. Help appreciated.

Minimal Reproduction

Spin up a new Postgres container and volume
Run sqlx database create
Run sqlx migrate run
Got error: while executing migrations: error returned from database: relation "_sqlx_migrations" does not exist

Info

SQLx version: 0.8
sqlx-cli version: 0.8.0
SQLx features enabled: runtime-tokio-rustls, migrate, postgres, uuid, chrono, macros
Database server and version: postgres:15 (Postgres)
Operating system: alpine linux container
rustc --version: 1.79

I'm not sure why you were expecting this to work. SET search_path is a persistent setting on the connection, so it's going to break resolution for anything that executes afterward, including for the _sqlx_migrations table since that was created in the public schema.

If you're going to change search_path, it needs to be changed on the connection/database before executing migrations, and then they won't know the difference. Unfortunately, this isn't surfaced anywhere in sqlx-cli, but making this configurable is one of the goals of https://github.com/launchbadge/sqlx/pull/3383 which is still in its very early stages.

My recommendation would be that, if you're going to use multiple schemas, just be explicit about them and don't muck around with search_path. IMO, you're only going to cause headaches for yourself down the road by doing that.

SET LOCAL unfortunately won't work because we mark the migration as complete in the transaction so it's all-or-nothing.

launchbadge / sqlx