Databases

No application is complete without a data store!

In this guide, you'll find references on how to connect your Laravel application to Databases running as Fly Apps or as managed solutions from services that suite global distribution of data.

Database Migrations

#1 Through the fly.toml [deploy] configuration ( which will run in every deployment )

[deploy]
  release_command = "php /var/www/html/artisan migrate --force"

#2 Through a Start Script in .fly/scripts:

echo "/usr/bin/php /var/www/html/artisan migrate --force" > ".fly/scripts/db.sh"

#3 Through fly ssh console shortcut:

fly ssh console -C "php /var/www/html/artisan migrate --force"

#4 Through fly ssh console:

fly ssh console
cd /var/www/html/
php artisan migrate --force

MySQL in Fly.io

You can start with a relational-database classic: MySQL. Follow our guide here and run MySQL as a Fly App! Afterwards you're good to connect:

  1. Connect to your MySQL Fly App from a Laravel Fly App
  2. Connect to your MySQL Fly App from a local environment

Connect From a Laravel Fly App

1) Revise the [env] configuration in your Laravel application's fly.toml file to connect with your MySQL Fly App's Fly .internal address:

[env]
  APP_ENV = "production"
  DB_CONNECTION = "mysql"
  DB_HOST = "<MYSQL Fly .internal Address>"
  DB_DATABASE= "<MYSQL_DATABASE>"

2) Then, set up your Laravel Fly App's database username and password through Fly Secrets:

fly secrets set DB_USERNAME=<MYSQL_USER> DB_PASSWORD=<MYSQL_PASSWORD>

3) Finally deploy your Laravel Fly App changes with:

fly deploy 

Connect From a Local Environment

The MySQL instance you spun up in Fly.io "is closed to the public internet", and can only be accessed by another application found in your Fly.io organization's private network. You'll need a way to tunnel into the network, and finally connect to your MySQL instance.

In this guide you'll tunnel to your MySQL instance through the use of flyctl proxy

1) Open your MySQL application's fly.toml and take note of the following:

app = "<mysql-app-name>"

[env]
  MYSQL_DATABASE = "<database-name>"
  MYSQL_USER =  "<database-user>"

2) Then use flyctl proxy to tunnel to your MySQL application:

flyctl proxy 3306 -a <mysql-app-name>

3) Finally, update your Laravel application's local .env file with the values from your MySQL fly.toml file:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=<MYSQL_DATABASE>
DB_USERNAME=<MYSQL_USER>
DB_PASSWORD=<MYSQL_PASSWORD>

MySQL on PlanetScale

For a basic PlanetScale and Fly.io connection, follow our guide here. If you're up for a multi-region level up, check out our Multi-Region Laravel with PlanetScale article.

Once you're setup with PlanetScale, connect your Laravel application in Fly.io through the following steps below:

  1. Get Laravel connection information from PlanetScale instance
  2. Connect from Laravel application in Fly.io

Get Laravel Connection From PlanetScale Instance

Once initialized, your database dashboard should have metrics and options like so:

PlanetScale initialize database dashboard

  1. Click on the Connect button at the top right, this should provide a box of information for connecting with your PlanetScale database.
  2. First though, make sure to add a password, by clicking on the "New Password" button at the upper right corner. This should show you a new password afterwards.
  3. Next, select "Laravel" in the list labeled "Connect with"

PlanetScale Laravel connection string Take note of the connection string provided and let's move on!

Connect From a Laravel Fly App

1) Update the [env] configuration in Laravel application's fly.toml with details from the PlanetScale connection string

[env]
  APP_ENV = "production"
  DB_CONNECTION = "mysql"
  DB_HOST = "<DB_HOST>"
  DB_DATABASE= "<DB_DATABASE>"
  MYSQL_ATTR_SSL_CA="/etc/ssl/certs/ca-certificates.crt"

Take note that the value for MYSQL_ATTR_SSL_CA vary depending on the Docker container used. For the default docker container used by Fly.io, the above value is the path

2) Next, set up the database username and password through flyctl secrets:

fly secrets set DB_USERNAME=<DB_USERNAME> DB_PASSWORD=<DB_PASSWORD>

3) Finally deploy your changes with:

fly deploy 

Postgres in Fly.io

We have a whole section dedicated for Postgres, we even have a flyctl integration to simplify both creation and interaction with your Postgres Fly Apps!

Make sure you have a Postgres Fly App running, afterwards you can connect it with your Laravel 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
  4. Test Connection

Connect From a Laravel Fly App by Attachment

1) You can Attach your Laravel Fly App to your Postgres Fly App:

  • This should generate a database named after <laravel-app-name> in your Postgres Fly App
  • This should generate an env variable DATABASE_URL containing a connection string in you Laravel fly app

2) Make sure the fly.toml of your Laravel Fly App uses postgres:

[env]
  APP_ENV = "production"
  DB_CONNECTION = "pgsql"

Postgres Connection would now be available to your Laravel Fly App using the auto generated DATABSE_URL from the attachment process.

Connect From a Laravel Fly App Manually

