Leaf 5 — Database Reference

Leaf DB (Query Builder)

Pre-installed in MVC. For Basic apps:

leaf install db

In MVC, prefer Models over db() for structured code. db() is great for quick queries and Basic apps.

Connecting

// MySQL (default)
db()->connect([
    'host'     => '127.0.0.1',
    'username' => 'root',
    'password' => '',
    'dbname'   => 'myapp',
]);

// PostgreSQL
db()->connect([
    'dbtype'   => 'pgsql',
    'host'     => '127.0.0.1',
    'username' => 'root',
    'password' => '',
    'dbname'   => 'myapp',
    'port'     => '5432',
]);

// SQLite
db()->connect([
    'dbtype' => 'sqlite',
    'dbname' => 'db.sqlite',
]);

Multiple Connections

db()->addConnections([
    'conn1' => ['dbtype' => 'mysql', 'host' => '...', /* ... */],
    'conn2' => ['dbtype' => 'pgsql', 'host' => '...', /* ... */],
], 'conn1');  // 'conn1' is default

db('conn2')->select('users')->all();  // use specific connection

---

Raw SQL

$users = db()->query('SELECT * FROM users')->all();

db()->query('SELECT * FROM users WHERE id = ?')
    ->bind('1')
    ->fetchObj();

db()->query('CREATE DATABASE dbname')->execute();  // no return value
$users = db()->query('SELECT * FROM users')->first();

Result methods: ->all(), ->first(), ->fetchObj(), ->fetchAssoc(), ->fetchAll()

query() takes no bindings parameter — chain ->bind(...$values) for each ? placeholder. There is no whereIn(); build the placeholders yourself: db()->query('SELECT * FROM users WHERE id IN (' . rtrim(str_repeat('?,', count($ids)), ',') . ')')->bind(...$ids).

---

Query Builder

DDL

db()->create('dbname')->execute();
db()->drop('dbname')->execute();

Insert

db()->insert('users')->params(['username' => 'mychi'])->execute();

// Unique constraint check
db()->insert('users')
    ->params(['username' => 'mychi', 'email' => 'mychi@leafphp.dev', 'password' => hash('test')])
    ->unique('username', 'email')
    ->execute();

$lastId = db()->lastInsertId();

Select

db()->select('users')->all();
db()->select('users', 'name, created_at')->all();   // specific columns

db()->select('users')->where('name', 'John Doe')->fetchObj();
db()->select('users')->where(['name' => 'John Doe', 'age' => 20])->fetchAssoc();

// Operators: >, <, >=, <=, !=, LIKE
db()->select('items')->where('tag', 'LIKE', '%new%')->fetchAll();

// orWhere
db()->select('users')->where('name', 'John Doe')->orWhere('age', 20)->fetchAll();
db()->select('users')->orWhere(['name' => 'John Doe', 'age' => 20])->fetchAll();

db()->select('users')->find(1);  // find by id

Relationships

db()->select('users')->with('posts')->all();
db()->select('users')->where('id', 1)->with('posts')->first();

Update

db()->update('users')->params(['name' => 'Jane Doe'])->execute();          // all rows
db()->update('users')->params(['name' => 'Jane Doe'])->where('id', 1)->execute();

Delete

db()->delete('users')->execute();                      // all rows — careful!
db()->delete('users')->where('id', '1')->execute();

---

Result Modifiers

// Hide fields from results
db()->select('users')->hidden('password')->all();
db()->select('users')->where('id', '1')->hidden('remember_token', 'reset_q_id')->first();

// Add computed fields
db()->select('users')->add('full_name', 'Something Here')->all();
db()->select('users')->where('id', '1')->add('full_name', 'Value')->first();

// Ordering & limiting
db()->select('users')->orderBy('created_at', 'DESC')->all();
db()->select('users')->orderBy('created_at')->limit(10)->all();

// Count
db()->select('users')->count();
db()->select('users')->where('age', '>', 20)->count();

As of leafs/db 5.1.1, count() counts SELECT result rows correctly on every driver. Older versions used PDO's rowCount(), which returns 0 for SELECTs on sqlite.

---

Transactions

db()->transaction(function ($db) {
    $db->insert('purchases')->params([/* ... */])->execute();
    $db->update('balances')->params([/* ... */])->where([/* ... */])->execute();
    // can include non-DB logic too
});

$success = db()->transaction(function () { /* ... */ });
if (!$success) {
    $errors = db()->errors();
}

In Leaf MVC (leafs/db 5.1+), db() and your models share one connection, so a db()->beginTransaction() covers model reads and writes too — wrap a check-then-insert (an availability check plus a booking insert, say) in one transaction to close the race between concurrent requests. On older setups where the connections are separate, transactions only cover db() queries.

---

Error Handling

$res = db()->insert('users')->params('username', 'mychi')->unique('username')->execute();

if ($res === false) {
    response()->exit(db()->errors());
    // errors: ['username' => 'username already exists']
}

---

Leaf Redis

MVC only (Basic app support planned).

leaf install redis

Config (.env)

REDIS_HOST=127.0.0.1
REDIS_PORT=6379
REDIS_PASSWORD=null
REDIS_SESSION=false

For advanced config:

leaf config:publish redis   # → config/redis.php

Usage

echo redis()->ping();

// Set
redis()->set('name', 'Michael');
redis()->set(['name' => 'Michael', 'age' => 22]);
redis()->set('name', 'Michael', 3600);  // with TTL (seconds)
redis()->set('name', 'Michael', 0);     // no expiration

// Get
$name   = redis()->get('name');
$values = redis()->get(['name', 'age']);  // ['name' => 'Michael', 'age' => 22]

// Delete
redis()->delete('name');
redis()->delete(['name', 'age']);

// Check
$exists = redis()->exists('name');
$exists = redis()->has('name');       // alias

// Utility
redis()->flush();
$keys = redis()->keys();