SQL Query Builder

LavaLust includes a PDO-based Database class that supports a fluent Query Builder pattern and direct raw SQL execution. All values passed through the builder are automatically bound as prepared statement parameters, protecting against SQL injection by default.

Configuration

Database connection settings live in app/config/database.php. The main group is used by default when no group name is specified.

<?php
$database['main'] = [
    'driver'   => 'mysql',
    'hostname' => 'localhost',
    'port'     => '3306',
    'username' => 'root',
    'password' => '',
    'database' => 'my_database',
    'charset'  => 'utf8mb4',
    'dbprefix' => '',
];

Tip

Never commit database credentials to version control. Load them from environment variables instead:

<?php
$database['main'] = [
    'driver'   => 'mysql',
    'hostname' => getenv('DB_HOST'),
    'username' => getenv('DB_USER'),
    'password' => getenv('DB_PASS'),
    'database' => getenv('DB_NAME'),
    'charset'  => 'utf8mb4',
    'dbprefix' => '',
];

Configuration Values

Key	Required	Description
driver	Yes	Database driver. See Supported Drivers below. Must be lowercase.
hostname	Yes	Hostname or IP of the database server. For SQLite, leave blank — use path instead.
port	No	Port number. Omit to use the driver default.
username	Yes	Database user. Not required for SQLite.
password	Yes	Password for the database user. Not required for SQLite.
database	Yes	Name of the database. Not required for SQLite.
charset	No	Connection character set. Recommended: utf8mb4.
dbprefix	No	Optional table name prefix prepended by the Query Builder.
path	SQLite only	Absolute path to the SQLite .db file (e.g. /var/www/app/database/app.db).

Supported Drivers

Driver value	Database	Default port	Notes
mysql	MySQL / MariaDB	3306	Recommended for most applications.
pgsql	PostgreSQL	5432	Requires the PHP pgsql extension. INSERT automatically uses RETURNING id to retrieve the last inserted ID.
sqlsrv	Microsoft SQL Server	1433	Requires the PHP sqlsrv extension. LIMIT is translated to OFFSET … FETCH NEXT … ROWS ONLY.
sqlite	SQLite 3	n/a	File-based. No username, password, or port needed. Set path to the absolute path of the .db file.

Note

LIMIT syntax is automatically adapted per driver so the same ->limit() call produces correct SQL for MySQL, PostgreSQL, SQLite, and SQL Server without any extra configuration.

Connecting

Auto-connect (recommended)

Register 'database' in app/config/autoload.php to connect on every request:

<?php
$autoload['libraries'] = ['database'];

This connects to the main group and assigns the instance to $this->db.

Manual connect

<?php
// Connect to the 'main' group → $this->db
$this->call->database();

// Connect to a named group → $this->analytics
$this->call->database('analytics');

Multiple simultaneous connections

<?php
$this->call->database();             // → $this->db
$this->call->database('analytics');  // → $this->analytics

$users  = $this->db->table('users')->get_all();
$events = $this->analytics->table('events')->get_all();

Note

The property name on $this always matches the group name from database.php. The default main group is always $this->db.

Query Builder

Selecting Data

<?php
// SELECT * FROM users
$users = $this->db->table('users')->get_all();

// SELECT specific columns
$users = $this->db->table('users')
                  ->select('id, name, email')
                  ->get_all();

// Aggregate functions with optional alias
$this->db->table('orders')->select_count('id', 'total')->get_all();
$this->db->table('products')->select_max('price', 'highest')->get_all();
$this->db->table('products')->select_min('price', 'lowest')->get_all();
$this->db->table('products')->select_sum('stock', 'total_stock')->get_all();
$this->db->table('products')->select_avg('price', 'avg_price')->get_all();
$this->db->table('users')->select_distinct('country')->get_all();

Aggregate method reference