If you decide not to use the flyctl postgres attach command above, and would want to connect to your Laravel Fly App in a manual, grueling step by step procedure, I hear you! You'll have to:

  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

Manually connecting with your Postgres Fly App also means manually creating your Postgres Database. You can interact with your Postgres Fly App using flyctl postgres connect.

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

flyctl postgres connect -a <postgres-fly-app>
Connecting to <postgres-fly-app>.internal... complete
psql (14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.

postgres=# 

In case you've forgotten about your app name, but managed to save the output from the Postgres Fly App creation (which you should've!), then check the value of the Hostname. The App name would simply be every little character before ".internal".

2) Great, you're connected! Go ahead and complete your setup with a new database:

create database <DB_NAME>;

3) Now if you check your database list, you should find the recent database created:

SELECT datname FROM pg_database;
  datname  
-----------
 postgres
 testdb
 template1
 template0
(4 rows)
flyctl postgres db list -a <postgres-fly-app-name>

Manually Connect

Once you have your running and database configured Postgres Fly App, it's time to connect with your 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 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>

Possible 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.

Test Connection

To test whether you are connected to your sparkling new Postgres Fly App, a simple php artisan migrate should let you know.

Once migration completes, you can check the tables migrated in your Postgres Fly App:

# 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

Redis in Fly.io

Redis is a NoSQL database popularly used for cache storage, as a message broker, and even as primary database. Through this section you'll learn how to:

  1. Setup using the Fly.io Redis Docker Image
  2. Setup using the Official Redis Docker Image

Setup Using the Fly.io Redis Docker Image

1) First create a new directory and initialize it with fly launch, using Fly.io's Redis Image for the build:

mkdir fly-redis
cd fly-redis

fly launch --image flyio/redis:6.2.6 --name fly-redis --region ams --no-deploy
  • Pull the flyio/redis:6.2.6 image from the docker repository through the --image argument.
  • You can specify your Redis Fly App name through the --name argument
  • You can specify your region code through the --region attribute
  • You can opt not to immediately deploy by adding --no-deploy argument

2) Afterwards, add a volume that will persist your Redis data

fly volumes create redis_server --size 1
  • You can specify any volume name to replace redis_server above
  • You can specify your preferred volume size in GB through the --size argument

3) Then, attach your volume to your Redis Fly App by revising its fly.toml to include [[mounts]]:

app = "fly-redis"
kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[build]
  image = "flyio/redis:6.2.6"

[env]

[experimental]
  allowed_public_ports = []
  auto_rollback = true

[[services]]

[[mounts]]
  destination = "/data"
  source = "redis_server"
  • Under [[mounts]] attach the volume created in step #2, make sure that the name specified for the created volume is exactly the same string specified for its source attribute

4) Next, set up the Redis Fly App password through fly secrets

fly secrets set REDIS_PASSWORD=<redacted>

5) Finally, deploy your Redis Fly App!

fly deploy

Setup Using the Official Redis Docker Image

If you would like to use Redis' official docker image to setup your Redis Fly App, the steps to get up and running are almost identical to the previous guide. There is just one minor revision on the image pulled and an additional step to set the Redis Fly App's password.

1) Follow the steps above, with a revision to the value passed for --image

mkdir off-redis
cd off-redis

fly launch --image redis --name off-redis --region ams --no-deploy
  • Pull the redis image from the docker repository through the --image argument.

2) Before deploying, make sure to revise your fly.toml file to specify your Redis Fly App's password by adding commands under [experimental]

[experimental]
  allowed_public_ports = []
  auto_rollback = true
  cmd = ["sh", "-c", "exec redis-server --requirepass \"$REDIS_PASSWORD\""]
  • Use the fly secret REDIS_PASSWORD you've configured earlier
  • If this passphrase is not set and your Laravel configuration sends a REDIS_PASSWORD during its connection to your Redis Fly App, watch out! You'll get the infamous "ERR Client sent AUTH, but no password is set" error

Connect From a Laravel Fly App

Now that you have your Redis Fly App running, the next step is to connect it with your Laravel Fly App! Follow the steps below:

1) First, retrieve your Redis Fly App's Fly .internal Address.

2) Next, make sure you move to your Laravel Fly App's directory for better navigation:

cd <laravel-app-folder>

3) Then revise your Laravel Fly App's fly.toml [env] configuration with the retrieved Fly .internal Address:

[env]
  ...
  REDIS_HOST= "<redis_app_name>.internal"
  CACHE_DRIVER= "redis"

4) Make sure you set your Laravel Fly App's REDIS_PASSWORD through the flyctl secrets command:

fly secrets set REDIS_PASSWORD=<redacted>

5) Finally, deploy!

fly deploy

That's it! Your Laravel and Redis Fly Apps should now be connected.

Connect From a Local Environment

The simplest way to connect your local Laravel application to a Redis Fly App is to use flyctl proxy:

1) Open your Redis Fly App's local directory

cd off-redis

2) Then run the flyctl proxy for port 6379 or whichever REDIS_PORT you have configured:

fly proxy 6379

3) Next, revise your local Laravel application's .env file to update your Redis connection:

