charm / database
Yet another PDO wrapper. Because I don't like to explicitly prepare and execute and fetch every single query. Can use a PDO instance from you, or it makes a new connection standalone.
Requires
- charm/error: ^0.0.1
This package is auto-updated.
Last update: 2021-03-27 16:37:17 UTC
README
Charm\DB is a very thin wrapper around PDO. The only reason it exists, is because I hate to write many lines of code many times over.
It works nicely together with frameworks that use PDO, like Doctrine or Symfony, which is nice if you are writing a library and don't want your library to depend on Doctrine.
I have written this type of wrapper several times, for different projects. So I finally decided to make some documentation and put it on packagist - mainly to make it easier to install for myself. :-D
Examples for lazy readers
Charm\DB query:
$rows = $db->query('SELECT * FROM users WHERE id = ?', [ 123 ] );
PDO query:
$stmt = $db->prepare('SELECT * FROM users WHERE id = ?', [ 123 ] ); $stmt->execute([123]); $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
Charm\DB exec:
$affectedRows = $db->exec('DELETE FROM trash WHERE age < ?', [ gmdate('Y-m-d H:i:s') ]);
PDO exec:
$stmt = $db->prepare('DELETE FROM trash WHERE age < ?'); $stmt->execute([ gmdate('Y-m-d H:i:s') ]); $affectedRows = $stmt->rowCount();
PDO for lazy people, prone to human mistakes causing huge security issues:
$affectedRows = $db->exec('DELETE FROM trash WHERE age < '.$db->quote(gmdate('Y-m-d H:i:s')));
No special method to fetch a single row, just use
->current()
.$row = $db->query('SELECT * FROM users WHERE id = ?', [123])->current();
No special method to fetch a single column, just provide the column number.
$usernames = $db->query('SELECT username FROM users', [], 0);
Fetching a single field, just use
->current()
.$value = $db->query('SELECT username FROM users WHERE id = ? LIMIT 1', [123], 0)->current();
Sometimes you'll want the extra power that the PDO API provides. Two methods are relevant:
$db->prepare( string $statement, array $driver_options = array() )
which works identically to thePDO::prepare()
method, but with statement caching.$db->pdo(): ?PDO
which returns the raw PDO instance.
Using it
Connect to the database (using PDO DSN strings).
$db = Charm\DB::__construct ( string $dsn, string $username = ?, string $passwd = ?, array $options = ? )
or wrap an existing PDO instance:
$db = Charm\DB::wrap ( PDO $pdoInstance ) : Charm\DB;
Use the built-in factory
In the PHP world, there seems to be a million ways that people want to make it "easy" to configure the framework application. We have to learn how to inject configuration into a configuration that will then be injected into another tool. But it will only be injected, if you configure it to inject it unless you have configured autowiring.
This is all understandable, when you're building what you believe will become the framework that is as loved by Facebook and by Johnny, lead developer of "Johnny Inc." in Georgia. I prefer refactoring my code when (if) I grow out of the architecture I started with.
Us developers keep designing the most complext structures, to make it easy to configure stuff.
But just in case you don't have time to do that, the Charm* components generally allow you to
configure them via public static properties and then you can retrieve the instance via their
::instance()
static method.
Charm\DB::instance()
can be configured via
// Either put in some configuration:
Charm\DB::$dsn = $dsn;
Charm\DB::$username = ?;
Charm\DB::$passwd = ?;
Charm\DB::$options = ?;
or:
// Or provide an instance directly
Charm\DB::$instance = $pdoInstance;
Now, the database can be accessed throughout your project via Charm\DB::instance()
.
If that doesn't suit you, just go ahead and setup Symfony or design your own method to construct it.
`Charm\DB::query (
More examples, which essentially are the same examples as above under the TLDR headline.
Querying data
Database queries are done through either $db->query()
or $db->column()
.
// Multiple rows returns an iterable
$users = $db->query('SELECTFROM users WHERE last_login < ?', [gmdate('Y-m-d H:i:s')]);
// Single rows can be fetched this way (sending 'LIMIT 1' is kind to your database engine)
$user = $db->query('SELECTFROM users WHERE username = ? LIMIT 1', [ $username ])->current();
// Fetch a single column
$usernames = $db->column('SELECT username FROM users);
// Fetcha single value
$numberOfUsers = $db->column('SELECT COUNT(*) FROM users')->current();
Definition, Manipulation, Control
All modifications to the database are done through the $db->exec()
function.
// Insert a row into a table
if ($db->exec('INSERT INTO users (username, password ) VALUES (?, ?)', [ $username, $password ])) {
echo "Created a new user with id ".$db->lastInsertId()."!\n";
}
// Delete some data
if ($count = $db->exec('DELETE FROM users WHERE expires_date < ?', [ gmdate('Y-m-d H:i:s') ])) {
echo "Deleted $count users\n";
}
Error Handling
The wrapper defaults to PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
, so you'll be receiving
standard PDO exceptions.
Invalid usage of this wrapper will trigger Charm\DB\Error
exceptions with error codes
according to the class constants. If you're not using PDO::ERRMODE_EXCEPTION
we'll only
throw Charm\DB\Error
exceptions.
The query issued when the last error was triggered is available in $db->lastFailedQuery
.
Security
This class does NOTHING for security, but it makes it easier for YOU to do more secure database queries.
This class overrides the __debugInfo()
magic method to avoid accidentally exposing secrets,
but in general you should NEVER display raw error messages in production.