Basic Laravel Database Connection Example Tutorial

Basic Laravel Database Connections Tutorial

Introduction to Laravel Database Connections

Laravel Framework provides multiple methods for interacting with the application’s databases. But, the Eloquent is most popular, Laravel’s ActiveRecord ORM (object-relational mapper). Laravel allows developers to interact with a variety of database back-ends using either raw SQL, the fluent query builder, and the Laravel Model (Eloquent ORM). This Laravel Database Connection guide will help you understand the required configuration settings and how to use raw SQL queries to execute read, write, update and delete operations.

To configure database connections, Laravel supports many database platforms and we can choose any of them to develop our applications:

MySQL is one of the most popular and free platforms for database development

Understand How Laravel Request Lifecycle, works in laravel applications?

Configuring Database Connections in Laravel

Before we get into how to use Laravel’s database tools, let’s learn how to configure your  database connections and credentials. All the configurations for database connection in laravel are saved in the config/database.php file.

Laravel allows to define multiple database “connections” and then decide which the code will use by default. This file contains examples for most of the supported database systems are provided in this file.

 

Also, read our below posts on Laravel Framework:

 

Connecting to Laravel Database Connections

By default, there’s one connection for each of the connection types, as you can see in the config/database.php database configuration file. Each connection allows you to define the properties necessary for connecting to and customizing each connection type.

How to connect to MySQL in Laravel?

This is one of the basic questions every Laravel fresher looking for before building Laravel application. Again, Laravel provides a very simple mechanism to manage your database connections through app/config/database.php. This file is also known as database config file. The following code snippet explains the database configuration MySQL:

<?php

return [
/*
|--------------------------------------------------------------------------
| Default Database Connection Name
|--------------------------------------------------------------------------
|
| Here you may specify which of the database connections below you wish
| to use as your default connection for all database work. Of course
| you may use many connections at once using the Database library.
|
*/

'default' => env('DB_CONNECTION', 'mysql'),

/*
|--------------------------------------------------------------------------
| Database Connections
|--------------------------------------------------------------------------
|
| Here are each of the database connections setup for your application.
| Of course, examples of configuring each database platform that is
| supported by Laravel is shown below to make development simple.
|
|
| All database work in Laravel is done through the PHP PDO facilities
| so make sure you have the driver for your particular database of
| choice installed on your machine before you begin development.
|
*/

'connections' => [

'sqlite' => [
'driver' => 'sqlite',
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
],

'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],

To avoid the hard-coded values in the configuration file, Laravel 5 provides a mechanism to define a common set of configuration settings in the .env Laravel Environment File.

The environment file contains the following variables for database connections:

Database configuration environment:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db_demo
DB_USERNAME=user
DB_PASSWORD=password

So, the following are basic steps to connect to the MySQL database in Laravel:

Step 1   – Create a new Database using PhpMyAdmin interface
Step2   – Modify the following property in your .env file according to your database settings.
Step3   – Run the Migrations. This will automatically create the tables.

Configure Multiple Database Connections in Laravel

Yes, Laravel can handle multiple database connections at a time in the same application.

Laravel allows developers to configure and use multiple database connections at a time in the same Laravel application. In that case how to specify which one to use while using SQL operations.

When the application is using multiple database connections, the specific database connection can be accessed using the “connection” method on the DB facade. Here’s how you ask Laravel to connect to a specific database:

Syntax - DB::connection('database_name')->select(...);
Example - $users = DB::connection('secondary')->select('select * from users');

The name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file.

Reconnecting a Given Database:

DB::reconnect('foo');

Disconnect the database connection in Laravel

DB::disconnect('foo');

Executing Raw SQL Queries in Laravel

The common database SQL operations are – SELECT, UPDATE, CREATE, INSERT and DELETE. After you have configured your database connection, you can run SQL queries using the Laravel’s DB facade.

The Laravel’s DB facade provides methods for each type of query: select, update, insert, delete, and statement. Following is la ist of Raw SQL methods:

Running A Select Query

The select method can be used on Laravel DB facade to retrieve data from the database.

$teachers = DB::select('select * from teachers where active = ?', [1]);

Here, the first parameter to select method is Raw SQL query and the second parameter is variable bound to the query. So, here the resultant query would be:

‘select * from users where active = 1’;

SQL query result format:

The select method always returns an array of results values.
Each result within the array will be a PHP stdClass object, allowing you to access the values of the results:

foreach ($teachers as $teacher)
{
   echo $teacher->name;
}

Running An Insert Statement

Similar to the select method, there is an insert method called on DB facade. Its used to insert new values into MySQL DB table.

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);

Running An Update Statement

To update the existing records in the database, the update method is used.

$affectedRows = DB::update('update teachers set votes = 100 where name = ?', ['ABC']);

Here, the result value would be the number of affected rows.

Running A Delete Statement

The delete method is used to delete existing records in the database tables.

$deleted = DB::delete('delete from teachers');

Returns the number of records deleted.

Delete Specific Records

The delete method is used to delete existing records in the database tables.

DB::delete('delete from student where id = ?',[$id]);

Returns the number of records deleted.

Deleting tables in Laravel

DB::statement('drop table users');

Returns the number of records deleted.

Laravel Database Query Logging:

Laravel framework, by default database connection stores the log of recently executed DB queries. If required we can disable the default query logging using the disableQueryLog() method.

Disable Query Logging

DB::connection()->disableQueryLog();

Retrieve Executed Query Logs:

$queries = DB::getQueryLog();

This returns an array of executed database queries.

End Notes!

Now, that we have discussed all the knowledge to start making database connections
and executing a set of SQL queries. We discussed in detail about Laravel database connection configuration.

Further Reading:

Laravel Database Tutorial

Run Multiple Database

Connecting Laravel Application to Database

Leave a Reply

Your email address will not be published. Required fields are marked *