Skip to content
Astera Technology

Why did a migrated BLOB gain bytes after a text round trip?

Transfer the binary field as bytes and retain empty BLOB separately from NULL.

Download complete case notes and answer keys ↓

Read the source and its declared rule

The original binary field contains bytes[0,255,65]. It is not declared UTF-8 text. Converting it to UTF-8 text then re-encoding substitutes invalid byte 255, creating more bytes. Corrected SQLite binding supplies the original Buffer and reads the actual BLOB type, length and bytes.

Filled synthetic input
RecordInput JSON
Base input{"bytes":[0,255,65]}
Download editable CSV ↓

Compare the actual paths

Bad bytes become [0,239,191,189,65], length 5. Correct bytes remain [0,255,65], length 3 and storage blob.

Misleading and corrected outputs
PathOutput JSON
Misleading path{"storage":"blob","length":5,"bytes":[0,239,191,189,65]}
Corrected path{"storage":"blob","length":3,"bytes":[0,255,65]}
Download editable CSV ↓
Local Node.js program: 22.23.3 or 24.14.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('CREATE TABLE target(content BLOB);');let content=i.bytes===null?null:Buffer.from(i.bytes);
      if(!corrected&&content!==null)content=Buffer.from(content.toString('utf8'),'utf8');db.prepare('INSERT INTO target VALUES(?)').run(content);
      const r=db.prepare('SELECT typeof(content) AS storage,length(content) AS length,content FROM target').get();
      return {storage:r.storage,length:r.length,bytes:r.content===null?null:[...r.content]};
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"bytes":[0,255,65]},"expected":{"storage":"blob","length":5,"bytes":[0,239,191,189,65]},"corrected":false},{"label":"corrected base","input":{"bytes":[0,255,65]},"expected":{"storage":"blob","length":3,"bytes":[0,255,65]},"corrected":true},{"label":"empty BLOB retains storage kind","input":{"bytes":[]},"expected":{"storage":"blob","length":0,"bytes":[]},"corrected":true},{"label":"explicit SQL NULL is separate","input":{"bytes":null},"expected":{"storage":"null","length":null,"bytes":null},"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);
}
Download runnable example ↓

Check boundaries before the next load

An empty BLOB has storage blob, length 0, bytes[]. Explicit null has storage null, length null, bytes null.

Editable boundary records and expected outputs
CaseInput JSONExpected output JSON
Empty BLOB retains its type{"bytes":[]}{"storage":"blob","length":0,"bytes":[]}
Explicit SQL NULL{"bytes":null}{"storage":"null","length":null,"bytes":null}
Download editable CSV ↓

Is matching readable text enough to verify binary fidelity?

Show the worked answer

No. Compare the actual bytes and storage kind. This corrected transfer preserves three exact bytes.

Related resources