31–40 of 364 posts
Earlier quoted context omitted.
Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)
That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.
;)
Earlier quoted context omitted.
SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html
Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)
All of those connections might need to write, and this is where SQLite gets tricky to implement at scale.
I love SQLite! It's perfect for many use cases, but not all. Fortunately, Postgres is also excellent.
Earlier quoted context omitted.
SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html
Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)
> There is nothing more convenient than SQLite for analyzing and transforming JSON. You can select data directly from a file as if it were a regular table. Personally I love jq[0] for this purpose. I haven't really used SQLite for working with JSON, but the examples given are very verbose. [0] https://stedolan.github.io/jq/
I never managed to wrap my head around jq syntax however
Last time I heard about this, it wasn't looking very good.
> SQLite is serverless. Maybe if you use SQLite as a file format. But if you use it like an actual database (e.g. in a web application), I find that one is best off setting up a daemon thread to queue/batch transactions.
I use a different pattern. A lot sqlite files for diff purposes (UserSession, User Files, etc each store in separate files) This way diff threads of webserver can open/query/read/write a lot of files concurrently without any issue.
[I assume if you are using this pattern successfully in production you are already aware of and taking proper steps to avoid them, but the pedant in me takes issue with "without any issue", since once you have multiple resources being locked in multiple threads, you need to be careful to acquire and release locks in such a way that deadlocks do not occur, either never acquiring more than one lock at a time, acquiring locks in specific orders, or acquiring batches of locks at a time.]
I've built a complex CRM that handles 2.1 million USD in transactions every year. It is running sqlite with a simple in-memory lru cache (just a dict) that gets purged when a mutating query (INSERT, UPDATE or DELETE) is executed. It is very simple and more than fast enough. Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s before making money.
Mindblowing.
SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html
SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html
And that's assuming you're making queries every time an event occurs versus persisting data at particular points in time.