README

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 7+
• License: MIT

Requirements

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

Author

Frédéric Guillot

Documentation

Installation

composer require elvanto/picodb

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
• persistent
• timeout
• verify_server_cert
• case

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

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

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

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(...)