Working with Databases in Laravel

As I introduced you to Laravel (Overview of Laravel) in my first blog, for this second entry in these series of blogs, I will be walking you through the steps necessary for working with databases in Laravel. For this blog, I am going to tell you how to configure a database connection for various databases such as MySQL, Postgress, SQLite and SQL Server.

Database Setup in Laravel:

Laravel provides two extremely simple ways for connecting and running queries in various databases using either raw SQL (fluent query builder), and the Eloquent ORM model. For this post we will cover only the Eloquent ORM model for the database creation. At the time of this writing, Laravel support four database systems:

· MySQL

· Postgress

· SQLite

· SQL Server

Note that Laravel gives you the option to work with third-party libraries which you can use with other database servers. However, this blog will focus on how to setup Laravel to work with a MySQL database.

Database Configuration:

The first thing we will do in this process is look at where the database configuration in located. The database configuration is located at config/database.php. In this file, you can define all of you the database connections, as well specify which connection should be used by default. As you will notice the information in this file is stored as array.

Another alternative to using this method for configuring your database, Laravel’s sample environment configuration is available via the Laravel Homestead. If you recall from my first blog, I talked about this virtual environment development which can be used for doing Laravel development on your local machine. So, this platform gives you the option to work with databases since it has built-in configuration which you can modify as needed for your local database. This is just a side note and will not be covered in this blog.

Let’s come back to the steps necessary for setting up a database connection in the config/database.php file.

The first key in the array we will look at is the fetch. This key is the data type that will be used to store the rows when retrieving them from the database. The options available for this key are the PD0::FETCH_* and the PD0::FETCH_OBJ which the default that we will be using for this connection. Note that there are many other options available that you can find at this link http://php.net/manual/en/pdo.constants.php . The following line shows how the fetch key is used:

‘fetch’ => PDO::FETCH_OBJ,

Next we will look at the connection key which is the connection information to use from the connections key. Since we are going to be setting up a single MySQL connection, this is the key we will be using. The default for this is env (‘DB_CONNECTION’, ‘mysql’). By setting it up like this, Laravel will look at the value of DB_CONNECTION in the .env file and if it is not set, it will use mysql as default database. So, here is how this key is called:

‘connection’ => env(‘DB_CONNECTION’, ‘mysql’),

Now we need to examine the connections key because this is what is used by the connection key. This is an array that has two parameters the connection name and the value for the connection.

Using the connections key we will be examining how to set the connection for three types of databases which consist of: SQLite, MySQL, and Postgress. You will notice that we will use only one key for setting up all three connections for these three databases.

· SQLite Configuration

‘connections’ => [

‘sqlite’ => [

‘driver’ => ‘sqlite’,

‘database’ => env(‘DB_DATABASE’, database_path(‘database.sqlite’)),

‘prefix’ => ‘’,

],

Notice that the key word ‘connections ‘ is the starting point for the SQLite configuration and is followed by the opening of the square bracket for the array. This bracket will be closed after all three databases have been configured each one having its own set of parameters. Also, each database configuration has its own set of square brackets taking in the required parameters such as the driver, the database and the prefix.

· MySQL configuration

‘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’ => true,

‘engine’ => null,

],

· Postgress Configuration

‘pgsql’ => [

‘driver’ => ‘pgsql’,

‘host’ => env(‘DB_HOST’, ‘localhost’),

‘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’,

],

],

As you can see at the end of the Postgress configuration, there is a closing square bracket. This is the end of the configuration using the connections key. Now let’s explain the role of the different parameters used for each database. First of all, as you will notice, these parameters definitions are the same for each database except there store different values according to which database is being configured.

One thing to note is that before setting up the configuration for the connection to the chosen database, here is the command which you use to create the database. After executing the following command, you can easily configure the database:

touch database/database.sqlite

Since the variables are the same for each database but store different values accordingly, we will examine only the mysql connection information.

· Driver: The driver is used to specify the connection. It takes in the database being used such as mysql, pgsql, sqlite and sqlsrv. Depending on the database you are configuring, you can choose to set to mysql or sqlite and so on. Because we are setting up a MySQL database, we will leave the driver set to mysql.

· host: this is going to be used for setting up the host name or IP address that will be used to connect to the database. The default value can be the DB_HOST value in the .env file or set it to use the localhost.

· unix_socket: This is the unix_socket to be used for the connection if set. Note that this field is not included in the default configuration. So, if it is set, Laravel will use it to connect to the database instead of using the host. A possible value is /tmp/mysql.sock.

· port: This specifies which port to use to connect to the database. If the port is set to host, it usually uses the value 3306 as the port number; however, this can be changed by setting the DB_PORT value in the .env file.

· Username: The DB_USERNAME value in the .env file is used; otherwise, forge is the username. Setting this variable tells Laravel which username to use to connect to the database.

· Password: This is the password that will be used for the connection. The DB_PASSWORD value in the .env file is used, otherwise the password field is left empty and an empty string will be used.

· Charset: The charset is how characters are encoded in the database. By default this setting can only be changed in the config/database.php file. Usually, this is left as utf8 or utf8mb4 (since Laravel v5.4).

· Collation: A collation tells Laravel how text will be stored and depends on what charset is used. Since charset is usually set to utf8 or utf8mb4, the collation is usually utf8_unicode_ci or utf8mb4_unicode_ci.

· prefix: The prefix is prepended to the table name. Even though, it is not required, for security reasons, it is recommended that prefix be set. Since Laravel does not pull this value from the .env file and it is set to an empty string by default, in order to set the value, you will need to open the database/config.php file and change the prefix value there.

· strict: This is a Boolean value that when set to true, enables the following SQL modes which are explained in the following link: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

If strict is set to false, only NO_ENGINE_SUBSTITUTION is set. Possible modes are:

o ONLY_FULL_GROUP_BY

o STRICT_TRANS_TABLES

o NO_ZERO_IN_DATE

o NO_ZERO_DATE

o ERROR_FOR_DIVISION_BY_ZERO

o NO AUTO_CREATE_USER

o NO_ENGINE_SUBSTITUTION

· modes: This is a hidden setting for MySQL in Laravel and it is an array which holds a list of SQL mode to set. If this is set, strict will be ignored.

· engine: This is the engine to use during tables creation (MyISAM, InnoDB). Note that the default for this setting is null which means that MySQL will automatically choose the table engine to use when creating it.

· Timezone: This also is a hidden setting in Laravel that sets the timezone to use for the date/time data type in MySQL. For example, setting this to +00:00 means data/times are in the UTC timezone.

Conclusion:

By following these steps, you will be able to configure a database connection in Laravel and connect it to the database. As you have noticed, this configuration applies to most of the databases you will be using in Laravel; all you need to change is the values that will be stored in those different fields. So, Laravel makes interacting with databases extremely simple across a variety of database backends. For my next post in this series, I will be talking about how to use Eloquent ORM model for working with your database.

Show your support

Clapping shows how much you appreciated Mamadou Diallo’s story.