habanero/grocery

Fancy database library for PHP

The main purpose

Provide a simple (and fancy) interface to work with databases, currently supports PostgreSQL (8.4+), MySQLi (5.1+) and SQLite3 (3.7+) using the same API., _(*1)

Perform migrations quickly, although you must write a wrapper for this if you want to keep the track., _(*2)

, _(*3)

First steps

You can just load tables and work with them almost easily, or you could create tables directly from the code and skip the CLI or GUI to achieve that., _(*4)

<?php

use Grocery\Base as DB;

# create a connection by DSN
$db = DB::connect('pgsql://postgres:test@localhost:5432/test#pdo');

# load existing table by accesing an array member
$foo = $db['my_table'];

# pick one row randomly!
$bar = $foo->select('*', [
  'field' => 'value',
  'OR' => [
    'foo' => null,
    'foo >=' => gmdate('Y-m-d H:i:s'),
  ], // WHERE "field" = 'value' AND ("foo" IS NULL OR "foo" >= '2023-08-22 23:45:12')
], [
  'order_by' => [$db->rand()], // ORDER BY RAND|RANDOM()
]);

# create another table
$db['other_table'] = [
  'id' => DB::pk(),
  'title' => DB::str(['not_null' => true]),
  'published_at' => DB::date(['default' => $db->now()]),
];

# inserting a new row
$db->other_table->insert([
  'title' => 'Hello World!',
  'published_at' => date('Y-m-d H:i:s'),
]);

There are generic types for creating new tables., _(*5)

• primary_key: The most common field, conventionally named id
• integer: Basic numeric types, for relations, counting, etc.
• float: Extended numeric types for floating point values
• numeric: For money, decimals and other non-float values
• string: Varchar, char, etc. Default to 255 max-length
• text: For plain text without limits, depending on its driver
• binary: Blob values, but please do not save your files on the database
• boolean: Real booleans, chars or tiny-ints depending on its driver
• timestamp: Not *nix timestamp, alias for datetime indeed
• datetime: Common date+time strings like date('Y-m-d H:i:s')
• date: Just the date part Y-m-d
• time: The time part H:i:s

About migrating

Grocery provides the hydrate() helper method for this purpose., _(*6)

<?php

# table fields
$foo = [
  'id' => 'primary_key',
  'bar' => 'string',
  'candy' => 'timestamp',
];

# indexed fields
$bar = ['bar', 'candy' => TRUE];

# create if not exists
isset($db['tbl']) || $db['tbl'] = $foo;

# performs the hydration
Grocery\Helpers::hydrate($db['tbl'], $foo, $bar);

Internally it will load the current details from the specified table, then compare and update your table definitions against your provided changes., _(*7)

Notice that some operations are restrictive depending on the driver limitations., _(*8)

Basic usage

Table access is granted through the $db[...] syntax, most operations can be chained., _(*9)

The last call MUST be a getter, e.g. first(), pick(), get() or all()., _(*10)

$tbl = $db['tbl'];
$tbl->where(['status' => 0])->count();
$tbl->where(['status' => -1])->delete();
$tbl->select('*')->where(['status' => 1])->all();
$tbl->order(['created_at' => 1])->where(['status' => 2])->select('*')->first();
$tbl->where(['id' => 42])->update(['value' => 'OSOM']);

Installation

Using the composer is the best way to get installed Grocery as dependency., _(*11)

{
  "require": {
    "habanero/grocery": "dev-master"
  }
}

Then include the vendor/autoload.php script at the top of your project et voila., _(*12)

Contribute!

So far there are many ways to work with Grocery but the README is too short already., _(*13)

If you want to test and write some documentation, examples, etc. you're welcome to do pull-requests., _(*14)