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

51–60 of 274 posts

Re: Consider SQLite

#51

Post by koeng »

brunoluiz wrote: post #13

I love to see that more projects are using SQLite as their main database. One thing that I always wondered though: does anyone knows a big project/service that uses Golang and is backed by SQLite? This because SQLite would require CGO and CGO generally adds extra complexities and performance costs. I wonder how big Golang applications fare with this.

You can use pure Golang SQLite (without requiring CGO) -

https://pkg.go.dev/modernc.org/sqlite

It works well, but the performance is worse than C version. Not a big deal for what I used it for, though. It was approx. 6x worse at inserts.

Re: Consider SQLite

#52

Post by srcreigh »

chasil wrote: post #25
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.

In Oracle, I can do this: RECOVER DATABASE UNTIL TIME '2021-10-01 02:00:00' USING BACKUP CONTROLFILE; SQLite does not implement such a feature.

There is a diff tool [0], and an online backup tool [1]. That kinda thing should be pretty easy to cobble together. You'll just need ~3x disk space to store 2 backups at an interval, diff them and discard the older backup, then apply backward diffs on your most recent backup to achieve a specific backup time.

EDIT: or maybe the Session Extension [2]

[0]: https://www.sqlite.org/sqldiff.html

[1]: https://www.sqlite.org/backup.html

[2]: https://www.sqlite.org/sessionintro.html

Re: Consider SQLite

#53

Post by dorian-marchal »

paulryanrogers wrote: post #38
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 evaluated sqlite for a web extension but ultimately decided it wasn't worth it. There is no easy way to save the data directly to the file system. And saving the data in other ways meant I was probably better off with IndexDB instead. Still it is a tempting option and one that seems to work well for separate tenancy.

> There is no easy way to save the data directly to the file system.

That's what absurd SQL is for (link in the parent comment).

Re: Consider SQLite

#54

Post by pgwhalen »

tyingq wrote: post #41
pgwhalen wrote: post #23

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

>it's not easy to inspect or fix data in production the way we would with postgres. I assume because you're using a remote socket connection from the client? I haven't tried it in a serious setting yet, but I did play around with dqlite and was impressed. Canonical uses it as the backing data store for lxd. Basically sqlite with raft clustering and the ability for clients to connect remotely via a wire protocol. http…

> I assume because you're using a remote socket connection from the client?

Yeah, it's common for all developers to connect to and query against prod postgres DBs via DataGrip or similar.

dqlite definitely looks interesting, but I worry it's a bit heavy given that our only use case for remote access is prod troubleshooting. I think I saw something recently where you could spin up a server on top of a sqlite file temporarily - that might be ideal for us.

Re: Consider SQLite

#55

Post by eternalban »

chasil wrote: post #4

There 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 tim…

> Only a single process can write to the database at any time; it does not support concurrent writers.

Potential plus in cloud/container deployments.

> 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.

There are other possibilities given a bit of additional wrappers (over the db process) and federating nodes with some (consensus) protocol. It may actually be a good fit as the 'node-unit' for very large scale distributed metadata store that would benefit from relational DB semantics instead of basic K/V.

Re: Consider SQLite

#56

Post by root_axis »

anderspitman wrote: post #42

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…

If you care about data normalization and data integrity then SQLite is going to be a much better choice.

Re: Consider SQLite

#57

Post by klysm »

anderspitman wrote: post #42

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…

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.

Re: Consider SQLite

#58

Post by pfarrell »

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.

Re: Consider SQLite

#59

Post by srcreigh »

pgwhalen wrote: post #23

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

Did you try SSH and using SQLite command line tool?

Re: Consider SQLite

#60

Post by aynyc »

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?
Post reply on HN