{"record":{"id":"50d1ec2e3cba0876","repo":"decolua/9router","slug":"db-sync-add-column-tablename-colname-fail","errorCode":null,"errorMessage":"[DB][sync] add column ${tableName}.${colName} failed: ${e.message}","messagePattern":"\\[DB\\]\\[sync\\] add column (.+?)\\.(.+?) failed: (.+?)","errorType":"console","errorClass":null,"httpStatus":null,"severity":"warning","filePath":"src/lib/db/migrate.js","lineNumber":99,"sourceCode":"    // Create table if absent\n    adapter.exec(buildCreateTableSql(tableName, def));\n\n    // Diff columns\n    const existing = adapter.all(`PRAGMA table_info(${tableName})`);\n    const existingNames = new Set(existing.map((r) => r.name));\n    for (const [colName, colDef] of Object.entries(def.columns)) {\n      if (!existingNames.has(colName)) {\n        // SQLite ADD COLUMN restrictions: no PRIMARY KEY / UNIQUE w/o NULL ok.\n        // We strip PRIMARY KEY / UNIQUE since those are only valid at create time.\n        const safeDef = colDef\n          .replace(/PRIMARY KEY( AUTOINCREMENT)?/i, \"\")\n          .replace(/UNIQUE/i, \"\")\n          .trim();\n        try {\n          adapter.exec(`ALTER TABLE ${tableName} ADD COLUMN ${colName} ${safeDef}`);\n          console.log(`[DB][sync] +column ${tableName}.${colName}`);\n        } catch (e) {\n          console.warn(`[DB][sync] add column ${tableName}.${colName} failed: ${e.message}`);\n        }\n      }\n    }\n\n    // Indexes (idempotent)\n    for (const idx of def.indexes || []) {\n      try { adapter.exec(idx); } catch {}\n    }\n  }\n}\n\n// ─── Legacy JSON import (one-time) ───────────────────────────────────────\nfunction importLegacyMain(adapter, data) {\n  if (!data || typeof data !== \"object\") return;\n\n  if (data.settings) {\n    adapter.run(`INSERT INTO settings(id, data) VALUES(1, ?) ON CONFLICT(id) DO UPDATE SET data = excluded.data`, [stringifyJson(data.settings)]);\n  }","sourceCodeStart":81,"sourceCodeEnd":117,"githubUrl":"https://github.com/decolua/9router/blob/90b52e06ffd666b7929554211474d01588f6b1f8/src/lib/db/migrate.js#L81-L117","documentation":"Non-fatal warning from syncSchemaFromTables(), which keeps the SQLite schema in sync with table definitions by ALTER TABLE ... ADD COLUMN for missing columns. The column definition is sanitized (NOT NULL/UNIQUE/PRIMARY KEY/UNIQUE stripped); if the ALTER still fails, it logs this warning and continues — the schema drift persists but startup is not blocked.","triggerScenarios":"ALTER TABLE ... ADD COLUMN fails because: the column already exists (race between multiple instances migrating simultaneously, or a stale marker), a lock on the database (another connection holds a write transaction), the sanitized definition is still invalid SQL for SQLite (e.g. unsupported constraint surviving the sanitization), or a disk I/O error.","commonSituations":"Two app instances starting at once against the same DB file; DB file opened read-only; custom table definitions in migrations/ adding columns with constraints SQLite can't add via ALTER; interrupted previous migration leaving inconsistent state.","solutions":["Check e.message: 'duplicate column name' is harmless — another process already added it; ignore.","'database is locked' — ensure only one instance runs against the DB, or enable WAL/retry.","Fix the table definition in src/lib/db/migrations/ so the column's DEFAULT/CONSTRAINT is valid for ALTER TABLE ADD COLUMN (SQLite only allows constant DEFAULTs and cannot add PRIMARY KEY columns).","Back up the DB file, then stop the app, remove the stale marker/lock, and restart so sync re-runs cleanly."],"exampleFix":"// before (migration table def)\ncolumns: { quota: \"INTEGER NOT NULL UNIQUE DEFAULT 0\" }\n// after (SQLite ADD COLUMN cannot apply UNIQUE / non-constant NOT NULL DEFAULTs)\ncolumns: { quota: \"INTEGER DEFAULT 0\" }","handlingStrategy":"try-catch","validationCode":"// Detect missing columns before startup so sync succeeds on first run\nimport Database from \"better-sqlite3\";\nconst db = new Database(DATA_FILE);\nconst cols = db.pragma(`table_info(${tableName})`).map(c => c.name);\nif (!cols.includes(\"expectedColumn\")) console.warn(\"Schema drift — sync will ALTER TABLE on next start\");","typeGuard":null,"tryCatchPattern":"try { await getAdapter(); }\ncatch (e) { /* sync failures are warnings, not throws — check logs for '[DB][sync] add column' */ }\nif (getLogs().some(l => l.includes(\"[DB][sync] add column\")) ) console.warn(\"Schema drift persisted — inspect migration definitions\");","preventionTips":["Run only one app instance per DB file to avoid concurrent ALTER TABLE lock/duplicate-column races.","Keep migration column definitions SQLite-ALTER-safe: no UNIQUE, no PRIMARY KEY, only constant DEFAULTs.","Enable WAL mode to reduce 'database is locked' errors during startup sync.","After schema changes, restart once cleanly and verify the [DB][sync] +column lines appear without warnings."],"tags":["sqlite","schema-migration","alter-table","database"],"backgroundTag":"schema-migration-failed","analyzedSha":"90b52e06ffd666b7929554211474d01588f6b1f8","analyzedAt":"2026-08-30T21:05:45.952Z","schemaVersion":2},"datasetVersion":"2026-08-30T23:17:21.991Z"}