Using an alternate schema

Configure a schema explicitly on a client using Config.Schema:

riverClient, err := river.NewClient(riverpgxv5.New(dbPool), &river.Config{
    Schema: "alternate_schema",
    ...
})
if err != nil {
    panic(err)
}

This option causes River to explicitly prefix all table, function, and enum references with an explicit schema like SELECT * FROM alternate_schema.river_job.

Ruby’s runtime drivers use the connection’s search path rather than a client schema option. Configure every connection in the pool:

DB = Sequel.connect(ENV.fetch("DATABASE_URL"), search_path: "alternate_schema")
client = River::Client.new(River::Driver::Sequel.new(DB))

For Active Record, configure schema_search_path on its connection pool. Create the schema before migrating it.

Set the schema on PostgresDatabase before passing it to the client builder. SchemaName::new validates the schema name:

use riverqueue::database::{PostgresDatabase, SchemaName};

let schema = SchemaName::new("alternate_schema")?;
let database = PostgresDatabase::new(pool.clone()).with_schema(schema);

let client = riverqueue::Client::builder(database).build()?;

Pass schema when constructing the Postgres driver so the client uses that schema for its River tables:

const driver = new PgDriver(pool, { schema: "alternate_schema" });
const client = new Client(driver);

Use an alternate schema explicitly to migrate with the River CLI:

go install github.com/riverqueue/river/cmd/river@latest

river migrate-up --database-url "$DATABASE_URL" --schema alternate_schema

Pass --schema to the bundled river command to apply migrations in the same schema as your client:

bundle exec river migrate-up --database-url "$DATABASE_URL" --schema alternate_schema

Use the CLI’s --schema flag to select the schema whose River tables should be migrated:

riverqueue migrate-up --database-url "$DATABASE_URL" --schema alternate_schema

Add --schema when invoking the CLI through npx to migrate your client’s alternate schema:

npx riverqueue migrate-up --database-url "$DATABASE_URL" --schema alternate_schema

Use an alternate schema explicitly with the Go rivermigrate API:

migrator, err := rivermigrate.New(bundle.driver, &rivermigrate.Config{
    Schema: "alternate_schema",
})
if err != nil {
    panic(err)
}

res, err := migrator.Migrate(ctx, rivermigrate.DirectionUp, nil)
if err != nil {
    panic(err)
}

Supply schema: to River::Migrator when applying migrations from application code:

River::Migrator.new(driver, schema: "alternate_schema").migrate

Configure the migrator with with_schema before applying migrations:

riverqueue::migrate::PostgresMigrator::new(pool.clone())
    .with_schema(riverqueue::database::SchemaName::new("alternate_schema")?)
    .migrate_up().await?;

Create the migrator with a driver configured for the alternate schema, just as you would for the client:

import { createMigrator } from "@riverqueue/migrate";

await createMigrator(new PgDriver(pool, { schema: "alternate_schema" })).migrateUp();

Alternatively, a non-standard schema can be configured by setting the Postgres search path (SET search_path TO ...) of the database pool:

dbPoolConfig, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    // handle error
}

// Set the schema in search path.
dbPoolConfig.ConnConfig.RuntimeParams["search_path"] = "alternate_schema"

dbPool, err := pgxpool.NewWithConfig(ctx, dbPoolConfig)
if err != nil {
    // handle error
}
defer dbPool.Close()

riverClient, err := river.NewClient(riverpgxv5.New(dbPool), &river.Config{
    ...
})
if err != nil {
    // handle error
}

Search paths are a list of schemas in which to look for database tables in order of preference. Storing River tables in one schema and other tables in another could be accomplished by including them both in search_path:

SET search_path TO 'my_river_schema, my_other_schema'

A downside of search paths is that they’re set on a per connection basis. Database pools like Pgx can set them automatically when procuring a new connection (see PgBouncer and AfterConnect), but it’s possible for search_path to become reconfigured or unset, resulting in River being unable to locate its tables:

ERROR: relation "river_job" does not exist (SQLSTATE 42P01)

Avoid this by preferring the use of an explicit Schema option as described above.

Use Sequel’s search_path or Active Record’s schema_search_path as shown above. The setting must apply to every connection, including connections used for application transactions that insert jobs. Be careful with transaction-mode poolers, where session state does not reliably follow the client connection.

Prefer PostgresDatabase::with_schema so River qualifies its own table references. If application queries use search_path, configure the SQLx connections independently and keep the target consistent with migrations.

Prefer new PgDriver(pool, { schema: "alternate_schema" }). If application queries need a search path too, configure that on the pg pool separately; it does not replace the River driver’s schema configuration.

When running migrations with the River CLI, search path should be set as a parameter on the database URL:

export DATABASE_URL="postgres://host:5432/db?search_path=alternate_schema"

river migrate-up --database-url "$DATABASE_URL"

A schema name in the database URL is also be respected by pgxpool.New or pgxpool.ParseConfig, and can act as an alternative way of pointing a pgx connection pool to a different schema.

dbPool, err := pgxpool.New(ctx, "postgres://host:5432/db?search_path=alternate_schema")
if err != nil {
    // handle error
}
defer dbPool.Close()

Pass --schema alternate_schema to the Ruby CLI and configure the runtime pool’s search path separately. A URL parameter understood by pgx is not automatically understood by Sequel or Active Record.

Pass --schema alternate_schema to the Rust CLI and use the same SchemaName in the runtime database configuration.

Pass --schema alternate_schema to the TypeScript CLI and configure the same schema on PgDriver.

Depending on configuration and deployment, using an alternate schema with PgBouncer is a little trickier because it may be maintaining connections that weren’t initialized with search_path.

A work around is to use pgx’s AfterConnect hook to always set search_path when procuring a connection from PgBouncer:

var (
    alternateSchema = "alternate_schema"
    setSearchPath   = fmt.Sprintf("SET search_path TO %s, public",
        pgx.Identifier{alternateSchema}.Sanitize())
)

dbPoolConfig, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    return nil, err
}

dbPoolConfig.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
    if _, err := conn.Exec(ctx, setSearchPath); err != nil {
        return fmt.Errorf("failed to set search_path: %w", err)
    }
    return nil
}

dbPool, err := pgxpool.NewWithConfig(ctx, dbPoolConfig)
if err != nil {
    // handle error
}
return dbPool, nil

A side effect of this technique is that connections become “tainted” in that even after River’s done with them, search_path stays set in PgBouncer and may affect other applications that subsequently use them. This effect should be benign as long as search_path contains a fallback schema like alternate_schema, public so clients can still find relations that aren’t found in the River schema.

Ruby’s runtime relies on session search paths. With PgBouncer, arrange a stable server-side search path for the database role or use session pooling; setting it on a client connection is not enough in transaction mode.

Use an explicit SchemaName on PostgresDatabase. See PgBouncer for the additional constraints around listeners and leadership.

Use an explicit schema on PgDriver. See PgBouncer for the additional constraints around listeners and leadership.