How to use sqlc + SQLite + jsonb?

[brandur]

Hey all,

I've been trying to use the combination of sqlc + SQLite. I've run into a number of smaller issues so far, but have managed to work through most of them via various workarounds. One that I've not been able to find a workaround for (despite trying everything I could think of), is the use of jsonb, and I'm wondering whether I'm missing something or if it's fairly broken right now (I suspect the latter, but wanted to gut check it).

I have a table with a jsonb field like (see metadata):

CREATE TABLE river_queue (
    name text PRIMARY KEY NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    metadata blob NOT NULL DEFAULT (jsonb('{}')),
    paused_at timestamp,
    updated_at timestamp NOT NULL
);

I've inserting to it using this query (this is an INSERT, but the same applies for a SELECT * and every other type of query):

-- name: QueueCreateOrSetUpdatedAt :one
INSERT INTO /* TEMPLATE: schema */river_queue (
    created_at,
    metadata,
    name,
    paused_at,
    updated_at
) VALUES (
    coalesce(cast(sqlc.narg('now') AS text), datetime('now', 'subsec')),
    jsonb(cast(@metadata AS blob)),
    @name,
    cast(sqlc.narg('paused_at') AS text),
    coalesce(cast(sqlc.narg('updated_at') AS text), cast(sqlc.narg('now') AS text), datetime('now', 'subsec'))
) ON CONFLICT (name) DO UPDATE
SET
    updated_at = EXCLUDED.updated_at
RETURNING *;

I'm running a test where I insert a row and then assert on the result:

t.Run("InsertsANewQueueWithDefaultUpdatedAt", func(t *testing.T) {
	t.Parallel()

	exec, bundle := setup(ctx, t)

	metadata := []byte(`{"foo": "bar"}`)
	now := time.Now().UTC()
	queue, err := exec.QueueCreateOrSetUpdatedAt(ctx, &riverdriver.QueueCreateOrSetUpdatedAtParams{
		Metadata: metadata,
		Name:     "new-queue",
		Now:      &now,
		Schema:   bundle.schema,
	})
	require.NoError(t, err)
	require.WithinDuration(t, now, queue.CreatedAt, bundle.driver.TimePrecision())
	require.Equal(t, metadata, queue.Metadata)
	require.Equal(t, "new-queue", queue.Name)
	require.Nil(t, queue.PausedAt)
	require.WithinDuration(t, now, queue.UpdatedAt, bundle.driver.TimePrecision())
})

It fails with an error like this:

riverdrivertest.go:3030:
            Error Trace:    /Users/brandur/Documents/projects/river/internal/riverinternaltest/riverdrivertest/riverdrivertest.go:3030
            Error:          Not equal:
                            expected: []byte{0x7b, 0x22, 0x66, 0x6f, 0x6f, 0x22, 0x3a, 0x20, 0x22, 0x62, 0x61, 0x72, 0x22, 0x7d}
                            actual  : []byte{0x8c, 0x37, 0x66, 0x6f, 0x6f, 0x37, 0x62, 0x61, 0x72}

                            Diff:
                            --- Expected
                            +++ Actual
                            @@ -1,3 +1,3 @@
                            -([]uint8) (len=14) {
                            - 00000000  7b 22 66 6f 6f 22 3a 20  22 62 61 72 22 7d        |{"foo": "bar"}|
                            +([]uint8) (len=9) {
                            + 00000000  8c 37 66 6f 6f 37 62 61  72                       |.7foo7bar|
                             }
            Test:           TestDriverRiverSQLite/QueueCreateOrSetUpdatedAt/InsertsANewQueueWithDefaultUpdatedAt

The problem is that the data is actually coming back as SQLite's jsonb binary format rather than usable JSON.

According to SQLite's docs on jsonb, the format is meant to be internal only, so even if we could deserialize it in our Go code, we should make no attempt to do that:

JSONB is not intended as an external format to be used by applications. JSONB is designed for internal use by SQLite only. Programmers do not need to understand the JSONB format in order to use it effectively. Applications should access JSONB only through the JSON SQL functions, not by looking at individual bytes of the BLOB.

So what should ideally be happening here is that jsonb is run through json(...) before being sent back through sqlc, but I don't know how to pull this off.

I thought about replacing RETURNING * with RETURNING sqlc.embed(river_job), json(metadata) AS metadata, but this isn't great (it breaks all our normal sqlc conventions), and leads to even more problems: (1) sqlc + SQLite doesn't support sqlc.embed here, and (2) sqlc + SQLite doesn't support renaming columns here with AS so you end up with a bunch of position results.

I'm not sure what the best approach here would be, but possibly something like sqlc recognizing a jsonb "sentinel" type in a table definition (there is no jsonb type; these are all stored as blob), and knows to json(...) it before returning.

Any thoughts/ideas?