Database Configuration

All information regarding the database (credentials) should be stored in the .env file and retrieved by /config/database.php. This configuration information includes the host, username, password, database name, driver, port, and path to sqlite file (if the selected driver is sqlite).

Currently, Zap only supports MySQL and SQLite drivers. If you wish, you can replace Zap database wrapper with those used in other PHP frameworks.

When the selected driver is MySQL (written just mysql, all lowercase), the database needs to be ready. In the development mode, you can use php console make:database to create a new local database. This CLI command does not work when the selected driver is SQLite (written just sqlite, all lowercase). However, once the database connection is made, the sqlite file will be created automatically in the given path.

Database structure can be created programmatically by using migration through CLI. If your app creates database structure during setup, you can use Schema Builder in a dedicated model for setup.

Making Connection

Database connection can be made or created anywhere in the app, and it has been registered in the Container in /bootstrap/bindings.php. By default, the database connection is made in the BaseModel, using the db() method. By doing this, the connection is only made when we really need it.

There are many ways to use the connection in the model. You can store the instance in a protected variable like $connection or directly use the $this->db()->query(...). Here are a few examples:

<?php
namespace Zap\App\Models;
use Zap\Core\Base\BaseModel;
use Zap\Core\Utils\Container;
use Zap\Core\Db\Database;

class UsersModel extends BaseModel {
protected mixed $connection;
public function __construct(){
 $this->connection = Container::getInstance()->make(Database::class);
 parent::__construct();
}
public function getActiveUsers(){
 $users = $this->connection->query('SELECT * FROM users WHERE status = ?', ['active']);
 return $users->fetchAll();
}
}

While this may be convenient for most people, doing this will always open a database connection whenever the model is instantiated in the controller method. This is less productive, particularly when you call a method in the model that does not need the connection. Use this alternative instead:

<?php
namespace Zap\App\Models;
use Zap\Core\Base\BaseModel;

class UsersModel extends BaseModel {

public function getActiveUsers(){
 $users = $this->db()->query('SELECT * FROM users WHERE status = ?', ['active'])->fetchAll();
 return $users;
}

public function sayHello(string $name){
 return 'Hello ' . $name;
}
}

Basic CRUD Operations

To perform basic Create, Read, Update, and Delete operations, you can use built-in methods in the BaseModel class you extend. It is very convenient, particularly when the data you send has been well prepared in the controller method.

Create

To insert new record, you can use the create(string $table, array $data) method, which returns boolean (true if success, otherwise, false). Using Try/Catch is also recommended.

public function addNewUser($userData){
try{
 $this->create('users', $userData);
 return [
 'status' => 'success',
 'message' => 'New user added'
 ];
}catch(\Exception $e){
 return [
 'status' => 'error',
 'message' => 'Error occured: ' . $e->getMessage()
 ];
}
}

While this does not implement technique like $fillable, such as in Laravel, you can prepare the data in the controller method by using Zap ArrayEngine. That class can be used to remove particular keys or to include only particular keys from the data.

Read

To retrieve data from the database, you can use the built-in method read(string $callback, string $table, array $where, array $options). For instance,

public function getUsersByStatus(string $status){
 $users = $this->read('fetchAll', 'users', ['status'=>'active']);
 return $users;
}

public function getUserById(int $id){
 $user = $this->read('fetchArray', 'users', ['id' => $id], ['limit' => 1]);
 return $user;
}

The $callback argument can be fetchAll or fetchArray. This depends on whether you want to return a list of records or only one record.

Update

To update records, you can use the built-in method update(string $table, array $data, array $where), which returns boolean.

public function updateUser(int $id, array $newData) {
try{
 $this->update('users', $newData, ['id' => $id]);
 return [
 'status' => 'success',
 'message' => 'User data updated'
 ];
}catch(\Exception $e){
 return [
 'status' => 'error',
 'message' => 'Error occured: ' . $e->getMessage()
 ];
}
}

Delete

To delete records from the database, you can use the built-in method delete(string $table, array $where). For example:

public function deleteUserById(int $id){
try{
 $this->delete('users', ['id' => $id]);
 return [
 'status' => 'success',
 'message' => 'User deleted'
];
}catch(\Exception $e){
 return [
 'status' => 'error',
 'message' => 'Error occured: ' . $e->getMessage()
];
}
}

Using Query Builder

For more complex queries, you can use either db()->query(string $sql, array $params) or Zap QueryBuilder by using builder(). The power of builder() is that it can create SQL string with complex where clauses and options. The QueryBuilder returns an associative array containing two keys, the sql (generated SQL query with named PDO placeholders), and the params (an associative array mapping parameter placeholders to their bound values).

Complex Select

The basic syntax for select is this:

