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 can use scp from your local computer to copy the sqlite db file for viewing locally.

Start by downloading Wireguard here. You’ll also need flyctl which I’m assuming you already have, but if you don’t it can be downloaded here.

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

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 command: 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'

OR

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 exit;

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 /data/backup.db.

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