Can't Connect to SQLite Database in a Volume

Questions / Help
,
1

I have a SvelteKit app up and running on Fly. I took the actions below to attempt to add a SQLite database to it, but encountered an error I can’t seem to resolve even after many hours of troubleshooting.

SQLite setup and new volume creation

Here are the steps I took to set things up:

Ran SvelteKit’s npx sv add drizzle command to add the Drizzle ORM & better-sqlite3 to my project and configure them.

Ran fly volumes create production_data to create a new volume named “production_data”.

Added this code to my fly.toml file to mount the volume in the /data directory:

# Allocate a volume for SQLite
[mounts]
    source = "production_data"
    destination="/data"

Ran fly ssh sftp shell, then put local.db /data/production.db to copy the local.db file on my local machine into the production volume at /data/production.db.

At this point, I was able to re-deploy my app, then run fly ssh console and confirm that there is a /data directory mounted inside of the VM with the production.db file inside of it. Everything looked good.

I updated my Dockerfile with ENV DATABASE_URL="file:///data/production.db" so that when better-sqlite3 is initialized accesses process.env.DATABASE_URL, it will try to connect to the database that is mounted at /data/production.db on the VM.

Failed Deployment

Ran fly deploy to redeploy my app, which resulted in this error:

TypeError [Error]: Cannot open database because the directory does not exist
    at new Database (/app/node_modules/better-sqlite3/lib/database.js:65:9)

Troubleshooting

I tried setting the DATABASE_URL to /data/production.db rather than file:///data/production.db and re-deploying, but that didn’t work either.

I also added console.log(process.env.DATABASE_URL) in the JS file where better-sqlite3 is initialized and confirmed that the DATABASE_URL I set in the Dockerfile is being passed through to my SvelteKit app as the process.env.DATABASE_URL environment variable successfully.

So if the /data/production.db is mounted within the VM, and the DATABASE_URL is being passed through to better-sqlite3 properly, why am I getting a “Cannot open database because the directory does not exist” message when my app is deployed?

Notes:

  • I am not using LiteFS at this point since I don’t need a distributed database.
  • My fly.toml file looks like this: fly.toml
  • My Dockerfile looks like this: Dockerfile

I have poured many hours into this and still can’t figure it out. If someone could help me resolve it I’d be very grateful. Thanks in advance for any help you can give! :pray:

2

Hi… It sounds like you might be trying to access /data/ during the build; this isn’t actually possible, since the builders never have the volumes, :cactus:.

In general, the Fly.io platform is rather opposed to doing database operations of any kind in the Dockerfile.

(Also, make sure that you have only one† machine, instead of the default two—since volumes are never shared between machines—nor does the platform auto-synchronize really.)

Hope this helps a little!

†This exposes you to the risk of downtime and total data loss, of course, so do this only if you can afford to lose the most recent 1–2 days of DB writes. (And also have your site be offline until you notice and manually create a replacement machine, etc.)

LiteFS isn’t just for being distributed across the world, but also for basic data durability…

4

Do you have a more complete stack trace?

As far as I know, there is nothing built into drizzle that is aware of the DATABASE_URL environment variable, it is up to you to pass the database name to drizzle. Here’s an example: Drizzle ORM - SQLite ; note in that example the environment variable they chose is named differently.

5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.