Database

Introduction

Bow makes interacting with databases extremely simple using raw SQL, the fluent query builder, and the Barry ORM.

Currently, Bow supports three databases:

• MySQL
• PostgreSQL
• SQLite

Configuration

Your application's database configuration is located in the config/database.php file. In this file, you can define all of your database connections. Configuration examples are provided for every supported database type.

SQLite Configuration

After creating a new SQLite database using a command such as touch var/database.sqlite, you can easily configure your environment variables (in the .env.json file) to point to this database using the absolute path:

{
  "DB_DEFAULT": "sqlite",
  "SQLITE_DATABASE": "/absolute/path/to/database.sqlite"
}

Multiple Connections

When using multiple connections, you can access each connection via the static connection method of the Bow\Database\Database::class class. The name passed to the method must match one of the connections defined in your config/database.php configuration file:

use Bow\Database\Database;

$users = Database::connection('sqlite')->select(...);

Or via the db helper:

$users = db('sqlite')->select(...);

Once the configuration is changed, it is directly applied to the models' connection. See the ORM documentation for more information about models.

Read & Write Connections

Bow lets you use one database connection for SELECT statements (reads) and another for INSERT, UPDATE, DELETE, and other statements (writes). This is useful when running a primary/replica setup, where writes are sent to the primary server while reads are served by one or more replicas.

To split a connection, add a read key to that connection in your config/database.php file:

'mysql' => [
    'driver' => 'mysql',
    'hostname' => '192.168.1.1',
    'username' => getenv('MYSQL_USER'),
    'password' => getenv('MYSQL_PASSWORD'),
    'database' => getenv('MYSQL_DATABASE'),
    'charset' => getenv('MYSQL_CHARSET'),
    'port' => 3306,
    'read' => [
        'hostname' => '192.168.1.2',
    ],
],

The read array only needs the keys that differ from the main (write) configuration. Every other key — username, password, database, charset, and so on — is inherited from the write configuration, so in practice you usually only override the hostname (or socket) that points to your replica.

When a read key is present, queries are routed automatically:

• Read queries (Database::select, Database::selectOne) are served by the read (replica) connection.
• Write queries (Database::insert, Database::update, Database::delete, Database::statement) are served by the write (primary) connection.

This routing also applies to the query builder and the Barry ORM. If a connection has no read key, reads and writes both use the same connection, exactly as before.

Lazy Connections

Both PDO connections are opened lazily — each one is established only the first time it is actually needed. An application that only reads never opens the write connection, and the other way around. The configuration is still validated up front, so a misconfigured connection fails fast.

Reads Inside a Transaction

Transactions always run on the write (primary) connection. While a transaction is open, read queries are also routed to the primary connection so that they observe the uncommitted changes made within the same transaction. Once the transaction is committed or rolled back, reads return to the replica connection.

Using Raw SQL Queries

Raw queries are SQL queries written directly without going through the Query Builder. In this section, we'll use a table named pets to run our examples.

Here is the structure of the pets table:

CREATE TABLE `pets` (
  id int primary key,
  name varchar(200),
  color varchar(50)
);

Description of the table columns:

Column	Description
id	Primary key of the table
name	Name of the animal
color	Color of the animal

💡 For your information, a pet is a domestic animal.

Running SELECT Queries

To run a raw SELECT query, you should use the Database::select method or the app_db_select helper. Consider our pets table and assume that we are properly connected to the database.

Example to retrieve all the information from the pets table:

use Bow\Database\Database;

$pets = Database::select('select * from `pets`');

Via the app_db_select helper:

$pets = app_db_select('select * from `pets`');

Selecting with Conditions

Here is how to retrieve a specific record from the pets table using a WHERE condition:

use Bow\Database\Database;

$pets = Database::select('select * from `pets` where id = :id', ['id' => 1]);

Via the app_db_select helper:

$pets = app_db_select('select * from `pets` where id = :id', ['id' => 1]);

info

