sqlc-dev · brandur · May 16, 2025 · May 25, 2025 · May 25, 2025 · May 25, 2025
diff --git 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
@@ -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
@@ -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
@@ -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
+}
diff --git a/internal/compiler/selector_test.go 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
diff --git a/internal/endtoend/testdata/jsonb/pgx/go/models.go b/internal/endtoend/testdata/jsonb/pgx/go/models.go
diff --git a/internal/endtoend/testdata/jsonb/pgx/go/query.sql.go b/internal/endtoend/testdata/jsonb/pgx/go/query.sql.go
diff --git a/internal/endtoend/testdata/jsonb/pgx/query.sql 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
@@ -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
@@ -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
diff --git a/internal/endtoend/testdata/jsonb/sqlite/go/models.go b/internal/endtoend/testdata/jsonb/sqlite/go/models.go
diff --git a/internal/endtoend/testdata/jsonb/sqlite/go/query.sql.go b/internal/endtoend/testdata/jsonb/sqlite/go/query.sql.go
diff --git a/internal/endtoend/testdata/jsonb/sqlite/query.sql 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
@@ -0,0 +1,7 @@
+CREATE TABLE foo (
+    a json not null,
+    b jsonb not null,
+    c json,
+    d jsonb
+);
+