PHP ORM Database

Inspiration

Having been introduced to learning Laravel Framework; Over the past yr(s), Coming back to vanilla PHP, was pretty tough. So i decided to create a much more easier way of communicating with Database, using native PHP PDO:: Driver.

Documentation

Requirements

>= php 8.0+

Installation

Prior to installing database package get the Composer dependency manager for PHP because it'll simplify installation.

composer require tamedevelopers/database

Instantiate

Step 1 — Require composer autoload:

require_once __DIR__ . '/vendor/autoload.php';

Step 2 — [optional] If you want the Package scalfolding

This will auto setup your entire application on a go!
- It's helper class can be called, using -- autoloader_start()

Description
It's important to install vendor in your project root, As we use this to get your root [dir]
By default you don't need to define any path again

Files you'll see after you reload browser:
`.env` `.env.example` `.gitignore` `.htaccess` `.user.ini` `init.php`

use Tamedevelopers\Database\AutoLoader; AutoLoader::start(); // then reload your browser to allow the system scalfold for you

init php

[optional] This will extends the composer autoload and other setup
- If you used the package Package scalfolding this file will be automatically generated, that you can include at the beginning of your project.

Tame Cli

Custom commands support for CLI

php tame list

tame-cli-scaffold

Scalffold the database files, instead of running AutoLoader::start() in browser.
- The -f|--force flag is needed if you're inside a framework or if on production server.
- This doesn't alter nor replace any files that exists and safe.

php tame scaffold:run --force

tame-artisan-call

Using the CLI from within php, without the CMD interface.

use Tamedevelopers\Support\Capsule\Artisan; Artisan::call('db:wipe --force');

BootLoader

[optional] from version ^6.0.3 If you do not want to include or use the init.php file
- You can as well call the bootloader, to start the database life-circle.

use Tamedevelopers\Database\Capsule\AppManager; AppManager::bootLoader(); // app_manager()->bootLoader();

Database Connection

Take two param as [$name|$options]
- Mandatory $name as string of connection name
- [optional] $options and an array, if no connection data is found
- First navigate to [config/database.php] file and add connection configuration or use .env

DB::connection('connName', $options);

Database Disconnect

If you want to connect to already connected database, You first need to disconnect
- Takes one param as string

DB::disconnect('connName');

Database Reconnect

same as Database Connection

DB::reconnect('connName', $options);

App Debug Env

The .env file contains a key called APP_DEBUG
- It's mandatory to set to false on Production environment
- This helps to secure your applicaiton and exit with error 404
- instead of displaying entire server errors.

key	Type	Default Value
APP_DEBUG	boolean	`true`

Database Connection Keys

All available connection keys
- The DB_CONNECTION uses only mysql
- No other connection type is supported for now.

key	Type	Default Value
driver	string	`mysql`
host	string	`localhost`
port	int	`3306`
database	string
username	string
password	string
charset	string	`utf8mb4`
collation	string	`utf8mb4_unicode_ci`
prefix	string
prefix_indexes	bool	`false`

Usage

All Methods of usage
- Without calling the DB::connection() and passing the driver name you want. It will automatically be using the default connection driver, you've in your setup'

Table

Takes a parameter as string table_name

$db = DB::connection(); $db->table('users');

Insert

Takes one parameter as assoc array column_name => value
- It returns an object on success or error

DB::table('users')->insert([ 'user_id' => 10000001, 'first_name' => 'Alfred', 'last_name' => 'Pete', 'wallet_bal' => 0.00, 'registered' => strtotime('now'), ]); -- To see data, you need to save into a variable

Insert Or Ignore

Same as insert() method
- It returns an object of created data or false on error

DB::table('users')->insertOrIgnore([ 'user_id' => 10000001, 'first_name' => 'Alfred', ]);

Update

Takes one parameter as assoc array column_name => value
- Returns an int numbers of affected rows or error

DB::table('users') ->where('user_id', 10000001) ->update([ 'first_name' => 'Alfred C.', ]);

Update Or Ignore

Same as update() method
- Returns an int numbers of affected rows or 0 on error

