Using Illuminate Database With Eloquent in Your PHP App Without Laravel
Illuminate is Laravel’s database engine minus Laravel. It comes bundled with the Eloquent ORM in Laravel. If you would like to build your PHP apps with ORMs and prefer not to use Laravel, this tutorial is for you.
In this tutorial, we are going to build the back end for a Q&A App with PHP, Illuminate Database, and the Eloquent ORM.
Project Dependencies
- PHP 5.5+
- MySQL
- Composer
App Capabilities
Our app will perform the following tasks:
- add a user
- add a question
- add an answer to a question
- upvote an answer
- get a question with answers
- get all questions and users who asked them
- get particular questions, answers, and upvotes
- count questions by a particular user
- update an answer by a user
- delete a question
Firstly, let's create our project directory and structure. In the rest of the tutorial, I assume that eloquent is our project root directory name.
In the main project directory, we’ll create an app folder, and then in this app folder, we’ll create two folders: models and controllers. In this picture, our main project folder is named eloquent. You should replace it with whatever name you prefer.



Install the Eloquent Library
Next, let's install the dependencies for our project. In the main project folder, let's create the eloquent/composer.json file. After creating it, paste the following code in our eloquent/composer.json file.
1 |
{
|
2 |
"name": "illuminate-example/eloquent", |
3 |
"description": "Implementation of Database Queries with illuminate and Eloquent", |
4 |
"type": "project", |
5 |
"require": {} |
6 |
}
|
To install the Illuminate database library, let's add “illuminate/database”: “^7.30”,
to our eloquent/composer.json file.
Next, let's add PSR-4 autoloading for our models and controllers:
1 |
"autoload": { |
2 |
"psr-4": { |
3 |
"Controllers\\": "app/controllers/", |
4 |
"Models\\": "app/models/" |
5 |
}
|
6 |
}
|
Now, our eloquent/composer.json file should look like this:
1 |
{
|
2 |
"name": "illuminate-example/eloquent", |
3 |
"description": "Implementation of Database Queries with illuminate and Eloquent", |
4 |
"type": "project", |
5 |
"require": { |
6 |
"illuminate/database": "^7.30" |
7 |
},
|
8 |
"autoload": { |
9 |
"psr-4": { |
10 |
"Controllers\\": "app/controllers/", |
11 |
"Models\\": "app/models/" |
12 |
}
|
13 |
}
|
14 |
}
|
Finally, let's run the following two commands in the root of our project directory.
1 |
$composer install |
2 |
$composer dump-autoload -o |
Set Up the Database Tables and Configuration File
Let’s add a config file for our database credentials. In the main project directory, let's create a file named eloquent/config.php and define DB details in the eloquent/config.php file as shown in the following snippet. Note that the values should be replaced with your own connection details.
1 |
<?php
|
2 |
defined('DBDRIVER') or define('DBDRIVER','mysql'); |
3 |
defined('DBHOST') or define('DBHOST','localhost'); |
4 |
defined('DBNAME') or define('DBNAME','eloquent-app'); |
5 |
defined('DBUSER') or define('DBUSER','root'); |
6 |
defined('DBPASS') or define('DBPASS','pass'); |
7 |
?>
|
Next, let's create a schema for our app.
One thing to note before we create the schema for the tables in our database is that we can add timestamps to our schema.
The Eloquent ORM expects two timestamp columns if we want to enable timestamp operation on a particular table or model. They are the created_at
and updated_at
columns. If we enable timestamps for a model, Eloquent automatically updates these fields with the time when we create or update a record.
There is a third column called deleted_at
. The deleted_at
timestamp works differently, though. Eloquent has a soft delete capability which uses the deleted_at
column to determine whether a record has been deleted. If you delete a record with the eloquent delete
function and you enable soft delete, the column is updated with the time of deletion. These deleted items can then be retrieved at any time.
In this app, we will be taking advantage of the timestamps, so we’ll use all three in our schema creation.
Let's create tables with the following commands in MySQL.
Questions
Let's use the following SQL statement to create the questions
table in our database.
1 |
CREATE TABLE `questions` ( |
2 |
`id` int(11) unsigned NOT NULL AUTO_INCREMENT, |
3 |
`question` tinytext, |
4 |
`user_id` int(11) DEFAULT NULL, |
5 |
`created_at` timestamp NULL DEFAULT NULL, |
6 |
`updated_at` timestamp NULL DEFAULT NULL, |
7 |
`deleted_at` timestamp NULL DEFAULT NULL, |
8 |
PRIMARY KEY (`id`) |
9 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Answers
Let's use the following SQL statement to create the answers
table in our database.
1 |
CREATE TABLE `answers` ( |
2 |
`id` int(11) unsigned NOT NULL AUTO_INCREMENT, |
3 |
`answer` tinytext, |
4 |
`user_id` int(11) DEFAULT NULL, |
5 |
`question_id` int(11) DEFAULT NULL, |
6 |
`created_at` timestamp NULL DEFAULT NULL, |
7 |
`updated_at` timestamp NULL DEFAULT NULL, |
8 |
`deleted_at` timestamp NULL DEFAULT NULL, |
9 |
PRIMARY KEY (`id`) |
10 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Upvotes
Let's use the following SQL statement to create the upvotes
table in our database.
1 |
CREATE TABLE `upvotes` ( |
2 |
`id` int(11) unsigned NOT NULL AUTO_INCREMENT, |
3 |
`answer_id` int(11) DEFAULT NULL, |
4 |
`user_id` int(11) DEFAULT NULL, |
5 |
`created_at` timestamp NULL DEFAULT NULL, |
6 |
`updated_at` timestamp NULL DEFAULT NULL, |
7 |
`deleted_at` timestamp NULL DEFAULT NULL, |
8 |
PRIMARY KEY (`id`) |
9 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Users
Let's use the following SQL statement to create the users
table in our database.
1 |
CREATE TABLE `users` ( |
2 |
`id` int(11) unsigned NOT NULL AUTO_INCREMENT, |
3 |
`username` varchar(100) DEFAULT NULL, |
4 |
`email` varchar(200) DEFAULT NULL, |
5 |
`password` varchar(200) DEFAULT NULL, |
6 |
`created_at` timestamp NULL DEFAULT NULL, |
7 |
`updated_at` timestamp NULL DEFAULT NULL, |
8 |
`deleted_at` timestamp NULL DEFAULT NULL, |
9 |
PRIMARY KEY (`id`) |
10 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
So we've done with the database setup now. Next, we'll create the model files that are required in our application.
Set Up the Eloquent Models
Firstly, we need to create a common Database
class for setting up the database connection.
The Database
Class
Create the eloquent/app/models/database.php file with your favorite text editor and add the following contents.
1 |
<?php
|
2 |
namespace Models; |
3 |
|
4 |
use Illuminate\Database\Capsule\Manager as Capsule; |
5 |
|
6 |
class Database { |
7 |
public function __construct() |
8 |
{
|
9 |
$capsule = new Capsule; |
10 |
$capsule->addConnection([ |
11 |
'driver' => DBDRIVER, |
12 |
'host' => DBHOST, |
13 |
'database' => DBNAME, |
14 |
'username' => DBUSER, |
15 |
'password' => DBPASS, |
16 |
'charset' => 'utf8', |
17 |
'collation' => 'utf8_unicode_ci', |
18 |
'prefix' => '', |
19 |
]);
|
20 |
|
21 |
// Setup the Eloquent ORM…
|
22 |
$capsule->bootEloquent(); |
23 |
}
|
24 |
}
|
In the above file, we've initialized the Capsule
class. Next, we've used the addConnection
method to create a new MySQL connection. Finally, we've used the bootEloquent
method to initialize the Eloquent model.
The User
Model
Let's create the eloquent/app/models/User.php file with the following contents.
1 |
<?php
|
2 |
namespace Models; |
3 |
|
4 |
use \Illuminate\Database\Eloquent\Model; |
5 |
|
6 |
class User extends Model { |
7 |
protected $table = 'users'; |
8 |
protected $fillable = ['username', 'email', 'password']; |
9 |
}
|
10 |
?>
|
The Question
Model
Let's create the eloquent/app/models/Question.php file with the following contents.
1 |
<?php
|
2 |
namespace Models; |
3 |
|
4 |
use \Illuminate\Database\Eloquent\Model; |
5 |
|
6 |
class Question extends Model { |
7 |
protected $table = 'questions'; |
8 |
protected $fillable = ['question','user_id']; |
9 |
}
|
10 |
?>
|
The Answer
Model
Let's create the eloquent/app/models/Answer.php file with the following contents.
1 |
<?php
|
2 |
namespace Models; |
3 |
use \Illuminate\Database\Eloquent\Model; |
4 |
|
5 |
class Answer extends Model { |
6 |
protected $table = 'answers'; |
7 |
protected $fillable = ['answer','user_id','question_id']; |
8 |
}
|
9 |
?>
|
The Upvote
Model
Let's create the eloquent/app/models/Upvote.php file with the following contents.
1 |
<?php
|
2 |
namespace Models; |
3 |
|
4 |
use \Illuminate\Database\Eloquent\Model; |
5 |
|
6 |
class Upvote extends Model { |
7 |
protected $table = 'upvotes'; |
8 |
protected $fillable = ['answer_id', 'user_id']; |
9 |
|
10 |
}
|
11 |
?>
|
And that's it for setting up the model classes.
Create the Front Controller Files
In this section, we'll create the core bootstrap and front controller files.
The bootstrap.php File
This is a common file which is used to bootstrap our application. Let's create the bootstrap.php file in the root directory of our application.
1 |
<?php
|
2 |
require './config.php'; |
3 |
require './vendor/autoload.php'; |
4 |
|
5 |
use Models\Database; |
6 |
|
7 |
// initialize Illuminate database connection
|
8 |
new Database(); |
9 |
?>
|
We have also set up a database connection by instantiating the Database
class.
The index.php File
This is a front controller of our application, so basically it's an entry point for our application.
Let's create the index.php file in the root of our application.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
|
4 |
// our example code goes here...
|
5 |
?>
|
In fact, we'll use the index.php file to test all our use cases in the rest of this article.
Now, we've set up models as well. From the next section onwards, we'll start testing our use cases.
Task 1: Add a User
Firstly, let's create the eloquent/app/controllers/Users.php controller file with the following contents.
1 |
<?php
|
2 |
namespace Controllers; |
3 |
use Models\User; |
4 |
|
5 |
class Users { |
6 |
public static function create_user($username, $email, $password) |
7 |
{
|
8 |
$user = User::create(['username'=>$username,'email'=>$email,'password'=>$password]); |
9 |
return $user; |
10 |
}
|
11 |
}
|
12 |
?>
|
In our Users
controller class, we've created the create_user
method, which is used to create a new user. We've used the create
method of the Eloquent
model to create a new user.
Let's call it from our index.php file to test it as shown in the following snippet. Please make sure you encrypt the password strongly before you actually store it. It's never recommended to store plain text passwords in db.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
|
4 |
use Controllers\Users; |
5 |
|
6 |
$user = Users::create_user("user1", "user1@example.com", "user1_pass"); |
7 |
?>
|
Once you run the index.php file, it should create a new user in the users
table.
Task 2: Add a Question
Firstly, let's create the eloquent/app/controllers/Questions.php controller file with the following contents.
1 |
<?php
|
2 |
namespace Controllers; |
3 |
use Models\Question; |
4 |
|
5 |
class Questions{ |
6 |
public static function create_question($question,$user_id) |
7 |
{
|
8 |
$question = Question::create(['question'=>$question,'user_id'=>$user_id]); |
9 |
return $question; |
10 |
}
|
11 |
}
|
12 |
?>
|
In our Questions
controller class, we've created the create_question
method, which is used to create a new question. We've used the create
method of the Eloquent
model to create a new question.
Let's call it from our index.php file to test it, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
|
4 |
use Controllers\Questions; |
5 |
|
6 |
$question = Questions::create_question("Have you ever met your doppelganger?", 1); |
7 |
?>
|
Once you run the index.php file, it should create a new question in the questions
table.
Task 3: Add an Answer to a Question
In this section, we'll see how to add answers to our questions.
Firstly, let's create the eloquent/app/controllers/Answers.php controller file with the following contents.
1 |
<?php
|
2 |
namespace Controllers; |
3 |
use Models\Answer; |
4 |
|
5 |
class Answers { |
6 |
public static function add_answer($answer,$question_id,$user_id) |
7 |
{
|
8 |
$answer = Answer::create(['answer'=>$answer,'question_id'=>$question_id,'user_id'=>$user_id]); |
9 |
return $answer; |
10 |
}
|
11 |
}
|
12 |
?>
|
In our Answers
controller class, we've created the add_answer
method, which is used to create a new answer.
Let's call it from our index.php file to test it, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
|
4 |
use Controllers\Answers; |
5 |
|
6 |
$answers = Answers::add_answer("This is an answer", 1, 2); |
7 |
?>
|
Once you run the index.php file, it should create a new answer in the answers
table.
Task 4: Upvote an Answer
These are pretty much the same steps we did earlier.
Let's add the following method in the eloquent/app/controllers/Answers.php controller.
1 |
...
|
2 |
...
|
3 |
public static function upvote_answer($answer_id,$user_id) |
4 |
{
|
5 |
$upvote = Upvote::create(['answer_id'=>$answer_id,'user_id'=>$user_id]); |
6 |
return $upvote; |
7 |
}
|
8 |
...
|
9 |
...
|
You also need to import the Upvote
model in the eloquent/app/controllers/Answers.php controller file by adding the following code.
1 |
use Models\Upvote; |
Finally, we call it from our index.php file to test it, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
|
4 |
use Controllers\Answers; |
5 |
|
6 |
$upvote = Answers::upvote_answer(1, 14); |
7 |
?>
|
Task 5: Get a Question With Answers
For tasks like this, we can use Eloquent relationships.
Types of relationships include one to one, one to many, many to many, etc.
When using these relations, Eloquent assumes a foreign key in the form modelname_id exists on the models. For this task, the relationship is a one-to-many relationship because a single question can own any number of answers.
Firstly, let's define this relationship by adding the following function to our Question
model.
1 |
...
|
2 |
...
|
3 |
public function answers() |
4 |
{
|
5 |
return $this->hasMany('\Models\Answer'); |
6 |
}
|
7 |
...
|
8 |
...
|
After that, in the eloquent/app/controllers/Questions.php controller file, let's add the following function to get questions with answers.
1 |
...
|
2 |
...
|
3 |
public static function get_questions_with_answers() |
4 |
{
|
5 |
$questions = Question::with('answers')->get()->toArray(); |
6 |
return $questions; |
7 |
}
|
8 |
...
|
9 |
...
|
It retrieves the questions with their corresponding answers.
Let's test it with the index.php file, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
use Controllers\Questions; |
4 |
|
5 |
$all = Questions::get_questions_with_answers(); |
6 |
?>
|
You can use the var_dump
or print_r
functions to print the $all
variable to see the results.
Task 6: Get All Questions and the Users Who Asked Them
This is going to be a one-to-one relationship because one question has one user, so let's add the following method to the Question
model.
1 |
...
|
2 |
...
|
3 |
public function user() |
4 |
{
|
5 |
return $this->belongsTo('\Models\User'); |
6 |
}
|
7 |
...
|
8 |
...
|
After that, in the eloquent/app/controllers/Questions.php controller file, let's add the following function.
1 |
...
|
2 |
...
|
3 |
public static function get_questions_with_users() |
4 |
{
|
5 |
$questions = Question::with('user')->get()->toArray(); |
6 |
return $questions; |
7 |
}
|
8 |
...
|
9 |
...
|
Let's test it with the index.php file, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
use Controllers\Questions; |
4 |
|
5 |
$all_with_users = Questions::get_questions_with_users(); |
6 |
?>
|
Task 7: Get One Question With Answers and Upvotes
First, we define a relationship between answers and upvotes. An answer has many upvotes, so the relationship is one to many.
Let's add the following function to our Answer
model:
1 |
...
|
2 |
...
|
3 |
public function upvotes() |
4 |
{
|
5 |
return $this->hasMany('\Models\Upvote'); |
6 |
}
|
7 |
...
|
8 |
...
|
In the eloquent/app/controllers/Questions.php controller file, let's create the following function.
1 |
...
|
2 |
...
|
3 |
public static function get_question_answers_upvotes($question_id) |
4 |
{
|
5 |
$questions = Question::find($question_id)->answers()->with('upvotes')->get()->toArray(); |
6 |
return $questions; |
7 |
}
|
8 |
...
|
9 |
...
|
Let's test it with the index.php file, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
|
4 |
use Controllers\Questions; |
5 |
|
6 |
$one_question = Questions::get_question_answers_upvotes(1); |
7 |
?>
|
We can print the $one_question
variable to see the results.
Task 8: Count All Questions by a Particular User
Firstly, let's import the Question
model in the eloquent/app/controllers/Users.php controller:
1 |
use Models\Question; |
After importing it, let's add the following function in the same file.
1 |
...
|
2 |
...
|
3 |
public static function question_count($user_id) |
4 |
{
|
5 |
$count = Question::where('user_id', $user_id)->count(); |
6 |
return $count; |
7 |
}
|
8 |
...
|
9 |
...
|
Finally, let's test it with the index.php file, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
use Controllers\Users; |
4 |
|
5 |
$user_question_count = Users::question_count(1); |
6 |
?>
|
It returns the count of the number of questions that have been added by a user with id 1
.
Task 9: Update Answer by a User
The concept of updating with the Eloquent ORM is pretty simple. First we find a record, and then we mutate and save.
In the eloquent/app/controllers/Answers.php controller, let's add this function:
1 |
...
|
2 |
...
|
3 |
public static function update_answer($answer_id,$new_answer) |
4 |
{
|
5 |
$answer = Answer::find($answer_id); |
6 |
$answer->answer = $new_answer; |
7 |
$updated = $answer->save(); |
8 |
return $updated; |
9 |
}
|
10 |
...
|
11 |
...
|
Finally, let's test it with the index.php file, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
use Controllers\Answers; |
4 |
|
5 |
$update_answer = Answers::update_answer(1, "This is an updated answer"); |
6 |
?>
|
This returns a boolean value—true—if the update is successful.
Task 10: Delete a Question (Soft Delete)
Finally, we'll implement the Eloquent SoftDelete feature.
Firstly, let's import the Illuminate\Database\Eloquent\SoftDeletes
trait in the Question
model with the following statement.
1 |
use Illuminate\Database\Eloquent\SoftDeletes; |
After it's imported, we can use it like this.
1 |
use SoftDeletes; |
Finally, let's add deleted_at
to the protected $dates
property of the model. These are the required steps.
1 |
protected $dates = ['deleted_at']; |
Our Question
model now looks like this:
1 |
<?php
|
2 |
namespace Models; |
3 |
|
4 |
use \Illuminate\Database\Eloquent\Model; |
5 |
use Illuminate\Database\Eloquent\SoftDeletes; |
6 |
|
7 |
class Question extends Model { |
8 |
use SoftDeletes; |
9 |
|
10 |
protected $table = 'questions'; |
11 |
protected $fillable = ['question','user_id']; |
12 |
protected $dates = ['deleted_at']; |
13 |
|
14 |
public function answers() |
15 |
{
|
16 |
return $this->hasMany('\Models\Answer'); |
17 |
}
|
18 |
|
19 |
public function user() |
20 |
{
|
21 |
return $this->belongsTo('\Models\User'); |
22 |
}
|
23 |
}
|
24 |
?>
|
Next, go ahead and create the delete_question
method in the eloquent/app/controllers/Questions.php controller.
1 |
...
|
2 |
...
|
3 |
public static function delete_question($question_id) |
4 |
{
|
5 |
$question = Question::find($question_id); |
6 |
$deleted = $question->delete(); |
7 |
return $deleted; |
8 |
}
|
9 |
...
|
10 |
...
|
Finally, let's test it with the index.php file, as shown in the following snippet.
1 |
<?php
|
2 |
require 'bootstrap.php'; |
3 |
use Controllers\Questions; |
4 |
|
5 |
$delete = Questions::delete_question(1); |
6 |
?>
|
Congratulations! You just built a fully functional back end with Illuminate and Eloquent. And we didn't have to write so much code to achieve all this.
Conclusion
Illuminate also comes with the Query Builder, which you can use for even more complex database queries and is definitely something you want to experiment with and use in your app.
The only things missing in the standalone Illuminate Database are database migrations, which are a lovely feature of Laravel, and Lumen, the micro-framework by Laravel. You should consider using both in your apps to take advantage of the useful features they come with.
You can find out more about Eloquent on the Official Eloquent Documentation Page.
References
This post has been updated with contributions from Sajal Soni. Sajal belongs to India, and he loves to spend time creating websites based on open-source frameworks.