database-basics.md

Database Basics

Table of Contents

1. Introduction
2. Configuration
3. Connection Pools
4. Single-Server Connection Pool
5. Master-Slave Connection Pool
6. Read/Write Connections
7. How to Query and Fetch Results
8. Binding Values
9. Binding Named Placeholders
10. Binding Unnamed Placeholders
11. Binding Multiple Values

Introduction

Relational databases store information about data and how it's related to other data. Opulence provides classes and methods for connecting to relational databases and querying them for data. It does this by extending PDO and PDOStatement to give users a familiar interface to work with. PDO is a powerful wrapper for database interactions, and comes with built-in tools to prevent SQL injection.

Configuration

Opulence supports a variety of drivers. If you're using the skeleton project, the PostgreSQL driver is enabled by default. To change drivers (eg MySQL), update the DB_DRIVER value in config/environment/.env.app.php.

Connection Pools

Connection pools help you manage your database connections by doing all the dirty work for you. You can use an assortment of PHP drivers to connect to multiple types of server configurations. For example, if you have a single database server in your stack, you can use a SingleServerConnectionPool. If you have a master/slave(s) setup, you can use a MasterSlaveConnectionPool.

Single-Server Connection Pool

Single-server connection pools are useful for single-database server stacks, eg not master-slave setups.

use Opulence\Databases\Adapters\Pdo\MySql\Driver;
use Opulence\Databases\ConnectionPools\SingleServerConnectionPool;
use Opulence\Databases\Server;

$connectionPool = new SingleServerConnectionPool(
    new Driver(), // The driver to use
    new Server(
        'localhost', // The host
        'username', // The server username
        'password', // The server password
        'databasename', // The name of the database to use
        3306 // The port
    ),
    [], // Any connection settings, eg "unix_socket" for MySQL Unix sockets
    [] // Any driver-specific connection settings, eg \PDO::ATTR_PERSISTENT => true
);
$readConnection = $connectionPool->getReadConnection();
// The next part should be familiar to people that have used PDO
$statement = $readConnection->prepare('SELECT name FROM users WHERE id = :id');
$statement->bindValue('id', 1234, \PDO::PARAM_INT);
$statement->execute();
$row = $statement->fetch(\PDO::FETCH_ASSOC);
// This will contain the user's name whose Id is 1234
$name = $row['name'];

Master-Slave Connection Pool

Master-slave connection pools are useful for setups that include a master and at least one slave server. Instead of taking a single server in their constructors, they take a master server and an array of slave servers.

use Opulence\Databases\Adapters\Pdo\PostgreSql\Driver;
use Opulence\Databases\ConnectionPools\MasterSlaveConnectionPool;
use Opulence\Databases\Server;

$connectionPool = new MasterSlaveConnectionPool(
    new Driver(), // The driver to use
    new Server(
        '127.0.0.1', // The master host
        'username', // The master username
        'password', // The master password
        'databasename', // The name of the database to use
        3306 // The master port
    ),
    [
        // List any slave servers
        new Server(
            '127.0.0.2', // The slave host
            'username', // The slave username
            'password', // The slave password
            'databasename', // The name of the database to use
            3306 // The slave port
        )
    ],
    [], // Any connection settings, eg "unix_socket" for MySQL Unix sockets
    [] // Any driver-specific connection settings, eg \PDO::ATTR_PERSISTENT => true
);

Slave Server Selection Strategies

In most master-slave setups, you select a slave to connect by picking a random slave. However, you can create your own strategy to pick slaves by implementing IServerSelectionStrategy. Then, pass it into the MasterSlaveConnectionPool constructor:

$connectionPool = new MasterSlaveConnectionPool(
    $driver,
    $masterServer,
    [
        $slaveServer1,
        $slaveServer2
    ],
    [],
    [],
    new MyStrategy()
);

Note: If no selection strategy is specified, RandomServerSelectionStrategy is used.

Read/Write Connections

To read from the database, simply use the connection returned by $connectionPool->getReadConnection(). Similarly, $connectionPool->getWriteConnection() will return a connection to use for write queries. These two methods take care of figuring out which server to connect to. If you want to specify a server to connect to, you can pass it in as a parameter to either of these methods.

How to Query and Fetch Results

Opulence uses the exact same methods as PDO to query and fetch results. To learn how to query using PDO, try the official PHP documentation.

Opulence's PDO wrappers make it easy to connect to the database without having to remember things like how to format the DSN. Opulence also provides type mappers for easy conversion between a database vendor's data types and PHP data types. They even provide support for nested database transactions.

Binding Values

Most database queries use a dynamic variable to filter results. The unsafe method would be to put it directly in the string:

$id = 24;
$query = "SELECT email FROM users WHERE id = $id";

The issue here is what's called SQL injection. What would happen if a malicious user input "1 OR 1=1" into the query above? We'd get:

'SELECT email FROM users WHERE id = 1 OR 1=1'

See the issue there? The malicious user just tricked your application into returning the email address for every user. This is where prepared statements and binding comes in handy. Instead of just concatenating your value into the query, PDO will automatically escape the data before using it in the query.

Note: For data binding to work properly, it is imperative that you include the type of the parameter being bound, eg \PDO::PARAM_INT or \PDO::PARAM_BOOL.

Binding Named Placeholders

It's convenient to name placeholders that you'll bind to in a query so that you can reference them by name:

$id = 24;
$statement = $connection->prepare('SELECT title FROM posts WHERE id = :id');
$statement->bindValue('id', $id, \PDO::PARAM_INT);
$statement->execute();

Binding Unnamed Placeholders

It's also possible to bind to unnamed placeholders in the case that the number of parameters you're binding is dynamic:

$statement = $connection->prepare('SELECT title FROM posts WHERE id = ?');
// Unnamed placeholders are 1-indexed
$statement->bindValue(1, $id, \PDO::PARAM_INT);
$statement->execute();

Binding Multiple Values

PDOStatement has a bindValue() method, but it does not natively support binding multiple values at once. Opulence's extension of PDOStatement does:

$statement->bindValues([
    // By default, values are interpreted as type \PDO::PARAM_STR
    'name' => 'Dave',
    // To bind a non-string type to a value, use an array
    // The first item is the value, and the second is the parameter type
    'id' => [23, \PDO::PARAM_INT]
]);

You can also bind unnamed placeholder values:

$statement->bindValues([
    'Boeing',
    [727, \PDO::PARAM_INT]
]);