LiteFS - Optimize Sqlite

1

Hey there! Hope everyone’s having a good day. I’m running into some issues and could really use your help figuring out what’s going wrong.

So I normally use these SQLite pragmas in my apps.

PRAGMA journal_mode = WAL; // Could this be the troublemaker?
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA foreign_keys = ON;
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 2147483648;
PRAGMA page_size = 8192;

Now, I don’t think these pragmas are causing problems with LiteFS, but I’d love to double-check that ALL of them are actually compatible. Maybe I’m missing something? If they’re not the issue, then I’m pretty sure it has something to do with my LiteFS configuration.

To keep things clean and avoid technical debt, I set up my database connection like this. Some of these pragmas need to run every time the app starts, so here’s what I do:

import { open } from 'sqlite';
import sqlite3 from 'sqlite3';

export const dbPromise = open({
    filename: process.env.NODE_ENV == 'production' ? '/app/src/database/database.sqlite' : './src/database/database.sqlite',
    driver: sqlite3.Database,
});

export async function optimizeDatabase() {
    const db = await dbPromise;
    await db.exec(`
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;
        PRAGMA busy_timeout = 5000;
        PRAGMA cache_size = -20000;
        PRAGMA foreign_keys = ON;
        PRAGMA auto_vacuum = INCREMENTAL;
        PRAGMA temp_store = MEMORY;
        PRAGMA mmap_size = 2147483648;
        PRAGMA page_size = 8192;
    `);
}

Then when my app starts up, I run this:

try {
    const { optimizeDatabase } = require(
        process.env.NODE_ENV == 'production' ? '/app/src/db.ts' : './db.ts'
    );
    optimizeDatabase().catch(console.error);
} catch (error) {
    console.error("Failed to import database module:", error);
}

And then anywhere in my app I can just do queries like this:

const { dbPromise } = require(
    process.env.NODE_ENV == 'production' ? '/app/src/db.ts' : '../../db.ts'
);

const db = await dbPromise;
await db.run("DELETE FROM stats WHERE name_stat LIKE '%_visitor_tracking' OR name_stat LIKE '%_session_tracking'");

The thing is, Im pretty sure the problem is actually in my LiteFS configuration, or I believe so LiteFS: Standard PRAGMA optimize usage?
I’ll share that config in my next comment .

Also, quick question - if LiteFS is meant to only be accessible from a specific location, please let me know! I’m doing a rewrite of my application right now and I need the database to live in a specific directory that’s different from whatever the default is.

Anyone have ideas on where I should be looking to debug this? Thanks so much in advance - really appreciate any help you can give me!

2 
# The fuse section describes settings for the FUSE file system. This file system
# is used as a thin layer between the SQLite client in your application and the
# storage on disk. It intercepts disk writes to determine transaction boundaries
# so that those transactions can be saved and shipped to replicas.
fuse:
  dir: "/litefs"

# The data section describes settings for the internal LiteFS storage. We'll 
# mount a volume to the data directory so it can be persisted across restarts.
# However, this data should not be accessed directly by the user application.
data:
  dir: "/app/src/database"

# This flag ensure that LiteFS continues to run if there is an issue on starup.
# It makes it easy to ssh in and debug any issues you might be having rather
# than continually restarting on initialization failure.
exit-on-error: false

# This section defines settings for the option HTTP proxy.
# This proxy can handle primary forwarding & replica consistency
# for applications that use a single SQLite database.
proxy:
  addr: ":8080"
  target: "localhost:3000"
  db: "db"
  passthrough: 
    - "*.ico"
    - "*.png"

# This section defines a list of commands to run after LiteFS has connected
# and sync'd with the cluster. You can run multiple commands but LiteFS expects
# the last command to be long-running (e.g. an application server). When the
# last command exits, LiteFS is shut down.
exec:
  - cmd: "bun run src/index.ts"

# The lease section specifies how the cluster will be managed. We're using the
# "consul" lease type so that our application can dynamically change the primary.
#
# Dynamic primary selection - any region can become primary
lease:
  type: "consul"
  advertise-url: "http://${HOSTNAME}.vm.${FLY_APP_NAME}.internal:20202"
  candidate: true  # All regions are candidates for primary
  promote: true

  consul:
    url: "${FLY_CONSUL_URL}"
    key: "litefs/${FLY_APP_NAME}"

# fly.toml app configuration file generated for serverelysia-landinganalytics2 on 2025-06-25T13:58:57-06:00
#
# See https://fly.io/docs/reference/configuration/ for information about how to use this file.
#

app = 'serverelysia-landinganalytics2'
primary_region = 'lax'

[build]

[[mounts]]
  source = 'litefs'
  destination = '/app/src/database'

[http_service]
  internal_port = 8080
  force_https = true
  auto_stop_machines = 'stop'
  auto_start_machines = true
  min_machines_running = 0
  processes = ['app']

[[vm]]
  memory = '1gb'
  cpu_kind = 'shared'
  cpus = 1


# syntax = docker/dockerfile:1

# Adjust BUN_VERSION as desired
ARG BUN_VERSION=1.2.5
FROM oven/bun:${BUN_VERSION}-slim as base

LABEL fly_launch_runtime="Bun"

# Bun app lives here
WORKDIR /app

# Set production environment
ENV NODE_ENV="production"

# Install LiteFS dependencies
RUN apt-get update -y && apt-get install -y ca-certificates fuse3 sqlite3
# Throw-away build stage to reduce size of final image
FROM base as build

# Install packages needed to build node modules
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y build-essential pkg-config python-is-python3

# Install node modules
COPY --link bun.lock package.json ./
RUN bun install --ci

# Copy application code
COPY --link . .

# Final stage for app image
FROM base

# Install LiteFS dependencies in final stage too
RUN apt-get update -y && apt-get install -y ca-certificates fuse3 sqlite3

# Copy LiteFS binary
COPY --from=flyio/litefs:0.5 /usr/local/bin/litefs /usr/local/bin/litefs

# Copy built application
COPY --from=build /app /app

# Copy LiteFS configuration
COPY litefs.yml /etc/litefs.yml

# Expose ports: 3000 for app, 8080 for LiteFS proxy
EXPOSE 3000 8080

# Use LiteFS as entrypoint
ENTRYPOINT ["litefs", "mount"]
3

Yep, you have the sense of fuse.dir and data.dir inverted. (Many others have made that same mistake!)

Your application should interact solely with fuse.dir, and that’s set to /litefs in the litefs.yml you posted. This is where the litefs daemon is watching for changes to the WAL, etc.

In contrast, data.dir should be considered a private implementation detail: don’t modify anything there directly.

This will also cause problems, :dragon:. As discussed in the previous thread, you want it to be ≥2 (and have at least that many Machines in lax, your declared primary_region).

Hope this helps!

2 Likes
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.