how to set the connection with a schema in a Postgresql DB with PDO PHP

Question

If I have a Postgresql DB with several Schemas and I want to set a connection with my Web App to one of this shemas (not public schema by default) of the DB using PDO, what will be the most optimal way?

The connection with the DB I do currently like this:

public static function Conecction(){

        $dbname = "start";
        $host = "localhost";
        $username = "user";
        $password = "123";
        return new PDO("pgsql:dbname=$dbname;host=$host", $username, $password);
    }

But here I just specified the name of the DB not with schema inside of the DB

Thx very much!

Possible duplicated [Best alternative to set a PostgreSQL schema using PHP PDO](https://stackoverflow.com/questions/21714096/best-alternative-to-set-a-postgresql-schema-using-php-pdo) — Marcio Mazzucato, Feb 23 '20 at 04:24

Elias Van Ootegem · Accepted Answer · 2016-01-04T12:22:37.560

To set the default schema, you'll have to execute a query like:

$conn = Foo::Conecction();
$conn->exec('SET search_path TO yourschema');

or, if you want to go about it in a more user-specific way:

$conn->exec('ALTER USER user SET search_path TO yourschema');

As a side-note: Please don't create your PDO instances like that (as the return value of a static method). PDO offers a clean API right out of the box. You're not allowing the caller to determine what DB to connect to, instead, you're hard coding the credentials. This is considered bad practice by any standard. Consider passing the connection to wherever you need it, or -if you insist- create a method that at least requires the caller to pass DB credentials themselves.

I'd also recommend you include the collation in your DSN string, and possibly set some attributes to make life easier when debugging:

$pdo = new PDO(
    "pgsql:dbname=$dbname;host=$host;port=5432;charset=utf8",
    $user,
    $pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,//set PDO to throw exceptions on error
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,//NULL values are returned as PHP null's
    ]
);

Check out other attributes and use accordingly.

Last thing: Some time ago PDO could occasionally run into trouble resolving the localhost host-name. I suspect this bug has been fixed already, but just in case it crops up (or even: hasn't been fixed), I'd recommend you use ip addresses whenever you can. If you know the IP already, there's no real point in having to bother the DNS server with resolving a string that you know will resolve to 127.0.0.1 anyway.

WOW Thx! Just two more thing, 1: how I set the path to my schema. 2: Could you share me a example of the correct way to set the connection with the DB using PDO? THX! — zgrizzly_7, Jan 04 '16 at 12:22
@zgrizzly_7: Not sure what you mean with 1. `ALTER USER username SET search_path TO schema1, schema2, schema3` or `ALTER ROLL some_role SET search_path` or even `ALTER DATABASE start SET search_path TO schema1,schema2` on the PG server directly allows you to do that — Elias Van Ootegem, Jan 04 '16 at 12:26
@zgrizzly_7: It's all in the (admittedly not so user-friendly) [documentation](http://www.postgresql.org/docs/8.3/static/sql-alteruser.html). As for your 2nd question: the last snippet is how I'd create the connection. That's a pretty standard way to create a PDO instance. I wouldn't wrap it in a static method that blindly returns new connections though, but that's up to you how you handle that — Elias Van Ootegem, Jan 04 '16 at 12:31

how to set the connection with a schema in a Postgresql DB with PDO PHP

1 Answers1

Linked