PHPackages                             unlight/sparrow - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. unlight/sparrow

ActiveLibrary[Database & ORM](/categories/database)

unlight/sparrow
===============

Sparrow is a fluent SQL builder.

217511PHP

Since Oct 20Pushed 12y ago2 watchersCompare

[ Source](https://github.com/unlight/sparrow)[ Packagist](https://packagist.org/packages/unlight/sparrow)[ RSS](/packages/unlight-sparrow/feed)WikiDiscussions master Synced 2w ago

READMEChangelogDependenciesVersions (1)Used By (1)

Sparrow
=======

[](#sparrow)

Sparrow is a simple but powerful database toolkit. Sparrow is a fluent SQL builder, database abstraction layer, cache manager, query statistics generator, and micro-ORM all rolled into a single class file.

Building SQL
------------

[](#building-sql)

```
// Include the library
include '/path/to/sparrow.php';

// Declare the class instance
$db = new Sparrow();

// Select a table
$db->from('user')

// Build a select query
$db->select();

// Display the SQL
echo $db->sql();

```

Output:

```
SELECT * FROM user

```

### Method Chaining

[](#method-chaining)

Sparrow allows you to chain methods together, so you can instead do:

```
echo $db->from('user')->select()->sql();

```

### Where Conditions

[](#where-conditions)

To add where conditions to your query, use the `where` function.

```
echo $db->from('user')
    ->where('id', 123)
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE id = 123

```

You can call where multiple times to add multiple conditions.

```
echo $db->from('user')
    ->where('id', 123)
    ->where('name', 'bob')
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE id = 123 AND name = 'bob'

```

You can also pass an array to the where function. The following would produce the same output.

```
$where = array('id' => 123, 'name' => 'bob');

echo $db->from('user')
    ->where($where)
    ->select()
    ->sql();

```

You can even pass in a string literal.

```
echo $db->from('user')
    ->where('id = 99')
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE id = 99

```

### Custom Operators

[](#custom-operators)

The default operator for where queries is `=`. You can use different operators by placing them after the field declaration.

```
echo $db->from('user')
    ->where('id >', 123)
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE id > 123;

```

### OR Queries

[](#or-queries)

By default where conditions are joined together by `AND` keywords. To use OR instead, simply place a `|` delimiter before the field name.

```
echo $db->from('user')
    ->where('id ', 20)
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE id < 10 OR id > 20

```

### LIKE Queries

[](#like-queries)

To build a LIKE query you can use the special `%` operator.

```
echo $db->from('user')
    ->where('name %', '%bob%')
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE name LIKE '%bob%'

```

To build a NOT LIKE query, add a `!` before the `%` operator.

```
echo $db->from('user')
    ->where('name !%', '%bob%')
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE name NOT LIKE '%bob%'

```

### IN Queries

[](#in-queries)

To use an IN statement in your where condition, use the special `@` operator and pass in an array of values.

```
echo $db->from('user')
    ->where('id @', array(10, 20, 30))
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE id IN (10, 20, 30)

```

To build a NOT IN query, add a `!` before the `@` operator.

```
echo $db->from('user')
    ->where('id !@', array(10, 20, 30))
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user WHERE id NOT IN (10, 20, 30)

```

### Selecting Fields

[](#selecting-fields)

To select specific fields, pass an array in to the `select` function.

```
echo $db->from('user')
    ->select(array('id','name'))
    ->sql();

```

Output:

```
SELECT id, name FROM user

```

### Limit and Offset

[](#limit-and-offset)

To add a limit or offset to a query, you can use the `limit` and `offset` functions.

```
echo $db->from('user')
    ->limit(10)
    ->offset(20)
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user LIMIT 10 OFFSET 20

```

You can also pass in additional parameters to the `select` function.

```
echo $db->from('user')
    ->select('*', 50, 10)
    ->sql();

```

Output:

```
SELECT * FROM user LIMIT 50 OFFSET 10

```

### Distinct

[](#distinct)

To add a DISTINCT keyword to your query, call the `distinct` function.

```
echo $db->from('user')
    ->distinct()
    ->select('name')
    ->sql();

```

Output:

```
SELECT DISTINCT name FROM user

```

### Table Joins

[](#table-joins)

To add a table join, use the `join` function and pass in an array of fields to join on.

```
echo $db->from('user')
    ->join('role', array('role.id' => 'user.id'))
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user INNER JOIN role ON role.id = user.id

```

The default join type is an `INNER` join. To build other types of joins you can use the alternate join functions `leftJoin`, `rightJoin`, and `fullJoin`.

The join array works just like where conditions, so you can use custom operators and add multiple conditions.

```
echo $db->from('user')
    ->join('role', array('role.id' => 'user.id', 'role.id >' => 10))
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user INNER JOIN role ON role.id = user.id AND role.id > 10

```

### Sorting

[](#sorting)

To add sorting to a query, use the `sortAsc` and `sortDesc` functions.

```
echo $db->from('user')
    ->sortDesc('id')
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user ORDER BY id DESC

```

You can also pass an array to the sort functions.

```
echo $db->from('user')
    ->sortAsc(array('rank','name'))
    ->select()
    ->sql();

```

Output:

```
SELECT * FROM user ORDER BY rank ASC, name ASC

```

### Grouping

[](#grouping)

To add a field to group by, use the `groupBy` function.

```
echo $db->from('user')
    ->groupBy('points')
    ->select(array('id','count(*)'))
    ->sql();

```

Output:

```
SELECT id, count(*) FROM user GROUP BY points;

```

### Insert Queries

[](#insert-queries)

To build an insert query, pass in an array of data to the `insert` function.

```
$data = array('id' => 123, 'name' => 'bob');

echo $db->from('user')
    ->insert($data)
    ->sql();

```

Output:

```
INSERT INTO user (id, name) VALUES (123, 'bob')

```

### Update Queries

[](#update-queries)

To build an update query, pass in an array of data to the `update` function.

```
$data = array('name' => 'bob', 'email' => 'bob@aol.com');
$where = array('id' => 123);

echo $db->from('user')
    ->where($where)
    ->update($data)
    ->sql();

```

Output:

```
UPDATE user SET name = 'bob', email = 'bob@aol.com' WHERE id = 123

```

### Delete Queries

[](#delete-queries)

To build a delete query, use the `delete` function.

```
echo $db->from('user')
    ->where('id', 123)
    ->delete()
    ->sql();

```

Output:

```
DELETE FROM user WHERE id = 123

```

Executing Queries
-----------------

[](#executing-queries)

Sparrow can also execute the queries it builds. You will need to call the `setDb()` method with either a connection string, an array of connection information, or a connection object.

The supported database types are `mysql`, `mysqli`, `pgsql`, `sqlite` and `sqlite3`.

Using a connection string:

```
$db->setDb('mysql://admin:hunter2@localhost/mydb');

```

The connection string uses the following format:

```
type://username:password@hostname[:port]/database

```

For sqlite, you need to use:

```
type://database

```

Using a connection array:

```
$db->setDb(array(
    'type' => 'mysql',
    'hostname' => 'localhost',
    'database' => 'mydb',
    'username' => 'admin',
    'password' => 'hunter2'
));

```

The possible array options are `type`, `hostname`, `database`, `username`, `password`, and `port`.

Using a connection object:

```
$mysql = mysql_connect('localhost', 'admin', 'hunter2');

mysql_select_db('mydb');

$db->setDb($mysql);

```

You can also use PDO for the database connection. To use the connection string or array method, prefix the database type with `pdo`:

```
$db->setDb('pdomysql://admin:hunter2@localhost/mydb');

```

The possible PDO types are `pdomysql`, `pdopgsql`, and `pdosqlite`.

You can also pass in any PDO object directly:

```
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'admin', 'hunter2');

$db->setDb($pdo);

```

### Fetching records

[](#fetching-records)

To fetch multiple records, use the `many` function.

```
$rows = $db->from('user')
    ->where('id >', 100)
    ->many();

```

The result returned is an array of associative arrays:

```
array(
    array('id' => 101, 'name' => 'joe'),
    array('id' => 102, 'name' => 'ted');
)

```

To fetch a single record, use the `one` function.

```
$row = $db->from('user')
    ->where('id', 123)
    ->one();

```

The result returned is a single associative array:

```
array('id' => 123, 'name' => 'bob')

```

To fetch the value of a column, use the `value` function and pass in the name of the column.

```
$username = $db->from('user')
    ->where('id', 123)
    ->value('username');

```

All the fetch functions automatically perform a select, so you don't need to include the `select` function unless you want to specify the fields to return.

```
$row = $db->from('user')
    ->where('id', 123)
    ->select(array('id', 'name'))
    ->one();

```

### Non-queries

[](#non-queries)

For non-queries like update, insert and delete, use the `execute` function after building your query.

```
$db->from('user')
    ->where('id', 123)
    ->delete()
    ->execute();

```

Executes:

```
DELETE FROM user WHERE id = 123

```

### Custom Queries

[](#custom-queries)

You can also run raw SQL by passing it to the `sql` function.

```
$posts = $db->sql('SELECT * FROM posts')->many();

$user = $db->sql('SELECT * FROM user WHERE id = 123')->one();

$db->sql('UPDATE user SET name = 'bob' WHERE id = 1')->execute();

```

### Escaping Values

[](#escaping-values)

Sparrow's SQL building functions automatically quote and escape values to prevent SQL injection. To quote and escape values manually, like when you're writing own queries, you can use the `quote` function.

```
$name = "O'Dell";

printf("SELECT * FROM user WHERE name = %s", $db->quote($name));

```

Output:

```
SELECT * FROM user WHERE name = 'O\'Dell'

```

### Query Properties

[](#query-properties)

After executing a query, several property values will be populated which you can access directly.

```
// Last query executed
$db->last_query;

// Number of rows returned
$db->num_rows;

// Last insert id
$db->insert_id;

// Number of affected rows
$db->affected_rows;

```

These values are reset every time a new query is executed.

### Helper Methods

[](#helper-methods)

To get a count of rows in a table.

```
$count = $db->from('user')->count();

```

To get the minimum value from a table.

```
$min = $db->from('employee')->min('salary');

```

To get the maximum value from a table.

```
$max = $db->from('employee')->max('salary');

```

To get the average value from a table.

```
$avg = $db->from('employee')->avg('salary');

```

To get the sum value from a table.

```
$avg = $db->from('employee')->sum('salary');

```

### Direct Access

[](#direct-access)

You can also access the database object directly by using the `getDb` function.

```
$mysql = $db->getDb();

mysql_info($mysql);

```

Caching
-------

[](#caching)

To enable caching, you need to use the `setCache` method with a connection string or connection object.

Using a connection string:

```
$db->setCache('memcache://localhost:11211');

```

Using a cache object:

```
$cache = new Memcache();
$cache->addServer('localhost', 11211);

$db->setCache($cache);

```

You can then pass a cache key to the query functions and Sparrow will try to fetch from the cache before executing the query. If there is a cache miss, Sparrow will execute the query and store the results using the specified cache key.

```
$key = 'all_users';

$users = $db->from('user')->many($key);

```

### Cache Types

[](#cache-types)

The supported caches are `memcache`, `memcached`, `apc`, `xcache`, `file` and `memory`.

To use `memcache` or `memcached`, you need to use the following connection string:

```
protocol://hostname:port

```

To use `apc` or `xcache`, just pass in the cache name:

```
$db->setCache('apc');

```

To use the filesystem as a cache, pass in a directory path:

```
$db->setCache('/usr/local/cache');

$db->setCache('./cache');

```

Note that local directories must be prefixed with `./`.

The default cache is `memory` and only lasts the duration of the script.

### Cache Expiration

[](#cache-expiration)

To cache data only for a set period of time, you can pass in an additional parameter which represents the expiraton time in seconds.

```
$key = 'top_users';
$expire = 600;

$users = $db->from('user')
    ->sortDesc('score')
    ->limit(100)
    ->many($key, $expire);

```

In the above example, we are getting a list of the top 100 highest scoring users and caching it for 600 seconds (10 minutes). You can pass the expiration parameter to any of the query methods that take a cache key parameter.

### Direct Access

[](#direct-access-1)

You can access the cache object directly by using the `getCache` function.

```
$memcache = $db->getCache();

echo $memcache->getVersion();

```

You can manipulate the cache data directly as well. To cache a value use the `store` function.

```
$db->store('id', 123);

```

To retrieve a cached value use the `fetch` function.

```
$id = $db->fetch('id');

```

To delete a cached value use the `clear` function.

```
$db->clear('id');

```

To completely empty the cache use the `flush` function.

```
$db->flush();

```

Using Objects
-------------

[](#using-objects)

Sparrow also provides some functionality for working with objects. Just define a class with public properties to represent database fields and static variables to describe the database relationship.

```
class User {
    // Class properties
    public $id;
    public $name;
    public $email;

    // Class configuration
    static $table = 'user';
    static $id_field = 'id';
    static $name_field = 'name';
}

```

### Class Configuration

[](#class-configuration)

- The `table` property represents the database table. This property is required.
- The `id_field` property represents the auto-incrementing identity field in the table. This property is required for saving and deleting records.
- The `name_field` property is used for finding records by name. This property is optional.

### Loading Objects

[](#loading-objects)

To define the object use the `using` function and pass in the class name.

```
$db->using('User');

```

After setting your object, you can then use the `find` method to populate the object. If you pass in an int Sparrow will search using the id field.

```
$user = $db->find(123);

```

This will execute:

```
SELECT * FROM user WHERE id = 123

```

If you pass in a string Sparrow will search using the name field.

```
$user = $db->find('Bob');

```

This will execute:

```
SELECT * FROM user WHERE name = 'Bob';

```

If you pass in an array Sparrow will use the fields specified in the array.

```
$user = $db->find(
    array('email' => 'bob@aol.com')
);

```

This will execute:

```
SELECT * FROM user WHERE email = 'bob@aol.com'

```

If the `find` method retrieves multiple records, it will return an array of objects instead of a single object.

### Saving Objects

[](#saving-objects)

To save an object, just populate your object properties and use the `save` function.

```
$user = new User();
$user->name = 'Bob';
$user->email = 'bob@aol.com';

$db->save($user);

```

This will execute:

```
INSERT INTO user (name, email) VALUES ('Bob', 'bob@aol.com')

```

To update an object, use the `save` function with the `id_field` property populated.

```
$user = new User();
$user->id = 123;
$user->name = 'Bob';
$user->email = 'bob@aol.com';

$db->save($user);

```

This will execute:

```
UPDATE user SET name = 'Bob', email = 'bob@aol.com' WHERE id = 123

```

To update an existing record, just fetch an object from the database, update its properties, then save it.

```
// Fetch an object from the database
$user = $db->find(123);

// Update the object
$user->name = 'Fred';

// Update the database
$db->save($user);

```

By default, all of the object's properties will be included in the update. To specify only specific fields, pass in an additional array of fields to the `save` function.

```
$db->save($user, array('email'));

```

This will execute:

```
UPDATE user SET email = 'bob@aol.com' WHERE id = 123

```

### Deleting Objects

[](#deleting-objects)

To delete an object, use the `remove` function.

```
$user = $db->find(123);

$db->remove($user);

```

### Advanced Finding

[](#advanced-finding)

You can use the sql builder functions to further define criteria for loading objects.

```
$db->using('User')
    ->where('id >', 10)
    ->sortAsc('name')
    ->find();

```

This will execute:

```
SELECT * FROM user WHERE id > 10 ORDER BY name ASC

```

You can also pass in raw SQL to load your objects.

```
$db->using('User')
    ->sql('SELECT * FROM user WHERE id > 10')
    ->find();

```

Statistics
----------

[](#statistics)

Sparrow has built in query statistics tracking. To enable it, just set the `stats_enabled` property.

```
$db->stats_enabled = true;

```

After running your queries, get the stats array:

```
$stats = $db->getStats();

```

The stats array contains the total time for all queries and an array of all queries executed with individual query times.

```
array(6) {
  ["queries"]=>
  array(2) {
    [0]=>
    array(4) {
      ["query"]=>
          string(38) "SELECT * FROM user WHERE uid=1"
      ["time"]=>
          float(0.00016617774963379)
      ["rows"]=>
          int(1)
      ["changes"]=>
          int(0)
    }
    [1]=>
    array(4) {
      ["query"]=>
          string(39) "SELECT * FROM user WHERE uid=10"
      ["time"]=>
          float(0.00026392936706543)
      ["rows"]=>
          int(0)
      ["changes"]=>
          int(0)
    }
  }
  ["total_time"]=>
      float(0.00043010711669922)
  ["num_queries"]=>
      int(2)
  ["num_rows"]=>
      int(2)
  ["num_changes"]=>
      int(0)
  ["avg_query_time"]=>
      float(0.00021505355834961)
}

```

Debugging
---------

[](#debugging)

When Sparrow encounters an error while executing a query, it will raise an exception with the database error message. If you want to display the generated SQL along with the error message, set the `show_sql` property.

```
$db->show_sql = true;

```

Requirements
------------

[](#requirements)

Sparrow requires PHP 5.1 or greater.

License
-------

[](#license)

Sparrow is released under the [MIT](https://github.com/mikecao/sparrow/blob/master/LICENSE) license.

###  Health Score

24

—

LowBetter than 31% of packages

Maintenance20

Infrequent updates — may be unmaintained

Popularity14

Limited adoption so far

Community16

Small or concentrated contributor base

Maturity41

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 93.9% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

### Community

Maintainers

![](https://www.gravatar.com/avatar/702161df5ae4d3307459cd39e75f5be03080529835ce78447cd86226732f5834?d=identicon)[unlight](/maintainers/unlight)

---

Top Contributors

[![mikecao](https://avatars.githubusercontent.com/u/477975?v=4)](https://github.com/mikecao "mikecao (62 commits)")[![AbeEstrada](https://avatars.githubusercontent.com/u/7937?v=4)](https://github.com/AbeEstrada "AbeEstrada (2 commits)")[![galic](https://avatars.githubusercontent.com/u/935176?v=4)](https://github.com/galic "galic (1 commits)")[![unlight](https://avatars.githubusercontent.com/u/301732?v=4)](https://github.com/unlight "unlight (1 commits)")

### Embed Badge

![Health badge](/badges/unlight-sparrow/health.svg)

```
[![Health](https://phpackages.com/badges/unlight-sparrow/health.svg)](https://phpackages.com/packages/unlight-sparrow)
```

###  Alternatives

[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k116.5M113](/packages/jdorn-sql-formatter)[propel/propel1

Propel is an open-source Object-Relational Mapping (ORM) for PHP5.

8351.6M87](/packages/propel-propel1)[yemenopensource/filament-excel

This package useful for importing excel files into models.

194.2k](/packages/yemenopensource-filament-excel)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)