Using an alternate schema

River can use an alternate schema in Postgres by configuring it in search path through a pgx connection pool or in a database URL.

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 desireable 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.

River pushes Postgres configuration down into its drivers and their underlying packages when possible. An alternate schema can be configured by setting the search path 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
}

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