PHPackages                             jsl/database - 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. jsl/database

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

jsl/database
============

Expressive Database Layer for PHP - Based on Illuminate/Database

2.6.3(3y ago)01232MITPHPPHP >=5.4.0

Since Oct 23Pushed 3y agoCompare

[ Source](https://github.com/jslphp/database)[ Packagist](https://packagist.org/packages/jsl/database)[ RSS](/packages/jsl-database/feed)WikiDiscussions master Synced 1mo ago

READMEChangelogDependencies (1)Versions (32)Used By (2)

Database
--------

[](#database)

The Database component is a framework agnostic PHP database abstraction layer, providing an expressive query builder. It currently supports MySQL, Postgres, SQL Server, and SQLite.

Features:

- Simple CRUD functions
- Support for Insert Ignore / Replace
- Support for Insert On Duplicate Key Update
- Support for direct `INSERT INTO ... SELECT * FROM` queries
- Buffered inserts from Traversable/Iterator interfaces
- Joins
- Sub Queries
- Nested Queries
- Bulk Inserts
- MySQL `SELECT * INTO OUTFILE '...'`
- MySQL `LOAD DATA INFILE '...'`
- Lazy Connections
- PSR Compatible Logging
- Database Connection Resolver

The component is based on Laravel's Illuminate\\Database and has very familiar syntax. The core Query Builder is mostly compatible. The main alterations are to the composition of the objects, and most significantly the creation and resolution of connections within the ConnectionFactory and ConnectionResolver classes.

### Installation

[](#installation)

```
composer require jsl/database

```

### Basic Example

[](#basic-example)

First, create a new "ConnectionFactory" instance.

```
$factory = new \Database\Connectors\ConnectionFactory();

$connection = $factory->make(array(
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'username'  => 'root',
    'password'  => 'password',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',

    // Don't connect until we execute our first query
    'lazy'      => true,

    // Set PDO attributes after connection
    'options' => array(
        PDO::MYSQL_ATTR_LOCAL_INFILE    => true,
        PDO::ATTR_EMULATE_PREPARES      => true,
    )
));

$connection->query("SELECT id, username FROM customers");
```

Documentation
-------------

[](#documentation)

### Table of Contents

[](#table-of-contents)

- [**Connection**](#connection)
    - [MySQL](#mysql)
    - [SQLite](#sqlite)
    - [Default Connection Options](#default-connection-options)
- [**Connection Resolver**](#connection-resolver)
- [**Raw Queries**](#raw-queries)
    - [Query Shortcuts](#query-shortcuts)
- [**Query Builder**](#query-builder)
- [Selects](#selects)
    - [Get All](#get-all)
    - [Get First Row](#get-first-row)
    - [Find By ID](#find-by-id)
    - [Select Columns](#select-columns)
    - [Limit and Offset](#limit-and-offset)
    - [Where](#where)
    - [Grouped Where](#grouped-where)
    - [Group By, Order By and Having](#group-by-order-by-and-having)
    - [Joins](#joins)
    - [Sub Selects](#sub-selects)
    - [MySQL Outfile](#mysql-outfile)
- [Insert](#insert)
    - [Insert Ignore](#insert-ignore)
    - [Replace](#replace)
    - [Batch Insert](#batch-insert)
    - [On Duplicate Key Update](#on-duplicate-key-update)
    - [Insert Select](#insert-select)
    - [Buffered Iterator Insert](#buffered-iterator-insert)
- [Update](#update)
- [Delete](#delete)
- [Raw Expressions](#raw-expressions)
- [Get SQL](#get-sql-query-and-bindings)
- [Raw PDO Instance](#raw-pdo-instance)
- [Credits](#credits)

Connection
----------

[](#connection)

The Database component supports MySQL, SQLite, SqlServer and PostgreSQL drivers. You can specify the driver during connection and the associated configuration when creating a new connection. You can also create multiple connections, but you can use alias for only one connection at a time.;

```
$factory = new \Database\Connectors\ConnectionFactory();
```

### MySQL

[](#mysql)

```
$connection = $factory->make(array(
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'database'  => 'database', // Optional
    'username'  => 'root',
    'password'  => 'password',
    'charset'   => 'utf8mb4', // Optional: default value if omitted
    'collation' => 'utf8mb4_unicode_ci', // Optional: default value if omitted
));

$connection->fetchAll("SELECT id, username FROM customers");

$connection->table('customers')
	   ->find(12);

$connection->table('customers')
	   ->join('products', 'customer.id', '=', 'customer_id')
	   ->where('favourites', '=', 1)
	   ->get();
```

### SQLite

[](#sqlite)

```
$connection = $factory->make(array(
    'driver'   => 'sqlite',
    'database' => '/path/to/sqlite.db',
    'create'   => true,
));
```

PDO always creates the database if it doesn't exist. Since you won't get any notice if you've entered the wrong path, it can make debugging much harder. Using the connection option `create` let's you decide that behavior. Set it to `true` to let PDO create the database. Setting it to `false` (default) will throw an exception if the database doesn't exist.

### Default Connection Options

[](#default-connection-options)

By default the following PDO attributes will be set on connection. You can override these or add to them in the `options` array parameter in the connection config.

```
PDO::ATTR_CASE              => PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => false,
PDO::ATTR_EMULATE_PREPARES  => false,
```

Connection Resolver
-------------------

[](#connection-resolver)

Many complex applications may need more than one database connection. You can create a set of named connections inside the connection resolver, and reference them by name within in your application.

```
$resolver = new Database\ConnectionResolver(array(
    'local' => array(
        'driver'    => 'mysql',
        'host'      => 'localhost',
        'username'  => 'root',
        'password'  => 'password',
        'charset'   => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
    ),
    'archive' => array(
        'driver'    => 'mysql',
        'host'      => '1.2.3.456',
        'username'  => 'root',
        'password'  => 'password',
        'charset'   => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
    ),
));

$dbLocal = $resolver->connection('local');

// Use it
$dbLocal->table('users')->get();

$dbArchive = $resolver->connection('archive');
// Etc...
```

If you request a connection that you have used previously in your application, the connection resolver will return the same connection, rather than create a new one.

You can set a default connection after creating the resolver, so you don't have to specify the connection name throughout your application.

```
$resolver->setDefaultConnection('local');

// Returns the `local` connection
$resolver->connection();
```

Raw Queries
-----------

[](#raw-queries)

Perform a query, with bindings and return the PDOStatement object

```
$statement = $connection->query('SELECT * FROM users WHERE name = ?', array('John Smith'));

// PDOStatement
$statement->rowCount();
$statement->fetchAll();
```

### Query Shortcuts

[](#query-shortcuts)

```
$firstRow = $connection->fetch('SELECT * FROM users WHERE name = ?', array('John Smith'));

$allRows = $connection->fetchAll('SELECT * FROM users WHERE name = ?', array('John Smith'));

$firstColumnFirstRow = $connection->fetchOne('SELECT COUNT(*) FROM users WHERE name = ?', array('John Smith'));
```

Query Builder
-------------

[](#query-builder)

### Selects

[](#selects)

#### Get PDOStatement

[](#get-pdostatement)

If you intend to iterate through the rows, it may be more efficient to get the PDOStatement

```
$rows = $connection->table('users')->query();
```

#### Get All

[](#get-all)

```
$rows = $connection->table('users')->get();
```

#### Get First Row

[](#get-first-row)

```
$row = $connection->table('users')->first();
```

#### Find By ID

[](#find-by-id)

```
$row = $connection->table('users')->find(6);
```

The query above assumes your table's primary key is `'id'` and you want to retreive all columns. You can specify the columns you want to fetch, and your primary key:

```
$connection->table('users')->find(3, array('user_id', 'name', 'email'), 'user_id');
```

#### Select Columns

[](#select-columns)

```
$rows = $connection->table('users')->select('name')->addSelect('age', 'dob')->get();
```

#### Limit and Offset

[](#limit-and-offset)

```
$connection->table('users')->offset(100)->limit(10);
```

#### Where

[](#where)

```
$connection->table('user')
    ->where('username', '=', 'jsmith')
    ->whereNotIn('age', array(10,20,30))
    ->orWhere('type', '=', 'admin')
    ->orWhereNot('name', 'LIKE', '%Smith%')
    ->get();
```

##### Grouped Where

[](#grouped-where)

```
$connection->table('users')
            ->where('age', '>', 10)
            ->orWhere(function($subWhere)
                {
                    $subWhere
                        ->where('animal', '=', 'dog')
                        ->where('age', '>', 1)
                });

SELECT * FROM `users` WHERE `age` > 10 or (`age` > 1 and `animal` = 'dog')`.
```

#### Group By, Order By and Having

[](#group-by-order-by-and-having)

```
$users = $connection->table('users')
                    ->orderBy('name', 'desc')
                    ->groupBy('count')
                    ->having('count', '>', 100)
                    ->get();
```

#### Joins

[](#joins)

```
$connection->table('users')
    ->join('products', 'user_id', '=', 'users.id')
    ->get();
/*
    ->leftJoin()
    ->rightJoin()
*/
```

##### Multiple Join Criteria

[](#multiple-join-criteria)

If you need more than one criterion to join a table then you can pass a closure as second parameter.

```
->join('products', function($table)
    {
        $table->on('users.id', '=', 'products.user_id');
        $table->on('products.price', '>', 'users.max_price');
    })
```

#### Sub Selects

[](#sub-selects)

```
$query = $connection->table('users')
            ->selectSub(function($subQuery){
            	$subQuery
            	->from('customer')
            	->select('name')
            	->where('id', '=', $subQuery->raw('users.id'));
            }, 'tmp');
```

This will produce a query like this:

```
SELECT (SELECT `name` FROM `customer` WHERE `id` = users.id) as `tmp` FROM `users`

```

#### Aggregates

[](#aggregates)

##### Count

[](#count)

```
$count = $connection->table('users')->count();
```

##### Min

[](#min)

```
$count = $connection->table('users')->min('age');
```

##### Max

[](#max)

```
$count = $connection->table('users')->max('age');
```

##### Average

[](#average)

```
$count = $connection->table('users')->avg('age');
```

##### Sum

[](#sum)

```
$count = $connection->table('users')->sum('age');
```

#### MySQL Outfile

[](#mysql-outfile)

```
$connection
	->table('users')
	->select('*')
	->where('bar', '=', 'baz')
	->intoOutfile('filename', function(\Database\Query\OutfileClause $out){
		$out
		->enclosedBy(".")
		->escapedBy("\\")
		->linesTerminatedBy("\n\r")
		->fieldsTerminatedBy(',');
	})->query();
```

### Insert

[](#insert)

```
$data = array(
    'username' = 'jsmith',
    'name' = 'John Smith'
);
$connection->table('users')->insert($data);
// Returns PDOStatement

`->insertGetId($data)` method returns the insert id instead of a PDOStatement
```

### Insert Ignore

[](#insert-ignore)

Ignore errors from any rows inserted with a duplicate unique key

```
$data = array(
    'username' = 'jsmith',
    'name' = 'John Smith'
);
$connection->table('users')->insertIgnore($data);
```

### Replace

[](#replace)

Replace existing rows with a matching unique key

```
$data = array(
    'username' = 'jsmith',
    'name' = 'John Smith'
);
$connection->table('users')->replace($data);
```

#### Batch Insert

[](#batch-insert)

The query builder will intelligently handle multiple insert rows:

```
$data = array(
	array(
	    'username' = 'jsmith',
	    'name' = 'John Smith'
	),
	array(
	    'username' = 'jbloggs',
	    'name' = 'Joe Bloggs'
	),
);
$connection->table('users')->insert($data);
```

You can also pass bulk inserts to replace() and insertIgnore()

### On Duplicate Key Update

[](#on-duplicate-key-update)

```
$data = array(
    'username' = 'jsmith',
    'name' = 'John Smith'
);

$now = $connection->raw('NOW()');

$connection->table('users')->insertUpdate(
    array('username' => 'jsmith', 'active' => $now), // Insert this data
    array('active' => $now)                          // Or partially update the row if it exists
);

//insertOnDuplicateKeyUpdate() is an alias of insertUpdate
```

#### Insert Select

[](#insert-select)

$connection->table('users')->insertSelect(function($select){ $select->from('admin') ->select('name', 'email') ->where('status', '=', 1);

}, array('name','email'));

`insertIgnoreSelect` and `replaceSelect` methods are supported for the MySQL grammar driver.

#### Buffered Iterator Insert

[](#buffered-iterator-insert)

If you have a large data set you can insert in batches of a chosen size (insert ignore/replace/on duplicate key update supported).

This is especially useful if you want to select large data-sets from one server and insert into another.

```
$pdoStatement = $mainServer->table('users')->query(); // Returns a PDOStatement (which implements the `Traversable` interface)

// Will be inserted in batches of 1000 as it reads from the rowset iterator.
$backupServer->table('users')->buffer(1000)->insertIgnore($pdoStatement);
```

### Update

[](#update)

```
$data = array(
    'username' = 'jsmith123',
    'name' = 'John Smith'
);

$connection->table('users')->where('id', 123)->update($data);
```

### Delete

[](#delete)

```
$connection->table('users')->where('last_active', '>', 12)->delete();
```

Will delete all the rows where id is greater than 5.

### Raw Expressions

[](#raw-expressions)

Wrap raw queries with `$connection->raw()` to bypass query parameter binding. NB use with caution - no sanitisation will take place.

```
$connection->table('users')
            ->select($connection->raw('DATE(activity_time) as activity_date'))
            ->where('user', '=', 123)
            ->get();
```

### Get SQL Query and Bindings

[](#get-sql-query-and-bindings)

```
$query = $connection->table('users')->find(1)->toSql();
$query->toSql();
// SELECT * FROM users where `id` = ?

$query->getBindings();
// array(1)
```

### Raw PDO Instance

[](#raw-pdo-instance)

```
$connection->getPdo();
```

Credits
-------

[](#credits)

This is a fork of [mrjgreen/database](https://github.com/mrjgreen/database)

###  Health Score

31

—

LowBetter than 68% of packages

Maintenance20

Infrequent updates — may be unmaintained

Popularity10

Limited adoption so far

Community12

Small or concentrated contributor base

Maturity69

Established project with proven stability

 Bus Factor1

Top contributor holds 91.4% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~143 days

Recently: every ~634 days

Total

22

Last Release

1223d ago

Major Versions

1.5.0 → 2.0.0-alpha2015-02-08

### Community

Maintainers

![](https://www.gravatar.com/avatar/34fb9c737b7428d30ad05f3b694c2ff60dcf9e448f674835173563e1907de65b?d=identicon)[jsl](/maintainers/jsl)

---

Top Contributors

[![mrjgreen](https://avatars.githubusercontent.com/u/2183391?v=4)](https://github.com/mrjgreen "mrjgreen (106 commits)")[![magnus-eriksson](https://avatars.githubusercontent.com/u/3640297?v=4)](https://github.com/magnus-eriksson "magnus-eriksson (10 commits)")

---

Tags

databasedbalsqlquery builder

### Embed Badge

![Health badge](/badges/jsl-database/health.svg)

```
[![Health](https://phpackages.com/badges/jsl-database/health.svg)](https://phpackages.com/packages/jsl-database)
```

###  Alternatives

[doctrine/dbal

Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management.

9.7k578.4M5.6k](/packages/doctrine-dbal)[cycle/database

DBAL, schema introspection, migration and pagination

64690.9k31](/packages/cycle-database)[usmanhalalit/pixie

A lightweight, expressive, framework agnostic query builder for PHP.

6872.2M15](/packages/usmanhalalit-pixie)[foolz/sphinxql-query-builder

A PHP query builder for SphinxQL and ManticoreQL with MySQLi and PDO drivers.

3232.2M32](/packages/foolz-sphinxql-query-builder)[mrjgreen/database

Expressive Database Layer for PHP - Based on Illuminate/Database

5347.8k10](/packages/mrjgreen-database)[yiisoft/db

Database abstraction layer and query builder

187468.4k30](/packages/yiisoft-db)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)