[HN Gopher] Why SQLite may become foundational for digital progress
___________________________________________________________________
Why SQLite may become foundational for digital progress
Author : alexrustic
Score : 49 points
Date : 2022-05-20 16:09 UTC (2 days ago)
(HTM) web link (venturebeat.com)
(TXT) w3m dump (venturebeat.com)
| ProtoMathics wrote:
| Just another filesystem container.
| adamnemecek wrote:
| Can't wait for something better to come along. The text
| orientedness of SQL is annoying. Something like Linq without the
| underlying SQL.
| beached_whale wrote:
| I know not what you ment but just mentioning that if one uses
| entity framework or linq to sql in .net, linq should work with
| sqlite.
| jdmichal wrote:
| Isn't "LINQ without the underlying SQL" simply list
| comprehensions?
| smitty1e wrote:
| Use an object-relational mapper and leave the boring stuff to a
| library.
| tehlike wrote:
| Amen. I made this point over and over. Sql is fairly annoying
| to deal with, linq is very close to me being perfect, prql is
| close
|
| https://github.com/prql/prql
| TazeTSchnitzel wrote:
| "may become"? It already is the backbone of so much software.
| smitty1e wrote:
| > "I think the biggest complication for us is that there's no
| tooling for it." said Mackey. "We have people deploy apps.
| They're like, 'How do I connect to my database and like query
| things? How do I import data?'"
|
| Wut?
|
| Every scripting language includes a SQLite driver.
|
| Python's SQLAlchemy wraps SQLite beautifully. You can develop
| locally and then deploy to an enterprise server with a simple
| change of connection string.
|
| I must be missing the speaker's point.
| nicoburns wrote:
| I think they mean GUI tooling for ad-hoc inspection of the
| database. And possibly the stumbling block is difficulty
| accessing it over a network?
| smitty1e wrote:
| https://sqlitebrowser.org/
| stefanos82 wrote:
| I prefer https://sqlitestudio.pl/ to be honest with you.
| Mertax wrote:
| I think the potential exists for many multi-tenancy B2B SaaS
| products to be converted to an SQLite-per-tenant model.
|
| CRUD Database products like Jira, Salesforce, Airtable, Notion,
| Clickup, Asana, etc. seem like internal line of business services
| that just need to be thick clients with a distributed database.
| Most individual businesses/tenants won't have to deal with more
| than 100 concurrent writes at a time (if that) so SQLite could
| definitely handle that.
| cropcirclbureau wrote:
| Interesting usage of the word folklore.
| Tao332 wrote:
| tiffanyh wrote:
| The answer is much simpler.
|
| When people created desktop apps - they needed a datastore that
| was simple to use and reliable. SQLite was perfect for this use
| case.
|
| Now, no-one is creating desktop apps anymore (everything is a web
| app), but people still need a simple & reliable datastore. Even
| though SQLite has historically advertised to _not_ be used for
| client /server, more people than not are finding that SQLite
| works for most web use cases as well.
| endisneigh wrote:
| I've yet to see how you can use SQLite for a multi-user multi-
| write app effectively. Unless we're all going back to single
| tenant, single user applications SQLite seems overhyped for the
| new usecases.
|
| Don't get me wrong, there are plenty of uses for SQLite, but I
| think the hype is getting out of hand IMHO.
|
| If you are making an app that only a single person is going to
| use at a time, then there are plenty of options, including
| SQLite. Heck, IndexedDB is sufficient. CouchDB tried the whole
| DB-per-user thing and it didn't end super well.
|
| Happy to be proven wrong though. If anyone has an example of a
| site with more than say, 10K concurrent writers (edit: changed
| from users) running on a single SQLite DB I'd probably change my
| mind.
|
| ---
|
| Now, what _would_ be interesting is a way to architect an app
| such that you can have a central entity, like this site, for
| instance, but all of your posts are actually referring to your
| own SQLite, or equivalent store. In that sense you can own your
| data completely. For performance you can specify a TTL for your
| data and the consumer (this site) could cache* it accordingly.
|
| Though this would probably end up being a lot less performant
| than simply using something like Postgres, but at least you'd
| have more control over your own data.
|
| * - This caching ideally would be enforced by the browser, and
| not the server, that way the server actually never touches or
| knows what data you have.
| anamexis wrote:
| If I understand your idea correctly, wouldn't that require
| connecting to the browser of every single person who has posted
| to load the content?
| endisneigh wrote:
| Yes - hypothetically you could allow for some notion of a
| remote TTL where the server could cache as opposed to the
| browser, but when you follow this to its logical conclusion
| you're just reinvented the sort of thing we already have.
| catmanjan wrote:
| 10K users isn't much, but 10k concurrent writer's is a
| different proposition - I'd be surprised if 100k users had 10k
| concurrent writers
| endisneigh wrote:
| That's a fair point. AFAIK SQLite uses a queue (in WAL mode)
| in order to handle concurrent writes. I imagine it basically
| couldn't handle 10K concurrent writes to begin with in a
| practical application since newly read data would be out of
| date pretty quickly.
| catmanjan wrote:
| I'm not sure as I've never used it but if the writes were
| fast enough perhaps it wouldn't be a problem
| endisneigh wrote:
| idk, the author themselves don't recommend it themselves:
|
| >If many threads and/or processes need to write the
| database at the same instant (and they cannot queue up
| and take turns) then it is best to select a database
| engine that supports that capability, which always means
| a client/server database engine.
|
| https://www.sqlite.org/whentouse.html
| stefanos82 wrote:
| Bloomberg [1] uses SQLite in a custom distributed RDBMS engine
| of theirs in a way I lack the knowledge to completely
| understand its design.
|
| I remember Richard Hipp mentioning Bloomberg in one of his
| interviews (don't ask me which one, I don't remember), that
| they use SQLite to serve billions of bank transaction on a
| daily basis without a problem. [1]
| https://github.com/bloomberg/comdb2
|
| Update: I have found the following paper that describes the
| engine in greater detail:
| http://www.vldb.org/pvldb/vol9/p1377-scotti.pdf
| endisneigh wrote:
| SQLite has been used on top of a lot of things, including
| FoundationDB
| (https://apple.github.io/foundationdb/architecture.html).
|
| That's different than using the DB directly though.
| nine_k wrote:
| It's like asking whether it's possible to write an OS kernel
| module in Python.
|
| No, not really possible, and attempts to do so would be
| misguided. But it does not prevent Python from being highly
| successful in other areas.
|
| Same with SQLite: it's a fine tool for a large spectrum of
| applications, but high-throughput OLTP is not it; SQLite can't
| reasonably replace Cassandra or even Postgres.
|
| The point of much of the "SQLite hype" is that there are many
| applications where read load is high and queries complicated,
| but the write load is low to non-existent. In situations like
| these, you can make do with a smaller DB than Postgres.
| endisneigh wrote:
| > The point of much of the "SQLite hype" is that there are
| many applications where read load is high and queries
| complicated, but the write load is low to non-existent. In
| situations like these, you can make do with a smaller DB than
| Postgres.
|
| Sure, but my point is that unless you know for sure your app
| will stay the same forever, why not just use Postgres or
| something more flexible to begin with? With things like
| Supabase, Hasura, and Managed Postgres why even bother with
| SQLite? Just seems like you're causing your tech team
| inevitable pain when you have more writes than expected and
| have to migrate off of it.
|
| Both Fly.io and Cloudflare's solutions are a good example of
| this. If you're going to use another service, why even bother
| with SQLite? I totally get the SQLite use case when you're
| using it directly like with X-Range-Requests
| (https://phiresky.github.io/blog/2021/hosting-sqlite-
| database...) or SQLite.js and a WASM app
| (https://blog.ouseful.info/2022/02/11/sql-databases-in-the-
| br...), but idk.
|
| tldr: if you're using a service, why bother with one backed
| by SQLite instead of Postgres? The underlying data store will
| be abstracted away anyways. One scales decently, and the
| other, not so much.
| alatkins wrote:
| > CouchDB tried the whole DB-per-user thing and it didn't end
| super well.
|
| Can anyone give further context here? Haven't looked at CouchDB
| for several years, remember it as being good for certain use
| cases (intermittently offline devices etc).
| endisneigh wrote:
| CouchDB doesn't support document ACLs so inevitably you will
| have to use a DB per user and map reduce in order to
| implement anything that has more than one user.
___________________________________________________________________
(page generated 2022-05-22 23:00 UTC)