SQLite is not a toy database

Post by neerajdotname2 » Thu, Mar 25, 2021, 5:50 PM UTC

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…

Post by Thaxll » Thu, Mar 25, 2021, 5:59 PM UTC

bob1029 wrote: post #170

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

With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives. Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize…

Post by hokkos » Thu, Mar 25, 2021, 6:02 PM UTC

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.

Post by bob1029 » Thu, Mar 25, 2021, 6:04 PM UTC

Thaxll wrote: post #173

bob1029 wrote: post #170

Earlier quoted context omitted.

With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives. Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize…

"The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you." It's not the right approach because it's hard to get right, you want to offload that to the DB.

Post by jstrong » Thu, Mar 25, 2021, 6:08 PM UTC

    select
      json_extract(value, '$.iso.code') as code,
      json_extract(value, '$.iso.number') as num,
      json_extract(value, '$.name') as name,
      json_extract(value, '$.units.major.name') as unit
    from
      json_each(readfile('currency.sample.json'))
    ;