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

171–180 of 274 posts

Re: Consider SQLite

#171

Post by bsenftner »

lenkite wrote: post #96
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…

How does one deal with fault tolerance ?

Using the transaction API for modifications, and ordinary fault tolerance measures for the hosting application. It is no more difficult than any other app-foundation technical feature.

Re: Consider SQLite

#173

Post by xcambar »

kevin_thibedeau wrote: post #162
vidanay wrote: post #124

Earlier quoted context omitted.

My recipe application is going to be written in a purely functional language with CQRS and a message bus that provides "guaranteed once" determinism. I should be able to spin up no more than a half dozen containers on two VM's to handle this architecture. The database will probably be redundant MSSQL and Oracle (just in case one technology is fundamentally broken). Database writes will be proxied by a Redis instance.…

It's never gonna scale. Get on the NoSQL train now to future proof and woo the VCs.

Yes. Make it web scale.

Also, think early on about your compensation packages. You don't want to lose a 10x engineer to a FAANG, do you?

Re: Consider SQLite

#174

Post by bobobob420 »

i have so much love for SQLite. Consider me, getting my first internship at a startup. They have a bunch of contractors doing work for them as part of their service. The whitelabel application they got developed would export data in CSV. My job was to take that data and get some meaning from it. data included availability, locations, etc (imagine data about delivery drivers). I had no idea what to do but realized I could definitely parse this CSV through Python.

Once I had this data in Python I needed a way to analyze it. I never worked with Databases but decided to install a local copy of SQLite. The rest is history. I feel like I learned how to use databases in an organic way: by looking for a solution from raw data. A couple of queries later and python was exporting excel sheets with color coded boxes that indicated something based on the analysis I did.

Of course this could be done with any database application but the low weight nature of sqlite allowed me to prototype a solution so easily. We just backed up that native sqlite dump with the cloud and had an easy (super easy) solution to analyze raw data.

Re: Consider SQLite

#175

Post by brunoluiz »

pgwhalen wrote: post #47
brunoluiz wrote: post #34

Earlier quoted context omitted.

I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right? Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file?

> I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right? Yeah. > Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file? We use "kubectl copy" currently when we want to inspect it, and we haven't actually had to write back to a production file yet. We've explored the "r…

Ah I see! Yeah, I expected your team would have to copy it locally. I wonder tho, in times of data leaks and whatnot, couldn’t it be dangerous to have lots of PII (personal identifiable information) copied around many dev laptops?

I mean, devs can do the same with Postgres, but it is more for backups instead of purely querying.

Re: Consider SQLite

#176

Post by aynyc »

aidenn0 wrote: post #131
aynyc wrote: post #60

I used it for ETL process extensive which is great. I still don't know how people use it for concurrent writes like a simple ToDo webapp?

With WAL, writes for something like a ToDo app finish in a small fraction of a millisecond so unless your todo webapp is writing to the DB at a rate exceeding 20k writes per second, the fact that writes are not concurrent becomes largely irrelevant.

This can't be right. As far as I can tell, WAL allows concurrent READS and WRITE, not concurrent WRITES. Am I doing this wrong all these years?

Re: Consider SQLite

#177

Post by frays »

Is anyone here running production workloads which perform read and write operations on a remote SQLite database?

Currently using Postgres and I'm open to switching but I haven't seen any libraries or implementations of SQLite being used as a client/server database.

Re: Consider SQLite

#178

Post by ridiculous_fish »

chasil wrote: post #158
ridiculous_fish wrote: post #152

Earlier quoted context omitted.

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?

I'm just reading this, and learning a few new things: https://www.sqlite.org/howtocorrupt.html SQLite has an alternate lock mode with dotfiles that seems to prevent database corruption over NFS. It is important that all SQLite accesses from all connected processes use the same lock mode. "2.3. Two processes using different locking protocols "The default locking mechanism used by SQLite on unix platforms is POSIX advi…

The problems with dotfile locking are:

1. A crash risks leaving the lock file in place; it must be manually removed or else you hang forever.

2. The same home directory may be local and remote on different machines, meaning different locking protocols.

I am considering a mode where the database is loaded fully into memory, and writes are atomically saved via the classic adjacent-file-renamed mechanism. You risk losing data due to races, but it can't corrupt the database.

Re: Consider SQLite

#179

Post by klysm »

newlisp wrote: post #138
klysm wrote: post #57

Earlier quoted context omitted.

SQLite hides a ton of complexity that lives in the filesystem. It’s incredibly hard to do robust IO correctly with the APIs we have. I almost always choose SQLite for persisting to disk over JSON files. It essentially removes a large class of bugs and is robust enough that I’m not worried about introducing new problems.

SQLite hides a ton of complexity that lives in the filesystem Since they are using Go, couldn't you say the same thing about the Golang std library? As long as they know how to use a local file as database (do the swap, flush, etc...) I don't see the problem.

No because it doesn’t give you IO abstractions as robust as SQLite.

Re: Consider SQLite

#180

Post by danielheath »

frays wrote: post #177

Is anyone here running production workloads which perform read and write operations on a remote SQLite database? Currently using Postgres and I'm open to switching but I haven't seen any libraries or implementations of SQLite being used as a client/server database.

Why switch? Typically the work involved would need to be justified by some benefit, right?
Post reply on HN