DB::table('users') ->where('user_id', 10000001) ->updateOrIgnore([ 'first_name' => 'Alfred C.', ]);

delete

Returns an int

DB::table('users') ->where('user_id', 10000001) ->delete();

destroy

Take two param as [value|column]
- Mandatory value as mixed value
- [optional] column as Default is id
- Returns an int

DB::table('posts')->destroy(1); // Query: delete from `posts` where `id` = ?  DB::table('posts')->destroy(10, 'post_id'); // Query: delete from `posts` where `post_id` = ?

Increment

Takes three parameter
- Only the first param is required

param	Data types
column `required`	string
`count or []`	int \| array
param	array

1 By default if the the second param not passed, this will increment by 1

DB::table('users') ->where('user_id', 10000001) ->increment('wallet_bal');

DB::table('users') ->where('user_id', 10000001) ->increment('wallet_bal', 10);

You can also pass in a second or third parameter to update additional columns

DB::table('users') ->where('user_id', 10000001) ->increment('wallet_bal', 100.23, [ 'first_name' => 'F. Peterson', 'status' => 1, ]);

You can ommit the second param and it'll be automatically seen as update param (If an array)

DB::table('users') ->where('user_id', 10000001) ->increment('wallet_bal', [ 'first_name' => 'F. Peterson', 'status' => 1, ]);

Decrement

Same as Increment

DB::table('users') ->where('user_id', 10000001) ->decrement('wallet_bal', [ 'first_name' => 'F. Peterson', 'status' => 1, ]);

min

Take one param as Expression|string

DB::table('blog')->min('amount');

max

Same as min

DB::table('blog')->max('amount');

sum

Take one param as Expression|string

DB::table('blog')->sum('amount');

avg

Take one param as Expression|string

DB::table('blog')->avg('amount'); DB::table('blog')->average('amount');

Fetching Data

object name	Returns
get()	array of objects
find()	`object` \| `null`
first()	`object` \| `null`
FirstOrIgnore()	`object` \| `null`
FirstOrCreate()	object
firstOrFail()	object or exit with 404 status
count()	int
paginate()	array of objects
exists()	boolean `true` \| `false`
tableExists()	boolean `true` \| `false`

GET

DB::table('users')->get();

First

DB::table('users')->first();

First or Create

Take two param as an array
- Mandatory $conditions param as array
- [optional] $data param as array
First it checks if codition to retrieve data. If fails, then it merge the $conditions to $data value to create new records

DB::table('users')->firstOrCreate( ['email' => 'example.com'] );

or -- Example 2

DB::table('users')->firstOrCreate( ['email' => 'example.com'], [ 'country' => 'Nigeria', 'age' => 18, 'dob' => 2001, ] );

First or Fail

Same as first() method but exit with error code 404, if data not found

DB::table('users')->firstOrFail();

Count

DB::table('users')->count();

Paginate

Takes param as int $per_page
- By default if no param is given, then it displays 10 per page

$users = DB::table('users') ->paginate(40); $users // this will return the data objects $users->links() // this will return the paginations links view $users->showing() // Display items of total results

Exists

Returns boolean true \| false

DB::table('users') ->where('email', 'email@gmail.com') ->orWhere('name', 'Mandison') ->exists();

Table Exists

Takes param as string $table_name

DB::tableExists('users');

Collections

You can directly use methods of Collections Instance on any of the below
All the below methods are received by Collection class
1. get()
2. find()
3. first()
4. firstOrIgnore()
5. firstOrCreate()
6. firstOrFail()
7. insert()
8. insertOrIgnore()

Collection Methods

Methods	Description
getAttributes()	`array` Returns an array of data
getOriginal()	`object` Returns an object of data
isEmpty()	`boolean` `true \| false` If data is empty
isNotEmpty()	`opposite` of `->isEmpty()`
count()	`int` count data in items collection
toArray()	`array` Convert items to array
toObject()	`object` Convert items to object
toJson()	`string` Convert items to json

Collection Usage

Colections are called automatically on all Database Fetch Request
- With this you can access data as an object\|array key property
- If no data found then it returns null on ->first() method only

