Database in Laravel 5

Currently Laravel supports four database systems: MySQL, Postgres, SQLite, and SQL Server. Database config is in file config >> database.php. Connecting with 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 find the 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 setup database correctly then open this link: http://localhost:8000/request . It will create a table in 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 database without using SQL.
  • If you refresh your webpage again then you will get 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 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();

More Tutorials on laravel in this series:



About Harish Kumar

Harish is an interested person in the field of web development and blogging. He works for the need of young web developers in learning various languages, latest technologies and other essential tips and tricks. If you need some help or you have some suggestion then you email him at harish@findalltogether.com without any hesitation. You can also suggest/demand for articles of your own choice.

Related Articles

Laravel is a MVC framework. Models are the real world entities in data form. Views are the response content to the vi...
Laravel framework architecture
Routes map urls with the controllers. In this section we will read about how route maps urls with controllers and how...
Basics routing and controllers in laravel 5
Views are the HTML content served by the server. These are served by returning from controllers. Views are .php or .b...
View in Laravel 5

Login or Sign up to leave comment.