First of all I need to anticipate excuses because it will be a long text, but the idea here is open a discussion to decide WHAT will do and IF we'll do something about it.

The aim of this issue is to discuss a redesign of the current API that will able us to:

1. Be more concise with use of schemas in PostgreSQL

In PostgreSQL today we have the following hierarchy:

Cluster
   |
   +-- Database
          |
          +-- Schema
                |
                +-- Object (table, view, function, etc)

Although that hierarchy is well defined it's not strict to the way we use the database objects at SQL level. For example:

SELECT * FROM foo;
SELECT * FROM foo.bar;

Of course we need always be connected to a database to execute SQL statements, but pay attention on the example above. We mentioned two times the object foo, but in the first query foo is a table (or view) and in the second foo is a schema.

How is this possible? Because when you use/reference/create/change an object without qualify the schema, by default PostgreSQL will look for it on the public schema. Besides that in PostgreSQL there are a way to even use bar object without need to mention the schema foo by just configuring properly the search_path.

According the documentation nowadays in pREST we always need to reference [DATABASE]/[SCHEMA]/[TABLE] when interact with it, so the idea of redesign is also support the usage of database objects like we do at SQL level. For example:

GET /TABLE?FIELD=$eq.VALUE
GET /SCHEMA/TABLE?FIELD=$eq.VALUE
GET /DATABASE/SCHEMA/TABLE?FIELD=$eq.VALUE

2. Access multiple PostgreSQL servers behind pREST

Nowadays the /DATABASE/ part of the pREST endpoints point direct to a PostgreSQL database inside the cluster. And there are not wrong with this approach, but what if we extend this to use some kind of alias to point to a PostgreSQL server? I mean, today in the TOML file we configure the PostgreSQL backend like the example below:

[pg]
host = "127.0.0.1"
user = "postgres"
pass = "mypass"
port = 5432
database = "prest"

And if we're able to create multiple configurations entries like:

[pg.foo]
host = "10.0.0.1"
user = "postgres"
pass = "mypass"
port = 5432
database = "db1"

[pg.bar]
host = "10.0.0.2"
user = "postgres"
pass = "mypass"
port = 5435
database = "other_database"

So with above configuration we'll be able to query pREST that way:

GET /foo/TABLE?FIELD=$eq.VALUE
GET /foo/SCHEMA/TABLE?FIELD=$eq.VALUE

GET /bar/TABLE?FIELD=$eq.VALUE
GET /bar/SCHEMA/TABLE?FIELD=$eq.VALUE

Of course we should forbid the usage of [pg] and [pg.${ALIAS}] together, because if we define [pg] section will be our Single Database Server Mode, and what I'm proposing where is to have a Multiple Database Server Mode.