$user = DB::tableExists('users') ->first(); if($user){ $user->first_name $user['first_name'] } $user->toArray() $user->getAttributes()

Example two(2) ->get() \| ->paginate() Request

$users = DB::tableExists('users') ->where('is_active', 1), ->random(), ->get(); if($users->isNotEmpty()){ foreach($users as $user){ $user->first_name $user['first_name'] $user->toArray() $user->getAttributes() } }

Auth

Lightweight guard-based authentication similar to Laravel.
attempt() only validates and sets in-memory user; call login() to persist to session.

method name	Description
guard()	Create a guard bound to a table and [optional] connection.
attempt()	Validate credentials, set in-memory user on success; does not persist to session.
login()	Persist the current user (or provided array) to session. If userData is not an array, it’s ignored.
user()	Get the in-memory user or rehydrate from session if available.
id()	Get the authenticated user’s id (or custom key).
logout()	Clear in-memory user and remove from session.

auth-guard

Set the authentication guard (Takes two param)
- Mandatory $table param as string
- [optional] $connection param as string | null database connection name.

use Tamedevelopers\Database\Auth; $admin = Auth::guard('admins');

Auth Usage

use Tamedevelopers\Database\Auth; // Create guards $admin = (new Auth)->guard('tb_admin'); $user = (new Auth)->guard('tb_user', 'woocommerce'); // Credentials (password is required in attempt) $credentials = [ 'email' => 'peter.blosom@gmail.com', 'status' => '1', 'password' => 'tagged', ]; // 1) Validate credentials only (no session persistence) if ($user->attempt($credentials)) { // In-memory user available $user->check(); // true $user->id(); // e.g., 123 $user->user(); // full user array } // 2) Persist explicitly (similar to Laravel Auth::login()) $user->login($user->user()); // stores sanitized user in session (no password) // 3) Retrieve later in another request $another = (new Auth)->guard('tb_user', 'woocommerce'); $another->user(); // rehydrated from session $another->check(); // true if session had user // 4) Logout $another->logout(); // clears in-memory and session

Pagination

Configuring Pagination
- It's helper class can be called, using -- config_pagination()

key	Data Type	Description
allow	`true` \| `false`	Default `false` Setting to true will allow the system use this settings across app
class	string	Css `selector` For pagination ul tag in the browser
span	string	Default `.page-span` Css `selector` For pagination Showing Span tags in the browser
view	`bootstrap` \| `cursor` \| `loading` \| `onloading`	Default `simple` - For pagination design
first	string	Change the letter `First`
last	string	Change the letter `Last`
next	string	Change the letter `Next`
prev	string	Change the letter `Prev`
showing	string	Change the letter `Showing`
of	string	Change the letter `of`
results	string	Change the letter `results`
buttons	int	Numbers of pagination links to generate. Default is 5 and limit is 20

Global Configuration

1 Setup global pagination on ENV autostart most preferred method

AutoLoader::configPagination([ 'allow' => true, 'prev' => 'Prev Page', 'last' => 'Last Page', 'next' => 'Next Page', 'view' => 'bootstrap', 'class' => 'Custom-Class-Css-Selector', ]);

Pagination Query

$users = DB::table('users')->paginate(40);

Pagination Data

$users // This will return `Collections` of pagination data

Pagination Links

$users->links(); // This will return pagination links view

Pagination Links Config

You can directly configure pagination links
- It'll override the global settings

$users->links([ 'first' => 'First Page', 'last' => 'Last Page', 'prev' => 'Previous Page', 'next' => 'Next Page', 'no_content' => 'All videos has been loaded', ])

Pagination Showing

$users->showing(); // This will create a span html element with text <span class='page-span'> Showing 0-40 of 500 results </span>

Pagination Showing Config

You can configure showing text directly as well

$users->showing([ 'showing' => 'Showing', 'of' => 'out of', 'results' => 'Results', 'span' => 'css-selector', ])

Pagination Foreach Numbers

Page numbering starts counting from 1
- This will format all pagination items collections
- On each page, it starts counting from last pagination item number

$users = DB::table('users')->paginate(20); foreach($users as $user){ echo $user->numbers(); }

Pagination Ajax Loading

