This package is abandoned and no longer maintained. No replacement package was suggested.

The Fistlab PHP Database Components

dev-master / 1.0.x-dev 2017-02-13 12:51 UTC

This package is not auto-updated.

Last update: 2020-12-10 13:16:33 UTC


StyleCI Build Status Total Downloads Latest Stable Version Latest Unstable Version License

The Fistlab Database component is a database toolkit, providing an expressive query builder. It currently supports MySQL and SQLite.

Languages: php.


Install using Composer.

composer require fist/database


The constructor accepts an instance of RepositoryInterface from fist/repository.


$db = new \Fist\Database\Database(
    $repository = new Fist\Repository\ArrayRepository([
        'default' => [
            'connection' => 'default',
            'driver' => 'mysql',
        'connections' => [
            'default' => [
                'driver' => 'mysql',
                'hostname' => '',
                'database' => 'database',
                'username' => 'root',
                'password' => '',
        'drivers' => [
            'mysql' => \Fist\Database\Connectors\MysqlConnection::class,

I have made more setup at this gist.


Running raw statements

Raw statements can be ran by using the statement-method.

$db->statement("SELECT * FROM `users` WHERE `username` = 'mark'");

It also takes an optional second argument with parameters to bind. Let's do the same query but by using bindings instead.

$db->statement("SELECT * FROM `users` WHERE `username` = ?", ['mark'])

Selecting all rows

Select all rows from a table using the query builder is quite easy.

$users = $db->table('users')->get();

foreach ($users as $user) {
    echo "Hello ".$user->username;

Select single row

Often you might want to get just a single database row object, like the current logged in user.

This can be done quite easy as well.

$user = $db->table('users')->first();

echo "Hello ".$user->username;

Note that in case of no results. null will be returned. To get an exception instead use the firstOrFail-method.

Select specific columns

Want to select only specific columns, like username, name and age.

$db->table('users')->select(['username', 'name', 'age'])->get();

You can also use aliases for the selected columns, like you want to get name as fullname.

$db->table('users')->select(['username', ['name' => 'fullname'], 'age'])->get();

Where clauses

You can use where clauses to the query builder to filter your results.

Basic where clauses

By default the operator is = for where clauses.

$db->table('users')->where('username', 'mark')->first();
$db->table('users')->where('username', '=', 'mark')->first();

The two methods above will do exactly the same, however you can use a set of other operators.

$db->table('users')->where('username', '!=', 'mark')->first();
$db->table('users')->where('age', '>', 18)->first();
$db->table('users')->where('age', '<', 18)->first();
$db->table('users')->where('age', '>=', 18)->first();
$db->table('users')->where('age', '<=', 18)->first();
$db->table('users')->where('age', 'LIKE', 'ma%')->first();

The default behaviour of the where clauses are all using and for combining.

However you might want to use or for some situations.

    ->where('username', 'mark')
    ->orWhere('username', 'topper')

You mind want to group the where clauses in sub clauses.

    ->where('username', 'mark')
    ->orWhere(function ($query) {
        $query->where('username', 'topper')
            ->orWhere('name', 'Mark Topper')
Where null

Want to use the where clause to filter value from that are not null.

Where not null

Want to use the where clause to filter value from that are null.



You can join additional tables using our joining methods.

Inner join table
    ->join('devices', '', '=', 'devices.user_id')

By default the operator is = for join clauses.
So you can actually use join('devices', '', 'devices.user_id')

Outer join table
    ->outerJoin('devices', '', '=', 'devices.user_id')
Left join table
    ->leftJoin('devices', '', '=', 'devices.user_id')
Right join table
    ->rightJoin('devices', '', '=', 'devices.user_id')
Cross join table
    ->crossJoin('devices', '', '=', 'devices.user_id')
Advanced join clause
    ->join('devices', function ($join) {
        $join->on('', '=', 'devices.user_id')
            ->where('devices.platform', 'ios');

Order results

You can other by a column, while the second argument controls the direction of the sort and may be either asc or desc.

    ->orderBy('name', 'desc')

You can other by multiple columns.

    ->orderBy('fistname', 'desc')
    ->orderBy('lastname', 'desc')
Order by random

Randomize the order


Grouping results

You can group the results.


Limit results (& offset)

Limiting results with an offset are often used, specially when paginating.


Count results

Count rows easily

$users = $db->table('users')->count();

Raw expressions

Sometimes you may need to use a raw expression in a query.

        $db->raw('count(*) as user_count'),

Conditional clauses

Sometimes you might want to only run a certain part of your query when something is true. You may for instance implement and where statement that only applies if a user is logged in.

$currentUserId = 1;
$loggedIn = true;

    ->when($loggedIn, function ($query) {
        $query->where('id', '=', $currentUserId);

Insert rows

The insert method accepts an array of column names and values.

    ['email' => '', 'username' => 'mark'],
    ['email' => '', 'username' => 'john'],

Or you can insert a single row.

    ['email' => '', 'username' => 'mark']
Auto incrementing IDs

Want to insert a row and get the auto incremented ID? You can do this using the insertGetId method.

$id = $db->table('users')->insertGetId(
    ['email' => '', 'username' => 'mark']

Update rows

Update name for the for the user with the username set to mark?

$db->table('user')->where('username', 'mark')->update(['name' => 'Foobar']);

Deleting rows

Deleting rows have never been easier.

$db->table('users')->where('last_login', '<', '2016-01-01 00:00:00')->delete();

If you wish to truncate the entire table, which will remove all rows and reset the auto-incrementing ID to zero, you may use the truncate method.


Connection swapping

Have multiple connections configured you may swap between connections. The default connection is used unless anything else specified.