Laravel makes connecting to and working with databases simple and flexible. It supports several database systems such as MySQL, PostgreSQL, SQLite, and SQL Server, and provides a unified API for interacting with these databases.

Steps to Configure Database Connection in Laravel:

1. Database Configuration in the .env File: Laravel stores the database configuration in the .env file. Here, you can define the type of database, host, port, database name, username, and password.

   Example of a typical .env file configuration for a MySQL database:

   DB_CONNECTION=mysql
   DB_HOST=127.0.0.1
   DB_PORT=3306
   DB_DATABASE=your_database_name
   DB_USERNAME=your_username
   DB_PASSWORD=your_password
   

   • DB_CONNECTION: The database driver you're using (mysql, pgsql, sqlite, sqlsrv).
   • DB_HOST: The database server's IP address or domain name (usually 127.0.0.1 for local development).
   • DB_PORT: The port number for the database (default MySQL is 3306).
   • DB_DATABASE: The name of your database.
   • DB_USERNAME: The database username.
   • DB_PASSWORD: The database password.

2. Database Configuration in config/database.php: While you typically configure the database connection in the .env file, Laravel uses the config/database.php file to manage the database settings. This file uses the env() helper to fetch values from the .env file.

   Here's an example for MySQL in config/database.php:

   'connections' => [
       'mysql' => [
           'driver' => 'mysql',
           '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' => '',
           'strict' => true,
           'engine' => null,
           'options' => extension_loaded('pdo_mysql') ? array_filter([
               PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
           ]) : [],
       ],
   ],
   

   This configuration is used when Laravel connects to the database. It handles various database options, including charset, collation, and strict mode.

3. Supported Database Systems: Laravel supports multiple database systems out of the box. Here are the common drivers:

   • MySQL: Use DB_CONNECTION=mysql.
   • PostgreSQL: Use DB_CONNECTION=pgsql.
   • SQLite: Use DB_CONNECTION=sqlite.
   • SQL Server: Use DB_CONNECTION=sqlsrv.

4. Multiple Database Connections: Laravel allows you to define multiple database connections in config/database.php. You can switch between different connections dynamically.

   Example:

   'connections' => [
       'mysql' => [ ... ],
       'pgsql' => [
           'driver' => 'pgsql',
           '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' => '',
           'schema' => 'public',
           'sslmode' => 'prefer',
       ],
   ],
   

   To use a different connection in a query, you can specify it as follows:

   DB::connection('pgsql')->select('SELECT * FROM users');
   

5. Running Migrations: Once your database is connected, you can run migrations to create tables and set up the schema. Use the following Artisan command to run migrations:

   php artisan migrate
   

6. Switching Between Databases Dynamically: If you need to use different database connections during runtime, you can switch between them using the DB::connection() method.

   Example:

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

7. Using SQLite Database: If you prefer to use an SQLite database, which stores data in a local file, the setup is slightly different. In the .env file, set DB_CONNECTION to sqlite and specify the database file path in config/database.php:

   DB_CONNECTION=sqlite
   

   In config/database.php, define the file path:

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

   You can create the SQLite database file manually:

   touch database/database.sqlite
   

8. Setting Up Database Indexes and Foreign Keys: When defining tables in migrations, you can specify indexes and foreign keys to optimize queries and ensure referential integrity.

   Example migration for a posts table:

   Schema::create('posts', function (Blueprint $table) {
       $table->id();
       $table->string('title');
       $table->text('content');
       $table->foreignId('user_id')->constrained(); // Adds a foreign key constraint to the users table
       $table->timestamps();
   });
   

9. Database Queries and Eloquent ORM: Laravel provides the Query Builder and Eloquent ORM to work with the database.

   • Query Builder: Example of querying the database using the Query Builder:

     $users = DB::table('users')->where('active', 1)->get();
     

   • Eloquent ORM: Example of querying the database using Eloquent:

     $users = User::where('active', 1)->get();