Consider SQLite
blog.wesleyac.com
Consider SQLite
1–10 of 274 posts
Re: Consider SQLite
#2I have tried adopting SQLite in my side projects. The problem I encountered is that using managed PostgreSQL/MySQL is still more convenient and more reliable than using SQLite on a bare metal VPS. I like to use Heroku or Digital Ocean App platform because I want to spend time creating and not managing the infrastructure (ci/cd, ssl certs, reverse proxy, db backup, scaling, container management and what not). I tried looking for a managed SQLite but could not find one. On an unrelated note I found using Redis a good lightweight alternative to the classical psql/MySQL. Although still multi-tier and more difficult to model data, it’s initially cheaper and easier to manage than its relational counterparts. Anyone has had similar setup/preference?
Re: Consider SQLite
#3More info about WAL mode concurrency [0]
No reader-writer lock. Still only 1 concurrent writer, but write via append to WAL file is cheaper. Can adjust read vs write performance by syncing WAL file more or less often. Can also increase performance with lower durability by not syncing WAL file to disk as often
Re: Consider SQLite
#4There are some important things that SQLite does not do.
It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good.
Only a single process can write to the database at any time; it does not support concurrent writers.
The backup tools do not support point-in-time recovery to a specific past time.
If your application can live with these limitations, then it does have some wonderful features.
Re: Consider SQLite
#5I use SQLite exclusively on a high performance crypto sniper project - https://bsctrader.app and I could not be happier with it.
Performs much better then postgres in terms of query latency which is ultra important for the domain we operate in.
I take machine level backups every 2 hours, so in the event of an outage, just boot the disk image on a new vm and it's off.
I would never do this on my professional job due to the stigma, but for this side project, it has been incredible
Re: Consider SQLite
#6I have tried adopting SQLite in my side projects. The problem I encountered is that using managed PostgreSQL/MySQL is still more convenient and more reliable than using SQLite on a bare metal VPS. I like to use Heroku or Digital Ocean App platform because I want to spend time creating and not managing the infrastructure (ci/cd, ssl certs, reverse proxy, db backup, scaling, container management and what not). I tried…
What would a managed sqlite even look like? I can't tell if this is a real response or not...
Re: Consider SQLite
#7I have tried adopting SQLite in my side projects. The problem I encountered is that using managed PostgreSQL/MySQL is still more convenient and more reliable than using SQLite on a bare metal VPS. I like to use Heroku or Digital Ocean App platform because I want to spend time creating and not managing the infrastructure (ci/cd, ssl certs, reverse proxy, db backup, scaling, container management and what not). I tried…
What would a managed sqlite even look like? I can't tell if this is a real response or not...
Maybe an NFS mount or something that handles back ups automatically? Scripting to handle an automatic restore of the database?
Maybe a heroku that knows about your database file and automatically loads the latest version for you?
I kind of feel like GP is a troll comment, as there's no real value add for a managed SQLite.
Re: Consider SQLite
#8SQLite database can be stored in git which seems like a great benefit. But I wonder would it also be possible to have different "branches" of the database and then merge them at some point?
Re: Consider SQLite
#9Just a note that there are significant features of SQLAlchemy that don’t work with SQLite such as ARRAY columns, UUID primary keys and certain types of foreign key constraints.
Re: Consider SQLite
#10I have tried adopting SQLite in my side projects. The problem I encountered is that using managed PostgreSQL/MySQL is still more convenient and more reliable than using SQLite on a bare metal VPS. I like to use Heroku or Digital Ocean App platform because I want to spend time creating and not managing the infrastructure (ci/cd, ssl certs, reverse proxy, db backup, scaling, container management and what not). I tried…
What would a managed sqlite even look like? I can't tell if this is a real response or not...
When hankchinaski says 'managed' I think they really mean that there's some capital-A App dashboard somewhere, on Digital Ocean or wherever, and they log in and click 'new database' and that's it. No ssh-ing to a VPS, and choosing the file location where the sqlite file will sit, figuring out backups and so on. But as you say, while you can wrap postgres or redis in that sort of 'just take care of it for me' approach, given the simplicity of sqlite it doesn't fit that paradigm, so perhaps hankchinaski is just misunderstanding what sqlite fundamentally is and how it works.