How to use PostgreSQL with Laravel

 

 

PostgreSQL is a powerful database that has been battle-tested in many applications. Laravel is a reliable and flexible PHP framework with great community support. Together, they enable PHP developers to deploy web applications quickly and cleanly. This quick tutorial will lay the groundwork for any aspiring PHP developer looking to merge the best of both worlds of fast application development and database reliability.

 

Prerequisites

As of the time of this writing, the latest available version of Laravel is 6.0 LTS, and can be used with any supported version of PostgreSQL. In reality, Laravel can be used with any of several database engines because of the underlying Eloquent ORM. This article will focus on how to set it up with Postgres, because why would you use anything else? Here’s what you’ll need:

• PHP 7.2+
• Composer (a dependency manager for PHP)
• PostgreSQL 9.5+

Installation of these components is falls outside the scope of this article, but if you need help, you can check out instructions on how to install PHP 7.3 (RHEL/CentOS, Ubuntu) and PostgreSQL (RHEL/CentOS, Ubuntu).

 

Installing Laravel

To install Laravel, simply use Composer to create a new project:

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

 

Note that Laravel is a framework, or a collection of PHP libraries, to assist in development while keeping the code clean. Once the project has been created and the Laravel framework is pulled, you should be able to see several directories and files in the myproject folder:

# ls -l

total 248

drwxr-xr-x  6 root root   4096 Nov 28 19:39 app

-rwxr-xr-x  1 root root   1686 Nov 21 17:28 artisan

drwxr-xr-x  3 root root   4096 Nov 21 17:28 bootstrap

-rw-r--r--  1 root root   1501 Nov 21 17:28 composer.json

-rw-r--r--  1 root root 178552 Nov 28 07:46 composer.lock

drwxr-xr-x  2 root root   4096 Nov 21 17:28 config

drwxr-xr-x  5 root root   4096 Nov 21 17:28 database

-rw-r--r--  1 root root   1013 Nov 21 17:28 package.json

-rw-r--r--  1 root root   1297 Nov 21 17:28 phpunit.xml

drwxr-xr-x  2 root root   4096 Nov 21 17:28 public

-rw-r--r--  1 root root   4455 Nov 21 17:28 README.md

drwxr-xr-x  6 root root   4096 Nov 21 17:28 resources

drwxr-xr-x  2 root root   4096 Nov 21 17:28 routes

-rw-r--r--  1 root root    563 Nov 21 17:28 server.php

drwxr-xr-x  5 root root   4096 Nov 21 17:28 storage

drwxr-xr-x  4 root root   4096 Nov 21 17:28 tests

drwxr-xr-x 41 root root   4096 Nov 28 07:46 vendor

-rw-r--r--  1 root root    538 Nov 21 17:28 webpack.mix.js

 

Getting the plumbing in place

PHP and Laravel both need to know how to talk to talk to PostgreSQL, so the first step is to make sure that the PostgreSQL drivers for PHP are installed. That means you need to have php-pgsql installed. For Linux users, this can be done with “apt-get install php-pgsql” or “yum install php-pgsql” (you may need to customize these commands based on your distribution and/or version of PHP).

Then, edit your “.env” file in the project folder and update the database information accordingly (the values included here are defaults—please adjust to match your configuration):

# cat myproject/.env | grep DB

DB_CONNECTION=pgsql

DB_HOST=<your_database_IP_address>

DB_PORT=5432

DB_DATABASE=postgres

DB_USERNAME=postgres

DB_PASSWORD=postgres

 

In some instances, you may need to link “pgsql.so” to “/usr/lib64/php/modules” and also create a corresponding “/etc/php.d/pdo_pgsql.ini”—it really depends on how your PHP was set up.

Finally, test to see if you can communicate with your PostgreSQL database via Laravel:

# cd myproject

# php artisan migrate:install

Migration table created successfully.

 

Installing the default schema

Laravel comes with a basic user/password schema for testing and tinkering. To load it, simply call it:

# php artisan migrate:fresh

Dropped all tables successfully.

Migration table created successfully.

Migrating: 2014_10_12_000000_create_users_table

Migrated:  2014_10_12_000000_create_users_table (0.01 seconds)

Migrating: 2014_10_12_100000_create_password_resets_table

Migrated:  2014_10_12_100000_create_password_resets_table (0.01 seconds)

Migrating: 2019_08_19_000000_create_failed_jobs_table

Migrated:  2019_08_19_000000_create_failed_jobs_table (0.01 seconds)

