Using an alternate schema

River can use an alternate schema in Postgres using the client's Schema option or configuring search_path on connections.

Clusters, databases, and schemas

Postgres clusters may be subdivided in many databases, and each database may be subdivided again into many schemas, each one containing a collection of tables. While it's often practical to keep all an application's tables in a single schema for convenience, some users may find it desirable to put River's tables in an alternate schema. The public schema is assigned to databases automatically, and by default all tables will be located there.

Configuring an alternate schema explicitly

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.

Migrating with the River CLI

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

Migrating with the Go API

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)
}

Configuring a schema in search path

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.

Migrations and search path in database URLs

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()

PgBouncer and AfterConnect

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.