QueryBuilder::select(
 string $table,
 string|array $columns = '*',
 array $where = [],
 array $options = []
): array

//in the model method, you can do $this->builder()::select()

In the where clause, you can use various operators, like:

$result = QueryBuider::select(
 table: 'users',
 columns: ['id', 'username', 'email'],
 where: [
 'status' => 'active',
 'age' => ['>=', 19],
 'name%' => 'john'
 ]
);

The builder will look for users age = or > 19 and name LIKE john. It automatically appends LIKE %john%.

It also support IN and NULL checks:

$result = QueryBuilder::select(
 table: 'products',
 columns: '*',
 where: [
 'category_id'  => ['IN' => [1, 5, 12]],
 'deleted_at'   => null,                    // IS NULL
 'published_at' => ['NOT NULL']             // IS NOT NULL
 ]
);

For nested logic (_or groups), you can use the special _or key to build logical OR conditions as follows:

//direct OR
$result1 = QueryBuilder::select(
table: 'orders',
columns: '*',
where: [
 'status' => 'pending',
 '_or'    => [
 'total' => ['>', 500],
 'is_vip' => 1
 ]
]
);

//grouped OR with sequential list (A AND B) OR (C AND D)
$result2 = QueryBuilder::select(
table: 'accounts',
columns: ['id', 'email', 'role'],
where: [
 'active' => 1,
 '_or' => [
 ['role' => 'admin', 'level' => ['>=', 3]],
 ['role' => 'manager', 'department' => 'finance']
 ]
]
);

Select with JOIN

The QueryBuilder::select() method supports INNER, LEFT, RIGHT, FULL, and CROSS joins via the $options['join'] array parameter. Join operations build table relationships and resolve qualified column names (table.column) without breaking PDO parameter binding.

Joins are passed as an array of tuples inside $options['join']. Each join definition requires an indexed array with 3 mandatory elements and 1 optional element:

[
 'table_to_join',   // [0] Foreign table name or alias
 'column_1',        // [1] Left column identifier (e.g., table1.id)
 'column_2',        // [2] Right column identifier (e.g., table2.foreign_id)
 'JOIN_TYPE'        // [3] Optional. 'INNER', 'LEFT', 'RIGHT', 'FULL', 'CROSS' (Default: 'INNER')
]

Join Types and Examples

Single INNER JOIN (Default)

If the 4th element (join type) is omitted, QueryBuilder defaults to INNER JOIN.

$query = QueryBuilder::select(
 table: 'posts',
 columns: ['posts.id', 'posts.title', 'users.username AS author'],
 where: [
 'posts.status' => 'published'
 ],
 options: [
 'join' => [
 ['users', 'posts.author_id', 'users.id'] // Defaults to INNER JOIN
 ],
 'order' => 'posts.created_at DESC'
 ]
);

LEFT JOIN with Complex Filters

Specify 'LEFT' as the 4th element to preserve records from the primary table even when matching join rows are absent.

$query = QueryBuilder::select(
table: 'users',
columns: ['users.id', 'users.email', 'orders.id AS order_id', 'orders.total'],
where: [
 'users.status'  => 'active',
 'orders.status' => null // Find users with NO matching orders (IS NULL)
],
options: [
 'join' => [
 ['orders', 'users.id', 'orders.user_id', 'LEFT']
 ]
]
);

Multiple Joined Tables (INNER + LEFT)

Multiple table relationships can be chained by passing additional join definitions inside the $options['join']

$query = QueryBuilder::select(
table: 'order_items',
columns: [
 'order_items.id',
 'orders.code AS order_code',
 'products.name AS product_name',
 'categories.title AS category_title'
],
where: [
 'orders.created_at' => ['>=', '2026-01-01'],
 '_or' => [
 'products.price' => ['>', 100],
 'categories.is_featured' => 1
 ]
],
options: [
 'join' => [
 ['orders', 'order_items.order_id', 'orders.id', 'INNER'],
 ['products', 'order_items.product_id', 'products.id', 'INNER'],
 ['categories', 'products.category_id', 'categories.id', 'LEFT']
 ],
 'order' => 'orders.created_at DESC',
 'limit' => 20
]
);

Finally, you can execute the queries with $this->db()->query().

$query = QueryBuilder::select(
 table: 'comments',
 columns: ['comments.body', 'users.username', 'posts.title AS post_title'],
 where: [
 'comments.approved' => 1
 ],
 options: [
 'join' => [
 ['users', 'comments.user_id', 'users.id', 'INNER'],
 ['posts', 'comments.post_id', 'posts.id', 'INNER']
 ],
 'limit' => 5
 ]
);

// Fetch joined rows from the database instance
$recentComments = $this->db()->select($query['sql'], $query['params']);

Complex Update

