Skip to content

SQLite: Coerce jsonb columns to json before returning to Go code #3968

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
kyleconroy merged 4 commits into from
Jun 13, 2025
Merged

SQLite: Coerce jsonb columns to json before returning to Go code #3968

Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
4 commits
Select commit Hold shift + click to select a range
3695ee3
SQLite: Coerce jsonb columns to json before returning to Go code
brandur May 16, 2025
967f653
Make SQLite json/jsonb values `json.RawMessage` instead of `[]byte`
brandur May 25, 2025
0555230
Introduce `Selector` interface for generating column expressions
brandur May 25, 2025
c62c6e7
Make selectors internal to the `compiler` package
brandur May 25, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Next Next commit
SQLite: Coerce jsonb columns to json before returning to Go code 
This one follows up the discussion in #3953 to try and make the `jsonb`
data type in SQLite usable (see discussion there, but I believe that
it's currently not).

According the SQLite docs on JSONB [1], it's considered a format that's
internal to the database itself, and no attempt should be made to parse
it elsewhere:

> 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.

Currently, when trying to use a `jsonb` column in SQLite, sqlc ends up
returning the internal binary data, which ends up being unparsable in Go:

    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 fix is that we should make sure to coerce `jsonb` columns back to
`json` before returning. That's what this pull request does, intercepting
`SELECT *` and wrapping `jsonb` columns with a `json(...)` invocation.

I also assign `json` and `jsonb` a `[]byte` data type by default so they
don't end up as `any`, which isn't very useful. `[]byte` is consistent
with the default for `pgx/v5`.

[1] https://sqlite.org/jsonb.html
  • Loading branch information
@brandur
brandur committed May 16, 2025
commit 3695ee3a8c2d9340dd71940152b65fed02b2a564
3 changes: 3 additions & 0 deletions internal/codegen/golang/sqlite_type.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -56,6 +56,9 @@ func sqliteType(req *plugin.GenerateRequest, options *opts.Options, col *plugin.
}
return "sql.NullTime"

case "json", "jsonb":
return "[]byte"
Copy link
Contributor Author

@brandur brandur May 16, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Is json.RawMessage preferred over []byte?

Copy link
Collaborator

@kyleconroy kyleconroy May 22, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Yeah, it's what we do for PostgreSQL.

Copy link
Contributor Author

@brandur brandur May 25, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Cool, that makes sense. Changed!


case "any":
return "interface{}"

Expand Down
12 changes: 12 additions & 0 deletions internal/compiler/expand.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -6,6 +6,7 @@ import (
"strings"

"github.com/sqlc-dev/sqlc/internal/config"
"github.com/sqlc-dev/sqlc/internal/engine/sqlite"
"github.com/sqlc-dev/sqlc/internal/source"
"github.com/sqlc-dev/sqlc/internal/sql/ast"
"github.com/sqlc-dev/sqlc/internal/sql/astutils"
Expand Down Expand Up @@ -149,6 +150,17 @@ func (c *Compiler) expandStmt(qc *QueryCatalog, raw *ast.RawStmt, node ast.Node)
if counts[cname] > 1 {
cname = tableName + "." + cname
}
// Under SQLite, neither json nor jsonb are real data types, and
// rather just of type blob, so database drivers just return
// whatever raw binary is stored as values. This is a problem
// for jsonb, which is considered an internal format to SQLite
// and no attempt should be made to parse it outside of the
// database itself. For jsonb columns in SQLite, wrap returned
// columns in `json(col)` to coerce the internal binary format
// to JSON parsable by the user-space application.
if _, ok := c.parser.(*sqlite.Parser); ok && column.DataType == "jsonb" {
Copy link
Contributor Author

@brandur brandur May 16, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

So I left the *sqlite.Parser in here as a dummy implementation for detecting when we're running as SQLite, but I could use some help determining what the preferred convention should be. It feels a little heavy handed to make it part of one of the interfaces like Parser given it's such an unusual corner case.

Copy link
Collaborator

@kyleconroy kyleconroy May 22, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I looked through the code to see if there was a better place to put this. The ideal solution is to create a SelectExpr function on the compiler.Column which adds the json(). That said, we'd have to pipe it through the catalog and the ast, all of which are engine-agnostic.

Instead, let's create a new Selector interface with a single method Expr(name, column) string. In NewCompiler we already switch on the engine so we can pick the correct one to use. Unlike the parser and the catalog, the selector structs shouldn't be exported and should live in the compiler package.

What do you think?

Copy link
Contributor Author

@brandur brandur May 26, 2025 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Yep, works for me. I just pushed a change that does roughly what you described here. The one tweak I made is that I made Expr -> ColumnExpr to make the interface function a little more clear that it's for use with columns specifically.

I just wanted to call this out just in case: while I was going back through this code, I found a couple places in expand.go that special case based on particular engine name already. e.g.

https://github.com/sqlc-dev/sqlc/blob/c62c6e78843ada0f137960d455baf638390b5c7b/internal/compiler/expand.go/#L60-L68

There is an argument to be made that a new interface makes the code a little more indirect, and given it's already existing convention in a couple places, it might be okay to just make an if statement instead. Either way though, the selector work is already done so I'm good with either.

cname = "json(" + cname + ")"
}
cols = append(cols, cname)
}
}
Expand Down
32 changes: 32 additions & 0 deletions internal/endtoend/testdata/jsonb/pgx/go/db.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

