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

61–70 of 364 posts

Re: SQLite is not a toy database

#61

Post by kwertyoowiyop »

midrus wrote: post #39
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.

Do you mean I don't need Go microservices talking gRPC deployed in multiple kubernetes clusters with bash script based migrations via GitOps with my hand made multi cloud automation (in case we move clouds) following all the SCRUM practices to ship working software? Mindblowing.

You will for your blog series that you mention prominently on your resume that gets you your next Senior Architect gig.

Re: SQLite is not a toy database

#62

Post by smoe »

andrewmcwatters wrote: post #13

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html

I'm sure most website could traffic wise. But for me it is not a question whether I could, but if I should.

If my goal is building a website, I don't necessarily want to experiment with different technologies if I already know Postgres will work perfectly fine without adding much operational overhead and covering use cases I don't have yet, vs the unknown unknowns of using SQLite and maintaining it over time. Again, this is not about some problem with SQLite, but just me not having experience using it this way. Same reason why I wouldn't just add any database system I haven't used before, even if on paper it would be the "better tool for the job" for a particular use case, and sure would be an interesting learning experience.

In my opinion practicality and prior experience often beats what is strictly necessary or "best".

Re: SQLite is not a toy database

#63

Post by dheera »

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…

Which if any of these is efficient enough to run on mobile and can cerealize an entire database to localStorage?

Re: SQLite is not a toy database

#64

Post by antoinealb »

michaelmcmillan wrote: post #53
juancampa wrote: post #44

Earlier quoted context omitted.

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

Backups.

Does that mean it's okay for your application to loose transactions (which occured between the backup point and the failure point) or do you have other mitigations ?

Re: SQLite is not a toy database

#65

Post by midrus »

kwertyoowiyop wrote: post #61
midrus wrote: post #39

Earlier quoted context omitted.

Do you mean I don't need Go microservices talking gRPC deployed in multiple kubernetes clusters with bash script based migrations via GitOps with my hand made multi cloud automation (in case we move clouds) following all the SCRUM practices to ship working software? Mindblowing.

You will for your blog series that you mention prominently on your resume that gets you your next Senior Architect gig.

Agh!... that's the catch... what I'm gonna give talks about and what do I write on medium then.... now I get it. Thanks!.

Re: SQLite is not a toy database

#66

Post by eingaeKaiy8ujie »

gwd wrote: post #46
Thaxll wrote: post #20

Earlier quoted context omitted.

SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html

The article addresses this. Basically, you can have any number of concurrent readers , but only a single writer . Writing and reading can happen concurrently just fine. So the question is -- how many users does a website need before having only a single concurrent writer becomes a bottleneck? That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [ED…

>That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where that number is actually "1".

I can imagine a static website where the content is read-only for users and is only editable by admins/developers/content managers through some CMS.

Re: SQLite is not a toy database

#67

Post by OldHand2018 »

_0w8t wrote: post #60
II2II wrote: post #42

Earlier quoted context omitted.

One of the disappointing developments of the past quarter century is the near demise of general purpose databases as an end user application. Yes, client/server models are useful when dealing with a large number of transactions. On the other hand, it is usually too complex to justify for personal or small office use. I miss the days when databases were included in office suites or could be purchased as relatively ine…

Access DB is still included in Microsoft Office

Only some versions of Office, mainly the non-cheapest business versions.

Re: SQLite is not a toy database

#68

Post by francoisp »

SQLite is a really neat thing. I was looking at extensions and how to augment it; you could even add a pg_notify -like feature: https://sqlite.org/c3ref/update_hook.html and have worker processes doing what would amount to out of process stored procedures in postgres (or UDF in SQLite) -- in any language you'd like.

You can only register one callback per table tho, although you could from this callback fire other functions... All in all it's an awesome tool for a project like tailscape, but I think the hackers there went for a flat file.

Personally I'd would love to see in process postgres; a build of postgres that is geared for integrating a set of your threads, and builds the whole of postgres with your app on all major OSs, only listening to the inside by default. For the same reason I'm using nodejs; to be able to run the same code anywhere. I think bundle size would be a minor issue, really, I downloaded Sage9.2 yesterday, it's 2GB! VSCode is 100MB download, and they refer to it as a small download...

cheers! happy coding,

Re: SQLite is not a toy database

#69

Post by hnrodey »

bob1029 wrote: post #4

Don't forget about User-Defined Functions. https://www.sqlite.org/appfunc.html We just started enhancing our SQL dialect with new functions which are implemented in C# code. One of them is an aggregate and it is really incredible to see how it simplifies projections involving multiple rows. One huge benefit of SQLite's idea of UDFs is that you can actually set breakpoints and debug them as SQL is executing.

Sounds very interesting. Can you elaborate on how you're leveraging C# for this?

Re: SQLite is not a toy database

#70

Post by tyingq »

petercooper wrote: post #22
andrewmcwatters wrote: post #13

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html

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://github.com/aaemnnosttv/wp-sqlite-db

Post reply on HN