Method	SQL equivalent
select_max($col, $alias)	MAX(col) AS alias
select_min($col, $alias)	MIN(col) AS alias
select_sum($col, $alias)	SUM(col) AS alias
select_count($col, $alias)	COUNT(col) AS alias
select_avg($col, $alias)	AVG(col) AS alias
select_distinct($col, $alias)	DISTINCT(col) AS alias

Joins

<?php
$this->db->table('posts')->inner_join('users', 'users.id = posts.user_id')->get_all();
$this->db->table('posts')->left_join('comments', 'comments.post_id = posts.id')->get_all();
$this->db->table('posts')->right_join('categories', 'categories.id = posts.cat_id')->get_all();
$this->db->table('orders')->full_outer_join('customers', 'customers.id = orders.user_id')->get_all();
$this->db->table('orders')->left_outer_join('products', 'products.id = orders.product_id')->get_all();
$this->db->table('orders')->right_outer_join('products', 'products.id = orders.product_id')->get_all();

Join method reference

Method	SQL join type
join($table, $cond)	JOIN
inner_join($table, $cond)	INNER JOIN
left_join($table, $cond)	LEFT JOIN
right_join($table, $cond)	RIGHT JOIN
full_outer_join($table, $cond)	FULL OUTER JOIN
left_outer_join($table, $cond)	LEFT OUTER JOIN
right_outer_join($table, $cond)	RIGHT OUTER JOIN

WHERE Conditions

Basic WHERE

<?php
// Equality (implicit =)
$this->db->table('users')->where('status', 'active')->get_all();

// With explicit operator
$this->db->table('products')->where('price', '>', 100)->get_all();
$this->db->table('orders')->where('total', '>=', 500)->get_all();

// Multiple conditions (AND by default)
$this->db->table('users')
         ->where('status', 'active')
         ->where('role', 'admin')
         ->get_all();

// Array syntax
$this->db->table('users')
         ->where(['status' => 'active', 'role' => 'admin'])
         ->get_all();

// OR / NOT variants
$this->db->table('users')->or_where('role', 'moderator')->get_all();
$this->db->table('users')->not_where('status', 'banned')->get_all();
$this->db->table('users')->or_not_where('status', 'suspended')->get_all();

NULL checks

<?php
$this->db->table('users')->where_null('deleted_at')->get_all();
$this->db->table('users')->where_not_null('email_verified_at')->get_all();

LIKE

<?php
$this->db->table('users')->like('name', '%john%')->get_all();
$this->db->table('users')->not_like('name', 'admin')->get_all();
$this->db->table('users')->or_like('email', 'gmail')->get_all();
$this->db->table('users')->or_not_like('name', 'test')->get_all();

BETWEEN

<?php
$this->db->table('products')->between('price', 100, 500)->get_all();
$this->db->table('orders')->not_between('total', 0, 10)->get_all();
$this->db->table('products')->or_between('price', 50, 100)->get_all();
$this->db->table('products')->or_not_between('stock', 0, 5)->get_all();

IN

<?php
$this->db->table('users')->in('id', [1, 2, 3])->get_all();
$this->db->table('users')->not_in('status', ['banned', 'suspended'])->get_all();
$this->db->table('users')->or_in('role', ['admin', 'moderator'])->get_all();
$this->db->table('users')->or_not_in('id', [99, 100])->get_all();

Grouped conditions

Use grouped() with a closure to wrap conditions in parentheses:

<?php
// WHERE status = 'active' AND (role = 'admin' OR role = 'moderator')
$this->db->table('users')
         ->where('status', 'active')
         ->grouped(function($q) {
             $q->where('role', 'admin')
               ->or_where('role', 'moderator');
         })
         ->get_all();

WHERE method reference