12 changes: 12 additions & 0 deletions internal/endtoend/testdata/jsonb/pgx/go/models.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

50 changes: 50 additions & 0 deletions internal/endtoend/testdata/jsonb/pgx/go/query.sql.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

15 changes: 15 additions & 0 deletions internal/endtoend/testdata/jsonb/pgx/query.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,15 @@
-- name: InsertFoo :exec
INSERT INTO foo (
a,
b,
c,
d
) VALUES (
@a,
@b,
@c,
@d
) RETURNING *;

-- name: SelectFoo :exec
SELECT * FROM foo;
7 changes: 7 additions & 0 deletions internal/endtoend/testdata/jsonb/pgx/schema.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,7 @@
CREATE TABLE foo (
a json not null,
b jsonb not null,
c json,
d jsonb
);

13 changes: 13 additions & 0 deletions internal/endtoend/testdata/jsonb/pgx/sqlc.json
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,13 @@
{
"version": "1",
"packages": [
{
"path": "go",
"engine": "postgresql",
"sql_package": "pgx/v5",
"name": "querytest",
"schema": "schema.sql",
"queries": "query.sql"
}
]
}
31 changes: 31 additions & 0 deletions internal/endtoend/testdata/jsonb/sqlite/go/db.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

12 changes: 12 additions & 0 deletions internal/endtoend/testdata/jsonb/sqlite/go/models.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

50 changes: 50 additions & 0 deletions internal/endtoend/testdata/jsonb/sqlite/go/query.sql.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

15 changes: 15 additions & 0 deletions internal/endtoend/testdata/jsonb/sqlite/query.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,15 @@
-- name: InsertFoo :exec
INSERT INTO foo (
a,
b,
c,
d
) VALUES (
@a,
@b,
@c,
@d
) RETURNING *;

-- name: SelectFoo :exec
SELECT * FROM foo;
7 changes: 7 additions & 0 deletions internal/endtoend/testdata/jsonb/sqlite/schema.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,7 @@
CREATE TABLE foo (
a json not null,
b jsonb not null,
c json,
d jsonb
);

12 changes: 12 additions & 0 deletions internal/endtoend/testdata/jsonb/sqlite/sqlc.json
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,12 @@
{
"version": "1",
"packages": [
{
"path": "go",
"engine": "sqlite",
"name": "querytest",
"schema": "schema.sql",
"queries": "query.sql"
}
]
}
Loading