Unable to open an existing database into memory via buffer

[apavlinovic]

Hi everyone, I am really not sure what's going on, but I am trying to load a database file into memory by using the provided example of serializing the database into a buffer. I've tried all possible combinations of options, including reading a file with node FS module, but no luck.

This code works if we are just returning the Database as a file reference. DB loading code:

import Database from "better-sqlite3";

const db = new Database("../go-hub.db", {
    verbose: console.log,
    readonly: true,
});
const buffer = db.serialize();
db.close();

export const HUB_DB = new Database(buffer);

And usage code, where it fails:

export const GetAllPokemon = (
    query: string | null = null,
    limit?: number
): DbPokemonResult[] => {
    const statement = HUB_DB.prepare(
        `
            select * from pokemon
            where 
                (
                    $query IS NULL 
                    or lower(name) like $query 
                    or lower(form) like $query 
                    or lower(name || form) like $query
                )
                and pokemon.name != 'Aripant'
            order by id
            ${limit ? "limit " + limit : ""}
        `
    );

    return statement.all({
        query: query ? `%${query}%` : null,
    }) as DbPokemonResult[];
};

The error is:

error SqliteError: unable to open database file
    at GetAllWithType (./loaders/PokemonRepository.ts:86:89)
    at fetchPokemonList (./app/pokemon-list/[slug]/page.tsx:45:93)
    at PokemonListPage (./app/pokemon-list/[slug]/page.tsx:92:21)
    at async Promise.all (index 0)
    at async Promise.all (index 0)
    at async Promise.all (index 0)

[Prinzhorn]

Your code works fine for me:

import Database from "better-sqlite3";

const db = new Database("./test.db", {
    verbose: console.log,
    readonly: true,
});

const buffer = db.serialize();
db.close();

const HUB_DB = new Database(buffer);

const statement = HUB_DB.prepare('select * from test');

console.log(statement.all());

So the issue lies somewhere in the code you didn't share. Please provide a minimal self contained example that demonstrates the issue.

Your stack trace also doesn't align with the code you've provided (GetAllPokemon !== GetAllWithType).

[lakmeer]

I am also having this issue. Mine seems to be related to the sqlite-vss extension. When using my test.db - which contains a VSS-created virtual table - I get this error, but using a fresh database with single basic table, it works. Is there some compatibility thing with extensions that are unsupported by .serialize()?

Repro Script

import Database from 'better-sqlite3'

const USE_BUFFER = true

let db = new Database('./test.db')

if (USE_BUFFER) {
  const buffer = db.serialize()
  db.close()
  db = new Database(buffer)
}

console.log(db.prepare('select * from items').get())

Outcome

• when USE_BUFFER is false, there is no error, and we successfully print one row from the items table (even without applying the VSS extension in this repro script)
• when USE_BUFFER is true, I get 'unable to open database file', but it seems to occur on the first line that I try to call some method on db, like prepare or pragma, and not the Database(buffer) constructor itself

Further context

• node v18.16.0
• test.db file is browsable using an external sqlite client
• buffer object is real and has an appropriate length
• verbose mode doesn't output anything before failure
• I am also using pragma user_version to track migrations

Schema for creating test.db

create table if not exists items (
  id integer primary key autoincrement,
  last_update timestamp default (unixepoch('now')),
  hash text not null,
  type text not null check(type in ('text', 'image', 'audio', 'link')),
  desc text,
  content text,
  data blob
);

create table if not exists tags (
  id integer primary key autoincrement,
  name text unique not null
);

create table if not exists item_tags (
  item_id integer not null,
  tag_id integer not null,
  foreign key (item_id) references items(id),
  foreign key (tag_id) references tags(id),
  primary key (item_id, tag_id)
);

-- VSS Extension
create virtual table if not exists vss_items using vss0(
  embedding(1536)
);

create table if not exists embedding_cache (
  id integer primary key,
  hash text not null unique,
  embedding text not null
);

Code that created my test.db using VSS:

const db = new Database('./test.db')
db.pragma('journal_mode = WAL')
VSS.load(db)
db.exec( <the above schema> )

Thanks for any advice you might have

[lakmeer]

@apavlinovic

Hi, unsure if you ever worked this out, but I was able to solve my case so I will put it here for future googlers:

Your example code doesn't mention the recommended WAL journaling pragma, but if the saved db file has WAL enabled, it is not supported by .serialize(). But you can still utilise it by switching it off and then on again. Here is my working code:

import Database from 'better-sqlite3'

const USE_BUFFER = true

let db = new Database('./test.db')
db.pragma('journal_mode = WAL')

// does other stuff...

if (USE_BUFFER) {
  db.pragma('journal_mode = DELETE')     // ++ kill WAL before serialise
  const buffer = db.serialize()
  db.close()

  db = new Database(buffer)
  db.pragma('journal_mode = WAL')        // ++ switch it back on again
}

VSS extension works with it just fine too. Hope this helps!