Database in BowPHP

• Introduction
• Configuration
• SQLite Configuration
• Connecting to multiple databases
• Using raw SQL queries
  • Running a Select query
  • Conditional selection
  • Running an Insert query
    • Multiple inserts
  • Running an Update query
  • Running a Delete query
  • Running a query
• Database Transactions
  • Manual use of transactions
• Joins

Introduction

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

Currently, Bow supports two databases:

• MySQL
• 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, and an example is 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 newly created database by using the database's absolute path:

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

Connecting to multiple databases

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

use Bow\Database\Database;

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

Or via the db helper:

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

Once the configuration is changed, it is applied directly to the connection used by models. Click here for more information about models.

Using raw SQL queries

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

Here is the description of the pets table:

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

So our table has the following columns:

Column name	Description
id	The primary key
name	The name of the pet
color	The color of the pet

For your information, a pet is a domestic animal

Running a Select query

To run a raw SELECT query you'll need to use the Database::select method or the select helper. We'll consider our pets table and assume that we are properly connected to the database.

Running a query to get all the information from the pets table:

use Bow\Database\Database;

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

Via the db_select helper:

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

Conditional selection

Running a query to get all the information from the pets table where id equals 1:

use Bow\Database\Database;

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

Via the db_select helper:

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

Note that the value returned by the db_select method is an array, or null if there is no information. When it is an array, the contents are of type stClass (more information on stClass).

Running an Insert query

To run a raw INSERT query you'll need to use the Database::insert method or the insert helper. We'll still consider our pets table and assume that we are properly connected to the database.

Running a query to insert 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 db_insert helper:

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

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

Note that the value returned by the insert method is an int or number, which is the number of inserted records.

Multiple inserts

You also have the ability to insert multiple records at the same time.

use Bow\Database\Database;

// List of pets
$pets = [
  [
    'id' => 1,
    'name' => 'Medor',
    'color' => 'Black'
  ],
  [
    'id' => 2,
    'name' => 'Milou',
    'color' => 'Gay'
  ]
];

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

Via the db_insert helper:

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

Running an Update query

To run a raw UPDATE query you'll need to use the Database::update method or the db_update helper. We'll still consider our pets table and assume that we are properly connected to the database.

Running a query to update 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 db_update helper:

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

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

Running a Delete query

To run a raw DELETE query you'll need to use the Database::delete method or the db_delete helper. We'll still consider our pets table and assume that we are properly connected to the database.

Running a query to delete a record from the pets table:

use Bow\Database\Database;

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

Via the db_delete helper:

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

Running a query

To run a raw query other than SELECT, UPDATE, INSERT, DELETE, there is a dedicated method Database::statement or the db_statement helper.

use Bow\Database\Database;

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

Via the db_statement helper:

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

Database Transactions

You can use the startTransaction method on the Database class to run a set of operations within a database transaction.

If you pass a Closure and an exception is thrown within the transaction's callback, the transaction will be automatically rolled back. If the Closure executes successfully, the transaction will be automatically committed. You don't have to worry about manually rolling back or committing when using the transaction method:

use Bow\Database\Database;

Database::startTransaction(function () {
  Database::update('update users set votes = :votes', ['votes' => 1]);

  Database::delete('delete from posts');
});

Via the db_transaction helper:

db_transaction(function () {
  update('update users set votes = :votes', ['votes' => 1]);

  delete('delete from posts');
});

Manual use of transactions

You can also use the transaction system manually. To start a transaction with the method:

use Bow\Database\Database;

Database::startTransaction();
// Or
db_transaction();

You can roll back the transaction with the method:

use Bow\Database\Database;

Database::rollback();
// Or
db_rollback();

You can commit the transaction with the method:

use Bow\Database\Database;

Database::commit();
// Or
db_commit();

With the inTransaction method you can check whether the database is in a transaction:

use Bow\Database\Database;

Database::inTransaction();
// Or
db_transaction_started();

Joins

Consider the following tables:

create table `author` (
  `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
);

Performing a join in BowPHP is actually very simple. To do so, we use the join method.

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

Often it's useful to add constraints to the query as usual with the WHERE clause.

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

You can of course add several joins within the method call. For the example, let's say there is another table named countries that holds the owner's country, and that the authors table is now:

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

Our query will now be:

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

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.