PHPackages                             thefinaloutpost/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 &amp; ORM](/categories/database)
4. /
5. thefinaloutpost/database

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

thefinaloutpost/database
========================

Flexible and powerful Database abstraction library with a familiar PDO-like API

5.0.0(4y ago)0191MITPHPPHP &gt;=7.2.0

Since Jan 1Pushed 4y agoCompare

[ Source](https://github.com/TheFinalOutpost/database)[ Packagist](https://packagist.org/packages/thefinaloutpost/database)[ Docs](https://cakephp.org)[ RSS](/packages/thefinaloutpost-database/feed)WikiDiscussions master Synced today

READMEChangelog (1)Dependencies (2)Versions (245)Used By (1)

[![Total Downloads](https://camo.githubusercontent.com/4e58183b0d6b55af11a23536d1a78530aba19934c201ff9e0d50c3698a735b66/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f63616b657068702f64617461626173652e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/cakephp/database)[![License](https://camo.githubusercontent.com/942e017bf0672002dd32a857c95d66f28c5900ab541838c6c664442516309c8a/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d4d49542d626c75652e7376673f7374796c653d666c61742d737175617265)](LICENSE.txt)

A flexible and lightweight Database Library for PHP
===================================================

[](#a-flexible-and-lightweight-database-library-for-php)

This library abstracts and provides help with most aspects of dealing with relational databases such as keeping connections to the server, building queries, preventing SQL injections, inspecting and altering schemas, and with debugging and profiling queries sent to the database.

It adopts the API from the native PDO extension in PHP for familiarity, but solves many of the inconsistencies PDO has, while also providing several features that extend PDO's capabilities.

A distinguishing factor of this library when compared to similar database connection packages, is that it takes the concept of "data types" to its core. It lets you work with complex PHP objects or structures that can be passed as query conditions or to be inserted in the database.

The typing system will intelligently convert the PHP structures when passing them to the database, and convert them back when retrieving.

Connecting to the database
--------------------------

[](#connecting-to-the-database)

This library is able to work with the following databases:

- MySQL
- Postgres
- SQLite
- Microsoft SQL Server (2008 and above)

The first thing you need to do when using this library is create a connection object. Before performing any operations with the connection, you need to specify a driver to use:

```
use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
	'database' => 'test',
	'username' => 'root',
	'password' => 'secret'
]);
$connection = new Connection([
	'driver' => $driver
]);
```

Drivers are classes responsible for actually executing the commands to the database and correctly building the SQL according to the database specific dialect. Drivers can also be specified by passing a class name. In that case, include all the connection details directly in the options array:

```
use Cake\Database\Connection;

$connection = new Connection([
	'driver' => Cake\Database\Driver\Sqlite::class,
	'database' => '/path/to/file.db'
]);
```

### Connection options

[](#connection-options)

This is a list of possible options that can be passed when creating a connection:

- `persistent`: Creates a persistent connection
- `host`: The server host
- `database`: The database name
- `username`: Login credential
- `password`: Connection secret
- `encoding`: The connection encoding (or charset)
- `timezone`: The connection timezone or time offset

Using connections
-----------------

[](#using-connections)

After creating a connection, you can immediately interact with the database. You can choose either to use the shorthand methods `execute()`, `insert()`, `update()`, `delete()` or use the `newQuery()` for using a query builder.

The easiest way of executing queries is by using the `execute()` method, it will return a `Cake\Database\StatementInterface` that you can use to get the data back:

```
$statement = $connection->execute('SELECT * FROM articles');

while($row = $statement->fetch('assoc')) {
	echo $row['title'] . PHP_EOL;
}
```

Binding values to parametrized arguments is also possible with the execute function:

```
$statement = $connection->execute('SELECT * FROM articles WHERE id = :id', ['id' => 1], ['id' => 'integer']);
$results = $statement->fetch('assoc');
```

The third parameter is the types the passed values should be converted to when passed to the database. If no types are passed, all arguments will be interpreted as a string.

Alternatively you can construct a statement manually and then fetch rows from it:

```
$statement = $connection->prepare('SELECT * from articles WHERE id != :id');
$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc');
```

The default types that are understood by this library and can be passed to the `bind()` function or to `execute()`are:

- biginteger
- binary
- date
- float
- decimal
- integer
- time
- datetime
- timestamp
- uuid

More types can be added dynamically in a bit.

Statements can be reused by binding new values to the parameters in the query:

```
$statement = $connection->prepare('SELECT * from articles WHERE id = :id');
$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc');

$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc');
```

### Updating Rows

[](#updating-rows)

Updating can be done using the `update()` function in the connection object. In the following example we will update the title of the article with id = 1:

```
$connection->update('articles', ['title' => 'New title'], ['id' => 1]);
```

The concept of data types is central to this library, so you can use the last parameter of the function to specify what types should be used:

```
$connection->update(
	'articles',
	['title' => 'New title'],
	['created >=' => new DateTime('-3 day'), 'created ' => 1]);
```

As you can see you can use any operator by placing it with a space after the field name. Adding multiple conditions is easy as well:

```
$query->where(['id >' => 1])->andWhere(['title' => 'My Title']);

// Equivalent to
$query->where(['id >' => 1, 'title' => 'My title']);
```

It is possible to generate `OR` conditions as well

```
$query->where(['OR' => ['id >' => 1, 'title' => 'My title']]);
```

For even more complex conditions you can use closures and expression objects:

```
$query->where(function ($exp) {
        return $exp
            ->eq('author_id', 2)
            ->eq('published', true)
            ->notEq('spam', true)
            ->gt('view_count', 10);
    });
```

Which results in:

```
SELECT * FROM articles
WHERE
	author_id = 2
	AND published = 1
	AND spam != 1
	AND view_count > 10
```

Combining expressions is also possible:

```
$query->where(function ($exp) {
        $orConditions = $exp->or(['author_id' => 2])
            ->eq('author_id', 5);
        return $exp
            ->not($orConditions)
            ->lte('view_count', 10);
    });
```

That generates:

```
SELECT *
FROM articles
WHERE
	NOT (author_id = 2 OR author_id = 5)
	AND view_count select(['count' => $query->func()->count('*')]);
```

A number of commonly used functions can be created with the func() method:

- `sum()` Calculate a sum. The arguments will be treated as literal values.
- `avg()` Calculate an average. The arguments will be treated as literal values.
- `min()` Calculate the min of a column. The arguments will be treated as literal values.
- `max()` Calculate the max of a column. The arguments will be treated as literal values.
- `count()` Calculate the count. The arguments will be treated as literal values.
- `concat()` Concatenate two values together. The arguments are treated as bound parameters unless marked as literal.
- `coalesce()` Coalesce values. The arguments are treated as bound parameters unless marked as literal.
- `dateDiff()` Get the difference between two dates/times. The arguments are treated as bound parameters unless marked as literal.
- `now()` Take either 'time' or 'date' as an argument allowing you to get either the current time, or current date.

When providing arguments for SQL functions, there are two kinds of parameters you can use, literal arguments and bound parameters. Literal parameters allow you to reference columns or other SQL literals. Bound parameters can be used to safely add user data to SQL functions. For example:

```
$concat = $query->func()->concat([
    'title' => 'literal',
    ' NEW'
]);
$query->select(['title' => $concat]);
```

The above generates:

```
SELECT CONCAT(title, :c0) ...;
```

### Other SQL Clauses

[](#other-sql-clauses)

Read of all other SQL clauses that the builder is capable of generating in the [official API docs](https://api.cakephp.org/4.x/class-Cake.Database.Query.html)

### Getting Results out of a Query

[](#getting-results-out-of-a-query)

Once you’ve made your query, you’ll want to retrieve rows from it. There are a few ways of doing this:

```
// Iterate the query
foreach ($query as $row) {
    // Do stuff.
}

// Get the statement and fetch all results
$results = $query->execute()->fetchAll('assoc');
```

Official API
------------

[](#official-api)

You can read the official [official API docs](https://api.cakephp.org/4.x/namespace-Cake.Database.html) to learn more of what this library has to offer.

###  Health Score

33

—

LowBetter than 77% of packages

Maintenance20

Infrequent updates — may be unmaintained

Popularity6

Limited adoption so far

Community21

Small or concentrated contributor base

Maturity78

Established project with proven stability

 Bus Factor2

2 contributors hold 50%+ of commits

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 ~10 days

Recently: every ~1 days

Total

239

Last Release

1553d ago

Major Versions

3.9.7 → 4.2.42021-02-25

3.9.9 → 4.2.62021-04-07

3.10.0 → 4.2.82021-05-24

3.x-dev → 4.3.02021-10-23

4.x-dev → 5.x-dev2021-12-20

PHP version history (3 changes)3.4.8PHP &gt;=5.6.0

4.0.0-beta1PHP &gt;=7.2.0

3.9.5PHP &gt;=5.6.0,&lt;8.0.0

### Community

Maintainers

![](https://www.gravatar.com/avatar/1e7e4dbc420c782f43fdafa17331eab9471f8e9fcd121651e4bd9ba2aa887fe2?d=identicon)[Corteo](/maintainers/Corteo)

---

Top Contributors

[![markstory](https://avatars.githubusercontent.com/u/24086?v=4)](https://github.com/markstory "markstory (591 commits)")[![ADmad](https://avatars.githubusercontent.com/u/142658?v=4)](https://github.com/ADmad "ADmad (330 commits)")[![lorenzo](https://avatars.githubusercontent.com/u/37621?v=4)](https://github.com/lorenzo "lorenzo (288 commits)")[![othercorey](https://avatars.githubusercontent.com/u/24221186?v=4)](https://github.com/othercorey "othercorey (128 commits)")[![dereuromark](https://avatars.githubusercontent.com/u/39854?v=4)](https://github.com/dereuromark "dereuromark (100 commits)")[![antograssiot](https://avatars.githubusercontent.com/u/4977112?v=4)](https://github.com/antograssiot "antograssiot (30 commits)")[![HavokInspiration](https://avatars.githubusercontent.com/u/5243386?v=4)](https://github.com/HavokInspiration "HavokInspiration (27 commits)")[![bcrowe](https://avatars.githubusercontent.com/u/752603?v=4)](https://github.com/bcrowe "bcrowe (23 commits)")[![ravage84](https://avatars.githubusercontent.com/u/625761?v=4)](https://github.com/ravage84 "ravage84 (16 commits)")[![chinpei215](https://avatars.githubusercontent.com/u/7399393?v=4)](https://github.com/chinpei215 "chinpei215 (14 commits)")[![ndm2](https://avatars.githubusercontent.com/u/5031606?v=4)](https://github.com/ndm2 "ndm2 (14 commits)")[![burzum](https://avatars.githubusercontent.com/u/162789?v=4)](https://github.com/burzum "burzum (13 commits)")[![jrbasso](https://avatars.githubusercontent.com/u/26548?v=4)](https://github.com/jrbasso "jrbasso (10 commits)")[![Theaxiom](https://avatars.githubusercontent.com/u/57013?v=4)](https://github.com/Theaxiom "Theaxiom (9 commits)")[![stickler-ci](https://avatars.githubusercontent.com/u/16011037?v=4)](https://github.com/stickler-ci "stickler-ci (8 commits)")[![bancer](https://avatars.githubusercontent.com/u/3830106?v=4)](https://github.com/bancer "bancer (8 commits)")[![tigrang](https://avatars.githubusercontent.com/u/921680?v=4)](https://github.com/tigrang "tigrang (7 commits)")[![garas](https://avatars.githubusercontent.com/u/2265694?v=4)](https://github.com/garas "garas (7 commits)")[![skie](https://avatars.githubusercontent.com/u/130799?v=4)](https://github.com/skie "skie (6 commits)")[![saeideng](https://avatars.githubusercontent.com/u/8215801?v=4)](https://github.com/saeideng "saeideng (5 commits)")

---

Tags

abstractiondatabasepdocakephpdatabase abstraction

### Embed Badge

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

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

###  Alternatives

[doctrine/dbal

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

9.7k570.0M5.0k](/packages/doctrine-dbal)[cakephp/database

Flexible and powerful Database abstraction library with a familiar PDO-like API

9724.0M27](/packages/cakephp-database)[ezsql/ezsql

Advance database access library. Make interacting with a database ridiculously easy. An universal interchangeable CRUD system.

86845.9k](/packages/ezsql-ezsql)[jv2222/ezsql

Advance database access library. Make interacting with a database ridiculously easy. An universal interchangeable CRUD system.

86911.3k1](/packages/jv2222-ezsql)

PHPackages © 2026

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