3 ms·

> It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepar

> It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepared statements, I can't see how you could run into problems with modern SSD hardware (unless you're Google-scale of coure). WAL mode is your friend. (Various SQLite drivers, including the Python one, are however somewhat buggy in their transaction handling and need workarounds; essentially they delay the BEGIN of a transaction until you issue DML statements which obviously breaks snapshot isolation entirely). WAL mode allows one writer at a time without impeding readers. (See https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... for the pysqlite workaround)

fforflo 8y ago
I see your point, but I'm very hesitant to change any configuration of SQLite. It kinda feels like one step too close to doing devops - which one wants to avoid by using SQLite I guess. Having said that, I do play around with PRAGMA statements when it's really needed, but usually tweaking the code usually works fine - even increasing the timeout is probably enough :D
- BoorishBears 8y ago
  I read that first sentence like 3 times and still don't get it.
  - sansnomme 8y ago
    Often people use SQLite for sheer convenience instead of perceived performance or resource efficiency i.e. because SQLite is zero config, works out of the box. Sure spinning up a docker container with PostgreSQL is easy enough but why do that when you can use the default standard library with SQLite already embedded and linker configed?
    - BoorishBears 8y ago
      SQLite is an alternative to fopen not a full blown DB... And it’s performance and reliability are a huge part of why it runs on millions of devices everywhere. Your browser was never going to embed a build of PostgreSQL and Docker. Enabling WAL can hardly be called config, it’s a one liner in every driver I’ve ever seen. It’s like saying specifying the file directory SQLite uses is config.
      - groovy2shoes 8y ago
        > It’s like saying specifying the file directory SQLite uses is config. Or, to continue the fopen analogy, like saying that the file you've given fopen is to be opened with mode "a+". The only real difference is that fopen's mode argument is required whereas SQLite's mode has a useful default. > Enabling WAL can hardly be called config, it’s a one liner in every driver I’ve ever seen. Even in the raw libsqlite3 C API (as long as you don't need any error checking ;) ): sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, NULL, NULL); presumably because it's just a one-liner in SQLite's dialect.

fforflo 8y ago

I see your point, but I'm very hesitant to change any configuration of SQLite. It kinda feels like one step too close to doing devops - which one wants to avoid by using SQLite I guess. Having said that, I do play around with PRAGMA statements when it's really needed, but usually tweaking the code usually works fine - even increasing the timeout is probably enough :D

BoorishBears 8y ago
I read that first sentence like 3 times and still don't get it.
- sansnomme 8y ago
  Often people use SQLite for sheer convenience instead of perceived performance or resource efficiency i.e. because SQLite is zero config, works out of the box. Sure spinning up a docker container with PostgreSQL is easy enough but why do that when you can use the default standard library with SQLite already embedded and linker configed?
  - BoorishBears 8y ago
    SQLite is an alternative to fopen not a full blown DB... And it’s performance and reliability are a huge part of why it runs on millions of devices everywhere. Your browser was never going to embed a build of PostgreSQL and Docker. Enabling WAL can hardly be called config, it’s a one liner in every driver I’ve ever seen. It’s like saying specifying the file directory SQLite uses is config.
    - groovy2shoes 8y ago
      > It’s like saying specifying the file directory SQLite uses is config. Or, to continue the fopen analogy, like saying that the file you've given fopen is to be opened with mode "a+". The only real difference is that fopen's mode argument is required whereas SQLite's mode has a useful default. > Enabling WAL can hardly be called config, it’s a one liner in every driver I’ve ever seen. Even in the raw libsqlite3 C API (as long as you don't need any error checking ;) ): sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, NULL, NULL); presumably because it's just a one-liner in SQLite's dialect.