Can't connect to an SQLite database with Dokku

Phoenix Forum Questions / Help
, , ,
1

Trying to put my app out in the wild. Received a Dockerfile from running

mix phx.gen.release --docker

Then on Dokku’s host I mounted a persistent directory and set the DATABASE_PATH env:

dokku config:set --no-restart myapp DATABASE_PATH=/app/storage/db.sqlite3 PHX_HOST=myapp.com SECRET_KEY_BASE=#beforehand do mix phx.gen.secret
dokku storage:ensure-directory myapp
dokku storage:mount myapp /var/lib/dokku/data/storage/myapp:/app/storage

When deploying, I was getting a “database does not exist” error. Therefore made it manually:

sqlite3 /var/lib/dokku/data/storage/myapp/db.sqlite3 "
  PRAGMA journal_mode = WAL;
  PRAGMA auto_vacuum = INCREMENTAL;
  PRAGMA page_size = 4096;
  PRAGMA wal_autocheckpoint = 1000;
  VACUUM;"
# P.S I executed this as root, for better or worse...

However, on another deployment attempt the app still couldn’t connect to this database or run migrations. This time, the error is different:

** (RuntimeError) connect raised MatchError exception. The exception details are hidden, as they may contain sensitive data such as database credentials. You may set :show_sensitive_data_on_connection_error to true when starting your connection if you wish to see all of the details
    (exqlite 0.33.1) lib/exqlite/connection.ex:396: Exqlite.Connection.get_pragma/2
    (exqlite 0.33.1) lib/exqlite/connection.ex:405: Exqlite.Connection.maybe_set_pragma/3
    (exqlite 0.33.1) lib/exqlite/connection.ex:551: Exqlite.Connection.do_connect/2
    (db_connection 2.8.1) lib/db_connection/connection.ex:79: DBConnection.Connection.handle_event/4

Any ideas on why this could be happening?

1 Like
2

I have set show_sensitive_data_on_connection_error to true in runtime.exs, just like it suggested in the error message, and can now see the actual error:

** (MatchError) no match of right hand side value:
  {:error, "attempt to write a readonly database"}

I’ll post an update when I have an answer.

3

It’s because the user inside the container is not root (good practice). I imagine you have nothing important there yet, so my suggestion is that you delete the file (as root) and recreate from inside the container (as the correct user).

E.g. with mix ecto.setup

(Typing on mobile off the top of my head, there can be mistakes)

1 Like
4

Thank you!

I deleted the SQLite file and added database creation to release.ex:

  def migrate do
    load_app()

    # This is what `mix ecto.create` does internally:
    opts = Application.get_env(:myapp, MyApp.Repo)
    MyApp.Repo.__adapter__().storage_up(opts)

    # ....

But, it still couldn’t access. So I added a touch command in app.json (for Dokku)

{
	"scripts": {
		"dokku": {
			"predeploy": "touch /app/storage/wetouched && /app/bin/migrate"
		}
	}
}

remote: touch: cannot touch '/app/storage/wetouched': Permission denied

This makes me think the container is somehow completely immutable at build time. I’ll dive deeper into Dokku documentation, and potentially try using buildpacks instead of Dockerfile.

5

Oh, you don’t want to create a database during build. The database itself is not to be part of the Docker image.

It will be created/migrated at run time, when replacing the current deploy with the new (after you get the app out to the world…)

1 Like
6

My bad for mentioning earlier: the predeploy script actually runs as new container from the built image, not during the build.

This command solved it for me:

chmod a+w /var/lib/dokku/data/storage/myapp

Source and relevant GitHub issue

:tada:

It was the “permissions” thing all along.

7

That does it, but makes the database file writable by all users.

8

A safer option is to use

dokku storage:ensure-directory --chown nobody:nobody myapp

(Assuming USER nobody from phoenix/priv/templates/phx.gen.release/Dockerfile.eex at v1.8.3 · phoenixframework/phoenix · GitHub)

3 Likes
9

Correcting a small mistake in my previous post – there’s no --chown uid:gid, but instead use --chown false and then manually chown the directory as needed:

APP_NAME=myapp
DIRECTORY_BASENAME=myapp
TARGET_PATH=/mnt/data
CONTAINER_USER_ID=4004
CONTAINER_GROUP_ID=4004
DOKKU_STORAGE_PATH=/var/lib/dokku/data/storage/${DIRECTORY_BASENAME}

dokku storage:ensure-directory --chown false ${DIRECTORY_BASENAME}
dokku storage:mount ${APP_NAME} ${DOKKU_STORAGE_PATH}:${TARGET_PATH}
sudo chown -R ${CONTAINER_USER_ID}:${CONTAINER_GROUP_ID} ${DOKKU_STORAGE_PATH}
1 Like