What is an ORM?
An ORM is an abstraction of the information of the database. For example, let's say we have a list of cities and we want to list it.
It is the PHP PDO version:
$stmt = $pdo->prepare("SELECT * FROM cities");
$stmt->execute();
$result = $stmt->get_result();
$cities=[];
while($row = $result->fetch_assoc()) {
$cities[]=$row;
}
$stmt->close();
While this code could work but it is prone to mistakes and it could be tedious in the long term. What if we could simplify it and turn more focused into a PHP model? That is the concept of ORM.
And it is the ORM version of it.
$cities=CitiesRepo::toList();
The ORM version is simple but there is more about ORM.
In general, ORM allows abstracting access to the database simplifying it.
ORM is not a solution fits all. I will explain it later.
Sometimes we don't want to add a whole framework to do this job, this example is about that.
Getting started
For this example, you will need:
PHP 7.1 or higher.
- PHP must have the PDO and PDOMYSQL extensions installed.
Composer
Mysql with the example tables
- For this example, I will use the next database: Sakila-Database-2021
And we will use the next library:
- 1) Create a new project using composer and add our library. (open a terminal, bash, PowerShell, cmd, etc.)
composer init # and fill the information of your project.
composer require eftec/pdoonerepo
If the composer fails, then remember to install it and add it to the path.
2) Once it is done and the libraries are downloaded, then run the next command
./vendor/pdooneorm
It will show the next screen
And now, you are in interactive mode.
In interactive mode you can:
select your own option
if you write "?" then it shows a simple help, that explains what command does what.
If you write "??" then it shows technical help
If you press the key tab, then you can autocomplete the options, so you won't need to write all the options.
If you press arrow-up or arrow-down then you can navigate in the history (if any).
The first option is it shows [connect] [pending] It is because we aren't connected to the database.
Write "connect" to enter the connect menu so we could create a new connection (remember that you can use autocomplete, i.e. if you write comple<table> then it will autocomplete the rest)
Then enter the option "configure".
And configure your database. If you fail, then you can try it again.
the type of database: "MySQL"
the server: 127.0.0.1 in my case.
The user: root
The password: abc.123 <-- that is my password, so please keep it secret (wink wink).
And the schema: "sakila" (or the schema that you want to use)
Once it is done, it will show:
the [ok] says that we are [ok] with this step. For example, you can save without configuration but it does not make any sense.
And about saving, enter the option "save" and save your configuration because you don't want to re-enter the entire configuration every time.
It will save the next file: config.config.php
<?php http_response_code(404); die(1); // eftec/CliOne(1.24) configuration file (date gen: 2023-03-11 17:40)?>
{
"databaseType": "mysql",
"server": "127.0.0.1",
"user": "root",
"pwd": "abc.123",
"database": "sakila"
}
You can reload the configuration using the option "load" or call as a flag.
But what this file means?
- It is a PHP file that contains a JSON inside.
Lets me explain about security. Let's say we write this file as config.json and we commit the mistake to avoid locking it, so everybody could read this configuration as mydomain.dom/config.json
Now, since it is a PHP file, then even if it is available directly, then mydomain.dom/config.config.php will return a 404 empty page. So, this file is already safe from prying eyes. How course, it keeps the password in plain sight, so it is not safe if you are exposing your configuration file to others.
It is a really common vulnerability.
About loading the previous configuration, you can load it in interactive mode or you could load it using the command line:
pdooneorm --fileconnect config
You can also save the file as a PHP file. It is the PHP file version
<?php // eftec/CliOne(1.24) PHP configuration file (date gen: 2023-03-11 17:50). DO NOT EDIT THIS FILE
/**
* it is the configuration of PdoOne
*/
$pdoOneConfig=[
'databaseType' => 'mysql',
'server' => '127.0.0.1',
'user' => 'root',
'pwd' => 'abc.123',
'database' => 'sakila',
];
It is also a safe file and you can expose it to the world and nobody will see its content. This file could be used later to code (or you could simply copy and paste its content).
Creating the repository classes
Once we are connected, we could use the options available in the menu "repo".
It is the repo menu. It has its own configuration but it also requires a connection.
Use the "scan" option.
- it will show that it is reading the structure. It is an important part. The library scans for tables and creates a catalog about what tables exist, what structure, relations, etc.
- Once it is done, then it will show [ok]
For every new change, then you must run this command.
And now, let's determine the folders. Use the "folder" option.
Select the relative directory. My example is "repo".
Select the postfix of the class. If the class is called City, then the repository class will be called CityRepo.
The current path (for my example) is
- D:\www\currentproject\PdoOneORM\examples\example2/repo
For the next step, we will need to configure Composer.
It is part of my composer.json
I will try to find a namespace that fits my route. In my case, it is my namespace. i.e:
folder: D:\www\currentproject\PdoOneORM\examples\
namespace: eftec\examples
Since my folder is D:\www\currentproject\PdoOneORM\examples\example2/repo
then the namespace must be
eftec\examples\example2\repo
If you do have not a path in composer, then save your configuration, close the program, then edit composer and re-open the program again.
So, we have configured:
✅ connection
✅ folder
✅scan.
So we could create the code. Use the option "create"
It will populate the repo folder with many files
Mainly there are 3 kinds of files.
Abstract classes. It will contain the definition of the table, indexes, relations, etc. You should not edit those files because if you "scan" again, it will rebuild this file.
Repo classes, is the file that you can edit.
Base class. There is a simple class (in this case called Sakila) that is used for all other classes. It has relations between classes.
Don't forget to save your configuration.
Using the repo classes.
In the same folder, write the next code: example1.php
<?php
include __DIR__."/../../vendor/autoload.php"; // edit the path for the correct one.
$cities=eftec\examples\example2\repo\CityRepo::toList();
var_dump($cities);
If you run this example, then it will fail because you are not connected to the database.
Do the next modification
<?php
use eftec\PdoOneORM;
use eftec\examples\example2\repo\CityRepo;
include __DIR__."/../../vendor/autoload.php"; // edit the path for the correct one.
$conn= PdoOneORM::factoryFromArray([
'databaseType' => 'mysql',
'server' => '127.0.0.1',
'user' => 'root',
'pwd' => 'abc.123',
'database' => 'sakila',
]); // you can include the PHP file generated previously or copy and paste its configuration.
$conn->open(); // opens the database connection. $conn is a singleton, so you don't need to open it every time you want to use.
$conn->logLevel=3; // it is optional,
$cities=CityRepo::toList(); // $conn is injected automatically inside every "Repo" class.
var_dump($cities);
It will show:
But we could do even more. In this case, the table "city" is related to the table "country".
Edit the line where you list the repo as follows:
$cities=CityRepo::recursive(['/_country_id'])->toList();
The function recursive will try to load this field. How do you know the name of the field? Every field indicated here starts with "/". If you look at the code of CityRepo, it will show every field:
It says that "_*country_*id" is a many-to-one relational field. Also "__address" is another relational field (one-to-many)
You can fetch both tables as follow:
$cities=CityRepo::recursive(['/_country_id','/_address'])->toList();
ORM or not.
ORM could make our life easiest but it could have some hidden costs.
many-to-one relations are "join" in disguise.
"one-to-many" relations are multiple queries.
ORM also loads every row and sometimes the internal queries could be long
select address.address_id as `/address_id`,address.address as `/address`,address.address2 as `/address2`,address.district as `/district`,address.city_id as `/city_id`,address.postal_code as `/postal_code`,address.phone as `/phone`,address.last_update as `/last_update` from address where address.`city_id` ='599'
If we want performance or we simply want to show a table, then we could use a native query:
$cities=CityRepo::query('select * from city inner join country on city.country_id=country.country_id');
It will return the next fields:
While the result is similar to the ORM but the structure is different.
Other examples
// inserting
$newCity=['city' => 'new city','country_id' => 105];
CityRepo::insert($newCity);
// updating
$newCity['city']='name changed';
CityRepo::update($newCity);
// deleting
CityRepo::delete($newCity);
// delete by id
CityRepo::deleteById(100);
There are more than +100 features available to use but they are the basic ones.
Costs
Why not a framework?
This library consists of 11 files
The complete dependency of this library is 28 files.
The example consists of 37 files.
So, the whole project example is less than 100 files. It doesn't all the job as a framework but it is a start.
In comparison, PHPUnit alone has +500 files and a project in Laravel is over +6000 files.
The more files, the slow to deploy, it uses more space, and PHP must deal with more files in the memory (OpCache must retain a catalog with every file). Even "Composer autoload" must have a huge file with all the files.
Also, since it is a simple project, you could even deploy without using Composer, however, you must include the files on your own.
Plus, you can even use it inside a framework.
Why or why not an ORM?
Sometimes we want both cases. For example, for the frontend, we could use native operations because they are fast and we don't need the whole structure of the ORM, and for the backend, we could use the ORM because it is easy to work with it.
So about ORM, the answer depends, and usually, the solution is both.