The app_db_select method returns an array of stdClass objects, or an empty array if there is no result. Each element of the array is an object containing the table's columns. For more information about stdClass, see the PHP documentation.

Retrieving a Single Row

When you expect exactly one result, selectOne returns a single object (or null) rather than an array:

use Bow\Database\Database;

$pet = Database::selectOne('select * from `pets` where id = :id', ['id' => 1]);

// Via helper
$pet = app_db_select_one('select * from `pets` where id = :id', ['id' => 1]);

Running INSERT Queries

To run a raw INSERT query, use the Database::insert method or the app_db_insert helper.

Example of inserting a record into the pets table:

use Bow\Database\Database;

$pet = [
  'id' => 1,
  'name' => 'Medor',
  'color' => 'Green'
];

$inserted = Database::insert('insert into `pets` (id, name, color) values (:id, :name, :color);', $pet);

Via the app_db_insert helper:

$pet = [
  'id' => 2,
  'name' => 'Marshmallow',
  'color' => 'White'
];

$inserted = app_db_insert('insert into `pets` (id, name, color) values (:id, :name, :color);', $pet);

info

The app_db_insert method returns an integer representing the number of inserted rows.

Multiple Insertion

You can also insert several records at once.

use Bow\Database\Database;

// List of several animals
$pets = [
  [
    'id' => 1,
    'name' => 'Médor',
    'color' => 'Black'
  ],
  [
    'id' => 2,
    'name' => 'Milou',
    'color' => 'White'
  ]
];

$inserted = Database::insert(
  'insert into `pets` (id, name, color) values (:id, :name, :color);',
  $pets
);

Via the app_db_insert helper:

$updated = app_db_insert(
  'insert into `pets` (id, name, color) values (:id, :name, :color);',
  $pets
);

Running UPDATE Queries

To run a raw UPDATE query, use the Database::update method or the app_db_update helper.

Example of updating a record in the pets table:

use Bow\Database\Database;

$pet = [
  'id' => 1,
  'name' => 'Medora',
  'color' => 'Purple'
];

$updated = Database::update(
  'update `pets` set id = :id, name = :name, color = :color where id = :id',
  $pet
);

Via the app_db_update helper:

$pet = [
  'id' => 2,
  'name' => 'Spark',
  'color' => 'Yellow'
];

$updated = app_db_update(
  'update `pets` set id = :id, name = :name, color = :color where id = :id',
  $pet
);

Running DELETE Queries

To run a raw DELETE query, use the Database::delete method or the app_db_delete helper.

Example of deleting a record from the pets table:

use Bow\Database\Database;

$deleted = Database::delete(
  'delete from `pets` where id = :id',
  ['id' => 1]
);

Via the app_db_delete helper:

$deleted = app_db_delete(
  'delete from `pets` where id = :id',
  ['id' => 2]
);

Running Generic Queries

To run queries other than SELECT, UPDATE, INSERT, or DELETE (such as ALTER TABLE, CREATE TABLE, etc.), use the Database::statement method or the app_db_statement helper.

use Bow\Database\Database;

Database::statement('alter table `pets` add `owner` varchar(80) default null;');

Via the app_db_statement helper:

app_db_statement('alter table `pets` add `owner` varchar(80) default null;');

Database Transactions

Bow offers two transaction styles: automatic (callback) and manual (begin / commit / rollback).

Automatic Transaction with Database::transaction()

Pass your logic in a closure to Database::transaction(): if an exception is thrown, the transaction is rolled back; otherwise it is committed. This is the recommended approach for most cases.

use Bow\Database\Database;

$result = Database::transaction(function () {
  Database::update('update users set votes = :votes', ['votes' => 1]);
  Database::delete('delete from posts');

  return 'ok'; // value returned by transaction()
});

Don't confuse them

Database::startTransaction() does not accept a callback: it is the low-level method that simply starts the transaction. For the callback style, use Database::transaction($callback).

Manual Transaction