QueryBuilder::update() accepts $data for the values to set and a $where array that supports all the same filtering features as select() (including comparison operators, IN clauses, auto-wildcard %, and _or grouping).

Bulk Status Update with Condition Groups (_or + IN)

$table = 'users';
$data = [
 'status'        => 'suspended',
 'suspended_at'  => '2026-10-04 18:00:00',
 'updated_by'    => 'system'
];

$where = [
 'account_type' => ['IN' => ['standard', 'freemium']],
 '_or' => [
 'failed_logins' => ['>=', 5],
 'last_login%'    => '2025-' // Auto-LIKE pattern matching
 ]
];

// 1. Generate SQL and Params via QueryBuilder
$query = QueryBuilder::update($table, $data, $where);

// 2. Execute via Database
$affectedRows = $this->db()->query($query['sql'], $query['params'])->numRows();

Complex Delete

QueryBuilder::delete() enforces non-empty WHERE arrays to prevent accidental full-table wipes. It leverages buildWhere() to allow precise scoping.

Soft-Delete Cleanup with Multi-Condition Scoping

Delete inactive records that meet multiple age/status metrics using _or array list syntax: (A AND B) OR (C AND D).

$where = [
'is_archived' => 1,
'_or' => [
 // Group 1: Guest accounts inactive over 30 days
 ['role' => 'guest', 'last_seen' => ['<', '2026-09-01']],
 
 // Group 2: Unverified accounts with null tokens
 ['role' => 'unverified', 'email_verified_at' => null]
]
];

// Generate SQL statement
$query = QueryBuilder::delete('users', $where);

// Execute directly through Database instance
$deletedCount = $this->db()->delete('users', $where);

Using Schema and Blueprint

The Schema class provides database-agnostic DDL (Data Definition Language) management for table creation, structural modifications, and column management across MySQL and SQLite drivers. It utilizes Blueprint instances to define columns, indexes, and foreign keys using a fluent interface.

Schema and Blueprint are used in migrations. However, they are also available in the BaseModel via db()->schema().

Creating Tables

The create() method initializes new database tables. In SQLite, it automatically enforces foreign keys via PRAGMA foreign_keys = ON;. In MySQL, tables are created with ENGINE=InnoDB.

use Zap\Core\Db\Blueprint;

$schema->create('users', function (Blueprint $table) {
 // Auto-increment primary key
 $table->id();
 // VARCHAR(100)           
 $table->string('name', 100);
 // Unique index                         
 $table->string('email')->unique('email');                   
 $table->string('password');
 // Driver-aware ENUM or CHECK constraint
 $table->enum('role', ['admin', 'user', 'editor']);
 $table->boolean('is_active')->default(1);
 $table->timestamps();             
});

Table with Foreign Key Constraints

Use the fluent foreign key methods (foreign(), references(), on(), onDelete(), onUpdate()) to define relationships.

$schema->create('posts', function (Blueprint $table) {
 $table->id();
 $table->integer('user_id');
 $table->string('title');
 $table->text('body')->nullable();
 $table->timestamps();

 // Foreign Key definition
 $table->foreign('user_id')
 ->references('id')
 ->on('users')
 ->onDelete('CASCADE')
 ->onUpdate('CASCADE');

 // Composite/named secondary indexes
 $table->index('title', 'idx_posts_title');
});

Modifying Tables

The table() method inspects existing columns using driver metadata (PRAGMA table_info for SQLite or DESCRIBE for MySQL) to dynamically add new columns or alter existing ones.

Adding New Columns to Existing Tables

$schema->table('users', function (Blueprint $table) {
 $table->string('phone', 20)->nullable();
 $table->date('birthdate')->nullable();
});

Modifying Column Definitions

To modify a column, chain the change() method.

$schema->table('users', function (Blueprint $table) {
 // Change name length to VARCHAR(150) (MySQL only)
 $table->string('name', 150)->nullable()->change();
});

Schema Inspection and Column Operations

Schema includes built-in methods for checking tables/columns and executing structural modifications safely across drivers.

Checking Table and Column Existence

// Check if table exists
if ($schema->hasTable('orders')) {
 // Check if column exists
 if ($schema->hasColumn('orders', 'tracking_code')) {
 // ...
 }
}

Renaming Columns

Handles column renaming across drivers. On SQLite, Schema automatically rebuilds the table via a temporary table (__tmp_tablename) to preserve data integrity when renaming.

$schema->renameColumn('users', 'phone', 'phone_number');

Dropping Columns and Tables

Safely drops columns. On SQLite, it executes a table rebuild while verifying that the table's last remaining column is not removed.

//dropping column birthdate from users table
 $schema->dropColumn('users', 'birthdate');
 //dropping table posts
 $schema->drop('posts');