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

151–160 of 364 posts

Re: SQLite is not a toy database

#151

Post by qbasic_forever »

hobo_mark wrote: post #15
michaelmior wrote: post #11

> There is nothing more convenient than SQLite for analyzing and transforming JSON. You can select data directly from a file as if it were a regular table. Personally I love jq[0] for this purpose. I haven't really used SQLite for working with JSON, but the examples given are very verbose. [0] https://stedolan.github.io/jq/

I never managed to wrap my head around jq syntax however

I'm with you too. I've never found a query language except SQL that has really stuck in my head. Pandas, jq, XSL, etc... it all has me running screaming back to SQL.

Re: SQLite is not a toy database

#152

Post by simonw »

tgb wrote: post #7

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

I do. I load data into SQLite using various tools I've written (geojson-to-sqlite, db-to-sqlite etc) and run the analysis in https://datasette.io

Re: SQLite is not a toy database

#153

Post by qbasic_forever »

tgb wrote: post #7

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

Yes, setup jupyter magics to execute SQL and it becomes MUCH more useful for data analysis in a notebook. There's a great extension that lets you just write and run SQL queries right in cells: https://blog.jupyter.org/a-jupyter-kernel-for-sqlite-9549c5d... Hook it up to an in memory or on disk SQLite instance and you can kick pandas to the curb.

Re: SQLite is not a toy database

#154

Post by emodendroket »

smulc wrote: post #144
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…

Check out rqlite ( https://github.com/rqlite/rqlite ) for that functionality - "rqlite is a lightweight, distributed relational database, which uses SQLite as its storage engine. Forming a cluster is very straightforward, it gracefully handles leader elections, and tolerates failures of machines, including the leader. rqlite is available for Linux, macOS, and Microsoft Windows."

To me this is obviating a lot of the advantage of SQLite over any other RDBMS.

Re: SQLite is not a toy database

#155

Post by emodendroket »

crazygringo wrote: post #146
nalgeon wrote: post #51

Earlier quoted context omitted.

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.

So if you're saying everything does support a web database... then what's the reason people aren't using it for websites? Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into? Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wro…

If writes are infrequent I think it might be fine. Otherwise I’d be wary.

Re: SQLite is not a toy database

#156

Post by nayuki »

formerly_proven wrote: post #83
hexmiles wrote: post #71

Earlier quoted context omitted.

> "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.

I don't think you can do naive file copy backups even in WAL mode. WAL will checkpoint the log into the main database file from time to time. You need to use the SQLite backup API to let you make a backup while blocking any potential checkpointing.

Re: SQLite is not a toy database

#158

Post by emodendroket »

TheRealPomax wrote: post #117
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…

Not sure I understand: sqlite is file based, so snapshots and concurrent backups are literally just file copies/backups. I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can. (Heck, even failover is just a file…

Every database is ultimately on the file system but there’s a reason that method of backup is rarely used.

Re: SQLite is not a toy database

#159

Post by gary_bernhardt »

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…

Seconded; it's a fantastic tool and ecosystem. We use sql.js in production (https://www.executeprogram.com). The SQL course's live code examples all run directly in the browser via sql.js.

I initially had low expectations because it's such a weird use case, but it's been totally reliable. We did have to ignore a few types of errors from old browsers that don't support wasm properly, but we've never had a bug in current browsers caused by sql.js.

Re: SQLite is not a toy database

#160

Post by majkinetor »

simonw wrote: post #145
TheRealPomax wrote: post #117

Earlier quoted context omitted.

Not sure I understand: sqlite is file based, so snapshots and concurrent backups are literally just file copies/backups. I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can. (Heck, even failover is just a file…

Unfortunately backing up with a simply file copy operation isn't guaranteed to work if you have write traffic at the same time. Instead you need to use the .backup mechanism or the VACCUM INTO command, both of which safely create a backup copy of your database in another file - which you can then move anywhere you like.

Depends on the file system. On Windows you can use shadow copy for example.
Post reply on HN