Repository: sqlc-dev/sqlc
Pull request #3968 (2025-05-16 06:11:38)

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

brandur: @kyleconroy I'm sure this could use some work, but I wanted to pitch something to prove out the concept. Thoughts?

Patch:
diff --git a/Dockerfile b/Dockerfile
index 564c04c7b..352c3f9dd 100644
--- a/Dockerfile
+++ b/Dockerfile
@@ -1,5 +1,5 @@
 # STEP 1: Build sqlc
-FROM golang:1.24.2 AS builder
+FROM golang:1.24.4 AS builder
 
 COPY . /workspace
 WORKDIR /workspace
diff --git a/docker-compose.yml b/docker-compose.yml
index 1173e8a14..f318d1ed9 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -1,7 +1,7 @@
 version: "3.8"
 services:
   mysql:
-    image: "mysql/mysql-server:8.0"
+    image: "mysql:9"
     ports:
       - "3306:3306"
     restart: always
diff --git a/internal/codegen/golang/sqlite_type.go b/internal/codegen/golang/sqlite_type.go
index 92c13557f..8a22aaa26 100644
--- a/internal/codegen/golang/sqlite_type.go
+++ b/internal/codegen/golang/sqlite_type.go
@@ -56,6 +56,9 @@ func sqliteType(req *plugin.GenerateRequest, options *opts.Options, col *plugin.
 		}
 		return "sql.NullTime"
 
+	case "json", "jsonb":
+		return "json.RawMessage"
+
 	case "any":
 		return "interface{}"
 
diff --git a/internal/compiler/engine.go b/internal/compiler/engine.go
index d263637d9..f742bfd99 100644
--- a/internal/compiler/engine.go
+++ b/internal/compiler/engine.go
@@ -23,6 +23,7 @@ type Compiler struct {
 	result   *Result
 	analyzer analyzer.Analyzer
 	client   dbmanager.Client
+	selector selector
 
 	schema []string
 }
