Repository files navigation

Trying to get Next.js + SQLite combo working onEasypanel with Docker

In real app, do not commit.env.development &.env.production to source control like Git. Add it to.gitignore &.dockerignore or better yet usedotenvx for environment variables.

Create.env.development &.env.production using.env.example format.

NOTE:SQLITE_DATABASE_NAME should be equal tousers.${MODE}.sqlite whereMODE is an environment variable in.env.* as it is referenced inrun.sh. For example, don't useSQLITE_DATABASE_NAME=users.dev.sqlite in.env.development if you are usingMODE=development in the same file. You have to either useMODE=dev orSQLITE_DATABASE_NAME=users.development.sqlite. I like using long-form.

NPM Scripts appended with:prod are production scripts and those without anything appended are scripts to be used in development.

Use/data in.env.production likeSQLITE_DATABASE_PATH=/data/users.production.sqlite & setup Cloudflare environment variables to have Database Backups using Litestream.

• Getdevelopment/Dockerfile to support HMR (Currently, Dockerfile in development does not work butpnpm dev is much better anyways)
• Switch back to usingdocker-compose.yml fordevelopment &staging as docker compose doesn't work only in production on a VPS like Easypanel. Most of the changes were in Dockerfile & Easypanel Settings so all the complexity isn't needed at all.

Note: If you need Redis, then only setup Redis in Docker & use local development environment for HMR as setting docker in development is very tedious & useless (at least it was in my case.)

1. pnpm db:generate generates migration files fordevelopment at/src/app/db/migrations
2. pnpm db:migrate generatesusers.dev.sqlite fordevelopment
3. pnpm turbo orpnpm dev runs the local server.
4. if you try to interact with database by clicking add, delete, or get buttons, then it creates*.sqlite-shm or*.sqlite-wal files. read more aboutwal mode athttps://til.simonwillison.net/sqlite/enabling-wal-mode.

1. make build-production to build a Docker Container forproduction
2. make start-production to start the Docker Container
3. make stop-production to stop the Docker Container
4. docker system prune -f && docker builder prune -f to delete all images & container

1. Go toEnvironment & paste.env.production intoEnvironment Variables & checkCreate .env file to create.env file.
2. Change port to3001 as specified inDockerfile. Go intoDomains, click onEdit button, changeInternal Port to3001. Make sure to use Custom Domain as Easypanel currently isn't working on*.easypanel.host domains.
3. Go toSource, addGithub credentials, chooseDockerfile & pastedocker/production/Dockerfile as the location.
4. EnableAuto Deploy by checking the box besidesDestroy (delete icon) button.
5. Finally, click onDeploy to launch it.

I noticed SQLite WAL Mode on Docker Container doesn't work too well & results in data loss when the*.sqlite file is opened in a database browser likeSQLite Database Desktop App.

Reproduction steps (you have to remove litestream specific code as WAL mode works with Litestream... see below) to see this issue after enabling WAL mode in 2 places (searchjournal_mode=WAL in VSCode):

1. ClickAdd inlocalhost:3000
2. ClickGet All
3. Open the Desktop AppSQLite Database by installing it fromhttps://sqlitebrowser.org/
4. ClickAdd again multiple times & try to refresh database insideSQLite Database Desktop App
5. Notice, how the data doesn't update in the Desktop app but works fine inlocalhost:3000
6. Now close the Docker Container resulting in a data loss

For this reason, I'll be avoiding WAL mode for now. When the time comes & I need multiple writes, I'll use PostgreSQL instead of SQLite if I need multiple writers on a database but since the process of multiple writes is instantanious (milliseconds) so I'll be going with SQLite for now anyways.

I repeated the above 6 steps exactly as specified & there was no data loss.

I guess Litestream wrote it to its WAL Mode & when it found a wrong pointer, Litestream restored the database.

This was the log from Litestream that got me to this conclusion:

time=2024-02-28T05:44:50.247Z level=WARN msg="init: cannot determine last wal position, clearing generation" db=/data/users.prod.sqlite error="primary wal header: EOF"time=2024-02-28T05:44:50.406Z level=INFO msg="sync: new generation" db=/data/users.prod.sqlite generation=ab8dd20a19bb28f7 reason="no generation exists"time=2024-02-28T05:44:51.298Z level=INFO msg="write snapshot" db=/data/users.prod.sqlite replica=s3 position=ab8dd20a19bb28f7/00000000:4152time=2024-02-28T05:44:51.720Z level=INFO msg="snapshot written" db=/data/users.prod.sqlite replica=s3 position=ab8dd20a19bb28f7/00000000:4152 elapsed=422.755427ms sz=1512time=2024-02-28T05:44:52.234Z level=INFO msg="write wal segment" db=/data/users.prod.sqlite replica=s3 position=ab8dd20a19bb28f7/00000000:0time=2024-02-28T05:44:52.602Z level=INFO msg="wal segment written" db=/data/users.prod.sqlite replica=s3 position=ab8dd20a19bb28f7/00000000:0 elapsed=367.834931ms sz=4152

1. Go toStorage > ClickAdd Volume Mount > PutName as anything andMount Path as/etc/easypanel/projects/[project]/[services]/volumes/data/
2. Use/data as directory

Hypothesis:

1. Use/etc/easypanel/projects/[project]/[services]/volumes/data/ asData Path
2. Use/data as directory

I usedDomains > Port & added3001 as internal port on my custom domain & it worked. It didn't work on*.easypanel.host domain for some reason.

I hadMounts > Add Volume Mount set todata asName &/data asMount Path which I don't think is needed if I useVOLUMES ["/data"] inDockerfile.