For finer control, you can manage the cycle yourself:

use Bow\Database\Database;

Database::startTransaction();

try {
  Database::update('update users set votes = :votes', ['votes' => 1]);
  Database::delete('delete from posts');

  Database::commit();
} catch (\Throwable $e) {
  Database::rollback();
  throw $e;
}

The equivalent helpers:

app_db_transaction();           // starts a transaction (alias of startTransaction)
app_db_commit();                // commits
app_db_rollback();              // rolls back
app_db_transaction_started();   // bool: is a transaction in progress?

Helpers and callbacks

The app_db_transaction(), app_db_commit(), and app_db_rollback() helpers do not accept arguments. For the callback style, call Database::transaction($callback) directly.

Last Inserted Identifier

After an insertion, you can retrieve the last auto-incremented value:

use Bow\Database\Database;

Database::insert('insert into `pets` (name, color) values (:name, :color)', [
  'name'  => 'Médor',
  'color' => 'Black',
]);

$id = Database::lastInsertId();

// For PostgreSQL with a named sequence:
$id = Database::lastInsertId('pets_id_seq');

Query Event

Each time a prepared write query is executed, Bow emits a Bow\Database\QueryEvent event. This is the ideal hook point to log the executed SQL, measure slow queries, or debug during development.

The event exposes three read-only properties:

Property	Type	Description
$event->sql	string	The executed SQL query
$event->execution_time	float	The query execution time, in seconds
$event->bindings	array	The bound values (parameters)

Listening to the Event

Register a listener on the event class via the event() helper. The QueryEvent instance is passed to your callback:

use Bow\Database\QueryEvent;

event()->on(QueryEvent::class, function (QueryEvent $event) {
    logger()->debug($event->sql, $event->bindings);
});

Most often, this listener is declared in the boot() method of a service provider so that it is active across the entire application.

Affected queries

The event is triggered by prepared write queries: Database::insert() (with bound values), Database::update(), and Database::delete() — therefore also by the writes from the Query Builder and the Barry ORM that go through these methods.

It is not emitted by Database::select() / selectOne(), nor by Database::statement() (DDL queries such as CREATE, ALTER, TRUNCATE), nor by an insert() without bound values.

Model events

To react to the operations of a specific model (creating, updated, deleting, etc.) rather than to all SQL queries, use Barry's model events.

SQL Joins

Joins allow you to combine data from multiple tables. Consider the following tables:

create table `authors` (
  `id` int primary key,
  `name` varchar(200)
);

create table `pets` (
  `id` int primary key,
  `name` varchar(200),
  `color` varchar(50),
  `author_id` int default 0
);

To perform a join in BowPHP, open a Query Builder via Database::table() (or the app_db_table() helper), then chain join():

use Bow\Database\Database;

$results = Database::table('pets')
  ->join('authors', 'authors.id', '=', 'pets.author_id')
  ->get();

// Via helper
$results = app_db_table('pets')
  ->join('authors', 'authors.id', '=', 'pets.author_id')
  ->get();

You can add conditions with the WHERE clause to filter the results:

$results = app_db_table('pets')
  ->join('authors', 'authors.id', '=', 'pets.author_id')
  ->where('pets.author_id', 1)
  ->get();

You can chain several joins to combine more than two tables. For example, if we have a countries table containing the owners' countries, and the authors table becomes:

create table `authors` (
  `id` int primary key,
  `name` varchar(200),
  `country_id` int
);

Our query with two joins will be:

$results = app_db_table('pets')
  ->join('authors',    'authors.id',    '=', 'pets.author_id')
  ->join('countries',  'countries.id',  '=', 'authors.country_id')
  ->where('pets.author_id', 1)
  ->get();

note

You can check the API of the Database class for more information.

Is something missing?

If you run into problems with the documentation or have suggestions to improve the documentation or the project in general, please open an issue for us, or send a tweet mentioning the Twitter account @bowframework or directly on github.