[root@pga laravel-contacts]# php artisan migrate:status

+------+------------------------------------------------+-------+

| Ran? | Migration                                      | Batch |

+------+------------------------------------------------+-------+

| Yes  | 2014_10_12_000000_create_users_table           | 1     |

| Yes  | 2014_10_12_100000_create_password_resets_table | 1     |

| Yes  | 2019_08_19_000000_create_failed_jobs_table     | 1     |

+------+------------------------------------------------+-------+

 

You can verify that the tables were created by connecting to your PostgreSQL database with “psql”:

postgres=# \d

                 List of relations

 Schema |        Name        |   Type   |  Owner   

--------+--------------------+----------+----------

 public | failed_jobs        | table    | postgres

 public | failed_jobs_id_seq | sequence | postgres

 public | migrations         | table    | postgres

 public | migrations_id_seq  | sequence | postgres

 public | password_resets    | table    | postgres

 public | users              | table    | postgres

 public | users_id_seq       | sequence | postgres

(7 rows)

 

Adding test data

Laravel comes with a nice facility to generate test data for its default schema. To generate users in the default schema, first uncomment the UsersTableSeeder reference in “myproject/database/seeds/DatabaseSeeder.php”:

# cat seeds/DatabaseSeeder.php 

<?php



use Illuminate\Database\Seeder;



class DatabaseSeeder extends Seeder

{

    /**

     * Seed the application's database.

     *

     * @return void

     */

    public function run()

    {

        $this->call(UsersTableSeeder::class);

    }

}

 

Then, create a “UsersTableSeeder” class:

# php artisan make:seeder UsersTableSeeder

 

Update the newly-generated “myproject/database/seeds/UsersTableSeeder.php” so that the `run()` function looks like this:

    public function run()

    {

        factory(App\User::class, 10)->create();

    }

 

Finally, seed the database:

# php artisan db:seed

Seeding: UsersTableSeeder

Seeded:  UsersTableSeeder (0.05 seconds)

Database seeding completed successfully.

 

You can now look in the tables to see that data was generated:

postgres=# \x

Expanded display is on.

postgres=# SELECT * FROM users LIMIT 1;

-[ RECORD 1 ]-----+-------------------------------------------------------------

id                | 1

name              | Miss Yvonne Kunze

email             | hschuster@example.org

email_verified_at | 2019-12-03 01:30:57

password          | $2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi

remember_token    | boCKVi9ydO

created_at        | 2019-12-03 01:30:57

updated_at        | 2019-12-03 01:30:57

 

Showing data in the browser

What’s the point of using Laravel if you can’t make a web page with it? Now that the data has been generated, you can display it in your browser. First, make sure the route exists:

# tail -n3  myproject/routes/web.php 

Route::get('/', function () {

    return view('welcome', ['users' => App\User::all()]);

});

 

Next, replace the default welcome Blade with a simple listing of all users:

# cat resources/views/welcome.blade.php 

<!doctype html>

<html>

    <head>

        <meta charset="utf-8">

        <meta name="viewport" content="width=device-width, initial-scale=1">



        <title>Users</title>

        <link rel="stylesheet" href="https://unpkg.com/tachyons@4.10.0/css/tachyons.min.css"/>

    </head>

    <body>

        <div class="mw6 center pa3 sans-serif">

            <h1 class="mb4">Users</h1>



            @foreach($users as $user)

            <div class="pa2 mb3 striped--near-white">

                <header class="b mb2">{{ $user->name }}</header>

                <div class="pl2">

                    <p class="mb2">id: {{ $user->id }}</p>

                    <p class="mb2">email: {{ $user->email }}</p>

                    <p class="mb2">details: {{ $user->remember_token }}</p>

                </div>

            </div>

            @endforeach

        </div>

    </body>

</html>

 

This code pulls all the users out of the database and prints each one out to the screen.  From within the “myproject” directory, call “php artisan serve” and point your browser to http://127.0.0.1:8000. You should see this result:

 

 

Note: If you are using a virtualization engine like Docker or Vagrant, you may need to add “--host=0.0.0.0” and a custom port number (“--port=5000” in the screenshot above) in order to route traffic to your VM properly.

 

Creating new tables

To expand the schema, you simply need to create a migration and fill in the blanks.  For example, to create a Cars table, first create a migration:

# cd myproject

# php artisan make:model -f -m Cars

 

