[CLI] How to set schema for _sqlx_migrations table?

[wileymc]

Hi there!

I'm wondering if it is possible to configure the schema that the _sqlx_migrations table is written to when invoking CLI commands. It seems to be defaulting to our public schema, whereas we would like it to write to a migrations table at a different schema (not publicly accessible), perhaps one called sqlx

[wileymc]

In reading through the source, it looks like _sqlx_migrations is hard coded into many of the sql queries so this might be a bit of a lift to implement. Would be nice to be able to set a SQLX_MIGRATIONS_TABLE in env

[tv42]

Another use case: Sharing a single Postgres instance between multiple apps. Each app gets a Postgres role and schema assigned to it, and gets to manage its own migrations inside that.

[sebadob]

For anyone who still has this problem, I stumbled across the same when I now just started a new project with postgres 15, which changed the default behavior for the public schema.

I found a way how sqlx creates the migrations table inside my new custom schema and the solution was pretty easy in the end. The key was to have a user with the exact same name as the DB / schema. This way, the new schema was prioritized for all queries because of the $user search path, which comes before public. To set it up:

create user your_schema_name
    with password 'SuperSecure1337';

create database your_schema_name
    with owner your_schema_name;

-- on 'your_schema_name' database:
create schema your_schema_name authorization your_schema_name;

When I then run sqlx migrate run with this user / DB, the migrations table will not be created in public but in the custom schema instead.

I hope this helps some of you until we maybe have another custom way to specify the target location.

[mrl5]

hello I have a multitenant db usecase that's nicely described:

• in this amazon whitepaper as "bridge model"
• in this notion blogpost as "logical sharding"

So my usecase is:

as a sqlx user in multitenant db that's using logical sharding, I'd like to apply same sqlx migrations separately for each tenant

interface proposal

I think for such use case it would be beneficial to keep _sqlx_migrations in each tenant schema.

from CLI perspective it could look like:

sqlx migrate run --schema tenant_a
sqlx migrate run --schema tenant_b

workaround for postgres >=15 (improved @sebadob workaround)

1. (as per https://github.com/launchbadge/sqlx/issues/1835#issuecomment-1493727747) MANUALLY create either only schema or both user and schema:

CREATE USER tenant_default WITH PASSWORD 'db_pass';
CREATE SCHEMA tenant_default;
ALTER SCHEMA tenant_default OWNER TO tenant_default;

2. Define search_path option in your DB URIs "Parameter Key Words" that's used by sqlx. !!! NOTICE !!! options=-c search_path=tenant_${TENANT} at the end of the URI:

TENANT=default
DATABASE_URL="postgres://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}?sslmode=disable&application_name=${APPLICATION_NAME}&options=-c search_path=tenant_${TENANT}"
export DATABASE_URL

3. run tenant specific migrations via sqlx

sqlx migrate run

[cdbennett]

Right now I'm having to use "DBMate" instead of sqlx-migrate simply because it does allow us to tell the migration tool to have a custom table name for the migrations schema tracking. We have multiple apps forced to use the same PostgreSQL database and this is the only way to do it cleanly right now.

Would love to have support in sqlx-migrate for custom table name.

[themondays]

After much deliberation, rather than switching ORMs or bending our architecture to fit the constraints of our existing tools, I decided to write a custom migration script. This decision wasn't made lightly. It meant stepping away from the safety net of widely-used tool. Yet, the benefits quickly became apparent.

use crate::db::conn::create_db_pool;
use std::fs;

pub async fn migrate() -> Result<(), sqlx::Error> {
    dotenv::dotenv().ok();
    let db_pool = create_db_pool().await.expect("Failed to create database pool.");
        let migrations_dir = "./migrations";
        let entries = fs::read_dir(migrations_dir).unwrap();
        for entry in entries {
            let entry = entry.unwrap();
            let path = entry.path();
            if path.is_file() && path.extension().and_then(std::ffi::OsStr::to_str) == Some("sql") {
                let sql = fs::read_to_string(path).unwrap();
                let result = sqlx::query(&sql).execute(&db_pool).await;
                match result {
                    Ok(_) => println!("Migration executed successfully for {:?}", entry.path()),
                    Err(e) => println!("Ignoring error for {:?}: {}", entry.path(), e),
                }
            }
        }
    Ok(())
}

[cdbennett]

After much deliberation, rather than switching ORMs or bending our architecture to fit the constraints of our existing tools, I decided to write a custom migration script.

Funnily enough, I did the same thing! I tried sqlx migrate but it wasn't flexible enough to handle coexisting with existing databases and namespace/schemas. Then I tried DBmate, but I had deployment issues when trying to run it on very old Linux distros, (some missing glibc symbols or other system libraries).

So we implemented custom migrations, in this case the migrations were written in Rust, and we keep our own "migrations" table to keep track of which were applied. It's really straightforward and much more lightweight.

At some point, if sqlx makes its migration support more flexible (must support optionally defining a Postgres schema to create tables in, and also must be able to change the name of the _sqlx_migrations table for cases where multiple applications must share a schema). Perhaps this is an uncommon case, but we have run into situations where the DB server requires this.