I am using a read-only SQLite database and accessing it during runtime of the application (but mostly during prerendering). Instead of using a remote database I have thought of the following solution, which apparently works in a minimal example that I created for testing purposes, but I wonder if this is reliable and recommended at all, in particular before I rewrite my actual application.
In my project there is a sqlite file in data/main.sqlite. It is not in the src folder. Then, I use better-sqlite3 and initialize the db as follows in a TypeScript file on the server (I use SvelteKit, but any Netlify function will do):
import Database from ‘better-sqlite3’
import { join } from ‘node:path’
const db_path = join(process.cwd(), ‘data’, ‘main.sqlite’)
export const db = new Database(db_path, { readonly: true })
That’s basically it. Now I can query the database.
I’ve been wondering in particular if this works because I could not find any documentation about this and because the database is not in the build output. When I log db_path, I get on Netlify:
/var/task/data/main.sqlite
This seems to be a file on the server itself, which is usually (as far as I know) not accessible by the application itself. But apparently it works (for now).
The file system is said to be ephemeral. Will the file go away at some point? I waited a few hours to get a cold start; the file still was there, and the db worked.
Just to reiterate: the database will not perform any writes during the runtime of the application. So it is fine when writes are not preserved; they just don’t happen. Also, I don’t want to use SQLite in the browser (sql.js), the queries should happen on the server-side. Reason: I don’t want to require users to download the whole thing.
EDIT. I have also researched before asking here. The only thing that seems relevant is that I should add
[functions]
included_files = ["data/main.sqlite"]
in the netlify toml file. For some reason my minimal example also works without that. But even when I add this, other threads (and Netlify’s AI assistant, and Google Gemini, etc.) suggest that what I do here is not 100% reliable, but they never really explain why.