Your SQLite File Deserves a URL

The first time it happens you assume you broke something. The app writes to app.db, it works on your laptop, you deploy, and the writes are gone by the next request. Or they survive for a while and then reset for no reason you can point at. Or two instances boot and each one starts from an empty schema.

Nothing in your code is wrong. The disk changed. Vercel's runtime docs describe functions as having "a read-only filesystem with writable /tmp scratch space up to 500 MB", and that scratch space belongs to one instance and does not outlive it. Most serverless platforms have that shape, and any container that gets rescheduled has a version of it. Your database was a file on a filesystem that is not there anymore.

This is not SQLite failing. SQLite is a library that reads and writes a file, reachable by the process that opens it, which is the whole point right up until something else needs the same rows. A deployed server, a second instance, a cron job, a teammate. You do not need a different database. You need this database at an address.

Two shapes of address

There are two ways to give a SQLite database a URL, and picking between them is the only real decision in this post.

Hosted SQLite on Layerbase is your actual SQLite file on disk, served over the Postgres wire protocol by a shim. The SQL you send is still SQLite SQL. What changes is the driver carrying it, because better-sqlite3 opens files and cannot open a network connection.

libSQL is the SQLite fork with a server built into it, so it answers over HTTPS and needs its own client. If you have read SQLite vs libSQL, that comparison is the long version.

Start with hosted SQLite. It is the shorter trip, and you can convert to libSQL later without redoing it.

Take a clean copy first

Do not upload the file your app has open. A live SQLite database in WAL mode keeps recent pages in a -wal sidecar next to the main file, so copying only app.db can leave the newest writes behind, and copying it mid-write can leave you with a torn page.

SQLite has a command for exactly this:

sqlite3 app.db "VACUUM INTO 'upload.db'"

The SQLite docs put the guarantee plainly: "the generated output database is a consistent snapshot of the original database." It runs against a live database, leaves the original untouched, and hands you one self-contained file with nothing outstanding beside it. One catch: the name you write to has to not exist yet, or be an empty file, so delete upload.db between runs instead of expecting SQLite to overwrite it. Check it before you go further:

sqlite3 upload.db "SELECT name FROM sqlite_master WHERE type='table'"

notes
tags
note_tags

Create the database and push the file

Through the dashboard, pick SQLite at layerbase.com/create/sqlite, name it, and open the query console on the new database. The import menu has a "Restore full database" option that takes your .db file directly, and you can drop the file onto the console instead of picking it.

From a terminal, the Layerbase CLI does the same thing. Cloud commands authenticate with a personal API key from your dashboard settings:

npm i -g layerbase
export LAYERBASE_API_KEY=sk_...

lbase cloud create notes --engine sqlite
lbase import ./upload.db --target notes --yes

Uploading dump...
Restoring...
Import complete (2.4 MB).

Two things are worth knowing about that upload. It is checked before anything happens: the file header has to actually say SQLite format 3, so a stray .sql dump or a gzipped archive is refused rather than written over your database. And it is capped at 100 MB, which is a lot of rows for a file that started life in a repo, but it is a real ceiling.

The free tier covers all of this: two databases, 5 GB, no credit card. SQLite and libSQL are both on it.

Connect

The connection string looks like Postgres, because the wire protocol is Postgres:

postgresql://layerbase:<password>@your-host.cloud.layerbase.dev:5432/notes?sslmode=require

So the code that used to open a file:

import Database from 'better-sqlite3'
const db = new Database('app.db')

becomes a normal pooled client:

import { Pool } from 'pg'
const pool = new Pool({ connectionString: process.env.DATABASE_URL })

Your tables, indexes, and queries came across in the file itself, so the SQL you already wrote is the SQL you keep. This is the honest cost of the trip: one driver swap, the parameter placeholder style that comes with it, and the shape of the call around it. better-sqlite3 is synchronous and hands you rows directly; pg returns a promise and wraps them in a result object:

// better-sqlite3
const note = db.prepare('SELECT * FROM notes WHERE id = ?').get(id)

// pg
const { rows } = await pool.query('SELECT * FROM notes WHERE id = $1', [id])
const note = rows[0]

Transactions move the same way: instead of wrapping a function, you take a client off the pool with pool.connect(), run BEGIN and COMMIT on that client, and release() it in a finally. There is no schema translation and no data migration to review.

If you would rather have the libSQL client

Open the database in the dashboard and use Convert to libSQL. It copies the schema and rows over each engine's own protocol, then verifies the result by comparing row counts per table. It runs one direction only, SQLite to libSQL, and there is no way back, which is why it is worth doing on purpose rather than by default. The details are in converting an engine.

Afterwards your app talks to an HTTPS endpoint with an auth token:

import { createClient } from '@libsql/client'

const client = createClient({
  url: process.env.LIBSQL_DATABASE_URL!,
  authToken: process.env.LIBSQL_AUTH_TOKEN!,
})

Two neighbours, if they are closer to your situation: moving an existing hosted libSQL database between providers is migrating from Turso, and if you are moving data by dump from anything that renders values through JavaScript, read why matching row counts are not proof first.

What the address is actually for

The URL is the point, but it is not all you get.

Backups that exist. Every destructive path takes one first, deletion is refused if a final backup cannot be secured, and the most recent backup is never pruned. That applies on the free tier too, which is not where most platforms put it.

Sleeping instead of billing. A free database sleeps after 60 idle minutes and wakes on the next connection in a few seconds, so a side project that nobody visits for a month costs nothing and is still there. Free databases that stay idle for two weeks are archived, which releases the address and keeps the data until you restore it. That mechanic is why the free tier can stay a free tier, and I wrote out the economics in the free tier that stays.

A query console, which matters more than it sounds like: the reason people avoid inspecting production SQLite is that it usually means finding a shell on the box the file lives on.

SQLite is one of 18 engines on Layerbase Cloud, which mostly matters for the next thing your app needs. When it wants a cache or a search index, it goes on the same account instead of a new vendor.

When to keep the file

Plenty of SQLite files should stay files, and the deploy failure at the top of this post does not apply to all of them.

Read-only data shipped with your app is fine on an ephemeral filesystem, because nothing is lost if nothing is written. Test fixtures are fine, and an in-process database that opens in microseconds is hard to beat. Single-user local software, a desktop app, a CLI tool, anything where the data belongs to the person running it, should stay where it is.

The question is narrower than "is this project serious". It is: does anything other than this one process need to read or write these rows? Until the answer is yes, keep the file. When it is yes, you are not rewriting anything. You are uploading one file and changing a connection.

If what you want is a real database on your own machine rather than a hosted one, the Layerbase CLI runs SQLite and libSQL locally with no Docker.

Create a SQLite database or start with libSQL, and take the copy first.