PHPackages                             elvanto/picodb - 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. elvanto/picodb

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

elvanto/picodb
==============

Minimalist database query builder

6.1.1(6mo ago)3341.7k—8.8%12[3 PRs](https://github.com/elvanto/picodb/pulls)1MITPHPPHP >=8.0CI passing

Since Jul 31Pushed 1mo ago4 watchersCompare

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

READMEChangelog (10)Dependencies (1)Versions (24)Used By (1)

PicoDb
======

[](#picodb)

PicoDb is a minimalist database query builder for PHP.

[![Build Status](https://camo.githubusercontent.com/7ba5489e084c0484a21e806ed27ec2f1a1902e14799d926ac1bae4fbc2fb0e57/68747470733a2f2f7472617669732d63692e6f72672f656c76616e746f2f7069636f64622e7376673f6272616e63683d6d6173746572)](https://travis-ci.org/elvanto/picodb)

Features
--------

[](#features)

- Easy to use, easy to hack, fast and very lightweight
- Supported drivers: Sqlite, Mssql, Mysql, Postgresql
- Requires only PDO
- Use prepared statements
- Handle schema migrations
- Fully unit tested on PHP 8+
- License: MIT

Requirements
------------

[](#requirements)

- PHP >= 8.0
- PDO extension
- Sqlite, Mssql, Mysql or Postgresql

Author
------

[](#author)

Frédéric Guillot

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

[](#documentation)

### Installation

[](#installation)

```
composer require elvanto/picodb
```

### Database connection

[](#database-connection)

#### Sqlite:

[](#sqlite)

```
use PicoDb\Database;

// Sqlite driver
$db = new Database(['driver' => 'sqlite', 'filename' => ':memory:']);
```

The Sqlite driver enable foreign keys by default.

#### Microsoft SQL server:

[](#microsoft-sql-server)

```
// Optional attributes:
// "schema_table" (the default table name is "schema_version")

$db = new Database([
    'driver' => 'mssql',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'my_db_name',
]);
```

Optional attributes:

- schema\_table

#### Mysql:

[](#mysql)

```
$db = new Database([
    'driver' => 'mysql',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'my_db_name',
    'ssl_key' => '/path/to/client-key.pem',
    'ssl_cert' => '/path/to/client-cert.pem',
    'ssl_ca' => '/path/to/ca-cert.pem',
]);
```

Optional attributes:

- charset
- schema\_table
- port
- ssl\_key
- ssl\_cert
- persistent
- timeout
- verify\_server\_cert
- case

#### Postgres:

[](#postgres)

```
$db = new Database([
    'driver' => 'postgres',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'my_db_name',
]);
```

Optional attributes:

- port
- schema\_table

#### Connecting from an environment variable:

[](#connecting-from-an-environment-variable)

Let's say you have defined an environment variable:

```
export DATABASE_URL=postgres://user:pass@hostname:6212/db
```

PicoDb can parse automatically this URL for you:

```
use PicoDb\UrlParser;
use PicoDb\Database;

$db = new Database(UrlParser::getInstance()->getSettings());
```

#### Connecting from a URL

[](#connecting-from-a-url)

```
use PicoDb\UrlParser;
use PicoDb\Database;

$db = new Database(UrlParser::getInstance()->getSettings('postgres://user:pass@hostname:6212/db'));
```

### Execute any SQL query

[](#execute-any-sql-query)

```
$db->execute('CREATE TABLE mytable (column1 TEXT)');
```

- Returns a `PDOStatement` if successful
- Returns `false` if there is a duplicate key error
- Throws a `SQLException` for other errors

### Insertion

[](#insertion)

```
$db->table('mytable')->save(['column1' => 'test']);
```

or

```
$db->table('mytable')->insert(['column1' => 'test']);
```

### Fetch last inserted id

[](#fetch-last-inserted-id)

```
$db->getLastId();
```

### Transactions

[](#transactions)

```
$db->transaction(function ($db) {
    $db->table('mytable')->save(['column1' => 'foo']);
    $db->table('mytable')->save(['column1' => 'bar']);
});
```

- Returns `true` if the callback returns null
- Returns the callback return value otherwise
- Throws an SQLException if something is wrong

or

```
$db->startTransaction();
// Do something...
$db->closeTransaction();

// Rollback
$db->cancelTransaction();
```

### Fetch all data

[](#fetch-all-data)

```
$records = $db->table('mytable')->findAll();

foreach ($records as $record) {
    var_dump($record['column1']);
}
```

### Updates

[](#updates)

```
$db->table('mytable')->eq('id', 1)->save(['column1' => 'hey']);
```

or

```
$db->table('mytable')->eq('id', 1)->update(['column1' => 'hey']);
```

### Remove records

[](#remove-records)

```
$db->table('mytable')->lt('column1', 10)->remove();
```

### Sorting

[](#sorting)

```
$db->table('mytable')->asc('column1')->findAll();
```

or

```
$db->table('mytable')->desc('column1')->findAll();
```

or

```
$db->table('mytable')->orderBy('column1', 'ASC')->findAll();
```

Multiple sorting:

```
$db->table('mytable')->asc('column1')->desc('column2')->findAll();
```

### Limit and offset

[](#limit-and-offset)

```
$db->table('mytable')->limit(10)->offset(5)->findAll();
```

### Fetch only some columns

[](#fetch-only-some-columns)

```
$db->table('mytable')->columns('column1', 'column2')->findAll();
```

### Fetch only one column

[](#fetch-only-one-column)

Many rows:

```
$db->table('mytable')->findAllByColumn('column1');
```

One row:

```
$db->table('mytable')->findOneColumn('column1');
```

### Custom select

[](#custom-select)

```
$db->table('mytable')->select(1)->eq('id', 42)->findOne();
```

### Distinct

[](#distinct)

```
$db->table('mytable')->distinct('columnA')->findOne();
```

### Group by

[](#group-by)

```
$db->table('mytable')->groupBy('columnA')->findAll();
```

### Count

[](#count)

```
$db->table('mytable')->count();
```

### Sum

[](#sum)

```
$db->table('mytable')->sum('columnB');
```

### Sum column values during update

[](#sum-column-values-during-update)

Add the value 42 to the existing value of the column "mycolumn":

```
$db->table('mytable')->sumColumn('mycolumn', 42)->update();
```

### Increment column

[](#increment-column)

Increment a column value in a single query:

```
$db->table('mytable')->eq('another_column', 42)->increment('my_column', 2);
```

### Decrement column

[](#decrement-column)

Decrement a column value in a single query:

```
$db->table('mytable')->eq('another_column', 42)->decrement('my_column', 1);
```

### Exists

[](#exists)

Returns true if a record exists otherwise false.

```
$db->table('mytable')->eq('column1', 12)->exists();
```

### Joins

[](#joins)

```
// SELECT * FROM mytable LEFT JOIN my_other_table AS t1 ON t1.id=mytable.foreign_key
$db->table('mytable')->left('my_other_table', 't1', 'id', 'mytable', 'foreign_key')->findAll();
```

or

```
// SELECT * FROM mytable LEFT JOIN my_other_table ON my_other_table.id=mytable.foreign_key
$db->table('mytable')->join('my_other_table', 'id', 'foreign_key')->findAll();
```

or

```
// SELECT * FROM mytable LEFT JOIN my_other_table AS t1 ON t1.id=mytable.foreign_key
$db->table('mytable')->inner('my_other_table', 't1', 'id', 'mytable', 'foreign_key')->findAll();
```

Additional equality conditions can be added to a left or inner join:

```
// SELECT * FROM mytable LEFT JOIN my_other_table AS t1 ON t1.id=mytable.foreign_key and t1.status="active"
$db->table('mytable')->left('my_other_table', 't1', 'id', 'mytable', 'foreign_key', ['status' => 'active'])->findAll();
```

or

```
// SELECT * FROM mytable LEFT JOIN my_other_table AS t1 ON t1.id=mytable.foreign_key and t1.status IN ("archived", "disabled")
$db->table('mytable')->left('my_other_table', 't1', 'id', 'mytable', 'foreign_key', ['status' => ['archived', 'disabled']])->findAll();
```

### Equals condition

[](#equals-condition)

```
$db->table('mytable')
   ->eq('column1', 'hey')
   ->findAll();
```

### IN condition

[](#in-condition)

```
$db->table('mytable')
       ->in('column1', ['hey', 'bla'])
       ->findAll();
```

### IN condition with subquery

[](#in-condition-with-subquery)

```
$subquery = $db->table('another_table')->columns('column2')->eq('column3', 'value3');

$db->table('mytable')
       ->columns('column_5')
       ->inSubquery('column1', $subquery)
       ->findAll();
```

### Like condition

[](#like-condition)

Case-sensitive (only Mysql and Postgres):

```
$db->table('mytable')
   ->like('column1', '%Foo%')
   ->findAll();
```

Not case-sensitive:

```
$db->table('mytable')
   ->ilike('column1', '%foo%')
   ->findAll();
```

### Lower than condition

[](#lower-than-condition)

```
$db->table('mytable')
   ->lt('column1', 2)
   ->findAll();
```

### Lower than or equal condition

[](#lower-than-or-equal-condition)

```
$db->table('mytable')
   ->lte('column1', 2)
   ->findAll();
```

### Greater than condition

[](#greater-than-condition)

```
$db->table('mytable')
   ->gt('column1', 3)
   ->findAll();
```

### Greater than or equal condition

[](#greater-than-or-equal-condition)

```
$db->table('mytable')
    ->gte('column1', 3)
    ->findAll();
```

### IS NULL condition

[](#is-null-condition)

```
$db->table('mytable')
   ->isNull('column1')
   ->findAll();
```

### IS NOT NULL condition

[](#is-not-null-condition)

```
$db->table('mytable')
   ->notNull('column1')
   ->findAll();
```

### Multiple conditions

[](#multiple-conditions)

Add conditions are joined by a `AND`.

```
$db->table('mytable')
    ->like('column2', '%mytable')
    ->gte('column1', 3)
    ->findAll();
```

How to make an OR condition:

```
$db->table('mytable')
    ->beginOr()
    ->like('column2', '%mytable')
    ->gte('column1', 3)
    ->closeOr()
    ->eq('column5', 'titi')
    ->findAll();
```

How to make an XOR condition:

```
$db->table('mytable')
    ->beginXor()
    ->like('column2', '%mytable')
    ->gte('column1', 3)
    ->closeXor()
    ->eq('column5', 'titi')
    ->findAll();
```

How to make a NOT condition:

```
$db->table('mytable')
    ->beginNot()
    ->like('column2', '%mytable')
    ->gte('column1', 3)
    ->closeNot()
    ->eq('column5', 'titi')
    ->findAll();
```

Logical conditions can be embedded within other logical conditions:

```
$db->table('mytable')
    ->beginOr()
    ->like('column2', '%mytable')
    ->beginAnd()
    ->gte('column1', 3)
    ->eq('column5', 'titi')
    ->closeAnd()
    ->closeOr()
    ->findAll();
```

### Debugging

[](#debugging)

Log generated queries:

```
$db->getStatementHandler()->withLogging();
```

Mesure each query time:

```
$db->getStatementHandler()->withStopWatch();
```

Get the number of queries executed:

```
echo $db->getStatementHandler()->getNbQueries();
```

Get log messages:

```
print_r($db->getLogMessages());
```

### Large objects (LOBs)

[](#large-objects-lobs)

Insert a file:

```
$db->largeObject('my_table')->insertFromFile('blobColumn', '/path/to/file', array('id' => 'something'));
```

Insert from a stream:

```
$db->largeObject('my_table')->insertFromStream('blobColumn', $fd, array('id' => 'something'));
```

Fetch a large object as a stream (Postgres only):

```
$fd = $db->largeObject('my_table')->eq('id', 'something')->findOneColumnAsStream('blobColumn');
```

Fetch a large object as a string:

```
echo $db->largeObject('my_table')->eq('id', 'something')->findOneColumnAsString('blobColumn');
```

Drivers:

- Postgres
    - Column type: `bytea`
- Sqlite and Mysql
    - Column type: `BLOB`
    - PDO do no not supports the stream feature (returns a string instead)

### Hashtable (key/value store)

[](#hashtable-keyvalue-store)

How to use a table as a key/value store:

```
$db->execute(
     'CREATE TABLE mytable (
         column1 TEXT NOT NULL UNIQUE,
         column2 TEXT default NULL
     )'
);

$db->table('mytable')->insert(['column1' => 'option1', 'column2' => 'value1']);
```

Add/Replace some values:

```
$db->hashtable('mytable')
   ->columnKey('column1')
   ->columnValue('column2')
   ->put(['option1' => 'new value', 'option2' => 'value2']);
```

Get all values:

```
$result = $db->hashtable('mytable')->columnKey('column1')->columnValue('column2')->get();
print_r($result);

Array
(
    [option2] => value2
    [option1] => new value
)
```

or

```
$result = $db->hashtable('mytable')->getAll('column1', 'column2');
```

Get a specific value:

```
$db->hashtable('mytable')
   ->columnKey('column1')
   ->columnValue('column2')
   ->put(['option3' => 'value3']);

$result = $db->hashtable('mytable')
             ->columnKey('column1')
             ->columnValue('column2')
             ->get('option1', 'option3');

print_r($result);

Array
(
    [option1] => new value
    [option3] => value3
)
```

### Schema migrations

[](#schema-migrations)

#### Define a migration

[](#define-a-migration)

- Migrations are defined in simple functions inside a namespace named "Schema".
- An instance of PDO is passed to first argument of the function.
- Function names has the version number at the end.

Example:

```
namespace Schema;

function version_1($pdo)
{
    $pdo->exec('
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE,
            email TEXT UNIQUE,
            password TEXT
        )
    ');
}

function version_2($pdo)
{
    $pdo->exec('
        CREATE TABLE tags (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE
        )
    ');
}
```

#### Run schema update automatically

[](#run-schema-update-automatically)

- The method `check()` execute all migrations until the version specified
- If an error occurs, the transaction is rollbacked
- Foreign keys checks are disabled if possible during the migration

Example:

```
$last_schema_version = 5;

$db = new PicoDb\Database(array(
    'driver' => 'sqlite',
    'filename' => '/tmp/mydb.sqlite'
));

if ($db->schema()->check($last_schema_version)) {

    // Do something...
}
else {

    die('Unable to migrate database schema.');
}
```

### Use a singleton to handle database instances

[](#use-a-singleton-to-handle-database-instances)

Setup a new instance:

```
PicoDb\Database::setInstance('myinstance', function() {

    $db = new PicoDb\Database(array(
        'driver' => 'sqlite',
        'filename' => DB_FILENAME
    ));

    if ($db->schema()->check(DB_VERSION)) {
        return $db;
    }
    else {
        die('Unable to migrate database schema.');
    }
});
```

Get this instance anywhere in your code:

```
PicoDb\Database::getInstance('myinstance')->table(...)
```

###  Health Score

59

—

FairBetter than 99% of packages

Maintenance79

Regular maintenance activity

Popularity42

Moderate usage in the ecosystem

Community24

Small or concentrated contributor base

Maturity77

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

Recently: every ~47 days

Total

20

Last Release

195d ago

Major Versions

1.1.0 → 2.0.02019-07-15

2.1.0 → 3.0.02021-10-26

3.0.0 → 4.0.02021-11-15

4.5.1 → 5.0.02025-04-01

5.1.0 → 6.0.02025-05-06

PHP version history (3 changes)1.0.0PHP >=5.3.0

2.0.0PHP >=7.0

5.0.0PHP >=8.0

### Community

Maintainers

![](https://www.gravatar.com/avatar/77a936b5c659456479744d404f68ba7bca9552f1d008aa2cf3b8a9175c9ddfb4?d=identicon)[bensinclair](/maintainers/bensinclair)

![](https://www.gravatar.com/avatar/7cf1c2feb7af5eafbcec1d7258f9909e785bb27bda7ca57e47a2794241bf960c?d=identicon)[joshmcrae](/maintainers/joshmcrae)

---

Top Contributors

[![bensinclair](https://avatars.githubusercontent.com/u/1043658?v=4)](https://github.com/bensinclair "bensinclair (42 commits)")[![bplainia](https://avatars.githubusercontent.com/u/1258753?v=4)](https://github.com/bplainia "bplainia (15 commits)")[![joshmcrae](https://avatars.githubusercontent.com/u/12324115?v=4)](https://github.com/joshmcrae "joshmcrae (12 commits)")[![beaudurrant](https://avatars.githubusercontent.com/u/6826620?v=4)](https://github.com/beaudurrant "beaudurrant (10 commits)")[![rrigby](https://avatars.githubusercontent.com/u/16025441?v=4)](https://github.com/rrigby "rrigby (4 commits)")[![StewPoll](https://avatars.githubusercontent.com/u/6897645?v=4)](https://github.com/StewPoll "StewPoll (4 commits)")[![b-hayes](https://avatars.githubusercontent.com/u/30540495?v=4)](https://github.com/b-hayes "b-hayes (2 commits)")[![Mr-Kaos](https://avatars.githubusercontent.com/u/65438130?v=4)](https://github.com/Mr-Kaos "Mr-Kaos (2 commits)")

###  Code Quality

TestsPHPUnit

### Embed Badge

![Health badge](/badges/elvanto-picodb/health.svg)

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

###  Alternatives

[doctrine/orm

Object-Relational-Mapper for PHP

10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[ramsey/uuid-doctrine

Use ramsey/uuid as a Doctrine field type.

90440.3M211](/packages/ramsey-uuid-doctrine)[reliese/laravel

Reliese Components for Laravel Framework code generation.

1.7k3.4M16](/packages/reliese-laravel)[wildside/userstamps

Laravel Userstamps provides an Eloquent trait which automatically maintains `created\_by` and `updated\_by` columns on your model, populated by the currently authenticated user in your application.

7511.7M13](/packages/wildside-userstamps)

PHPackages © 2026

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