Then, edit the “myproject/database/migrations/*_create_cars_table.php” and fill in “Schema::create”:

# cat database/migrations/2019_12_03_083214_create_cars_table.php

…

        Schema::create('cars', function (Blueprint $table) {

            $table->bigIncrements('id');

            $table->string('make');

            $table->string('model');

            $table->integer('year');

            $table->timestamps();

        });

…

 

To autogenerate Car information, edit the factory:

# cat database/factories/CarsFactory.php 

<?php



/** @var \Illuminate\Database\Eloquent\Factory $factory */



use App\Cars;

use Faker\Generator as Faker;



$factory->define(Cars::class, function (Faker $faker) {

    return [

        'make' => $faker->company,

        'model' => $faker->word,

        'year' => $faker->randomNumber,

    ];

});

 

Then, create the seeder and edit it:

# php artisan make:seeder CarsTableSeeder

Seeder created successfully.

# cat database/seeds/CarsTableSeeder.php

<?php



use Illuminate\Database\Seeder;



class CarsTableSeeder extends Seeder

{

    /**

     * Run the database seeds.

     *

     * @return void

     */

    public function run()

    {

        factory(App\Cars::class, 10)->create();

    }

}

 

Run the migration and seed the table:

# php artisan migrate --seed

Migrating: 2019_12_03_083214_create_cars_table

Migrated:  2019_12_03_083214_create_cars_table (0.01 seconds)

Seeding: UsersTableSeeder

Seeded:  UsersTableSeeder (0.06 seconds)

Seeding: CarsTableSeeder

Seeded:  CarsTableSeeder (0.01 seconds)

Database seeding completed successfully.

 

Add a route:

# tail -n 6 routes/web.php 

Route::get('/', function () {

    return view('welcome', ['users' => App\User::all()]);

});

Route::get('/cars', function () {

    $cars = DB::table('cars')

               ->join('users', 'users.id', 'cars.id')

               ->select('users.name', 'users.email', 'cars.*')

               ->get();

    return view('cars', ['cars' => $cars]);

});

 

Create a template:

# cat resources/views/cars.blade.php 

<!doctype html>

<html>

    <head>

        <meta charset="utf-8">

        <meta name="viewport" content="width=device-width, initial-scale=1">



        <title>Cars</title>

        <link rel="stylesheet" href="https://unpkg.com/tachyons@4.10.0/css/tachyons.min.css"/>

    </head>

    <body>

        <div class="mw6 center pa3 sans-serif">

            <h1 class="mb4">Cars</h1>



            @foreach($cars as $car)

            <div class="pa2 mb3 striped--near-white">

                <header class="b mb2">{{ $car->make }}</header>

                <div class="pl2">

                    <p class="mb2">model: {{ $car->model }}</p>

                    <p class="mb2">year: {{ $car->year }}</p>

                    <p class="mb2">owner: {{ $car->name }}</p>

                    <p class="mb2">email: {{ $car->email }}</p>

                </div>

            </div>

            @endforeach

        </div>

    </body>

</html>

 

Serve it up with “php artisan serve” and point your browser to http://127.0.0.1:8000/cars:

 

That’s all there is to it!  Note that creating seeders is not entirely necessary, but can be very useful for demonstrating proof-of-concept and for testing. In the real world, you should define ways for Laravel to insert/update data in the database.

Notice also that when displaying the “Cars” information, we didn’t access the “Cars” class (i.e., in “routes/web.php”, we didn’t call “App/Cars” like we called “App/User”), but used the DB object to join with the “users” table. Laravel allows database access both by using the Query Builder and by the Eloquent ORM. Depending on the design of your app, you may wish to tightly join the Cars and Users tables by defining a One-to-One or One-To-Many relationship in Eloquent.  This will not be covered here, but instructions are readily available in the Laravel documentation.

 

 

​

Popular Links

• Connecting PostgreSQL using psql and pgAdmin
• How to use PostgreSQL with Django
• 10 Examples of PostgreSQL Stored Procedures
• How to use PostgreSQL with Laravel
• How to use tables and column aliases...

Featured Links

• PostgreSQL vs. SQL Server (MSSQL)...
• The Complete Oracle to PostgreSQL Migration...
• PostgreSQL vs. MySQL: A 360-degree Comparison...
• PostgreSQL Replication and Automatic Failover...
• Postgres on Kubernetes or VMs: A Guide...

Resources

• Postgres Tutorials
• The EDB Blog
• White Papers
• The EDB Docs
• Webinars