← back to Cli Printing Press
fix(cli): always quote SQL identifiers emitted by safeSQLName (#1228)
c05d3bc75d9f098e9a99d6b5cd22ce07cf4e220c · 2026-05-12 12:17:34 -0700 · Trevin Chow
* fix(cli): always quote SQL identifiers emitted by safeSQLName
Generator's safeSQLName kept a hand-rolled allowlist of SQLite reserved
words that omitted strict-reserved keywords like add, to, and from. Any
spec whose sub-resource leaf snake-cased to one of those words produced
a CREATE TABLE statement that SQLite rejected at parse time, breaking
every store-backed command in the printed CLI on first run.
Replace the allowlist + bare-identifier check with unconditional
double-quoting. SQLite accepts any identifier when quoted; quoting is
harmless for ordinary names; and the dialect evolves over time in ways
a hand-maintained allowlist cannot track. Drop the now-dead
sqlReservedWords map and isBareSQLiteIdentifier helper. Quote the
remaining bare id in the upsert template's ON CONFLICT clause for
consistency across the SQL statement.
Consolidate the strict and non-strict reserved-word regression tests
into a single fixture that exercises three classes in one compile
cycle: typed table name (strict and non-strict) and a strict-reserved
column identifier. Add cheap pre-checks on the emitted store.go so the
heavy compile/test gates have bug-shaped SQL to catch — verified by
stashing safeSQLName and confirming the test fails with the original
`near "add": syntax error`.
Golden fixture sync-walker-golden updates accordingly: every
generator-emitted identifier in CREATE TABLE, CREATE INDEX, INSERT
INTO, UPDATE SET, and ON CONFLICT is now quoted.
Closes #1220
* docs(cli): document safeSQLName always-quote learning
Captures the bug, the rejected allowlist-extension lead, and the inert
TDD fixture that three reviewers caught. Prevention guidance covers
the always-quote-over-allowlist policy for SQL identifier emission and
the testing rule that integration tests must verify the bug-shaped SQL
is actually emitted.
* fix(cli): route FTS5 search query identifiers through safeName
Search<Resource> wrapped the main table via safeName but referenced
the FTS5 virtual table as bare <name>_fts in the JOIN and WHERE MATCH
clauses, leaving the only remaining quoting asymmetry in store.go.
Functionally safe (the _fts suffix never produces a strict-reserved
keyword), but the always-quote contract should hold uniformly across
every emitted SQL statement.
Files touched
A docs/solutions/logic-errors/safesqlname-allowlist-misses-strict-reserved-keywords-2026-05-12.mdM internal/generator/generator_test.goM internal/generator/schema_builder.goM internal/generator/schema_builder_test.goM internal/generator/templates/store.go.tmplM testdata/golden/expected/generate-sync-walker-api/sync-walker-golden/internal/store/store.go
Diff
commit c05d3bc75d9f098e9a99d6b5cd22ce07cf4e220c
Author: Trevin Chow <trevin@trevinchow.com>
Date: Tue May 12 12:17:34 2026 -0700
fix(cli): always quote SQL identifiers emitted by safeSQLName (#1228)
* fix(cli): always quote SQL identifiers emitted by safeSQLName
Generator's safeSQLName kept a hand-rolled allowlist of SQLite reserved
words that omitted strict-reserved keywords like add, to, and from. Any
spec whose sub-resource leaf snake-cased to one of those words produced
a CREATE TABLE statement that SQLite rejected at parse time, breaking
every store-backed command in the printed CLI on first run.
Replace the allowlist + bare-identifier check with unconditional
double-quoting. SQLite accepts any identifier when quoted; quoting is
harmless for ordinary names; and the dialect evolves over time in ways
a hand-maintained allowlist cannot track. Drop the now-dead
sqlReservedWords map and isBareSQLiteIdentifier helper. Quote the
remaining bare id in the upsert template's ON CONFLICT clause for
consistency across the SQL statement.
Consolidate the strict and non-strict reserved-word regression tests
into a single fixture that exercises three classes in one compile
cycle: typed table name (strict and non-strict) and a strict-reserved
column identifier. Add cheap pre-checks on the emitted store.go so the
heavy compile/test gates have bug-shaped SQL to catch — verified by
stashing safeSQLName and confirming the test fails with the original
`near "add": syntax error`.
Golden fixture sync-walker-golden updates accordingly: every
generator-emitted identifier in CREATE TABLE, CREATE INDEX, INSERT
INTO, UPDATE SET, and ON CONFLICT is now quoted.
Closes #1220
* docs(cli): document safeSQLName always-quote learning
Captures the bug, the rejected allowlist-extension lead, and the inert
TDD fixture that three reviewers caught. Prevention guidance covers
the always-quote-over-allowlist policy for SQL identifier emission and
the testing rule that integration tests must verify the bug-shaped SQL
is actually emitted.
* fix(cli): route FTS5 search query identifiers through safeName
Search<Resource> wrapped the main table via safeName but referenced
the FTS5 virtual table as bare <name>_fts in the JOIN and WHERE MATCH
clauses, leaving the only remaining quoting asymmetry in store.go.
Functionally safe (the _fts suffix never produces a strict-reserved
keyword), but the always-quote contract should hold uniformly across
every emitted SQL statement.
---
...t-misses-strict-reserved-keywords-2026-05-12.md | 93 ++++++++++++++++
internal/generator/generator_test.go | 123 ++++++++++++++-------
internal/generator/schema_builder.go | 51 ++-------
internal/generator/schema_builder_test.go | 9 +-
internal/generator/templates/store.go.tmpl | 6 +-
.../sync-walker-golden/internal/store/store.go | 16 +--
6 files changed, 199 insertions(+), 99 deletions(-)
diff --git a/docs/solutions/logic-errors/safesqlname-allowlist-misses-strict-reserved-keywords-2026-05-12.md b/docs/solutions/logic-errors/safesqlname-allowlist-misses-strict-reserved-keywords-2026-05-12.md
new file mode 100644
index 00000000..8cddc814
--- /dev/null
+++ b/docs/solutions/logic-errors/safesqlname-allowlist-misses-strict-reserved-keywords-2026-05-12.md
@@ -0,0 +1,93 @@
+---
+title: "safeSQLName hand-rolled reserved-word allowlist missed SQLite strict-reserved keywords"
+date: 2026-05-12
+category: logic-errors
+module: internal/generator/schema_builder
+problem_type: logic_error
+component: tooling
+severity: high
+symptoms:
+ - "Generated CLI fails at first store open with `running migrations: migration failed: SQL logic error: near \"add\": syntax error (1)`"
+ - "Every store-backed command (sync, search, analytics, transcendence) breaks because migrate() runs on every open"
+ - "Cross-API blast radius observed across 11 CLIs in the public library carrying unquoted reserved-word table names (cancel, track, errors, status, options, services)"
+ - "Bug surfaces only for SQLite strict-reserved keywords (add, to, from); non-strict reserved words shipped fragile-but-functional"
+root_cause: logic_error
+resolution_type: code_fix
+tags:
+ - sqlite
+ - sql-identifier-emission
+ - generator-templates
+ - schema-builder
+ - reserved-keywords
+ - ddl-quoting
+related_components:
+ - internal/generator/templates/store.go.tmpl
+---
+
+## Problem
+
+The generator's `safeSQLName` helper in `internal/generator/schema_builder.go` quoted SQL identifiers only when their lowercase form matched a hand-rolled allowlist of ~50 reserved words. The list omitted SQLite's strict-reserved keywords (`add`, `to`, `from`, and others) — words SQLite refuses to parse unquoted in `CREATE TABLE` context. Any printed CLI whose spec produced a sub-resource leaf snake-casing to one of those words shipped a `CREATE TABLE IF NOT EXISTS add (...)` statement that failed at parse time the first time the binary opened its store.
+
+## Symptoms
+
+- ShipStation printed CLI failed first run with `migration failed: SQL logic error: near "add": syntax error (1)` — `add` came from `/v2/batches/{id}/add`.
+- Public-library scan against `~/printing-press/.publish-repo-*/library/*/*/internal/store/store.go` found 11 CLIs already shipping with unquoted reserved-word tables (`cancel`, `track`, `errors`, `status`, `options`, `services`). They happened to work because SQLite is permissive about *non*-strict-reserved keywords in `CREATE TABLE` context — but the same generator path is one keyword away from breaking on every future regen.
+- The bug surfaces inside `migrate()`, so every store-backed command in the printed CLI breaks at once.
+
+## What Didn't Work
+
+- **Extending the allowlist with the observed missing keywords.** Adds 13 keywords today (`add`, `cancel`, `track`, etc.); doesn't prevent the next missing keyword from breaking another CLI. SQLite's keyword list grows across versions; a hand-maintained allowlist diverges from the dialect over time. Rejected.
+- **Initial TDD integration test using `add` as a top-level resource.** The test added an `add` resource with one list endpoint and no `Response.Item`. Three independent code reviewers caught that the fixture didn't actually exercise the bug: `computeDataGravity` scored at 1 (one endpoint, zero response fields), `hasTypedTable` returned false, and the typed `CREATE TABLE` for `add` was never emitted into the migrations slice. The test passed against the unpatched generator. The lesson: any test that hinges on a typed-table emission must push the resource over the gravity threshold (response type with fields, or a sub-resource that auto-gains a parent_id column — and beware that sub-resources hit a separate FK-NOT-NULL upsert_batch_test bug).
+
+## Solution
+
+Replace the allowlist + bare-identifier check with unconditional double-quoting. SQLite accepts any identifier when quoted, and quoting is a no-op for ordinary names at runtime — `sqlite_master` strips the surrounding quotes when storing the name, so all parameterized lookups by name continue to match.
+
+Before:
+
+```go
+var sqlReservedWords = map[string]bool{
+ "check": true, "default": true, "from": true, "to": true,
+ // ...about 50 entries, none of which include "add"
+}
+
+func safeSQLName(name string) string {
+ if sqlReservedWords[strings.ToLower(name)] || !isBareSQLiteIdentifier(name) {
+ return `"` + strings.ReplaceAll(name, `"`, `""`) + `"`
+ }
+ return name
+}
+```
+
+After:
+
+```go
+// safeSQLName returns an identifier that is safe to use in SQLite DDL.
+// Always double-quotes the name, escaping any embedded quote. Quoting is
+// harmless for non-keyword identifiers and is the only way to safely emit
+// SQLite strict-reserved keywords like "add", "to", and "from" in CREATE
+// TABLE / CREATE INDEX context, where they otherwise fail at parse time.
+// Maintaining a hand-rolled keyword allowlist diverges from SQLite over
+// time; quoting unconditionally is the durable contract.
+func safeSQLName(name string) string {
+ return `"` + strings.ReplaceAll(name, `"`, `""`) + `"`
+}
+```
+
+The change also dropped `sqlReservedWords` and `isBareSQLiteIdentifier` entirely. Companion fix in `internal/generator/templates/store.go.tmpl`: the upsert template's `ON CONFLICT(id)` was the only remaining unquoted identifier in the SQL block; routing `id` through `safeName` brings every identifier in the statement under the same quoting policy.
+
+## Why This Works
+
+- SQLite identifies tables by their stored name in `sqlite_master`. Quoted DDL stores the name without surrounding quotes; runtime queries like `tableExists` parameterize the name with bare Go strings, and the lookup still matches.
+- `CREATE TABLE IF NOT EXISTS "add"` is unambiguously a table identifier, never a parse-time syntax error.
+- Non-keyword identifiers (`leagues`, `id`, `data`) behave identically when quoted — SQLite treats `"leagues"` and `leagues` as the same column at lookup time.
+- `IF NOT EXISTS` gates compare the stored unquoted name, so re-running migration against existing user databases is a clean no-op. No data migration needed when an existing CLI is regenerated under the new quoting.
+
+## Prevention
+
+- **For SQL identifier emission, prefer always-quote over hand-rolled allowlists.** The dialect evolves; allowlists don't. Quoting is harmless for non-keyword names and the only safe form for keyword names.
+- **Pin the always-quote contract in a unit test.** A table-driven test asserting the function returns `"name"` for every input (plain, keyword, leading-digit, punctuation, embedded-quote) prevents future regressions from someone re-introducing a "skip quoting for plain identifiers" optimization.
+- **Integration tests for generator-emission bugs must verify the bug-shaped SQL is actually emitted.** Add cheap `assert.Contains(store, "CREATE TABLE IF NOT EXISTS \"add\"")` pre-checks before running heavy compile/test gates. A spec fixture that doesn't cross the gravity threshold silently produces an inert test that passes against the unpatched generator — and integration tests are expensive to run, so the inert-fixture problem is hard to notice during normal development cycles.
+- **Verify regression-catching by stashing the fix and re-running.** A test that fails when the fix is reverted is a real regression guard. A test that still passes when the fix is reverted is decoration.
+
+Cross-reference: `docs/solutions/logic-errors/store-columns-sourced-from-request-params-instead-of-response-2026-05-08.md` is an adjacent learning in the same `internal/generator/schema_builder` module — its lesson that `schema_builder` defects surface only via direct SQLite inspection (never at the CLI surface) applies here too, and motivates the pre-check assertion pattern recommended above.
diff --git a/internal/generator/generator_test.go b/internal/generator/generator_test.go
index a83cba28..fb290497 100644
--- a/internal/generator/generator_test.go
+++ b/internal/generator/generator_test.go
@@ -3059,7 +3059,8 @@ func TestGenerateStoreSubResourceUpsertBindingOrder(t *testing.T) {
// buildSubResourceTable inserts the FK column between id and data, so
// the column declaration order is (id, domains_id, data, synced_at).
- assert.Contains(t, src, "INSERT INTO verify (id, domains_id, data, synced_at)",
+ // safeSQLName quote-wraps every identifier in emitted SQL.
+ assert.Contains(t, src, `INSERT INTO "verify" ("id", "domains_id", "data", "synced_at")`,
"sub-resource table should declare FK column between id and data")
// The argument bindings must follow that same order.
@@ -6688,17 +6689,46 @@ func TestGenerateDependentSyncCompiles(t *testing.T) {
runGoCommand(t, outputDir, "test", "./internal/store")
}
-func TestGenerateDependentSyncReservedWordCompiles(t *testing.T) {
- t.Parallel()
-
- // A spec whose dependent-resource table name is a SQL reserved word
- // (e.g. references, trigger, view, order) must still produce a CLI
- // that builds and whose store tests pass. The store template's
- // backfillColumns slice and the upgrade-path test's t.Fatalf format
- // string both interpolate the table name into Go double-quoted
- // strings; safeSQLName quote-wraps reserved words for SQL contexts,
- // so applying it in a Go-string context would emit invalid Go for
- // any reserved-word resource. Regression for issue #272 follow-up.
+func TestGenerateReservedWordResourceTableNamesCompileAndMigrate(t *testing.T) {
+ t.Parallel()
+
+ // A spec whose resource or response field snake_cases to a SQL
+ // reserved word must still produce a CLI that builds and whose store
+ // migration succeeds. The single fixture exercises three regression
+ // classes in one compile cycle:
+ //
+ // - "references" as a typed top-level resource: non-strict keyword.
+ // Surfaces as a Go compile failure if safeSQLName ever leaks into
+ // a Go-string context (backfillColumns slice, upgrade-path
+ // t.Fatalf format string).
+ // - "add" as a typed top-level resource: strict-reserved keyword.
+ // A typed CREATE TABLE for `add` is emitted into the migrations
+ // slice. SQLite rejects that unquoted with `near "add": syntax
+ // error`, so migrate() — called from every test in the generated
+ // store package — fails at parse time without quoting.
+ // - "from" as a response field on the shared type: strict-reserved
+ // keyword in column position. The columnNames helper feeds INSERT
+ // INTO and the CREATE TABLE column list; both fail at parse time
+ // unless the column identifier is quoted.
+ //
+ // Top-level resources are used deliberately. Sub-resources would emit
+ // CREATE TABLE statements too, but the generated upsert_batch_test
+ // has a NOT NULL constraint bug on FK columns that masks the keyword
+ // regression with a different failure.
+ mkField := func(name string) spec.TypeField {
+ return spec.TypeField{Name: name, Type: "string"}
+ }
+ endpoints := func(path string) map[string]spec.Endpoint {
+ return map[string]spec.Endpoint{
+ "list": {
+ Method: "GET",
+ Path: path,
+ Description: "List items",
+ Response: spec.ResponseDef{Type: "array", Item: "Item"},
+ Pagination: &spec.Pagination{CursorParam: "after", LimitParam: "limit"},
+ },
+ }
+ }
apiSpec := &spec.APISpec{
Name: "docstore",
Version: "0.1.0",
@@ -6714,30 +6744,20 @@ func TestGenerateDependentSyncReservedWordCompiles(t *testing.T) {
Path: "~/.config/docstore-pp-cli/config.toml",
},
Resources: map[string]spec.Resource{
- "documents": {
- Description: "Manage documents",
- Endpoints: map[string]spec.Endpoint{
- "list": {
- Method: "GET",
- Path: "/documents",
- Description: "List documents",
- Response: spec.ResponseDef{Type: "array"},
- Pagination: &spec.Pagination{CursorParam: "after", LimitParam: "limit"},
- },
- },
- },
- // "references" snake_cases to "references" — a SQL reserved
- // word per internal/generator/schema_builder.go:322.
- "references": {
- Description: "Manage references attached to a document",
- Endpoints: map[string]spec.Endpoint{
- "list": {
- Method: "GET",
- Path: "/documents/{documentId}/references",
- Description: "List references for a document",
- Response: spec.ResponseDef{Type: "array"},
- Pagination: &spec.Pagination{CursorParam: "after", LimitParam: "limit"},
- },
+ "documents": {Description: "Documents", Endpoints: endpoints("/documents")},
+ "references": {Description: "References", Endpoints: endpoints("/references")},
+ "add": {Description: "Add operations", Endpoints: endpoints("/add")},
+ },
+ Types: map[string]spec.TypeDef{
+ "Item": {
+ Fields: []spec.TypeField{
+ mkField("id"),
+ mkField("title"),
+ mkField("body"),
+ // `from` is a SQLite strict-reserved keyword,
+ // exercising the column-identifier path through
+ // columnNames / updateSet.
+ mkField("from"),
},
},
},
@@ -6747,8 +6767,28 @@ func TestGenerateDependentSyncReservedWordCompiles(t *testing.T) {
gen := New(apiSpec, outputDir)
require.NoError(t, gen.Generate())
- // The generated store.go must compile (no embedded "" from safeName
- // quote-wrapping) and the per-table upgrade test must run cleanly.
+ storeSrc, err := os.ReadFile(filepath.Join(outputDir, "internal", "store", "store.go"))
+ require.NoError(t, err)
+ store := string(storeSrc)
+
+ // Cheap pre-check: the bug-shaped strings must appear in the emitted
+ // SQL so the compile-and-migrate gates below have something to catch.
+ // A previous iteration of this test left `add` without a typed column
+ // set — gravity stayed below the typed-table threshold and no CREATE
+ // TABLE for `add` was emitted, leaving the regression unreached.
+ assert.Contains(t, store, `CREATE TABLE IF NOT EXISTS "add"`,
+ "strict-reserved table name must emit a quoted CREATE TABLE")
+ assert.Contains(t, store, `CREATE TABLE IF NOT EXISTS "references"`,
+ "non-strict-reserved table name must emit a quoted CREATE TABLE")
+ assert.Contains(t, store, `"from" TEXT`,
+ "strict-reserved column identifier must be quoted in the CREATE TABLE column list")
+ assert.Contains(t, store, `INSERT INTO "add"`,
+ "strict-reserved table name must be quoted in the upsert INSERT")
+
+ // The generated store.go must compile (no embedded "" leaking into a
+ // Go-string context) and the per-table migration must run cleanly —
+ // the SQLite parse failure surfaces inside the store tests' migrate()
+ // call.
runGoCommand(t, outputDir, "mod", "tidy")
runGoCommand(t, outputDir, "build", "./...")
runGoCommand(t, outputDir, "test", "./internal/store")
@@ -9356,8 +9396,11 @@ func TestStoreSkipsDeadTablesForResourcesWithoutTypedUpsert(t *testing.T) {
// Pin the exact CREATE TABLE set: only resources, sync_state (always
// emitted), and items (has typed columns) survive. No demo_auth, no
- // renamed-with-suffix dead table, nothing else snuck in.
- createRe := regexp.MustCompile("`CREATE TABLE IF NOT EXISTS (\\w+) \\(")
+ // renamed-with-suffix dead table, nothing else snuck in. The framework
+ // tables (resources, sync_state) are hand-written in the template
+ // without safeSQLName, so the regex accepts both quoted and unquoted
+ // forms.
+ createRe := regexp.MustCompile("`CREATE TABLE IF NOT EXISTS \"?(\\w+)\"? \\(")
gotTables := map[string]bool{}
for _, m := range createRe.FindAllStringSubmatch(store, -1) {
gotTables[m[1]] = true
diff --git a/internal/generator/schema_builder.go b/internal/generator/schema_builder.go
index 28b005db..b3b38806 100644
--- a/internal/generator/schema_builder.go
+++ b/internal/generator/schema_builder.go
@@ -394,52 +394,15 @@ func buildSubResourceTable(name string, r spec.Resource, parentTable string) Tab
}
}
-// sqlReservedWords is the set of SQL keywords that must be quoted when used
-// as table or column names. Covers SQLite reserved words plus common SQL
-// keywords that appear as API resource or field names.
-var sqlReservedWords = map[string]bool{
- "check": true, "default": true, "from": true, "to": true,
- "order": true, "group": true, "select": true, "where": true,
- "table": true, "column": true, "index": true, "key": true,
- "values": true, "references": true, "create": true, "drop": true,
- "insert": true, "update": true, "delete": true, "set": true,
- "join": true, "on": true, "in": true, "not": true, "null": true,
- "primary": true, "foreign": true, "unique": true, "like": true,
- "between": true, "exists": true, "having": true, "limit": true,
- "offset": true, "union": true, "except": true, "case": true,
- "when": true, "then": true, "else": true, "end": true,
- "as": true, "is": true, "by": true, "and": true, "or": true,
- "transaction": true, "begin": true, "commit": true, "rollback": true,
- "trigger": true, "view": true, "replace": true, "match": true,
-}
-
// safeSQLName returns an identifier that is safe to use in SQLite DDL.
-// SQLite accepts many names when double-quoted, so quote reserved words and
-// anything that is not a valid bare identifier (for example, names beginning
-// with a digit).
+// Always double-quotes the name, escaping any embedded quote. Quoting is
+// harmless for non-keyword identifiers and is the only way to safely emit
+// SQLite strict-reserved keywords like "add", "to", and "from" in CREATE
+// TABLE / CREATE INDEX context, where they otherwise fail at parse time.
+// Maintaining a hand-rolled keyword allowlist diverges from SQLite over
+// time; quoting unconditionally is the durable contract.
func safeSQLName(name string) string {
- if sqlReservedWords[strings.ToLower(name)] || !isBareSQLiteIdentifier(name) {
- return `"` + strings.ReplaceAll(name, `"`, `""`) + `"`
- }
- return name
-}
-
-func isBareSQLiteIdentifier(name string) bool {
- if name == "" {
- return false
- }
- for i, r := range name {
- if i == 0 {
- if r != '_' && (r < 'A' || r > 'Z') && (r < 'a' || r > 'z') {
- return false
- }
- continue
- }
- if r != '_' && (r < 'A' || r > 'Z') && (r < 'a' || r > 'z') && (r < '0' || r > '9') {
- return false
- }
- }
- return true
+ return `"` + strings.ReplaceAll(name, `"`, `""`) + `"`
}
func sqlStringLiteral(s string) string {
diff --git a/internal/generator/schema_builder_test.go b/internal/generator/schema_builder_test.go
index 90882bd9..36aa7a58 100644
--- a/internal/generator/schema_builder_test.go
+++ b/internal/generator/schema_builder_test.go
@@ -38,18 +38,19 @@ func TestToSnakeCase(t *testing.T) {
}
}
-func TestSafeSQLNameQuotesUnsafeIdentifiers(t *testing.T) {
+func TestSafeSQLNameAlwaysQuotes(t *testing.T) {
tests := []struct {
name string
in string
want string
}{
- {name: "bare identifier", in: "messages", want: "messages"},
- {name: "reserved word", in: "references", want: `"references"`},
+ {name: "plain identifier", in: "messages", want: `"messages"`},
+ {name: "non-strict reserved word", in: "references", want: `"references"`},
+ {name: "strict reserved word", in: "add", want: `"add"`},
{name: "starts with digit", in: "0", want: `"0"`},
{name: "derived starts with digit", in: "0_fts", want: `"0_fts"`},
{name: "contains punctuation", in: "foo/bar", want: `"foo/bar"`},
- {name: "escapes quote", in: `foo"bar`, want: `"foo""bar"`},
+ {name: "escapes embedded quote", in: `foo"bar`, want: `"foo""bar"`},
}
for _, tt := range tests {
diff --git a/internal/generator/templates/store.go.tmpl b/internal/generator/templates/store.go.tmpl
index fe54b419..89afcce4 100644
--- a/internal/generator/templates/store.go.tmpl
+++ b/internal/generator/templates/store.go.tmpl
@@ -800,7 +800,7 @@ func (s *Store) upsert{{pascal .Name}}Tx(tx *sql.Tx, id string, obj map[string]a
if _, err := tx.Exec(
`INSERT INTO {{safeName .Name}} ({{columnNames .Columns}})
VALUES ({{columnPlaceholders .Columns}})
- ON CONFLICT(id) DO UPDATE SET {{updateSet .Columns}}`,
+ ON CONFLICT({{safeName "id"}}) DO UPDATE SET {{updateSet .Columns}}`,
{{- range .Columns}}
{{- if eq .Name "id"}}
id,
@@ -984,8 +984,8 @@ func (s *Store) Search{{pascal .Name}}(query string, limit int) ([]json.RawMessa
}
rows, err := s.db.Query(
`SELECT t.data FROM {{safeName .Name}} t
- JOIN {{.Name}}_fts ON {{.Name}}_fts.rowid = t.rowid
- WHERE {{.Name}}_fts MATCH ?
+ JOIN {{safeNameSuffix .Name "_fts"}} ON {{safeNameSuffix .Name "_fts"}}.rowid = t.rowid
+ WHERE {{safeNameSuffix .Name "_fts"}} MATCH ?
ORDER BY rank LIMIT ?`,
query, limit,
)
diff --git a/testdata/golden/expected/generate-sync-walker-api/sync-walker-golden/internal/store/store.go b/testdata/golden/expected/generate-sync-walker-api/sync-walker-golden/internal/store/store.go
index 374b95c3..585e79d5 100644
--- a/testdata/golden/expected/generate-sync-walker-api/sync-walker-golden/internal/store/store.go
+++ b/testdata/golden/expected/generate-sync-walker-api/sync-walker-golden/internal/store/store.go
@@ -267,13 +267,13 @@ func (s *Store) migrate(ctx context.Context) error {
total_count INTEGER DEFAULT 0
)`,
resourcesFTSCreateSQL,
- `CREATE TABLE IF NOT EXISTS leagues (
- id TEXT PRIMARY KEY,
- data JSON NOT NULL,
- synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- parent_id TEXT
+ `CREATE TABLE IF NOT EXISTS "leagues" (
+ "id" TEXT PRIMARY KEY,
+ "data" JSON NOT NULL,
+ "synced_at" DATETIME DEFAULT CURRENT_TIMESTAMP,
+ "parent_id" TEXT
)`,
- `CREATE INDEX IF NOT EXISTS idx_leagues_parent_id ON leagues(parent_id)`,
+ `CREATE INDEX IF NOT EXISTS "idx_leagues_parent_id" ON "leagues"("parent_id")`,
}
// Run every migration — including the column backfill and the
@@ -750,9 +750,9 @@ func lookupFieldValue(obj map[string]any, snakeKey string) any {
// opening a per-item transaction.
func (s *Store) upsertLeaguesTx(tx *sql.Tx, id string, obj map[string]any, data json.RawMessage) error {
if _, err := tx.Exec(
- `INSERT INTO leagues (id, data, synced_at, parent_id)
+ `INSERT INTO "leagues" ("id", "data", "synced_at", "parent_id")
VALUES (?, ?, ?, ?)
- ON CONFLICT(id) DO UPDATE SET data = excluded.data, synced_at = excluded.synced_at, parent_id = excluded.parent_id`,
+ ON CONFLICT("id") DO UPDATE SET "data" = excluded."data", "synced_at" = excluded."synced_at", "parent_id" = excluded."parent_id"`,
id,
string(data),
time.Now(),
← a8ad9820 fix(skills): generate absolute manuscript URLs in publish PR
·
back to Cli Printing Press
·
fix(cli): gate refreshAccessToken emission on Auth.TokenURL 5c359ba6 →