WiseLibs / better-sqlite3

The fastest and simplest library for SQLite3 in Node.js.
MIT License
5.55k stars 393 forks source link

Serialization -> Deserialization doesn't seem to be working #1291

Open lukeschaefer opened 1 week ago

lukeschaefer commented 1 week ago

For performance reasons, I'd like to do a number of large operations in memory, rather than interacting with the disk. This succeeds in creating an in-memory db: const db = new Database(':memory:');

However if I want to take an existing db and fully bring it into memory, this fails:

const db = new Database("./snapshot.db");
const buffer = db.serialize();
const inMemoryDb = new Database(buffer);
// Failure occurs on the next line:
inMemoryDb.prepare("SELECT phrase FROM Phrases");

I get SqliteError: unable to open database file. I can confirm the buffer is filled with data that can be written to disk and loaded via the filesystem like usual. But creating a Database out of a buffer seems to always result in a failure.

Prinzhorn commented 1 week ago

Cannot reproduce with the latest better-sqlite3:

import Database from 'better-sqlite3'

const db = new Database('./Chinook_Sqlite.sqlite');
const buffer = db.serialize();
const inMemoryDb = new Database(buffer);
const stmt = inMemoryDb.prepare('SELECT Name FROM Artist');
console.log(stmt.all());

With database downloaded from https://github.com/lerocha/chinook-database/releases

You're likely using WAL, see https://github.com/WiseLibs/better-sqlite3/issues/1066#issuecomment-1880301361 and https://github.com/WiseLibs/better-sqlite3/issues/939#issuecomment-1613955190

@JoshuaWise maybe serialize could detect that and throw a more descriptive error? At least the docs should be updated.

lukeschaefer commented 1 week ago

You're likely using WAL

Yup, you're dead on, thanks for the quick assessment. Rebuilding the DB without using WAL fixed the issue.