Laravel and PostgreSQL

​

Laravel and PostgreSQL Fly App

1. Connect From a Laravel Fly App by Attachment
2. Connect From a Laravel Fly App Manually
3. Connect From a Local Environment

​

Connect From a Laravel Fly App by Attachment

1. You can Attach your Laravel Fly App to your Postgres Fly App with fly postgres attach:
   • This will create a database named after <laravel-app-name> in your Postgres Fly App
   • This will create an env variable DATABASE_URL containing a connection string in your Laravel fly app
2. Please make sure the fly.toml of your Laravel Fly App uses the postgres driver by specifying it as the value of DB_CONNECTION:
   [env]
   APP_ENV = "production"
   DB_CONNECTION = "pgsql"
   
   Thanks to the attach command, Postgres connection will now be available to your Laravel Fly App using the auto-generated DATABASE_URL from the attachment process.
3. If you are using Laravel version 11, it no longer uses the DATABASE_URL env variable name, instead it uses DB_URL. So, please set an additional Fly secret, DB_URL with the value of the DATABASE_URL.
   fly secrets set DB_URL="<DATABASE_URL_VALUE>"
   

​

Connect From a Laravel Fly App Manually

1. Manually set up a Database in your Postgres Fly App
2. Manually connect your Postgres Database with your Laravel Fly App

​

Manually set up a Database in your Postgres Fly App

1. Connect with your Postgres Fly App using its App name.
   cmd

   output

   fly postgres connect -a <postgres-fly-app>
   
   
   
   
2. Great, you’re connected! Go ahead and complete your set-up with a new database:
   create database <DB_NAME>;
   
3. Now if you check your database list, you should find the recent database created:
   cmd

   output

   SELECT datname FROM pg_database;
   

flyctl postgres db list -a <postgres-fly-app-name>

​

Manually connect to Laravel Fly App

1. Revise your Laravel Fly App’s fly.toml file to connect with the Hostname( your Postgres Fly App’s .internal Address ) from the configuration output, and the DB_NAME configured above:
   [env]
     APP_ENV = "production"
     DB_CONNECTION = "pgsql"
     DB_HOST = "<Hostname>"
     DB_DATABASE= "<DB_NAME>" 
     DB_PORT = 5432
   
2. Set your Laravel database DB_USERNAME and DB_PASSWORD with fly secrets based on the configuration output received during your Postgres Fly App creation:
   fly secrets set DB_USERNAME=<Username> DB_PASSWORD=<PASSWORD>
   
3. You’ve finally reached the last part. You can now rest easy and deploy:
   fly deploy
   

​

Connect From a Local Environment

1. You can check out various ways to connect with your Postgres Fly App from your local environment, here.
2. Once you’re locally connected to your Postgres Fly App, setup your Laravel .env file to connect with the Postgres database:
   DB_CONNECTION=pgsql
   DB_HOST=127.0.0.1
   DB_PORT=5432
   DB_DATABASE=<DB_NAME>
   DB_USERNAME=<Username>
   DB_PASSWORD=<Password>
   

​

Laravel Possible PGSQL Errors

1. First up, check your php.ini file and make sure the driver for Postgres is available and uncommented:
   extension=pgsql
   
2. Next, make sure you have the correct php-pgsql installed for your PHP version. Let’s assume you have a PHP 8.1 running locally, then you must also have the compatible php8.1-pgsql in your environment.

• To fix this, update your Dockerfile. In there, you can install an updated version of the PostgreSQL clients by adding the following commands:

# Install pgsql client for version 14
RUN ...other stuff here... \
    && apt-get -q install -y lsb-release wget \
    && echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list \
    && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - \
    && apt-get -q update \
    && apt-get -q install -y postgresql-client-14

​

Test Connection

# Connect to your Postgres Fly App
$ fly postgres connect -a <postgres-app-name>

# Use the database created 
postgres=# \c <database_name_created>

You are now connected to database "<database_name_created>" as user "<user>".

# List your current database's tables
<database_name_created>=# \d