How to connect to PostgreSQL database in Laravel

Introduction

Laravel is a powerful PHP framework that follows the MVC (Model-View-Controller) architectural pattern. It provides simple and elegant tools to interact with databases. In this tutorial, you will learn how to connect to a PostgreSQL database using Laravel. We’ll go from establishing a basic connection to diving into more advanced features offered by Laravel.

Prerequisites

• A running Laravel application.
• PostgreSQL server installed and accessible.
• Basic knowledge of Laravel and databases.

Step-by-Step Instructions

Step 1: Install Laravel

composer create-project --prefer-dist laravel/laravel myProject

Step 2: Configure the .env File

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password

Replace your_database, your_username, and your_password with your actual PostgreSQL database credentials.

Step 3: Configure Database Settings

'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' => '',
    'prefix_indexes' => true,
    'schema' => 'public',
    'sslmode' => 'prefer',
],

Make sure you add this array to the connections array in your config/database.php file.

Step 4: Creating a Model and Migration

php artisan make:model BlogPost -m

This will generate a new model and a corresponding migration file for a ‘BlogPost’ entity.

Step 5: Migrations

Define the schema for the ‘BlogPost’ table in the generated migration file.

public function up()
{
    Schema::create('blog_posts', function (Blueprint $table) {
        $table->id();
        $table->string('title');
        $table->text('content');
        $table->timestamps();
    });
}

Then, run the migration:

php artisan migrate

You should see a message indicating that the migration was successful.

Step 6: Using Eloquent with PostgreSQL

Start creating and retrieving records using Eloquent ORM. Insert a new record into the ‘blog_posts’ table using the model:

$post = new BlogPost();
$post->title = 'My First Post';
$post->content = 'Content of my first post.';
$post->save();

To retrieve the record:

$blogPost = BlogPost::find(1);
echo $blogPost->title; // Outputs 'My First Post'

Step 7: Advanced Configurations (Optional)

Explore advanced settings and usage scenarios, like setting up a read/write connection or using PostgreSQL specific features like JSONB columns.

Conclusion

In this tutorial, you’ve learned how to connect and interact with a PostgreSQL database in Laravel. With Laravel’s built-in database tools and Eloquent ORM, you can efficiently work with databases, making your development process smoother and more productive.