When the view is either loading| | onloading | cursor | bootstrap
- This can automatically fetched data without page load
- You need to give your DOM-element data-pagination-content and data-pagination-append

$users = DB::table('users')->paginate(20);

<div data-pagination-content> <div class="wallet-container" data-pagination-append> <?php foreach($users as $user) {?> <!-- Content to be loaded structure --> <?php }?> </div> </div> or <div data-pagination-content data-pagination-append> <!-- Content to be loaded structure --> </div> <!-- pagination links --> <div> <?= $users->links([ 'no_content' => 'All users have been loaded.' ]); ?> </div>

Get Pagination

Returns pagination informations

key	Description
limit	Pagination limit `int`
offset	Pagination offset `int`
page	Pagination Current page `int`
pageCount	Pagination Total page count `int`
perPage	Pagination per page count `int`
totalCount	Pagination total items count `int`

$users = DB::table('users')->paginate(20); $users->getPagination();

Clause

Multiple clause

Query

Allows the use direct sql query SQL query syntax
- Or direct query exec()
- [important] you cannot use paginate on query() method

DB::query("SHOW COLUMNS FROM users") ->limit(10) ->get(); DB::query("ALTER TABLE `langs` ADD COLUMN es TEXT; UPDATE `langs` SET es = en;") ->exec();

Select

Used to select needed columns from database

DB::table('users') ->where('user_id', 10000001) ->select(['first_name', 'email']) ->select('email', 'name') ->first();

orderBy

Takes two param $column and $direction
- By default $direction param is set to ASC

DB::table('wallet') ->orderBy('date', 'DESC') ->get();

orderByRaw

Takes one param $query

DB::table('wallet') ->orderByRaw('CAST(`amount` AS UNSIGNED) DESC') ->get();

Latest

Takes one param $column by default the column used is id

DB::table('wallet') ->latest('date') ->get();

Oldest

Takes one param $column by default the column used is id

DB::table('wallet') ->oldest() ->get();

inRandomOrder

DB::table('wallet') ->inRandomOrder() ->get();

random

Same as inRandomOrder()

DB::table('wallet') ->random() ->get();

limit

Takes one param $limit as int. By default value is 1

DB::table('wallet') ->limit(10) ->get();

offset

Takes one param $offset as int. By default value is 0

DB::table('wallet') ->limit(3) ->offset(2) ->get();

Example 2 (Providing only offset will return as LIMIT without error)

DB::table('wallet') ->offset(2) ->get();

join

Includes join|leftJoin|rightJoin|crossJoin

Params	Description
table	table
foreignColumn	table.column
operator	operator sign
localColumn	local_table.column

DB::table('wallet') ->join('users', 'users.user_id', '=', 'wallet.user_id') ->get();

or

DB::table('wallet') ->join('users', 'users.user_id', '=', 'wallet.user_id') ->where('wallet.email', 'example.com') ->orWhere('wallet.user_id', 10000001) ->paginate(10);

leftJoin

Same as join

DB::table('wallet') ->leftJoin('users', 'users.user_id', '=', 'wallet.user_id') ->where('wallet.email', 'example.com') ->get();

where

Takes three parameter
- Only the first param is required

param	Data types
column	string
operator	string
value	string

DB::table('wallet') ->where('user_id', 10000001) ->where('amount', '>', 10) ->where('balance', '>=', 100) ->get();

orWhere

Same as Where clause

DB::table('wallet') ->where('user_id', 10000001) ->where('amount', '>', 10) ->orWhere('first_name', 'like', '%Peterson%') ->where('amount', '<=', 10) ->get();

whereRaw

Allows you to use direct raw SQL query syntax

$date = strtotime('next week'); DB::table("tb_wallet") ->whereRaw("NOW() > created_at") ->whereRaw("date >= ?", [$date]) ->where(DB::raw("YEAR(created_at) = 2022")) ->where('email', 'email@gmail.com') ->limit(10) ->random() ->get();

whereColumn

Takes three parameter column operator column2

DB::table('wallet') ->where('user_id', 10000001) ->whereColumn('amount', 'tax') ->whereColumn('amount', '<=', 'balance') ->get();