Method	Description
where($col, $op, $val)	AND WHERE
or_where($col, $op, $val)	OR WHERE
not_where($col, $op, $val)	AND NOT WHERE
or_not_where($col, $op, $val)	OR NOT WHERE
where_null($col)	AND WHERE col IS NULL
where_not_null($col)	AND WHERE col IS NOT NULL
like($col, $val)	AND WHERE col LIKE
not_like($col, $val)	AND WHERE col NOT LIKE
or_like($col, $val)	OR WHERE col LIKE
or_not_like($col, $val)	OR WHERE col NOT LIKE
between($col, $v1, $v2)	AND WHERE col BETWEEN
not_between($col, $v1, $v2)	AND WHERE col NOT BETWEEN
or_between($col, $v1, $v2)	OR WHERE col BETWEEN
or_not_between($col, $v1, $v2)	OR WHERE col NOT BETWEEN
in($col, $arr)	AND WHERE col IN (…)
not_in($col, $arr)	AND WHERE col NOT IN (…)
or_in($col, $arr)	OR WHERE col IN (…)
or_not_in($col, $arr)	OR WHERE col NOT IN (…)
grouped(Closure)	Wrap conditions in parentheses

Ordering, Grouping & Limiting

<?php
// ORDER BY
$this->db->table('posts')->order_by('created_at', 'DESC')->get_all();
$this->db->table('products')->order_by('RAND()')->get_all();

// GROUP BY — string or array
$this->db->table('orders')->group_by('status')->get_all();
$this->db->table('orders')->group_by(['status', 'user_id'])->get_all();

// HAVING
$this->db->table('orders')
         ->select('user_id, SUM(total) as total_spent')
         ->group_by('user_id')
         ->having('total_spent', '>', 1000)
         ->get_all();

// LIMIT (driver-aware)
$this->db->table('posts')->limit(10)->get_all();
$this->db->table('posts')->limit(20, 10)->get_all();  // offset 20, take 10

// OFFSET (standalone)
$this->db->table('posts')->limit(10)->offset(20)->get_all();

// Pagination shortcut — page number based
$this->db->table('posts')->pagination(10, 2)->get_all();  // page 2, 10/page

// COUNT rows matching conditions
$total = $this->db->table('users')->where('status', 'active')->count();

Inserting Data

<?php
// Single row
$this->db->table('users')->insert([
    'name'       => 'Jane Doe',
    'email'      => 'jane@example.com',
    'created_at' => date('Y-m-d H:i:s'),
]);
$id = $this->db->last_id();

// Bulk insert — multiple rows in one query
$this->db->table('users')->bulk_insert([
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob',   'email' => 'bob@example.com'],
]);

Updating Data

<?php
// Standard update with WHERE
$this->db->table('users')
         ->where('id', 42)
         ->update(['name' => 'Jane Smith', 'updated_at' => date('Y-m-d H:i:s')]);

// Increment / decrement a numeric column
$this->db->table('posts')->where('id', 1)->increment('views');
$this->db->table('products')->where('id', 5)->decrement('stock', 3);

// Bulk update — different values per row, keyed by primary key
$this->db->table('users')->bulk_update([
    ['id' => 1, 'status' => 'active'],
    ['id' => 2, 'status' => 'inactive'],
], 'id');

Deleting Data

<?php
// Single row
$this->db->table('users')->where('id', 42)->delete();

// Multiple rows
$this->db->table('users')->in('id', [1, 2, 3])->delete();

// Check rows affected
$deleted = $this->db->table('sessions')
                    ->where('last_seen', '<', date('Y-m-d', strtotime('-30 days')))
                    ->delete();

Result Methods

<?php
// All rows as associative arrays
$rows = $this->db->table('users')->result_array();

// All rows as stdClass objects
$rows = $this->db->table('users')->result();

// All rows as numeric arrays
$rows = $this->db->table('users')->result_num();

// Single row as associative array / object
$user = $this->db->table('users')->where('id', 1)->row_array();
$user = $this->db->table('users')->where('id', 1)->row();

// Single row as numeric array
$row = $this->db->table('users')->where('id', 1)->row_num();

// Row by index (0-based)
$second = $this->db->table('users')->row_array(1);

// First and last row
$first = $this->db->table('posts')->order_by('id')->first_row();
$last  = $this->db->table('posts')->order_by('id')->last_row();