@@ -39,12 +40,15 @@ func NewCompiler(conf config.SQL, combo config.CombinedSettings) (*Compiler, err
 	case config.EngineSQLite:
 		c.parser = sqlite.NewParser()
 		c.catalog = sqlite.NewCatalog()
+		c.selector = newSQLiteSelector()
 	case config.EngineMySQL:
 		c.parser = dolphin.NewParser()
 		c.catalog = dolphin.NewCatalog()
+		c.selector = newDefaultSelector()
 	case config.EnginePostgreSQL:
 		c.parser = postgresql.NewParser()
 		c.catalog = postgresql.NewCatalog()
+		c.selector = newDefaultSelector()
 		if conf.Database != nil {
 			if conf.Analyzer.Database == nil || *conf.Analyzer.Database {
 				c.analyzer = analyzer.Cached(
diff --git a/internal/compiler/expand.go b/internal/compiler/expand.go
index 60e654b69..c60b7618b 100644
--- a/internal/compiler/expand.go
+++ b/internal/compiler/expand.go
@@ -149,6 +149,11 @@ func (c *Compiler) expandStmt(qc *QueryCatalog, raw *ast.RawStmt, node ast.Node)
 				if counts[cname] > 1 {
 					cname = tableName + "." + cname
 				}
+
+				// This is important for SQLite in particular which needs to
+				// wrap jsonb column values with `json(colname)` so they're in a
+				// publicly usable format (i.e. not jsonb).
+				cname = c.selector.ColumnExpr(cname, column)
 				cols = append(cols, cname)
 			}
 		}
diff --git a/internal/compiler/selector.go b/internal/compiler/selector.go
new file mode 100644
index 000000000..04d118ff9
--- /dev/null
+++ b/internal/compiler/selector.go
@@ -0,0 +1,46 @@
+package compiler
+
+// selector is an interface used by a compiler for generating expressions for
+// output columns in a `SELECT ...` or `RETURNING ...` statement.
+//
+// This interface is exclusively needed at the moment for SQLite, which must
+// wrap output `jsonb` columns with a `json(column_name)` invocation so that a
+// publicly consumable format (i.e. not jsonb) is returned.
+type selector interface {
+	// ColumnExpr generates output to be used in a `SELECT ...` or `RETURNING
+	// ...` statement based on input column name and metadata.
+	ColumnExpr(name string, column *Column) string
+}
+
+// defaultSelector is a selector implementation that does the simpliest possible
+// pass through when generating column expressions. Its use is suitable for all
+// database engines not requiring additional customization.
+type defaultSelector struct{}
+
+func newDefaultSelector() *defaultSelector {
+	return &defaultSelector{}
+}
+
+func (s *defaultSelector) ColumnExpr(name string, column *Column) string {
+	return name
+}
+
+type sqliteSelector struct{}
+
+func newSQLiteSelector() *sqliteSelector {
+	return &sqliteSelector{}
+}
+
+func (s *sqliteSelector) ColumnExpr(name string, column *Column) string {
+	// 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 values
+	// in `json(col)` to coerce the internal binary format to JSON parsable by
+	// the user-space application.
+	if column.DataType == "jsonb" {
+		return "json(" + name + ")"
+	}
+	return name
+}


Test patch:
diff --git a/internal/compiler/selector_test.go b/internal/compiler/selector_test.go
new file mode 100644
index 000000000..e460dd281
--- /dev/null
+++ b/internal/compiler/selector_test.go
@@ -0,0 +1,35 @@
+package compiler
+
+import "testing"
+
+func TestSelector(t *testing.T) {
+	t.Parallel()
+
+	selectorExpectColumnExpr := func(t *testing.T, selector selector, expected, name string, column *Column) {
+		if actual := selector.ColumnExpr(name, column); expected != actual {
+			t.Errorf("Expected %v, got %v for data type %v", expected, actual, column.DataType)
+		}
+	}
+
+	t.Run("DefaultSelectorColumnExpr", func(t *testing.T) {
+		t.Parallel()
+
+		selector := newDefaultSelector()
+
+		selectorExpectColumnExpr(t, selector, "my_column", "my_column", &Column{DataType: "integer"})
+		selectorExpectColumnExpr(t, selector, "my_column", "my_column", &Column{DataType: "json"})
+		selectorExpectColumnExpr(t, selector, "my_column", "my_column", &Column{DataType: "jsonb"})
+		selectorExpectColumnExpr(t, selector, "my_column", "my_column", &Column{DataType: "text"})
+	})
+
+	t.Run("SQLiteSelectorColumnExpr", func(t *testing.T) {
+		t.Parallel()
+
+		selector := newSQLiteSelector()
+
+		selectorExpectColumnExpr(t, selector, "my_column", "my_column", &Column{DataType: "integer"})
+		selectorExpectColumnExpr(t, selector, "my_column", "my_column", &Column{DataType: "json"})
+		selectorExpectColumnExpr(t, selector, "json(my_column)", "my_column", &Column{DataType: "jsonb"})
+		selectorExpectColumnExpr(t, selector, "my_column", "my_column", &Column{DataType: "text"})
+	})
+}
diff --git a/internal/endtoend/testdata/jsonb/pgx/go/db.go b/internal/endtoend/testdata/jsonb/pgx/go/db.go
new file mode 100644
index 000000000..e83d6a948
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/pgx/go/db.go
@@ -0,0 +1,32 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package querytest
+
+import (
+	"context"
+
+	"github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgconn"
+)
+
+type DBTX interface {
+	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
+	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
+	QueryRow(context.Context, string, ...interface{}) pgx.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx pgx.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/internal/endtoend/testdata/jsonb/pgx/go/models.go b/internal/endtoend/testdata/jsonb/pgx/go/models.go
new file mode 100644
index 000000000..1932ce7f5
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/pgx/go/models.go
@@ -0,0 +1,12 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package querytest
+
+type Foo struct {
+	A []byte
+	B []byte
+	C []byte
+	D []byte
+}
diff --git a/internal/endtoend/testdata/jsonb/pgx/go/query.sql.go b/internal/endtoend/testdata/jsonb/pgx/go/query.sql.go
new file mode 100644
index 000000000..1d7532f6e
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/pgx/go/query.sql.go
@@ -0,0 +1,50 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+// source: query.sql
+
+package querytest
+
+import (
+	"context"
+)
+
+const insertFoo = `-- name: InsertFoo :exec
+INSERT INTO foo (
+    a,
+    b,
+    c,
+    d
+) VALUES (
+    $1,
+    $2,
+    $3,
+    $4
+) RETURNING a, b, c, d
+`
+
+type InsertFooParams struct {
+	A []byte
+	B []byte
+	C []byte
+	D []byte
+}
+
+func (q *Queries) InsertFoo(ctx context.Context, arg InsertFooParams) error {
+	_, err := q.db.Exec(ctx, insertFoo,
+		arg.A,
+		arg.B,
+		arg.C,
+		arg.D,
+	)
+	return err
+}
+
+const selectFoo = `-- name: SelectFoo :exec
+SELECT a, b, c, d FROM foo
+`
+
+func (q *Queries) SelectFoo(ctx context.Context) error {
+	_, err := q.db.Exec(ctx, selectFoo)
+	return err
+}
diff --git a/internal/endtoend/testdata/jsonb/pgx/query.sql b/internal/endtoend/testdata/jsonb/pgx/query.sql
new file mode 100644
index 000000000..6959bd1a7
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/pgx/query.sql
@@ -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;
diff --git a/internal/endtoend/testdata/jsonb/pgx/schema.sql b/internal/endtoend/testdata/jsonb/pgx/schema.sql
new file mode 100644
index 000000000..6b4a1bb0f
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/pgx/schema.sql
@@ -0,0 +1,7 @@
+CREATE TABLE foo (
+    a json not null,
+    b jsonb not null,
+    c json,
+    d jsonb
+);
+
diff --git a/internal/endtoend/testdata/jsonb/pgx/sqlc.json b/internal/endtoend/testdata/jsonb/pgx/sqlc.json
new file mode 100644
index 000000000..32ede0715
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/pgx/sqlc.json
@@ -0,0 +1,13 @@
+{
+  "version": "1",
+  "packages": [
+    {
+      "path": "go",
+      "engine": "postgresql",
+      "sql_package": "pgx/v5",
+      "name": "querytest",
+      "schema": "schema.sql",
+      "queries": "query.sql"
+    }
+  ]
+}
diff --git a/internal/endtoend/testdata/jsonb/sqlite/go/db.go b/internal/endtoend/testdata/jsonb/sqlite/go/db.go
new file mode 100644
index 000000000..a92cd6e8e
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/sqlite/go/db.go
@@ -0,0 +1,31 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package querytest
+
+import (
+	"context"
+	"database/sql"
+)
+
+type DBTX interface {
+	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
+	PrepareContext(context.Context, string) (*sql.Stmt, error)
+	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
+	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx *sql.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/internal/endtoend/testdata/jsonb/sqlite/go/models.go b/internal/endtoend/testdata/jsonb/sqlite/go/models.go
new file mode 100644
index 000000000..7c4f7cd8c
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/sqlite/go/models.go
@@ -0,0 +1,16 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package querytest
+
+import (
+	"encoding/json"
+)
+
+type Foo struct {
+	A json.RawMessage
+	B json.RawMessage
+	C json.RawMessage
+	D json.RawMessage
+}
diff --git a/internal/endtoend/testdata/jsonb/sqlite/go/query.sql.go b/internal/endtoend/testdata/jsonb/sqlite/go/query.sql.go
new file mode 100644
index 000000000..9c0858a9c
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/sqlite/go/query.sql.go
@@ -0,0 +1,51 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+// source: query.sql
+
+package querytest
+
+import (
+	"context"
+	"encoding/json"
+)
+
+const insertFoo = `-- name: InsertFoo :exec
+INSERT INTO foo (
+    a,
+    b,
+    c,
+    d
+) VALUES (
+    ?1,
+    ?2,
+    ?3,
+    ?4
+) RETURNING a, json(b), c, json(d)
+`
+
+type InsertFooParams struct {
+	A json.RawMessage
+	B json.RawMessage
+	C json.RawMessage
+	D json.RawMessage
+}
+
+func (q *Queries) InsertFoo(ctx context.Context, arg InsertFooParams) error {
+	_, err := q.db.ExecContext(ctx, insertFoo,
+		arg.A,
+		arg.B,
+		arg.C,
+		arg.D,
+	)
+	return err
+}
+
+const selectFoo = `-- name: SelectFoo :exec
+SELECT a, json(b), c, json(d) FROM foo
+`
+
+func (q *Queries) SelectFoo(ctx context.Context) error {
+	_, err := q.db.ExecContext(ctx, selectFoo)
+	return err
+}
diff --git a/internal/endtoend/testdata/jsonb/sqlite/query.sql b/internal/endtoend/testdata/jsonb/sqlite/query.sql
new file mode 100644
index 000000000..6959bd1a7
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/sqlite/query.sql
@@ -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;
diff --git a/internal/endtoend/testdata/jsonb/sqlite/schema.sql b/internal/endtoend/testdata/jsonb/sqlite/schema.sql
new file mode 100644
index 000000000..6b4a1bb0f
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/sqlite/schema.sql
@@ -0,0 +1,7 @@
+CREATE TABLE foo (
+    a json not null,
+    b jsonb not null,
+    c json,
+    d jsonb
+);
+
diff --git a/internal/endtoend/testdata/jsonb/sqlite/sqlc.json b/internal/endtoend/testdata/jsonb/sqlite/sqlc.json
new file mode 100644
index 000000000..cd66df063
--- /dev/null
+++ b/internal/endtoend/testdata/jsonb/sqlite/sqlc.json
@@ -0,0 +1,12 @@
+{
+  "version": "1",
+  "packages": [
+    {
+      "path": "go",
+      "engine": "sqlite",
+      "name": "querytest",
+      "schema": "schema.sql",
+      "queries": "query.sql"
+    }
+  ]
+}