whereNull

Takes one parameter column

DB::table('wallet') ->where('user_id', 10000001) ->whereNull('email_status') ->get();

whereNotNull

Takes one parameter column

DB::table('wallet') ->where('user_id', 10000001) ->whereNotNull('email_status') ->get();

whereBetween

Takes two parameter column as string param as array
- Doesn't support float value

param	Data types	Value
column	string	`column_name`
param	array	[10, 100]

DB::table('wallet') ->where('user_id', 10000001) ->whereBetween('amount', [0, 100]) ->get();

whereNotBetween

Same as whereBetween() method

DB::table('wallet') ->where('user_id', 10000001) ->whereNotBetween('amount', [0, 100]) ->get();

whereIn

Takes two parameter column as string param as array
- Doesn't support float value

param	Data types	Value
column	string	`column_name`
param	array	[0, 20, 80]

DB::table('wallet') ->where('user_id', 10000001) ->whereIn('amount', [10, 20, 40, 100]) ->get();

whereNotIn

Same as whereIn() method

DB::table('wallet') ->where('user_id', 10000001) ->whereNotIn('amount', [10, 20, 40, 100]) ->get();

groupBy

Takes one param $column

DB::table('wallet') ->where('user_id', 10000001) ->groupBy('amount') ->get();

Database Migration

Similar to Laravel DB Migration Just to make database table creation more easier

method name	Returns
create()	Create table schema
run()	Begin migration
drop()	Drop migration tables

use Tamedevelopers\Database\Migrations\Migration;

Create Table Schema

Takes param as string $table
- [optional] Second parameter string jobs|sessions If passed will create a dummy jobs|sessions table schema
- It's helper class can be called, using -- migration()

Migration::create('users'); Migration::create('users_wallet'); Migration::create('tb_jobs', 'jobs'); Migration::create('tb_sessions', 'sessions'); // migration()->create('users'); // Table `2023_04_19_1681860618_user` has been created successfully // Table `2023_04_19_1681860618_user_wallet` has been created successfully // Table `2023_04_19_1681860618_tb_jobs` has been created successfully // Table `2023_04_19_1681860618_tb_sessions` has been created successfully

Default String Length

In some cases you may want to setup default string legnth to all Migration Tables
- It's helper class can be called, using -- schema()

Description
The Default Set is `255` But you can override by setting custom value
According to MySql v:5.0.0 Maximum allowed legnth is `4096` chars
If provided length is more than that, then we'll revert to default as the above
This affects only `VACHAR`
You must define this before start using the migrations

use Tamedevelopers\Database\Migrations\Schema; Schema::defaultStringLength(200); // schema()->defaultStringLength(2000);

Update Column Default Value

In some cases you may want to update the default column value
- Yes! It's very much possible with the help of Schema. Takes three (3) params
- $tablename as string
- $column_name as string
- $values as mixed data NULL NOT NULL\|None STRING current_timestamp()

use Tamedevelopers\Database\Migrations\Schema; Schema::updateColumnDefaultValue('users_table', 'email_column', 'NOT NULL'); Schema::updateColumnDefaultValue('users_table', 'gender_column', []); // or // schema()->updateColumnDefaultValue('users_table', 'gender_column', []);

Run Migration

This will execute and run migrations using files located at [root/database/migrations]

Migration::run(); or migration()->run(); // Migration runned successfully on `2023_04_19_1681860618_user`  // Migration runned successfully on `2023_04_19_1681860618_user_wallet`

Drop Migration

Be careful as this will execute and drop all files table located in the migration
[optional param] bool to force delete of tables

Migration::drop(); or migration()->drop(true);

Drop Table

Takes one param as string $table_name

use Tamedevelopers\Database\Migrations\Schema; Schema::dropTable('table_name'); or schema()->dropTable('table_name');

Drop Column

To Drop Column takes two param
- This will drop the column available

use Tamedevelopers\Database\Migrations\Schema; Schema::dropColumn('table_name', 'column_name'); or schema()->dropColumn('table_name', 'column_name');

Get Database Config

$db->getConfig()

Get Database Connection

