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

SQLite is not a toy database

antonz.org

Post reply on HN
Print view View original

81–90 of 364 posts

Re: SQLite is not a toy database

#81

Post by blacksmith_tb »

tyingq wrote: post #70
petercooper wrote: post #22

Earlier quoted context omitted.

I ran a niche community social bookmarking site (around 100-200k pageviews per month) on SQLite for several years and it was no problem at all. If a write was occurring, having a simultaneous request wait 100 milliseconds was no big deal. It only became a problem when I got tired of ops and wanted to put it on Heroku at which time I had to migrate to Postgres. I've always been surprised WordPress didn't go with SQLit…

Someone did write a plugin to have wordpress use SQLite as the backend: https://wordpress.org/plugins/sqlite-integration/ Perhaps not great for production since Wordpress automatically updates itself, and you would have to keep up with any changes. And not just for wordpress, but for any other plugins that use the database. Edit: A single file fork (albeit 5k lines of PHP) of the plugin that looks interesting: https:…

I maintain a corp Ghost blog that's backed by SQLite, it's been solid for years.

Re: SQLite is not a toy database

#82

Post by no-s »

petercooper wrote: post #30

With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further: https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten. https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replica…

>so I have a bunch of links some of you might find interesting if you want to dig further

oooh, thank you! I'm starting to see SQLite the same way!

Re: SQLite is not a toy database

#83

Post by formerly_proven »

hexmiles wrote: post #71
ben509 wrote: post #16

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…

> "No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc." I think it has concurrent backups via the backup api: https://www.sqlite.org/backup.html

I'd expect this to be unnecessary in WAL mode (which you should use, when possible), since WAL allows concurrent readers while permitting up to one writer. In the old undo-log mode (which remains the default for compatibility) writing excluded readers.

Re: SQLite is not a toy database

#86

Post by eurleif »

Derpdiherp wrote: post #76

---Was talking about a previous company who made steps to shift from it for pricing reasons. Seems they where misinformed.

Huh? SQLite is public domain.[0] There are paid extensions for encrypted and compressed databases[1], but those cost a flat fee for unlimited devices:[2] "Your license is perpetual. You have paid a one-time fee that allows you to use and modify the software forever. You can ship as many copied of the software to your customers as you want so long as you ensure that only compiled binaries are shipped (you cannot distribute source code) and that your customers cannot make additional copies of the software to use for other purposes."

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

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

[2] https://sqlite.org/see/doc/release/www/readme.wiki

Re: SQLite is not a toy database

#87

Post by zokier »

nalgeon wrote: post #51
ben509 wrote: post #16

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…

I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.

I see availability as orthogonal issue to scale/size. You might be processing one transaction per day but it can still be super important that the service is available for that one transaction.

Re: SQLite is not a toy database

#88

Post by benbjohnson »

ben509 wrote: post #16

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…

I agree that high availability features are outside of the goals of an embedded database. There's an ecosystem of tools SQLite to provide these benefits though. There's dqlite & rqlite for providing HA over SQLite. I'm the author of Litestream[1] which provides streaming replication to S3 for SQLite databases so folks can safely run a single node instance.

[1]: https://litestream.io/

Re: SQLite is not a toy database

#89

Post by pupdogg »

juancampa wrote: post #44
michaelmcmillan wrote: post #19

I've built a complex CRM that handles 2.1 million USD in transactions every year. It is running sqlite with a simple in-memory lru cache (just a dict) that gets purged when a mutating query (INSERT, UPDATE or DELETE) is executed. It is very simple and more than fast enough. Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s before making money.

How do you ensure data is not lost to oblivion if a catastrophic system failure occurs?

You put in-place a loss mitigation strategy. This strategy will vary by application. In my case, I have a similar setup where we write 25-30k records to SQLite daily. We start each day fresh with a new SQLite db file (named yyyy-mm-dd.db) and back it up to AWS S3 daily under the scheme /app_name/data/year/month/file. You could say that's 9 million records a year or 365 mini-sqlite dbs containing 25-30k records. Portability is another awesome trait of SQLite. Then, at the end of the week (after 7 days that is), we use AWS Glue (PySpark specifically) to process these weekly database files and create a Parquet (snappy compression) file which is then imported into Clickhouse for analytics and reporting.

At any given point in time, we retain 7 years worth of files in S3. That's approx. 2275 files for under $10/month. Anything older, is archived into AWS Glacier...all while the data is still accessible within Clickhouse. As of right now, we have 12 years worth of data. Hope it helps!

Re: SQLite is not a toy database

#90

Post by SigmundA »

hackeraccount wrote: post #35

Is it the unix version of MS Access? It's sort of interesting to compare the two.

No it would be closer to the Jet db engine that Access uses, they are both file based in process relational db engines. Back in the day for a Windows app if you wanted a in process file based db you would use Jet since the engine was included with windows even though Access was a separate product.
Post reply on HN