undefined | Better HN

0 pointstikotus28d ago0 comments

I've slowly evolved from just writing to and looking up json files to using SQLite, since I had to do a bit more advanced querying. I'm glad I did. But the defaults did surprise me! I'm using it with php, and I noticed some inserts were failing. Turns out there's no tolerance for concurrent writes, and there's no global config that can be changed. Rertry/timeout has to be configured per connection.

I'm still not sure if I'm missing something, since this felt like a really nasty surprise, since it's basically unusable by default! Or is this php's PDO's fault?

0 comments

formerly_proven28d ago

This is the fault/price of backwards compatibility. Most users of SQLite should just fire off a few pragmas on each connection:

    PRAGMA journal_mode = WAL
    PRAGMA foreign_keys = ON
    # Something non-null
    PRAGMA busy_timeout = 1000
    # This is fine for most applications, but see the manual
    PRAGMA synchronous = NORMAL
    # If you use it as a file format
    PRAGMA trusted_schema = OFF

You might need additional options, depending on the binding. E.g. Python applications should not use the defaults of the sqlite3 module, which are simply wrong (with no alternative except out-of-stdlib bindings pre-3.12): https://docs.python.org/3/library/sqlite3.html#transaction-c...

Also use strict tables. https://www.sqlite.org/stricttables.html

SomeUserName43227d ago

> PRAGMA journal_mode = WAL

Pretty sure this is a persistent setting. Don't need to set it per-connection.

j / k navigate · click thread line to collapse