It's helper class can be called, using -- db_connection()

$db->dbConnection()

Get Database Name

$db->getDatabaseName()

Get Database PDO

$db->getPDO()

Get Database TablePrefix

$db->getTablePrefix()

Database Import

You can use this class to import .sql into a database programatically
- Take two param as [$path|$connection]
- Mandatory $path as string of path to .sql file
- [optional] $connection define the connection of database you want to run

use Tamedevelopers\Database\DBImport; $database = new DBImport('path_to/orm.sql', 'connName'); // new DBImport(base_path('path_to/orm.sql')) // run the method $status = $database->run(); // - Status code // ->status == 404 (Failed to read file or File does'nt exists // ->status == 400 (Query to database error // ->status == 200 (Success importing to database

Update Env Variable

You can use this class to import .sql into a database programatically

Params	Description
key	ENV key
value	ENV value
allow_quote	`true` \| `false` - Default is true (Allow quotes within value)
allow_space	`true` \| `false` - Default is false (Allow space between key and value)

use Tamedevelopers\Support\Env; Env::updateENV('DB_PASSWORD', 'newPassword'); Env::updateENV('APP_DEBUG', false); Env::updateENV('DB_CHARSET', 'utf8', false); // env_update('DB_CHARSET', 'utf8', false); // Returns - Boolean // true|false

Collation And Charset

Collation and Charset Data listing

Collation

utf8_bin
utf8_general_ci
utf8mb4_bin
utf8mb4_unicode_ci
utf8mb4_general_ci
latin1_bin
latin1_general_ci

Charset

utf8
utf8mb4
latin1

Extend Model Class

You can as well extends the DB Model class directly from other class

use Tamedevelopers\Database\Model; class Post extends Model{ // define your custom model table name protected $table = 'posts'; // -- You now have access to the DB public instances public function getPost(){ return $this->select(['images', 'title', 'description'])->get(); } }

Helpers Functions

function name	Description
db()	Return instance of `new DB($options)` class
db_connection()	Same as `$db->dbConnection()`
config_pagination()	Same as `$db->configPagination()` or `AutoLoader::configPagination`
autoloader_start()	Same as `AutoLoader::start()`
env_update()	Same as `Env::updateENV` method
app_manager()	Return instance of `(new AppManager)` class
import()	Return instance of `(new DBImport)->import()` method
migration()	Return instance of `(new Migration)` class
schema()	Return instance of `(new Schema)` class

Error Dump

function	Description
dump	Dump Data
dd	Dump and Die

Error Status

On error returns 404 status code
On success returns 200 status code

Useful Links

@author Fredrick Peterson (Tame Developers)
If you love this PHP Library, you can Buy Tame Developers a coffee
Lightweight - PHP ORM Database
Support - Library

Name		Name	Last commit message	Last commit date
Latest commit History 314 Commits
.github/workflows		.github/workflows
src		src
tests		tests
.env.example		.env.example
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
composer.json		composer.json
console.md		console.md
sessions.png		sessions.png

License

tamedevelopers/database

Folders and files

Latest commit

History

Repository files navigation

PHP ORM Database

Inspiration

Documentation

Requirements

Installation

Instantiate

init php

Tame Cli

tame-cli-scaffold

tame-artisan-call

BootLoader

Database Connection

Database Disconnect

Database Reconnect

App Debug Env

Database Connection Keys

Usage

Table

Insert

Insert Or Ignore

Update

Update Or Ignore

delete

destroy

Increment

Decrement

min

max

sum

avg

Fetching Data

GET

First

First or Create

First or Fail

Count

Paginate

Exists

Table Exists

Collections

Collection Methods

Collection Usage

Auth

auth-guard

Auth Usage

Pagination

Global Configuration

Pagination Query

Pagination Data

Pagination Links

Pagination Links Config

Pagination Showing

Pagination Showing Config

Pagination Foreach Numbers

Pagination Ajax Loading

Get Pagination

Clause

Query

Select

orderBy

orderByRaw

Latest

Oldest

inRandomOrder

random

limit

offset

join

leftJoin

where

orWhere

whereRaw

whereColumn

whereNull

Packages