Hazaar DBI Configuration

About 4 min

Hazaar DBI Configuration

Hazaar provides a simple and easy to use database abstraction layer that allows you to interact with a variety of database systems in a consistent way. The database abstraction layer is built on top of the PDO extension and provides a simple and easy to use interface for interacting with databases.

Configuring a Database Connection

The recommended way to configure a database connection is with the dbitool interactive wizard. From your application root, run:

dbitool configure

The wizard will:

Detect whether a configuration already exists for the current environment and ask before overwriting it.
Present a numbered list of supported database drivers to choose from.
Prompt for the connection parameters relevant to the selected driver.
Write (or update) configs/database.php automatically, preserving any other environment blocks already in the file.

Supported drivers

#	Driver	Parameters collected
1	PostgreSQL	host, port, database name, username, password
2	MySQL	host, port, database name, username, password
3	SQLite	path to database file (defaults to `.runtime/database.db`)

Example session

$ dbitool configure

DBI Adapter Configuration Wizard

Select the database type:
  1. PostgreSQL
  2. MySQL
  3. SQLite

Enter number: 1

Configuring PostgreSQL adapter...

Host [localhost]: db.example.com
Port [5432]:
Database name: my_database
Username: my_user
Password:

Configuration written to: /path/to/app/configs/database.php

Running for a specific environment

dbitool uses the active APPLICATION_ENV value (defaulting to development). Pass the -e flag to target a different environment:

dbitool configure -e production

Manual Configuration

If you prefer to create the configuration file by hand, add a database.php file to your application's configs/ directory. The file must return an array keyed by environment name:

PHP

<?php
return [
    'development' => [
        'type' => 'pgsql',
        'host' => 'localhost',
        'dbname' => 'my_database',
        'user' => 'my_user',
        'password' => 'my_password',
    ],
    'test' => [
        'type' => 'sqlite',
        'file' => 'test.db',
    ],
];

JSON

{
    "development": {
        "type": "pgsql",
        "host": "localhost",
        "dbname": "my_database",
        "user": "my_user",
        "password": "my_password"
    },
    "test": {
        "type": "sqlite",
        "file": "test.db"
    }
}

INI

[development]
type = pgsql
host = localhost
dbname = my_database
user = my_user
password = my_password

[test]
type = sqlite
file = test.db

Configuration Options

The configuration keys available for each connection depend on the PDO driver you choose. Hazaar uses the driver-specific PDO options to build the DSN, so only keys supported by that driver are used for the connection string. For example, a PostgreSQL connection uses values like host, port, dbname, user, and password, while SQLite uses a local database path.

Alongside DSN keys, there are additional optional settings you can use in the connection config:

type selects the PDO driver (for example, pgsql, sqlite).
options allows you to pass PDO attributes (keys should match PDO:: constants like ATTR_ERRMODE).
schema sets the default schema name for drivers that support schemas (see Working with Schemas).
timezone sets the session timezone after connecting.
encrypt enables transparent table/column encryption (see the DBI encryption docs).
master enables PostgreSQL replication support for read/write splitting.

The database configuration file can contain multiple database configurations. The default configuration to use is determined by the current APPLICATION_ENV environment variable. If the APPLICATION_ENV environment variable is not set, the default configuration is development.

$db = Hazaar\DBI\Adapter::create();

Alternatively, you can specify the configuration to use by passing the name of the configuration to Hazaar\DBI\Adapter::create().

$db = Hazaar\DBI\Adapter::create('test');

Lastly, you can also specify the configuration as an array. This is useful for quick testing or when you don't want to use a configuration file but is not recommended for production use.

$db = Hazaar\DBI\Adapter::create(array(
    'type' => 'pgsql',
    'host' => 'localhost',
    'dbname' => 'test',
    'user' => 'test',
    'password' => 'test'
));

Working with Schemas

PostgreSQL groups tables into schemas (namespaces). By default every object lives in the public schema, but you can keep an application's tables in a schema of their own by setting the schema key in the connection config:

'development' => [
    'type' => 'pgsql',
    'host' => 'localhost',
    'dbname' => 'my_database',
    'user' => 'my_user',
    'password' => 'my_password',
    'schema' => 'my_app',
],

What `schema` does

When a non-public schema is configured, the PostgreSQL driver sets the connection's search_path on connect:

SET search_path TO "my_app", "public"

From then on, unqualified table names resolve against the configured schema first, falling back to public (so shared objects such as extension functions installed there stay reachable). This applies to everything running on that connection:

SQL built through the query builder ($db->table('widget')->find(...))
Raw SQL you write yourself ($db->query('SELECT * FROM widget'))

Because table names are not prefixed with the schema in generated SQL, hand-written SQL and builder-generated SQL behave identically, and you can change the schema name in config without touching a single line of query code. The schema manager (dbitool) also creates the schema for you automatically the first time it runs migrations against a fresh database.

Tips

The schema value defaults to public. Leave it unset unless you specifically want your application to live in its own schema.

Cross-schema references

The whole point of search_path is that you rarely need to name a schema at all. The one time you do is a cross-schema query — reaching a table that lives outside the configured schema. The preferred way is to pass the table name as a [schema, table] array, which lets the schema come from application config instead of being hard-coded into your query:

// Reach the "billing" schema explicitly.
$db->table(['billing', 'invoices'])->find(['id' => $id]);

// The schema can come from config so it isn't hard-coded:
$reporting = $this->config['reportingSchema']; // e.g. 'reporting'
$db->table([$reporting, 'daily_totals'])->find();

// Joins accept the same array form for the joined table:
$db->table('widget')
    ->join(['billing', 'invoices'], ['invoices.widget_id' => 'widget.id'], 'inv')
    ->find();

The array form is equivalent to a dotted "schema"."table" string ($db->table('billing.invoices')), but keeps the schema name out of your string literals so it can be injected from config — which is exactly the coupling the search_path approach is designed to avoid.

Connection pooling caveat

SET search_path is a per-session setting. Hazaar issues it on every connection it opens, so this is transparent for normal use. However, if you put a connection pooler such as PgBouncer in front of PostgreSQL in transaction or statement pooling mode, the session-level search_path can be reset between statements, and unqualified names may then resolve against public instead of your schema. If you pool connections, either:

use session pooling mode (which preserves session settings), or
set the search path at the role level so it applies regardless of the session:
```
ALTER ROLE my_user SET search_path TO my_app, public;
```

PostgreSQL Replication

If your database host is running PostgreSQL 9.0+ replication then Hazaar has some extra magic for you. It's possible to use a read-only slave for most queries and then have Hazaar's database adapter automatically send all write operations to the master. Without the application knowing, or caring.

To achieve this, all you need to do is add the following to your database.php file:

'master' => 'master-db-host',

Add this parameter to the relevant environment configuration in your database.php file and the Hazaar DB adapter will take care of the rest.

How does it work?

Basically, if the db.master parameter is set, then the adapter knows to check if the db.host is a slave by executing thePGSQL specific query:

SELECT pg_is_in_recovery()

This query indicates that the host is in recovery mode, meaning it is a replication slave. If this is true, then the adapter will create a second connection using the main connection parameters but switches out the host parameter with the value in db.master.

After that, any write operations will use the second connection which will write to the master.