Skip to content
Branch: master
Find file History
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
lib/PicoDb
tests
LICENSE
README.md
phpunit.xml

README.md

PicoDb

PicoDb is a minimalist database query builder for PHP.

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 5.3, 5.4, 5.5, 5.6 and 7.0
  • License: MIT

Requirements

  • PHP >= 5.3
  • PDO extension
  • Sqlite, Mssql, Mysql or Postgresql

Author

Frédéric Guillot

Documentation

Installation

composer require fguillot/picodb @stable

Database connection

Sqlite:

use PicoDb\Database;

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

The Sqlite driver enable foreign keys by default.

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:

$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
  • ssl_key

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:

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

use PicoDb\UrlParser;
use PicoDb\Database;

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

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

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

or

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

Fetch last inserted id

$db->getLastId();

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

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

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

Updates

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

or

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

Remove records

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

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

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

Fetch only some columns

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

Fetch only one column

Many rows:

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

One row:

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

Custom select

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

Distinct

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

Group by

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

Count

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

Sum

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

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 a column value in a single query:

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

Decrement column

Decrement a column value in a single query:

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

Exists

Returns true if a record exists otherwise false.

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

Left 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();

Equals condition

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

IN condition

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

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

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

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

Lower than or equal condition

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

Greater than condition

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

Greater than or equal condition

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

IS NULL condition

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

IS NOT NULL condition

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

Multiple conditions

Add conditions are joined by a AND.

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

How to make a OR condition:

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

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)

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)

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

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

  • 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

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(...)
You can’t perform that action at this time.