Database

NaN provides its own database abstraction based around PHP’s PDO, under the NaN\Database namespace. For databases not supported by PDO you’ll have to write your own PDO-based driver.

Installation

composer require aimfornan/nan-db

Connect to a database

The DSN takes either an array or a string. The configuration must match the parameters of the \PDO::connect method.

MySQL example

use NaN\Database\Connection;

/** @var \Nan\Database\Interfaces\ConnectionInterface $mysql_connection */
$mysql_connection = Connection::connect([
    'dsn' => [
        'prefix' => 'mysql'
        'host' => 'localhost',
        'port' => 3306,
        'charset' => 'utf8mb4',
    ],
    'username' => 'username',
    'password' => 'password',
    'options' => [ // PDO options!
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_SILENT,
    ],
]);

SQLite example

use NaN\Database\Connection;

/** @var \Nan\Database\Interfaces\ConnectionInterface $sqlite_connection */
$sqlite_connection = Connection::connect([
    'dsn' => 'sqlite::memory:',
]);

Queries

Each connection supports executing raw queries.

/** @var \Nan\Database\Interfaces\ConnectionInterface $connection */
$connection->raw(/* raw query string */);

The raw method of the connection accepts an optional, second parameter. The second parameter is used to bind values for prepared statements. An empty array will not induce a prepared statement.

Prepared statement

/** @var \Nan\Database\Connection $sql_connection */
$sql_connection->raw('SELECT FROM `users` WHERE `id` = ?', [100]);

Regular statement

/** @var \Nan\Database\Connection $sql_connection */
$sql_connection->raw('SELECT FROM `users` WHERE `id` = 100');

Query builders

SQL

Basic SQL support falls under the NaN\Database\Sql namespace.

use NaN\Database\Sql\Query\Builders\SqlQueryBuilder;

/** @var \NaN\Database\Query\Builders\Interfaces\QueryBuilderInterface $query_builder */
$query_builder = new SqlQueryBuilder();

Query builders provide a higher-level API to constructing queries. Query builders support typical CRUD operations via the patch, pull, purge, and push methods.

CRUD operation	NaN operation	SQL equivalent
CREATE	PUSH	INSERT
RETRIEVE	PULL	SELECT
UPDATE	PATCH	UPDATE
DELETE	PURGE	DELETE

How these operate exactly are left up to the implementation.

Executing statements

/**
 * @var \NaN\Database\Interfaces\ConnectionInterface $connection
 * @var \NaN\Database\Query\Builders\Interfaces\QueryBuilderInterface $query_builder
 * @var \NaN\Database\Query\Statements\Interfaces\StatementInterface $pull_statement
 */
$pull_statement = $query_builder->pull();

$pull_statement->exec($connection);

Bindings are handled automatically. As with raw statements, if the bindings are empty, then a regular query will be used instead of a prepared statement.

Retrieving results

In both exec and raw cases, a \PDOStatement object is returned if the query succeeds, and false if it fails. Depending on the PDO configuration, it may also throw an exception.

exec

/**
 * @var \NaN\Database\Interfaces\ConnectionInterface $connection
 * @var \NaN\Database\Query\Statements\Interfaces\StatementInterface $query
 * @var \PDOStatement|false $pdo_statement
 */
$pdo_statement = $query->exec($connection);

raw

/**
 * @var \NaN\Database\Interfaces\ConnectionInterface $connection
 * @var \PDOStatement|false $pdo_statement
 */
$pdo_statement = $connection->raw(/* raw query string */);

Patch

use \NaN\Database\Sql\Query\Statements\Clauses\WhereClause;

/**
 * @var \NaN\Database\Query\Builders\Interfaces\QueryBuilderInterface $query_builder
 * @var \NaN\Database\Sql\Query\Statements\UpdateStatement $stmt
 */
$stmt = $query_builder
    ->patch('users')
    ->with([
        'display_name' => 'NaN',
    ])
    ->where(function (WhereClause $where) {
        $where->is('id', '=', 1);
    })
;

Pull

/**
 * @var \NaN\Database\Query\Builders\Interfaces\QueryBuilderInterface $query_builder
 * @var \NaN\Database\Sql\Query\Statements\SelectStatement $stmt
 */
$stmt = $query_builder
    ->pull()
    ->from('users')
    ->last('id')
;

Purge

use \NaN\Database\Sql\Query\Statements\Clauses\WhereClause;

/**
 * @var \NaN\Database\Query\Builders\Interfaces\QueryBuilderInterface $query_builder
 * @var \NaN\Database\Sql\Query\Statements\DeleteStatement $stmt
 */
$stmt = $query_builder
    ->purge('users')
    ->where(function (WhereClause $where) {
        $where->is('id', '=', 1);
    })
;

Push

/**
 * @var \NaN\Database\Query\Builders\Interfaces\QueryBuilderInterface $query_builder
 * @var \NaN\Database\Sql\Query\Statements\InsertStatement $stmt
 */
$stmt = $query_builder
    ->push([
        'foo' => 'bar',
    ])
    ->into('users')
;