Database in Laravel 7

Currently Laravel supports four database systems: MySQL, Postgres, SQLite, and SQL Server. Database config is in file config >> database.php. Connecting with a database is very simple. Config database in connections key.

  1. 'connections' => [
  2. 'sqlite' => [
  3. 'driver' => 'sqlite',
  4. 'database' => storage_path().'/database.sqlite',
  5. 'prefix' => '',
  6. ],
  7. 'mysql' => [
  8. 'driver' => 'mysql',
  9. 'host' => env('DB_HOST', 'localhost'),
  10. 'database' => env('DB_DATABASE', 'forge'),
  11. 'username' => env('DB_USERNAME', 'forge'),
  12. 'password' => env('DB_PASSWORD', ''),
  13. 'charset' => 'utf8',
  14. 'collation' => 'utf8_unicode_ci',
  15. 'prefix' => '',
  16. 'strict' => false,
  17. ],
  18. ],

NOTE:

  • It is good to define private data in .env file. env('DB_HOST', 'localhost') will check for DB_HOST variable in .env file and if variable is found then it will return its value otherwise, it will return 'localhost' (second argument).
  • .env is a hidden file in root folder of the application.
  • Set default database to the key default.
  • Database connection name may be anything. For example, we can write mysql2 instead of mysql.

Reading and writing to different hosts:

If you are using different hosts for reading and writing then use read and write keys.

  1. 'mysql' => [
  2. 'driver' => 'mysql',
  3. 'read' => [
  4. ' host' => env('DB_HOST1', 'localhost'),
  5. ],
  6. 'write' => [
  7. ' host' => env('DB_HOST2', 'localhost'),
  8. ],
  9. 'database' => env('DB_DATABASE', 'forge'),
  10. 'username' => env('DB_USERNAME', 'forge'),
  11. 'password' => env('DB_PASSWORD', ''),
  12. 'charset' => 'utf8',
  13. 'collation' => 'utf8_unicode_ci',
  14. 'prefix' => '',
  15. 'strict' => false,
  16. ],

Running a SQL statement:

For database transaction, Laravel provides DB class. A general statement is executed by statement function. If you are following the code of this series, then change index() function of YourController.php as:

  1. public function index($id = 12)
  2. {
  3. $query1 = "CREATE TABLE IF NOT EXISTS `mytable` (
  4. `id` int(10) NOT NULL,
  5. `name` varchar(50) NOT NULL,
  6. `age` int(10) NOT NULL
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1";
  8. $query2 ="ALTER TABLE `mytable` ADD PRIMARY KEY (`id`)";
  9. $query3 = "ALTER TABLE `mytable` MODIFY `id` int(10) NOT NULL AUTO_INCREMENT";
  10. DB::statement($query1);
  11. DB::statement($query2);
  12. DB::statement($query3);
  13. return view('first');
  14. }

Also add this line before YourController class for including DB class:

  1. use Illuminate\Support\Facades\DB;

Change first.blade.php as:

  1. query executed successfully

If you have set up database correctly, then open this link: http://localhost:8000/request. It will create a table in a database. If you are using mysql then you can see it using phpmyadmin.

NOTE:

  • $query1, $query2 and $query3 are storing SQL statements. statement() function accepts SQL statements. If you don't know about SQL, then you can skip this section of the tutorial. Laravel provides other methods to interact with the database without using SQL.
  • If you refresh your webpage again, then you will get an error because one table can not have multiple primary key. We have already defined a primary key using $query2.

Running special statement:

DB class has special functions for insert, update, select and delete queries. For example:

  1. // for insertion a row
  2. DB::insert('insert into mytable (id, name) values (?, ?)', [1, 'Harish']);
  3. // for selecting row/ rows
  4. $results = DB::select('select * from mytable where id = ?', [1]);
  5. $results2 = DB::select('select * from mytable where id = :id', ['id' => 1]);
  6. // for updating table
  7. DB::update('update mytable set age = 20 where name = ?', ['Harish']);
  8. //for deletion
  9. DB::delete('delete from mytable');

Connect with multiple databases:

For using multiple databases follow following steps:

  • Config database in config >> database.php
    1. 'connections' => [
    2. 'database1' => [
    3. // config database1
    4. ],
    5. 'database2' => [
    6. // config database2
    7. ],
    8. // more databases
    9. ],
  • Set one of the databases as default. For using default database you need not to do anything additionally.
  • For using other databases use connection() function before using other functions.
    1. DB::connection('database2')->insert('insert into mytable (id, name) values (?, ?)', [1, 'Harish']);
    2. $results = DB::connection('database2')-> select('select * from mytable where id = :id', ['id' => 1]);

More DB functions:

DB class has some more functions:

  • getpdo(): It returns the raw PDO instance.
  • reconnect('connection_name'): It reconnects with a database.
  • disconnect('connection_name'): It disconnects with database
  • enableQueryLog(): It saves the queries for current request.in memory.
  • getQueryLog(): Returns all stored queries for current request.
  1. $pdo = DB::connection()->getPdo();
  2. DB::reconnect('database1');
  3. DB::disconnect('database1');
  4. DB::connection()->enableQueryLog();
  5. $queries = DB::getQueryLog();

Further Reading:



ReactJS with Redux Online Training by Edureka

About Harish Kumar

Harish, a fullstack developer at www.lyflink.com with five year experience in full stack web and mobile development, spends most of his time on coding, reading, analysing and curiously following businesses environments. He is a non-graduate alumni from IIT Roorkee, Computer Science and frequently writes on both technical and non-technical topics.

Related Articles

With the expanding market of mobile apps, the developers are struggling to maintain the code bases for Native apps. M...
5 Elite and Imperative Hybrid App Frameworks
Django is a great framework in python. But all of the hosting do not provide django hosting in their shared or free h...
Cheap Django Hosting
Laravel provides blade template. Blade files are similar to php files and cover all features of php files. In additio...
Blade Template in Laravel 7

Complete Python Bootcamp: Go from zero to hero in Python 3

Top Posts

Recent Posts

The Complete Web Developer Course - Build 25 Websites

Meet on Twitter

Subscribe

Subscribe now and get weekly updates directly in your inbox!

Any Course on Udemy in $6 (INR 455)

Development Category (English)300x250

Best Udemy Courses

PHP with Laravel for beginners - Become a Master in Laravel