Open I-Dont-Remember opened 1 year ago
Thanks for the feedback!
SQLite has different security considerations as a plain file than something like PostgreSQL does as a server. If someone grabs the file, they have your whole database and can query it.
I'd consider this an unavoidable downside of using SQLite. Best you can do is store the DB on an encrypted disk, which you should be able to configure with the cloud provider.
If you're running an app, at some point you'll likely need to be able to easily edit a field for a user, or check their state. Since it's not a running server, you can't just attach to the DB from SQLWorkbench or the like.
You should either write an admin site if you need to do this often, or have command line scripts you can run on your web machines. It's safe to write to the same DB from multiple processes as long as WAL mode is enabled (or so I've been told).
That's why the migration instructions are now just "SSH in and run make maintenance-db-upgrade
." :-)
Might be worth mentioning Litestream since it's commonly suggested, even if it you don't want it implemented on the project.
Yeah, and I'm also waiting for LiteFS to be released. I'll consider mentioning these in the future.
Love the idea behind this repo (and your comment on hn)! Have ran into similar questions when I started trying to actually use SQLite for a production grade app like so many articles suggest. Had a couple more questions I've run into that may be useful for those exploring SQLite in the future (not that you're required to find answers for them, but figured it was worth at least attaching to the repo):
Security/Encryption at Rest
SQLite has different security considerations as a plain file than something like PostgreSQL does as a server. If someone grabs the file, they have your whole database and can query it.
DB Administration/Querying
If you're running an app, at some point you'll likely need to be able to easily edit a field for a user, or check their state. Since it's not a running server, you can't just attach to the DB from SQLWorkbench or the like.
option A: From a discussion on the fly forums on exposing SQLite to external connections, there was a suggestion to set up [Nocodb]() on the same server and then connect with
fly proxy
.FROM python:3.10.7-slim
ENV WB_DATA_DIR=/usr/app/data/ ENV ENV=PROD ENV DB_TYPE='sqlite' ENV NC_DISABLE_TELE=true ENV NC_MIN=true ENV NC_DB='sqlite3://sqlite3?d=/usr/app/data/noco.db'
WORKDIR /usr/src/app/
This is how we get nocodb into our image from the multi-stage build
COPY --from=fetch /usr/src/nocodb ./ COPY requirements.txt ./requirements.txt RUN mkdir -p /usr/app/data/; pip install -U pip && pip install -r ./requirements.txt ADD buddy ./buddy COPY ./.py ./.sh /usr/src/app/
RUN useradd demo && chown demo /usr/app/data/
RUN chown demo /usr/src/app/
USER demo
EXPOSE 4747 8080
ENTRYPOINT ["bash", "/usr/src/app/run-prod.sh"]
TODO: try out the gross way of running it https://fly.io/docs/app-guides/multiple-processes/#just-use-bash
TODO: file is 116MB, don't download every time
/usr/src/app/nocodb &
Backups
Might be worth mentioning Litestream since it's commonly suggested, even if it you don't want it implemented on the project.
Other topics
I'm sure there are large topics that haven't been covered but would be worth discussing.