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

31–40 of 274 posts

Re: Consider SQLite

#31

Post by dkjaudyeqooe »

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

Using the right tool for the job does indeed carry a lot of stigma in many professional environments. Instead you use the tool that some VP has been sold by some salesman.

Re: Consider SQLite

#32

Post by srcreigh »

umvi wrote: post #20
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. I think you can do concurrent writing now with Write-Ahead Logging (WAL): https://www.sqlite.org/wal.html I've never tried it though, so I don't know how suitable it is for web apps that might potentially have multiple processes trying to write to the DB at the same time.

There is no concurrent writing with WAL mode

Re: Consider SQLite

#33

Post by mholt »

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.

Not a "big project/service" but a Go project that uses Sqlite is one of my own, Timeliner[1] and its successor, Timelinize[2] (still in development). Yeah the cgo dependency kinda sucks but you don't feel it in code, just compilation. And it easily manages Timeline databases of a million and more entries just fine.

[1]: https://github.com/mholt/timeliner

[2]: https://twitter.com/timelinize

Re: Consider SQLite

#34

Post by brunoluiz »

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.

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?

Re: Consider SQLite

#35

Post by chasil »

cperciva wrote: post #24
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.

Be careful! If you copy a database file while it is being modified, you may end up with an inconsistent view of the database depending on the order in which different parts of the file are modified. There are ways around this (filesystem snapshots and potentially file locking) but it's not trivial and failing to handle this correctly is a very good way to build a system which passes tests under low load (when the fil…

The sqlite3 utility can be used to create a transaction-consistent backup of a live database. This interface is the only way that a backup should be taken, either from the utility or the C API.

    .backup ?DB? FILE        Backup DB (default "main") to FILE
https://sqlite.org/cli.html

Re: Consider SQLite

#36

Post by aflag »

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.

Can't you run your queries in a copy of the data (eg.: a backup)? I think that'd be advisable even if you were running postgresql.

Re: Consider SQLite

#38

Post by paulryanrogers »

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.

Re: Consider SQLite

#39

Post by hoaljasio »

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.

https://github.com/gravitational/teleport/ has the option to use it, but it only uses it as a key value store.

CGO isnt too big a problem and if it is a real dealbreaker something like https://pkg.go.dev/modernc.org/sqlite will work as it transpiled the c into go and passes the sqlite test suite. I think there is performance degradation with writes but reads are still pretty quick.

Re: Consider SQLite

#40

Post by teraflop »

umvi wrote: post #20
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. I think you can do concurrent writing now with Write-Ahead Logging (WAL): https://www.sqlite.org/wal.html I've never tried it though, so I don't know how suitable it is for web apps that might potentially have multiple processes trying to write to the DB at the same time.

Nope. In the default rollback-journal mode, SQLite supports either multiple readers or a single writer at a time. WAL mode improves this to multiple readers and at most one writer.

But transactions in SQLite are often fast enough that this could still be acceptable for a lot of purposes, especially on an SSD.

Post reply on HN