Upgrading

[alxndrsn]

Hi, I've searched the docs & discussions and I can't find any notes on upgrading better-sqlite3 versions. Are there any specific guidelines? Should there ever be issues, e.g. going from v8 -> v11?

[mceachen]

You can skim the releases, but for as long as I’ve been involved with this project (4+ years), I can’t remember a breaking API change.

I update the major version number when versions of Node.js are dropped from support, and when SQLite changes seem substantial enough to warrant caution (but again, the changes they make are overwhelmingly forward compatible).

[alxndrsn]

I've skimmed release notes, and couldn't see any likely issues.

My main concern is actually about SQLite changes, and compatibility with existing databases.

[mceachen]

As far as I know and have tested, the file format on disk used by SQLite is extremely stable.

There are some newer features that older SQLite engines won’t be able to read and obey (like STRICT and FTS5) but, most importantly, newer versions of SQLite3 can always read databases written by older versions of SQLite3 (in my experience).

[alxndrsn]

Would there be support for adding a section to the README or other docs, e.g.

Upgrading

Upgrading your better-sqlite3 dependency can potentially introduce breaking changes, either in the better-sqlite3 API, or between your existing database(s) and the underlying version of SQLite. Before upgrading, review:

• better-sqlite3 release notes: https://github.com/WiseLibs/better-sqlite3/releases
• SQLite changelog: https://www.sqlite.org/changes.html

[mceachen]

That’d be a great addition, I think.

[alxndrsn]

I think I found one change, between better-sqlite3 v9.2.2 and v9.3.0: the handling of JSON paths has changed a little bit.

The following SQL produces different results with each of those versions:

  SELECT value
       , value->>'a'
       , value->>'a.b'
       , value->>'a'->>'b'
       , value->'a'
       , value->'a.b'
       , value->'a'->'b'
       , value->>'$.a.b'
    FROM JSON_EACH('[ {"a":{"b":1}}, {"a":{"b":7} }]')

I suspect this is a change in SQLite due to rewriting their JSON handling. Mentioned in the changelog for v3.45.0:

All JSON functions are rewritten to use a new internal parse tree format...
-https://sqlite.org/releaselog/3_45_0.html

[alxndrsn]

This is actually a change in SQLite to make JSON path handling more similar to PostreSQL's - see https://sqlite.org/forum/forumpost/27ae058947.

[alxndrsn]

9.2.2

[
  {
    "value": "{\"a\":{\"b\":1}}",
    "value->>'a'": "{\"b\":1}",
    "value->>'a.b'": 1,
    "value->>'a'->>'b'": 1,
    "value->'a'": "{\"b\":1}",
    "value->'a.b'": "1",
    "value->'a'->'b'": "1",
    "value->>'$.a.b'": 1
  },
  {
    "value": "{\"a\":{\"b\":7}}",
    "value->>'a'": "{\"b\":7}",
    "value->>'a.b'": 7,
    "value->>'a'->>'b'": 7,
    "value->'a'": "{\"b\":7}",
    "value->'a.b'": "7",
    "value->'a'->'b'": "7",
    "value->>'$.a.b'": 7
  }
]

9.3.0

[
  {
    "value": "{\"a\":{\"b\":1}}",
    "value->>'a'": "{\"b\":1}",
    "value->>'a.b'": null,
    "value->>'a'->>'b'": 1,
    "value->'a'": "{\"b\":1}",
    "value->'a.b'": null,
    "value->'a'->'b'": "1",
    "value->>'$.a.b'": 1
  },
  {
    "value": "{\"a\":{\"b\":7}}",
    "value->>'a'": "{\"b\":7}",
    "value->>'a.b'": null,
    "value->>'a'->>'b'": 7,
    "value->'a'": "{\"b\":7}",
    "value->'a.b'": null,
    "value->'a'->'b'": "7",
    "value->>'$.a.b'": 7
  }
]

[mceachen]

Nice research, thank you!