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

281–290 of 364 posts

Re: SQLite is not a toy database

#281

Post by therealdrag0 »

tyingq wrote: post #50
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

A nice fix for this might be to extract just the wire protocol from dqlite to make a network connected, but not Raft replicated, sqlite. https://github.com/canonical/dqlite

There's also Expensify's wrapper of sqlite, bedrockdb.com. But at that point why not just use traditional RDMS?

Re: SQLite is not a toy database

#282

Post by samatman »

kragen wrote: post #48

This is a great introductory guide! I like SQLite a lot, but I found SQLite's recursive CTE implementation to be somewhat limited: https://dercuano.github.io/notes/why-html-is-not-a-programmi... Has that improved? (sorry about the embarrassing arrogant pedant attitude in that note, but it's too late to fix it now)

It has! Search for "2020-12-01 (3.34.0)" here: https://www.sqlite.org/draft/changes.html

The 3.35 release added more good stuff for CTEs, as well as a RETURNING clause and a much more flexible UPSERT.

Re: SQLite is not a toy database

#283

Post by pdimitar »

smoe wrote: post #62
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,…

Completely fair. It's just that some of us got obsessed with minimalism lately. With the mind-boggling power of the computers today it pays off to be able to make stuff small and still very functional.

But not wanting to get out of your comfort zone is a completely valid stance to take. We do this for money after all.

Re: SQLite is not a toy database

#284

Post by samatman »

JimmaDaRustla wrote: post #195

Weird. They never mentioned the amazing power of inserting text and numbers into a boolean column.

BOOLEAN in SQLITE is spelled `flag INTEGER NOT NULL CHECK (flag = 0 or flag = 1)`.

Less than ideal, sure. But typing is there when you want to reach for it.

Re: SQLite is not a toy database

#285

Post by dfox »

haberman wrote: post #178
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…

> SQLite doesn't really enforce column types[0], the choice is really puzzling to me. This is acknowledged as a likely mistake, but one that will never be fixed due to backward compatibility: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more…

I would not say that it is acknowledged as a mistake. Sqlite originally comes from Tcl world and thus it is somewhat natural that it follows Tcl's stringly-typed object model.

Re: SQLite is not a toy database

#286

Post by tyingq »

therealdrag0 wrote: post #281
tyingq wrote: post #50

Earlier quoted context omitted.

A nice fix for this might be to extract just the wire protocol from dqlite to make a network connected, but not Raft replicated, sqlite. https://github.com/canonical/dqlite

There's also Expensify's wrapper of sqlite, bedrockdb.com. But at that point why not just use traditional RDMS?

An embedded DB with an optional network protocol, but without raft, replication, etc, seems "normal" to me. Raima used to be popular in the 90's and was pretty much that. It's still less to manage than MySql or Postgres.

Re: SQLite is not a toy database

#287

Post by gryn »

ericlewis wrote: post #163
finiteseries wrote: post #143

Earlier quoted context omitted.

I’m not anywhere near the banking industry but from HN alone I’ve been led to believe dailyish huge file transfers are also the norm in a variety of situations (aka SQLite’s backup strategy).

ftp or sftp if you're lucky - upload a giant CSV or receive one. It is crazy to me it still works this way .

and sometimes CSVs sent by mail on a set frequency (by a bot i'm guessing?)

Re: SQLite is not a toy database

#288

Post by smoe »

pdimitar wrote: post #283
smoe wrote: post #62

Earlier quoted context omitted.

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,…

Completely fair. It's just that some of us got obsessed with minimalism lately. With the mind-boggling power of the computers today it pays off to be able to make stuff small and still very functional. But not wanting to get out of your comfort zone is a completely valid stance to take. We do this for money after all.

For me it is more about not leaving all the comfort zones all at once. Say if I start doing a project with 5 different technologies in the stack, I pick at least 4 that I’m solid with and maybe 1 wildcard.

E.g. I got interested SvelteJS exactly because I got jaded of how ridiculously complex many front end applications have gotten, albeit they are doing just barely more than fetching a JSON from a server and turning it into html. Then I got a really good use case for using it in production because the low end mobile phones and bad internet connections of our users were struggling with the very heavy SPA the company started off with.

For another project in the future, it might well be SQLite that is the more experimental part, but in the end it comes down to managing risks and benefits.

While one part of me would love to experiment with everything all the time, the other part likes to finish the work day on time to be able to have plenty time dedicated to non tech related things and that sleeps well at night being fairly sure that stuff is running smoothly

Re: SQLite is not a toy database

#289

Post by justsomeuser »

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…

SQLite itself does not have those features, but at the disk level you can get those features:

On GCP:

- A regionally replicated disk will replicate writes synchronously to another zone (another data centre 100km away). This means when the SQLite write call returns, the data will be geographically replicated, and the second disk can be used as a fail over. This is all transparent to app/db.

- Disk snapshots are incremental, and are stored in Cloud Storage, which is geo replicated across regions (E.g. Europe and US).

In a way, this gets you even better failover and backups than traditional server DBMS's that have these features built in, and often need custom administration to ensure they are working.

Re: SQLite is not a toy database

#290

Post by le-mark »

samatman wrote: post #280
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…

It's more accurate to phrase point 1. as SQLite column constraints are opt-in. If you explicitly add a check constraint on a column schema, SQLite will dutifully perform it for you. It's a little extra work, and as siblings have pointed out, a greenfield SQLite would probably not have done things this way. But it's also easy, I have check constraints on many columns and they serve the purpose. Think of SQLie as havin…

Using a single write thread and multiple readers gives perfectly sound and high performance concurrency in SQLite. Of course the particulars of how one actually does that depend on the language in use.
Post reply on HN