Hello! PHP is one the most popular programming languages, and Laravel is the most popular PHP framework. In this article i will show you how to connect laravel to PostgreSQL.

Google search statistics shows how to connect to PostgreSQL using Laravel is a very common question. Laravel is a very popular PHP framework, that includes many features to develop web applications. You can develop anything from SPAs to REST APIs using Laravel.

How to Connect Laravel to PostgreSQL?

Let’s start with initiating a new Laravel project:

laravel new postgresql

Laravel’s new project wizard will ask you a few questions in the beginning. Last question is critical:

Visual studio code is my favorite editor on Windows, if you are running on Linux or OsX i suggest vim. Start with editing .env file with database server details.

Parameters starting with DB_ prefix are about PostgreSQL connection.

DB_HOST is the ip address or hostname, DB_PORT is the port 5432 is default, DB_DATABASE is name of the database, DB_USERNAME is database username and DB_PASSWORD is password.

Creating a new Controller

Laravel uses MVC application architecture. Create a controller first to connect to PostgreSQL.

Querying Current Version

Let’s code our PostgreSQL controller as:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class PostgreSQL extends Controller
{
    public function version(){
        $version = DB::select('SELECT VERSION()');
        return response()->json(array(
            "Version" => $version[0]
        ), 200);
    }
}

Update routes/web.php file to define route:

Route::get('/PostgreSQL/Version', [PostgreSQL::class, 'version']);

And run the Laravel application:

php artisan serve

Now navigate to http://127.0.0.1:8000/PostgreSQL/Version that will show you a json output with version information of current PostgreSQL installation:

Querying Query History

In my last article about how to check running queries in postgres, i created a query history table and populated it with slow query data. In this chapter we will query this table and return data as json.

Update web.php in routes first:

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\PostgreSQL;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider and all of them will
| be assigned to the "web" middleware group. Make something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});

Route::get('/PostgreSQL/Version', [PostgreSQL::class, 'version']);
Route::get('/PostgreSQL/History', [PostgreSQL::class, 'queryHistory']);

And add one more method to our controller:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class PostgreSQL extends Controller
{
    public function version(){
        $version = DB::select('SELECT VERSION()');
        return response()->json(array(
            "Version" => $version[0]
        ), 200);
    }

    public function queryHistory(){
        $history = DB::select('SELECT * FROM query_history');
        return response()->json(array(
            "Version" => $history
        ), 200);
    }
}

Now navigate to http://127.0.0.1:8000/PostgreSQL/History This url will return a json back to you with all data in the query_history table: