Laravel / SQLite database gets uploaded from my local machine everytime I run `fly deploy`

Laravel
1

Hey there, I’m new to fly.io (and I’m loving it) and I’m new to Laravel as well.

I’m using SQLite as my DB locally, and I’m doing the same for my production environment. But the problem I’m seeing is that if I change something in my local DB, and then I run fly deploy, the production DB will be overridden by the data I have on my local machine.

This is what I have in my configuration files (what I think it’s relevant):

# fly.toml
[env]
DB_CONNECTION = "sqlite"

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

I have already copied storage -> _storage as suggested in this tutorial Persisting the Storage Folder · Fly Docs

echo "Running 1_storage_init.sh"
FOLDER=/var/www/html/storage/app
if [ ! -d "$FOLDER" ]; then
  # I double checked that it doesn't enter here on every deployment by running fly logs
    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
  # I double checked that it doesn't enter here on every deployment by running fly logs
    echo "$FOLDER is not a directory, initializing database"
    mkdir /var/www/html/storage/database
    touch /var/www/html/storage/database/database.sqlite
fi

I also have two machines and two volumes (maybe here’s the problem?)

# fly machines list
machine_id_1 has vol_id_1 associated with it
machine_id_2 has vol_id_2 associated with it

# fly volumes list
vol_id_1 is attached to machine_id_1
vol_id_2 is attached to machine_id_2

I appreciate your help, I bet this is something silly.

Thank you!

1 Like
2

Hi @alejandronanez! Welcome to Fly.io and Laravel!

First of:
If you would check your [env] section, there is only variable for DB_CONNECTION, but not DB_DATABASE. The default location of sqlite would be initialized in /var/www/html/database if no path to the sqlite file is specified through DB_DATABASE.

Next, notice that the volume is mounted on /var/www/html/storage ( courtesy of [mounts.destination] ), and not the database directory above. Thus, only data stored in the storage directory would be persisted, but not on the location where the sqlite db is actually in( database/ ).

Whenever we do a fly deploy, everything in the project we’re deploying gets deployed to our Fly app as they are( with the exception of files/directories mentioned in .dockerignore ). This is why your local database gets included in your Fly app on each deploy. And since the default path points to the location your local db gets added in, database/database.sqlite, your Fly app would also use the copy of this local db file in production.

Possible Solution:
Can you please add a DB_DATABASE=/var/www/html/storage/database/database.sqlite under your [env] section? Then do a quick fly deploy. This should point your Fly app to use the sqlite file in the storage directory, which is persisted by a volume.

Notes on the Possible Solution:
With DB_DATABASE pointing to an sqlite file in the storage directory persisted by a volume, every deploy will mount this volume to its mounted path (/var/www/html/storage), and use the data stored in the volume. Even if let’s say we have the same /var/www/html/storage/database/database.sqlite in our local project, the data persisted in our Fly app’s volume would be mounted, and override the local sqlite file copy

Secondly:
Great job on adding a back up to your Fly app ( 2 machines, 2 volumes )! Now, you have 2 different volumes. But! Data between these volumes are not automatically synced. So let’s say your first VM receives a user creation in its db, that user object will not automatically be found on the 2nd VM which uses a different volume. You’ll have to manually sync data between these two volumes.

Luckily, you are using SQLITE. And, as it happens the powerful LiteFS exists! It provides data replication across different SQLITE dbs of your Fly app. All you’ll need is to set it up on your Fly app, and it will do the replication for you. You can read more about the getting started guide here, and also check out our Laravel section’s quick notes on setting LITEFS on a Laravel Fly app here..

Please let us know if the Possible Solution section helps your set up. And again, welcome to Fly.io!

2 Likes
3

And no, your post is far from silly, it’s actually very helpful!

Because of your post, there are two things that has possibly been uncovered, that needs to be added to the Fly.io Laravel section’s documentation on SQLITE integration:

  1. Specifying the DB_DATABASE to point to an sqlite db in a persisted folder, so that the behavior above does not happen on future deployments.

  2. Advising on using LITEFS on Fly apps that have more than one machine, and therefore, more than one sqlite db

So thank you for sharing this post in our community at Fly.io!

2 Likes