Skip to content
River Docs

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.

Configuring a schema in search path

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.

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
}

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:

Terminal window
export DATABASE_URL="postgres://host:5432/db?search_path=alternate_schema"
Terminal window
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.