// Map to a custom class
$objects = $this->db->table('users')->custom_result_object('UserEntity');
$object  = $this->db->table('users')->where('id', 1)->custom_row_object(0, 'UserEntity');

// Single column from all rows (0-based column index)
$emails = $this->db->table('users')->select('email')->get_column(0);

// Key-value pairs — first column as key, second as value (great for dropdowns)
$options = $this->db->table('users')->select('id, name')->get_key_pair();

// Group results by the first column's value
$grouped = $this->db->table('orders')->select('status, id, total')->get_grouped();

// Counts
$num  = $this->db->row_count();    // rows affected by last exec()
$num  = $this->db->num_rows();     // rows in last get() / get_all() result

// Last insert ID and last SQL string
$id  = $this->db->last_id();
$sql = $this->db->get_sql();

Result method reference

Method	Returns
result_array()	All rows as array of associative arrays
result()	All rows as array of stdClass objects
result_num()	All rows as array of numeric arrays
row_array($index)	Single row as associative array (optional 0-based index)
row($index)	Single row as stdClass object (optional 0-based index)
row_num()	Single row as numeric array
first_row()	First row as stdClass object
last_row()	Last row as stdClass object
custom_result_object($class)	All rows mapped to instances of $class
custom_row_object($index, $class)	Single row at $index mapped to an instance of $class
get_column($index)	All values from one column (0-based column index)
get_key_pair()	Associative array — first column as key, second as value
get_grouped()	Results grouped by the first column’s value
num_rows()	Row count from the last get() / get_all() call
row_count()	Rows affected by the last exec() (INSERT/UPDATE/DELETE)
last_id()	Auto-increment ID of the last inserted row
get_sql()	Last SQL string sent to the database

Raw Queries

<?php
// Simple raw query
$stmt  = $this->db->raw('SELECT * FROM users WHERE status = "active"');
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// With bound parameters (always preferred for user input)
$stmt = $this->db->raw(
    'SELECT * FROM users WHERE email = ? AND status = ?',
    ['jane@example.com', 'active']
);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

Warning

Always pass user input as bound parameters to raw(). Never interpolate $_GET or $_POST values directly into a SQL string.

Transactions

Group multiple write operations into a single atomic unit. Nested transactions are supported via savepoints.

<?php
$this->db->transaction();

try
{
    $this->db->table('orders')->insert($order_data);
    $order_id = $this->db->last_id();

    foreach ($items as $item) {
        $this->db->table('order_items')->insert([
            'order_id'   => $order_id,
            'product_id' => $item['id'],
            'qty'        => $item['qty'],
        ]);
        $this->db->table('products')
                 ->where('id', $item['id'])
                 ->decrement('stock', $item['qty']);
    }

    $this->db->commit();
}
catch (Exception $e)
{
    $this->db->roll_back();
    log_message('error', 'Order transaction failed: ' . $e->getMessage());
}

Method	Description
transaction()	Begin a transaction (or create a savepoint for nested calls)
commit()	Commit the transaction (or release the savepoint)
roll_back()	Roll back to the last savepoint, or fully roll back the transaction

Tips and Best Practices

Practice	Details
Keep SQL in models	Controllers should call model methods; never build queries directly in a controller.
Prefer the Query Builder	Values are bound as prepared statement parameters automatically — no manual escaping needed.
Use bound parameters for raw SQL	Always pass user input as $args to raw(); never interpolate $_GET / $_POST values into a query string.
Use transactions for multi-step writes	Wrap any operation involving more than one related INSERT/UPDATE/DELETE in transaction() / commit() / roll_back().
Avoid SELECT *	Always name the columns you need to prevent accidentally exposing sensitive fields and to reduce unnecessary data transfer.
Use get_key_pair() for dropdowns	Returns ['id' => 'label'] arrays directly usable in <select> elements without any extra mapping in the controller.
Secure credentials	Load database passwords from environment variables and never commit them to version control.
Debug with get_sql()	Call $this->db->get_sql() after a query to inspect the raw SQL that was sent to the database. Remove all such calls before deploying.