CACHE_DRIVER=redis
REDIS_HOST=127.0.0.1
REDIS_PASSWORD=<redacted>
REDIS_PORT=6379

Make sure the REDIS_PORT in your .env configuration matches the port used in flyctl proxy

That's it! Your local Laravel application should now be connected with your Redis Fly App.

Testing Connection

Upstash Managed Redis Fly App

Want a fully-managed Redis Fly App? Try Upstash Redis! To set up, you can follow our-in-depth guide here

Once you've configured the necesary details through the flyctl prompts, and the app deployment completes, you should get a summary of the Redis cluster you've just deployed, like so:

Your Upstash Redis database blue-brook-3843 is ready.
Apps in the personal org can connect to at redis://default:<redacted>@fly-blue-brook-3843.upstash.io
If you have redis-cli installed, use fly redis connect to connect to your database.

Connect From a Laravel Fly App

To test whether your Laravel Fly App can successfully connect with your Upstash Redis database, you can use your Upstash Redis Fly App as your Cache driver, then perform a quick cache:clear command.

1) Revise the [env] configuration in your Laravel Fly App's fly.toml file:

[env]
  ...
  CACHE_DRIVER="redis"
  REDIS_URL="redis://default:<redacted>@fly-blue-brook-3843.upstash.io"
  REDIS_CACHE_DB=0
  • Set the Laravel cache driver to redis
  • Using REDIS_URL should override REDIS_HOST, add here the REDIS_URL received during fly redis create
  • Upstash only supports "database 0", so update your REDIS_CACHE_DB config to 0. Otherwise, you will receive an error : "SELECT` failed: ERR Only 0th database is supported! Selected DB: 1..."

2) Then deploy your fly.toml changes:

fly deploy

3) Once deployment completes, you can test whether your connection is working by simply clearing your Laravel application's cache

fly ssh console
Connecting to top1.nearest.of.cool-moon-2754.internal... complete
#
cd /var/www/html
php artisan cache:clear

If all goes well here, you should be good to go!

SQLite in a Laravel Fly App

"SQLite" is a file-based, lightweight, low-latency, and in-process library providing an embeddable SQL database engine.

This guide walks through a single instance of your Laravel Fly App running with SQLite as its main database. For a multi-region setup, do check our guide here.

Since SQLite is "embeddable", we no longer have to setup a separate Fly App for it. We simply configure our Laravel Fly App with one:

1) Make sure you are in your Laravel Fly App's directory

cd <laravel-fly-configured-app>

2) Create a volume, you'll be attaching this later to your Laravel Fly App's storage directory

fly volumes create storage_vol --region ams --size 20 #GB

3) Revise your Laravel Fly App's fly.toml file to mount the volume created for your storage directory, and connect with an sqlite database you'll be configuring further down below:

[env]
  APP_ENV = "production"
  LOG_CHANNEL = "stderr"
  LOG_LEVEL = "info"
  LOG_STDERR_FORMATTER = "Monolog\\Formatter\\JsonFormatter"
  DB_CONNECTION="sqlite"
  DB_DATABASE="/var/www/html/storage/database/database.sqlite"

[mounts]
  source="storage_vol"
  destination="/var/www/html/storage"

3) To fix the little storage-content-erasure issue, please go ahead and make a copy of your storage folder in a "backup" folder. You can name this directory "storage_".

cp -r storage storage_

You'll later use this folder to copy over its contents to the volumized storage folder.

4) Next create a Startup Script that will initialize the volumized storage folder's contents.

touch .fly/scripts/1_storage_init.sh

Side Note: Start up scripts are run in numeric-alphabetical order. Naming 1_storage_init.sh makes sure it is the first script run. Otherwise, naming the file as storage_init.sh alone would've moved the caches.sh script above it, and would've executed before storage initialization happened. One of the commands in the caches.sh will not have worked properly, due to a lack of properly initialized storage directory.

On to the content of the Start Up script:

FOLDER=/var/www/html/storage/app
if [ ! -d "$FOLDER" ]; then
    echo "$FOLDER is not a directory, copying storage_ content to storage"
    cp -r /var/www/html/storage_/. /var/www/html/storage
    echo "deleting storage_..."
    rm -rf /var/www/html/storage_
fi


FOLDER=/var/www/html/storage/database
if [ ! -d "$FOLDER" ]; then
    echo "$FOLDER is not a directory, initializing database" 
    mkdir /var/www/html/storage/database
    touch /var/www/html/storage/database/database.sqlite
fi

So what happened above?

  • The first condition statement checks if the app folder does not exist in the volumized storage folder. If it does not exist, it copies over the contents of the storage_ folder to the volumized storage folder.
  • The second condition statement checks if the database folder does not exist in the volumized storage folder. If it does not exist, it creates a database directory inside storage/ and finally creates a database.sqlite file in the storage/database folder

5) Finally, deploy your Laravel Fly App!

fly deploy

Testing the Laravel Fly App's SQLite

A simple php artisan migrate should let you know if all is well:

fly ssh console -C "php /var/www/html/artisan migrate --force"