Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Consider SQLite

blog.wesleyac.com

Post reply on HN
Print view View original

221–230 of 274 posts

Re: Consider SQLite

#221

Post by stjohnswarts »

koeng wrote: post #43
samwillis wrote: post #27

I believe SQLite is about to explode in usage into areas it’s not been used before. SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to build PWAs and hybrid mobile apps with a local SQL db. Absurd SQL uses IndexedDB as a block store fs for SQLite so you don’t have to load the whole db into memory and get atomic writes. Also I recently discovered the Session Extension[2] which would potentially ena…

The golang equivalent of native SQL can be found here - https://pkg.go.dev/modernc.org/sqlite Very useful for pure go applications!

Wouldn't that be leaving one huge advantage of sqlite behind (namely it's insane test regimen and battle tested history)?

Re: Consider SQLite

#222

Post by stjohnswarts »

ridiculous_fish wrote: post #152
chasil wrote: post #45

Earlier quoted context omitted.

Databases on remote filesystems should be limited to SQLITE3_OPEN_READONLY access, agreed.

I'm experimenting with using SQLite to store users' history in fish shell, but the remote filesystems problem seems likely to be a showstopper. What can be done about it?

talk to a server on the remote "storage" where the history is rather than directly to the sqlite server? Queue up changes and make them serial in the "service"

Re: Consider SQLite

#223

Post by jks »

masklinn wrote: post #29
lrem wrote: post #18

Earlier quoted context omitted.

I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.

> modulo write ahead journal and whatnot That's like saying immortality is trivial modulo death and whatnot. If you don't integrate with sqlite's locking system, you can easily "backup" a file mid-write, corrupted and unusable. That's why sqlite has a built-in backup command .

Litestream does streaming backups using a separate process: https://litestream.io/

Re: Consider SQLite

#224

Post by altantiprocrast »

bob1029 wrote: post #126
greatjack613 wrote: post #5

I 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 t…

> I would never do this on my professional job due to the stigma, but for this side project, it has been incredible I love controversy so I was able to push SQLite through as the sole persistence mechanism for our product. Virtually every constraint lined up with our business perfectly. We sell a B2B product that gets installed by our customers, so they love it too. No more sweaty hands conversations about why their…

I think that's one of the inspirations behind sqlite.

> They’d double click on the thing and a dialog box would pop that says, “Can’t connect to database server,” and it wasn’t our fault. We didn’t have any control over the database server, but what do you do if you can’t connect to the server, so we got the blame all the same because we were painting the dialog box.

https://corecursive.com/066-sqlite-with-richard-hipp/

Re: Consider SQLite

#225

Post by gbear605 »

qvrjuec wrote: post #144
gbear605 wrote: post #134

Earlier quoted context omitted.

I can think of plenty of services that an occasional (once a year? less?) outage is okay. Heck, anything relying on AWS us-east-1 is going to have outages that frequently based on the last few months. Meanwhile, almost any service is better off when its response times are cut drastically. I’ve seen many instances where a service’s response times are more than half waiting for a db to respond.

It's not the threat of an outage with data loss that is concerning to me- I just want to understand use case that needs fractions of a second shaved off of query times by using SQLite in this way that is also ok with the possibility of data loss.

If my webpage loading makes ten database calls, and each database call takes 45 milliseconds, my webpages takes 300 milliseconds longer to load than if each call only took 15 milliseconds. 0.3 seconds across each page load, especially if the rest of the call is less than a second, can significantly improve the usability of a website and increase user retention. This applies for any user facing website whether its a social media platform, an online shop, or just a blog.

Obviously there are other improvements that can be made too, like batching db requests or in-memory caching, but this is one option.

Re: Consider SQLite

#226

Post by adroitboss »

lenkite wrote: post #107
zaptheimpaler wrote: post #64

I don't doubt the power of SQLite, but its difficult to see why its worth using over Postgres anyways. This is what it takes to run a basic postgres database on my own PC (in a docker compose file): postgres: image: postgres:12.7 container_name: postgres environment: - PGDATA=/var/lib/postgresql/data/pgdata - POSTGRES_PASSWORD= volumes: - ./volumes/postgres/:/var/lib/postgresql/data/ For someone who's completely alle…

It's unfortunate you are getting downvoted. Nobody in this thread has given clear solutions for addressing issues like fault-tolerance, failover, backup and all the managed goodness you can get from choosing Postgres.

I believe the addressed backups in the article with litestream.

Re: Consider SQLite

#227

Post by edwinyzh »

pfarrell wrote: post #58
samwillis wrote: post #27

I believe SQLite is about to explode in usage into areas it’s not been used before. SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to build PWAs and hybrid mobile apps with a local SQL db. Absurd SQL uses IndexedDB as a block store fs for SQLite so you don’t have to load the whole db into memory and get atomic writes. Also I recently discovered the Session Extension[2] which would potentially ena…

> I can imagine building a SAAS app where each customer has a “workspace” each as a single SQLite db I did just that at my (now defunct) startup a few years ago. We were building a collaborative database query tool. The software we built used sqlite to keep a local db for storing user credentials, caching query results, etc. Bonus, we were able to have the local database file be encrypted protecting the data at rest.

> we were able to have the local database file be encrypted protecting the data at rest. How do you store the password locally? Or do you requesting the passwords from a remote server?

Re: Consider SQLite

#228

Post by koeng »

stjohnswarts wrote: post #221
koeng wrote: post #43

Earlier quoted context omitted.

The golang equivalent of native SQL can be found here - https://pkg.go.dev/modernc.org/sqlite Very useful for pure go applications!

Wouldn't that be leaving one huge advantage of sqlite behind (namely it's insane test regimen and battle tested history)?

It’s cross compiled, so all the tests are brought along and test fine. And god, there are so many tests.

Re: Consider SQLite

#229

Post by lokedhs »

stjohnswarts wrote: post #219
lelanthran wrote: post #210

Earlier quoted context omitted.

All users understand and empathise when you say "Sorry, the system is down right now" once or twice a year. None of them display any understanding or empathy whatsoever when you say "Your trade will always be executed after a 1 second delay, even if the price has moved" Users find occasional downtime awful, but they find consistent lethargy worse.

No customer in fintech is going to accept the "we lost some data transactions" and buy the software so your use case is covered in that they are up front with the customer that if the server goes down any transaction in progress will not complete.

There is a difference between losing data, and losing transactions. Transactions can be replayed from the message log (depending on the implementation) and reprocessed if needed.

I have seen plenty of recovery operations in finance, sometimes cases where the entire database server was lost, and even though the transactions were not saved, no actual financial data was ever lost, as this data could always be reloaded.

Of course, certain systems exist that do store data in a way where loss is not accepted, but even in finance these are somewhat rare compared to the vast number of other systems.

Re: Consider SQLite

#230

Post by devwastaken »

bob1029 wrote: post #135
gfody wrote: post #129

Earlier quoted context omitted.

> It is my understanding that something about not having to take a network hop and being able to directly invoke the database methods makes a huge difference. Are you able to execute queries and reliably receive results within microseconds with your current database setup? with SQL Server you can get a very fast local connection by specify "server=(local)" in the connection string - this uses shared memory protocol b…

This is true - The counterpoint is that now you have this leviathan that is the SQL Server process(es) running on the same machine as the application. If I am constraining my SQL Server usage to fit on 1 box, I might as well use SQLite (assuming no future plans for horizontal scaling).

How is this handling privledge separation? Typically you'd have all accesses as root in SQLite.
Post reply on HN