elvanto / picodb
Minimalist database query builder
Installs: 24 064
Dependents: 1
Suggesters: 0
Security: 0
Stars: 27
Watchers: 5
Forks: 11
Open Issues: 2
Requires
- php: >=7.0
- ext-pdo: *
Requires (Dev)
- phpunit/phpunit: 6.5.14
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();
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();
Further AND conditions can be embedded within an OR:
$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
- Column type:
- Sqlite and Mysql
- Column type:
BLOB
- PDO do no not supports the stream feature (returns a string instead)
- Column type:
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(...)