Gonna give a quick how to for connecting to and getting your sqlite database
from your production app on fly.io. It can be a bit confusing but once you get
your ssh connection set up and openssh installed on your fly.io server, it’s
much easier to replicate. In this tutorial we will be connecting to your fly.io
instance via wireguard and ssh, then insalling openssh on your instance so we
scp from your local computer to copy the sqlite db file for viewing
You’ll need to connect wireguard to fly.io by creating a wireguard config file
with the fly CLI. Use
flyctl wireguard create to create a file called
wireguard.conf. Then open wireguard and import that file and activate that ssh
Next you’re going to connect to your fly instance via ssh. Type
fly ssh issue --agent then
fly ssh console to ssh into your instance.
Once you’re in you’ll need to install openssh on the instance to be able to
securely copy files from your instance to your local computer. Do this with the
apt-get install openssh-client.
At this stage you’ll want to create a backup of your database to copy to your local machine so nothing goes wrong trying to copy the file while a transaction is happening. You can do this in one of 2 ways:
sqlite3 data/sqlite.db '.backup data/backup.db'
sqlite3 data/sqlite.db "VACUUM INTO 'data/backup.db'"
You may have to change the path to your database, mine was in the data directory.
Exit ssh with
Now you can copy your sqlite db file to your local computer with
scp root@[app-name].internal:/data/backup.db fly_backup.db.
Don’t forget to fill in the name of your app on fly.io and change the path to
wherever you backed up your sqlite db on your server. Mine is at
Written by Matt Gregg, a UI engineer who lives and works in Minneapolis, MN
Have something to say about this post? Tweet at me @itwasmattgregg