Read the source and its declared rule
The original target has parent 1 and childL with ON DELETE CASCADE. Incoming New changes the parent name while childL must stay. INSERT OR REPLACE removes the conflicting old parent before inserting its replacement, so this cascade deletesL. The corrected UPSERT updates the name.
| Record | Input JSON |
|---|---|
| Base input | {"id":1,"name":"New","childPresent":true} |
Compare the actual paths
Corrected parent 1 becomes New with childL still linked. A genuinely new parent 2 inserts without affecting parent 1’s child.
| Path | Output JSON |
|---|---|
| Misleading path | {"parents":[{"id":1,"name":"New"}],"children":[]} |
| Corrected path | {"parents":[{"id":1,"name":"New"}],"children":[{"code":"L","parent_id":1}]} |
import {DatabaseSync} from 'node:sqlite';
import {TextDecoder} from 'node:util';
import {inflateSync} from 'node:zlib';
import {posix} from 'node:path';
import {createHash} from 'node:crypto';
const rows=(db,sql)=>db.prepare(sql).all().map(r=>({...r}));
const failed=(action)=>{try{action();return null;}catch(e){return {code:e.errcode??e.code??e.name};}};
import assert from 'node:assert/strict';
const run=function run(i,corrected=true){
const db=new DatabaseSync(':memory:');try{db.exec("PRAGMA foreign_keys=ON;CREATE TABLE parent(id INTEGER PRIMARY KEY,name TEXT);CREATE TABLE child(code TEXT,parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE);INSERT INTO parent VALUES(1,'Old');");
if(i.childPresent)db.exec("INSERT INTO child VALUES('L',1);");
db.prepare(corrected?'INSERT INTO parent VALUES(?,?) ON CONFLICT(id) DO UPDATE SET name=excluded.name':'INSERT OR REPLACE INTO parent VALUES(?,?)').run(i.id,i.name);
return {parents:rows(db,'SELECT * FROM parent ORDER BY id'),children:rows(db,'SELECT * FROM child')};
}finally{db.close();}
};
const cases=[{"label":"misleading base","input":{"id":1,"name":"New","childPresent":true},"expected":{"parents":[{"id":1,"name":"New"}],"children":[]},"corrected":false},{"label":"corrected base","input":{"id":1,"name":"New","childPresent":true},"expected":{"parents":[{"id":1,"name":"New"}],"children":[{"code":"L","parent_id":1}]},"corrected":true},{"label":"genuinely new parent","input":{"id":2,"name":"New","childPresent":true},"expected":{"parents":[{"id":1,"name":"Old"},{"id":2,"name":"New"}],"children":[{"code":"L","parent_id":1}]},"corrected":true},{"label":"existing parent without child","input":{"id":1,"name":"New","childPresent":false},"expected":{"parents":[{"id":1,"name":"New"}],"children":[]},"corrected":true}];
for(const test of cases){
const actual=run(test.input,test.corrected);
assert.deepEqual(actual,test.expected,test.label);
console.log(test.label,actual);
}
Check boundaries before the next load
New parent 2 preserves old 1/L. An existing parent without children still updates normally.
| Case | Input JSON | Expected output JSON |
|---|---|---|
| Genuinely new parent | {"id":2,"name":"New","childPresent":true} | {"parents":[{"id":1,"name":"Old"},{"id":2,"name":"New"}],"children":[{"code":"L","parent_id":1}]} |
| Parent has no children | {"id":1,"name":"New","childPresent":false} | {"parents":[{"id":1,"name":"New"}],"children":[]} |
Are REPLACE and an existing-row UPDATE equivalent here?
Show the worked answer
No. Replacement deletes the old parent and triggers its cascade. Use the intended update to preserveL.