Output binary columns in "sqlite-utils query" JSON

[simonw]

You get an error if you try to run a query that returns data from a BLOB.

[simonw]

Best solution I can think of is to return the data as base64. It's a bit nasty since it means you can't round trip it back again.

[simonw]

I could solve round tripping (at least a bit) by allowing insert to be run with a flag that says "these columns are base64 encoded, store the decoded data in a BLOB".

That would solve inserting binary data using JSON too.

[simonw]

I'm going to imitate how Datasette solves this problem:

[
  {
    "name": "lorem.txt",
    "mode": 33188,
    "mtime": 1595805965,
    "sz": 16984,
    "data": {
      "$base64": true,
      "encoded": "eJzt0c1xAyEMBeC7q1ABHleR3HxNAQrIjmb4M0gelx+RTY7p4N2WBYT0vmufUknH8kq5lz5pqRFXsTOl3pYkE/NJnHXoStruJEVjc0mOCyTqq/ZMJnXEZW1Js2ZvRm5U+DPKk9hRWqjyvTFx0YfzhT6MpGmN2lR1fzxjyfVMD9dFrS+bnkleMpMam/ZGXgrX1I/K+5Au3S/9lNQRh0k4Gq/RUz8GiKfsQm+7JLsJ6fTo5JhVG00ZU76kZZkxePx49uIjnpNoJyYlWUsoaSl/CcVATje/Kxu13RANnrHweaH3V5Jh4jvGyKCnxJLiXPKhmW3fiCnG7Jql7RR3UvFo8jJ4z039dtOkTFmWzL1be9lt8A5II471m6vXy+l0BR/4wAc+8IEPfOADH/jABz7wgQ984AMf+MAHPvCBD3zgAx/4wAc+8IEPfOADH/jABz7wgQ984AMf+MAHPvCBD3zgAx/4wAc+8IEPfOADH/jABz7wgQ984PuP7xubBoN9"
    }
  }
]

[simonw]

Documentation: 20e543e?short_path=7240b7c#diff-7240b7c71b1a8194da0c001c64fc8d40