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

211–220 of 364 posts

Re: SQLite is not a toy database

#211

Post by mastre_ »

lsb wrote: post #166
doomleika wrote: post #148

Two major gripe I had with SQlite 1. SQLite doesn't really enforce column types[0], the choice is really puzzling to me. Since schema enforced type check is one of the strong suit of SQL/RDMBS based data solution. 2. Whole database lock on write, this make it unsuitable to high write usages like logging and metric recording. WAL mode will help but it will only alleviate the issue, you will need row based lock solutio…

If you are in WAL mode, you can have unlimited readers as one writer is writing.

Also, another issue when not in WAL mode is that a long read will actually have a write lock and block writes.

Re: SQLite is not a toy database

#212

Post by eb0la »

Sqlite is also an EXTREMELY well tested database. Last time I checked test code was about 2x productionncode. This is also good indicator to spot software that is not a toy.

Re: SQLite is not a toy database

#214

Post by eb0la »

eb0la wrote: post #212

Sqlite is also an EXTREMELY well tested database. Last time I checked test code was about 2x productionncode. This is also good indicator to spot software that is not a toy.

Turns out it is much, much, more tested than I remembered:

https://www.sqlite.org/testing.html

Re: SQLite is not a toy database

#215

Post by [deleted] »

mbreese wrote: post #203
mastre_ wrote: post #192

Earlier quoted context omitted.

> but one that will never be fixed due to backward compatibility I wonder if a fork/"new version" could address this. Like, sqlite2 (v1.0, etc.).

This would be something on the order of the python2 -> python3 transition. Meaning, it would likely take a decade. After going though that, I'm not sure it would be worth it to just change from (default) flexible data types.

[deleted]

Re: SQLite is not a toy database

#216

Post by outworlder »

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.

> Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s

Oh, but most companies need this!

The biggest feat of microservices (which require ways to manage them, like k8s) was to provide the ability for companies to ship their organizational chart to production.

If you don't need to ship your org chart and you can focus on designing a product, then you can go a long way without overly complicating your architecture.

Re: SQLite is not a toy database

#217

Post by jyrkesh »

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 learned this the hard way when I stuck some app containers down on a few RPis and mapped the folder of stateful stuff (including a SQLite database) to an NFS share on my NAS.

It's....not great.

Re: SQLite is not a toy database

#218

Post by outworlder »

eleog wrote: post #201

Sqlite is not a toy, but it's so sloppy that it's not really a tool either. Like a saw with a loose blade, it can help you cut but it can also hurt you. My pet peave is the way sqlite accepts non-aggregated columns in a GROUP BY query. You'll get a result in the column, but it's not clear what it means. More of sqlite's sloppyness is detailed here https://sqlite.org/src/wiki?name=StrictMode

MySQL is also very "sloppy". People have learned to work around its quirks.

Honestly, SQLite is a wonderful tool if properly used. Calling it "sloppy" is a disservice.

Re: SQLite is not a toy database

#219

Post by D13Fd »

re wrote: post #102
D13Fd wrote: post #49

My only "problem" with SQLite was that it was so fast, running locally, that it hid just how much my app was needlessly hitting the database. It was a surprise when I transitioned to a networked Postgres server and performance completely tanked due to my crappy code...

I know you put "problem" in quotes, but in case you haven't seen it, this document was posted here a little while ago: https://sqlite.org/np1queryprob.html It describes how access patterns that would be bad practices with networked databases are actually appropriate for SQLite as an in-memory DB.

That's interesting! Now I have to resist the urge to move back...

Re: SQLite is not a toy database

#220

Post by kroggen »

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…

we can also have a SQLite database with branches, like in Git:

https://github.com/aergoio/litetree

Post reply on HN