PHPackages                             dawidgorecki/dbal - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. dawidgorecki/dbal

ActiveLibrary[Database & ORM](/categories/database)

dawidgorecki/dbal
=================

Database abstraction layer (DBAL) for PHP

v2.1.0(7y ago)08MITPHPPHP >=7.1.0

Since Sep 8Pushed 7y agoCompare

[ Source](https://github.com/dawidgorecki/dbal-php)[ Packagist](https://packagist.org/packages/dawidgorecki/dbal)[ Docs](https://github.com/dawidgorecki/dbal-php)[ RSS](/packages/dawidgorecki-dbal/feed)WikiDiscussions master Synced today

READMEChangelog (5)DependenciesVersions (6)Used By (0)

DBAL for PHP
============

[](#dbal-for-php)

[![GitHub (pre-)release](https://camo.githubusercontent.com/5792b913c1f222f60adaf599633688fde0352b25209348e30dd1a9dde245d506/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f72656c656173652f6461776964676f7265636b692f6462616c2d7068702f616c6c2e737667)](https://github.com/dawidgorecki/dbal-php/releases) [![GitHub license](https://camo.githubusercontent.com/2f30bc39ae49cc64ab53e89cc7e9cec7a7a2482ef2351eb20b13bc4616c8b404/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f6461776964676f7265636b692f6462616c2d7068702e737667)](https://github.com/dawidgorecki/dbal-php/blob/master/LICENSE)

Database Abstraction Library for PHP with ActiveRecord features. It based on PDO extension.

Requirements
------------

[](#requirements)

- PHP 7.1.0+
- PDO driver
- Enabled extensions: pdo\_pgsql and/or pdo\_mysql

Installation
------------

[](#installation)

Installation via Composer is the recommended way to install. Add this line to your composer.json file:

```
"dawidgorecki/dbal": "~2.0"

```

or run

```
composer require dawidgorecki/dbal

```

Usage
-----

[](#usage)

### Configuration

[](#configuration)

```
use Reven\DBAL\Configuration\DBConfig;
use Reven\DBAL\Configuration\DSN;

$dsn = new DSN(DSN::DRIVER_PGSQL, 'my_db');
// $dsn = new DSN(DSN::DRIVER_MYSQL, 'my_db', 'localhost', DSN::PORT_MYSQL);

$config = new DBConfig($dsn, 'username', 'passwd');
// $config = new DBConfig($dsn, 'username', 'passwd', 'utf8', true);
```

### Getting connection and DBAL instance

[](#getting-connection-and-dbal-instance)

Using Connection Manager:

```
use Reven\DBAL\ConnectionManager;
use Reven\DBAL\DBALDatabase;
use Reven\DBAL\Exceptions\DBALException;

try {
    ConnectionManager::createConnection($config);
    // ConnectionManager::createConnection($config1, 'db1', PDO::FETCH_ASSOC);
    // ConnectionManager::createConnection($config2, 'db2', PDO::FETCH_OBJ);
} catch (DBALException $e) {
    die($e);
}

$dbal = new DBALDatabase(ConnectionManager::getConnection());
// $dbal = new DBALDatabase(ConnectionManager::getConnection('db1'));
```

Using Database Factory:

```
use Reven\DBAL\DatabaseFactory;
use Reven\DBAL\DBALDatabase;
use Reven\DBAL\Exceptions\DBALException;

try {
    $pdo = DatabaseFactory::getConnection($config, PDO::FETCH_ASSOC);
} catch (DBALException $e) {
    die($e);
}

$dbal = new DBALDatabase($pdo);
```

### Errors & Exceptions

[](#errors--exceptions)

You can turn off all exceptions thrown by DBALDatabase by setting false as second parameter in object constructor.

```
$dbal = new DBALDatabase($pdo, false);
```

Getting error message and query string:

```
$dbal->getLastError();
$dbal->getQueryString();
```

DBAL API
--------

[](#dbal-api)

#### getPDO()

[](#getpdo)

Returns a PDO instance representing a connection to a database

```
$dbal->getPDO();
```

#### startTransaction()

[](#starttransaction)

Initiates a transaction (turns off autocommit mode)

```
$dbal->startTransaction();
```

#### commit()

[](#commit)

Commits a transaction, returning the database connection to autocommit mode

```
$dbal->commit();
```

#### rollback()

[](#rollback)

Rolls back the current transaction

```
$dbal->rollback();
```

#### fetchAll()

[](#fetchall)

Returns all rows of the query result

```
$users = $dbal->fetchAll("SELECT * FROM users");

/*
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Dawid
            [age] => 31
        )
)
*/
```

#### fetchFirst()

[](#fetchfirst)

Returns first row of the query result

```
$user = $dbal->fetchFirst("SELECT * FROM users ORDER BY id", [], PDO::FETCH_ASSOC);

/*
Array
(
    [id] => 1
    [name] => Dawid
    [age] => 31
)
*/
```

#### fetchArray()

[](#fetcharray)

Returns first row of the query result as numeric indexed array

```
$user = $dbal->fetchArray("SELECT * FROM users ORDER BY id");

/*
Array
(
    [0] => 1
    [1] => Dawid
    [2] => 31
)
*/
```

#### fetchAssoc()

[](#fetchassoc)

Returns first row of the query result as associative array

```
$user = $dbal->fetchAssoc("SELECT * FROM users WHERE name = ?", ["Dawid"]);

/*
Array
(
    [id] => 1
    [name] => Dawid
    [age] => 31
)
*/
```

#### fetchColumn()

[](#fetchcolumn)

Returns a single column from the first row of the query result

```
$user = $dbal->fetchColumn("SELECT * FROM users WHERE id = ?", [1], 1);

// Dawid
```

#### delete()

[](#delete)

Deletes rows of a given table

```
$dbal->delete('users', ["id" => 1]);
$dbal->delete('users', ["name" => "Dawid"]);
```

#### insert()

[](#insert)

Inserts a row into the given table

```
$dbal->insert('users', ["name" => "John", "age" => 35]);
```

#### update()

[](#update)

Updates rows of a given table

```
$dbal->update('users', ["name" => "New John", "age" => 40], ["id" => 15]);
```

#### executeQuery()

[](#executequery)

Executes a prepared statement with the given SQL and parameters and returns PDOStatement instance

```
$stmt = $dbal->executeQuery("SELECT * FROM users");

while ($user = $stmt->fetchObject()) {
    print_r($user);
}

/*
stdClass Object
(
    [id] => 1
    [name] => Dawid
    [age] => 31
)
stdClass Object
(
    [id] => 15
    [name] => New John
    [age] => 40
)
*/
```

#### updateQuery()

[](#updatequery)

Executes a prepared statement with the given SQL and parameters and returns the affected rows count

```
$rows_affected = $dbal->updateQuery("DELETE FROM users WHERE name = ?", ["New John"]);
```

#### prepare()

[](#prepare)

Prepare a given SQL statement and return the PDOStatement instance

```
$stmt = $dbal->prepare("SELECT * FROM users WHERE name LIKE 'D%'");
$stmt->execute();

while ($user = $stmt->fetch(PDO::FETCH_NUM)) {
    print_r($user);
}

/*
Array
(
    [0] => 1
    [1] => Dawid
    [2] => 31
)
Array
(
    [0] => 12
    [1] => Dominik
    [2] => 1
)
*/
```

#### quote()

[](#quote)

Quotes a string for use in a query

```
$quoted = $dbal->quote("Hello", PDO::PARAM_STR);
```

#### lastId()

[](#lastid)

Return ID of the last inserted row

```
$last_id = $dbal->lastId();
```

ActiveRecord
------------

[](#activerecord)

Model private properties should have the same names as columns in database table.

### Naming convention

[](#naming-convention)

- `Database Table` - Plural with underscores separating words (e.g., user\_details)
- `Model Class` - Singular with the first letter of each word capitalized (e.g., UserDetail)

### Usage

[](#usage-1)

Extend your model class with `Reven\DBAL\ActiveRecord`

```