Do not convert BLOB value to base64

[wyhaya]

Is your feature request related to a problem? Please describe.

When querying via the HTTP API, BLOB are always encoded as base64 and represented as a String in JSON, making it impossible for us to differentiate whether it is a BLOB or a TEXT.

"values": [
    [
        1,
        1.234,
        "Hello world",
        // BLOB or TEXT?
        "/+4i"       
    ]
]

Describe the solution you'd like

There should be a way to distinguish between BLOB and TEXT types.

Describe alternatives you've considered

1. We can use a binary protocol to replace JSON, which can avoid this situation.
2. Convert the BLOB to a JSON Array. For example: [0, 255, 255, 128].

[otoolep]

I agree that this part of rqlite could use improvement. I'm trying to decide the best way to enhance the API to do so.

However can't you use the types column of the returned response to check it? Can I see the full request and response?

[wyhaya]

However can't you use the types column of the returned response to check it?

SQLite has affinity type, so even if the column type is 'BLOB', it can still store 'TEXT'.

HTTP Request Body

[
  "SELECT blob FROM all_types where id >= 12 LIMIT 2;"
]

HTTP Response Body

{
  "results": [
    {
      "columns": [
        "blob"
      ],
      "types": [
        "blob"
      ],
      "values": [
        [
          "/+4i"
        ],
        [
          "text"
        ]
      ]
    }
  ]
}

[otoolep]

Can I ask what your use case is, such that you could end up writing text to a blob field? If you control the application writing to rqlite, don't you control what goes into the columns? Also, are you aware of the STRICT keyword?

Since SQLite supports storing any value in any column, I want to make rqlite easy to use in this mode. But if I can learn more about your use case, it will help me provide a better solution.

[otoolep]

This is a related issue: #1345

[wyhaya]

I am writing a database manager which supports connection to rqlite.

When rqlite return data, I can't tell at all if it's BLOB or TEXT because it's always JSON(String).
If I can't tell if it's BLOB or TEXT, then I can't edit it because I don't know if I should base64 decode it or not, that's the problem.

---

This is the solution in Cloudflare D1, which is very similar to rqlite, but we can use Array to determine if it is a BLOB value.

{
  "result": [
    {
      "results": [
        {
          "id": 1,
          "real": 1.23456555,
          "text": "Hello world",
          "blob": [
            255, 255, 128
          ]
        }
      ],
      "success": true,
      ...
    }
  ],
  ...
}

[otoolep]

An alternative idea is to add a new (optional) key to the responses. The key would be vtypes and would be a list of the actual types of the returned value. That would probably involve calling sqlite3_column_type() and packaging up the results.

I would make it optional because it would add a lot of extra data to the response, and most of it might be repeated.

[otoolep]

Response would then be:

{
  "results": [
    {
      "columns": [
        "blob"
      ],
      "types": [
        "blob"
      ],
      "vtypes": [
        [
          "blob"
        ],
        [
          "text"
        ]
      ],
      "values": [
        [
          "/+4i"
        ],
        [
          "text"
        ]
      ]
    }
  ]
}

[otoolep]

Formal proposal for a solution here: #1701

I'm also considering a flag targeted at this use case, which would enable a different encoding for BLOB types.

[otoolep]

@wyhaya -- I can't change existing behaviour as it would be a breaking change. However I could add support for an optional query-time flag, blob_array, which would instruct rqlite to render blobs as you suggest. For example:

curl -G 'localhost:4001/db/query?pretty&blob_array' --data-urlencode 'q=SELECT * FROM foo'
{
    "results": [
        {
            "columns": [
                "data"
            ],
            "types": [
                "blob"
            ],
            "values": [
                [
                    [20, 5, 2, 6]
                ]
            ]
        }
    ]
}

Any thoughts?

[wyhaya]

Thanks @otoolep, This completely meets my needs.

[otoolep]

Minor tweak, the URL flag will be blob_array instead of byte_array.