Laravel comes with first party support for several database drivers. Laravel gives much better flexibility in terms of using the RDBMS based databases. Laravel supports MySQL, PostgreSQL, SQLite and SQL Server by default but if you want to make connection with Mongo or other database then you need to install a separate package for it.
So, in this article i will show you to connect the MySQL database and its configuration. Let’s first discuss about the configuration files.
Configuration of database in Laravel 8
We can configure the database in two file one is .env where we store simple database connection details and another is config/database.php where we store specific and several configuration related to database.
So if we want to connect simply without much more advance connection then we can simply define in .env file
.....
DB_CONNECTION=mysql // driver name
DB_HOST=127.0.0.1. // host it can be domain , ip or localhost
DB_PORT=3306 //port to connect
DB_DATABASE=name_of_database // database name of mysql
DB_USERNAME=root // username of database
DB_PASSWORD=password // pasword
.......
....So, here we defined DB_CONNECTION=mysql which means we are going to connect Laravel with MySQL database and larvae will use MySQL drivers for queries. then DB_HOST=127.0.0.1 means server address it can be localhost , domain or ip as well.
DB_DATABASE=name_of_database name for the database, DB_PORT=3306 is port used by mysql, DB_USERNAME=root is username of mysql database and DB_PASSWORD=password is password for mysql database.
Sometime we need to more configuration to this so we can change the configuration in config/database.php
<?php
use Illuminate\Support\Str;
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',
'url' => env('DATABASE_URL'),
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],
'mysql' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
'pgsql' => [
'driver' => 'pgsql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '5432'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
'schema' => 'public',
'sslmode' => 'prefer',
],
'sqlsrv' => [
'driver' => 'sqlsrv',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '1433'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
],
],
/*
|--------------------------------------------------------------------------
| Migration Repository Table
|--------------------------------------------------------------------------
|
| This table keeps track of all the migrations that have already run for
| your application. Using this information, we can determine which of
| the migrations on disk haven't actually been run in the database.
|
*/
'migrations' => 'migrations',
/*
|--------------------------------------------------------------------------
| Redis Databases
|--------------------------------------------------------------------------
|
| Redis is an open source, fast, and advanced key-value store that also
| provides a richer body of commands than a typical key-value system
| such as APC or Memcached. Laravel makes it easy to dig right in.
|
*/
'redis' => [
'client' => env('REDIS_CLIENT', 'phpredis'),
'options' => [
'cluster' => env('REDIS_CLUSTER', 'redis'),
'prefix' => env('REDIS_PREFIX', Str::slug(env('APP_NAME', 'laravel'), '_').'_database_'),
],
'default' => [
'url' => env('REDIS_URL'),
'host' => env('REDIS_HOST', '127.0.0.1'),
'password' => env('REDIS_PASSWORD', null),
'port' => env('REDIS_PORT', '6379'),
'database' => env('REDIS_DB', '0'),
],
'cache' => [
'url' => env('REDIS_URL'),
'host' => env('REDIS_HOST', '127.0.0.1'),
'password' => env('REDIS_PASSWORD', null),
'port' => env('REDIS_PORT', '6379'),
'database' => env('REDIS_CACHE_DB', '1'),
],
],
];
As you can see it have multiple configuration for database, here you can change any database connection, collation, url, charset, prefix and even you can define read and write different database connection as below
'mysql' => [
'read' => [
'host' => [
'192.168.1.1',
'196.168.1.2',
],
],
'write' => [
'host' => [
'196.168.1.3',
],
],
'sticky' => true,
'driver' => 'mysql',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
],Now we can run queries in our controller, view or in routes. In Laravel there is two ways to run the sql statements first using Eloquent and second using QueryBuilder . So here we will use Query Builder for example.
Fetch all rows from database
Above we created database connection now we can use our query as below
use Illuminate\Support\Facades\DB;
$users = DB::table('users')->get();
foreach ($users as $user) {
echo $user->name;
}Get a single row from database
If you want to fetch a single record then you can use First method.
use Illuminate\Support\Facades\DB;
$user = DB::table('users')->where('name','test')->first();
echo $user->name;Insert a single row in database
use Illuminate\Support\Facades\DB;
$user = DB::table('users')->insert([
'name' => 'test',
'status' => 0
]);Delete rows in database
You can delete a multiple or single record using delete method of query builder.
use Illuminate\Support\Facades\DB;
$user = DB::table('users')->where('name','test')->delete();
