MEHDI.
RETURN_TO_INDEX

Running Next.js with SQLite in Docker Without Losing Data

7 min read
#Docker#Prisma#Self-Hosting#Next.js#SQLite#Database

The first time I deployed a Next.js application with SQLite in Docker, everything worked perfectly. I created some data through the admin panel, restarted the container, and all the data was gone. This happened because I did not understand how Docker containers handle the filesystem.

Why Container Data Disappears

A Docker container has its own filesystem layer. When you write a file inside a container (like a SQLite database), it exists only in that container's writable layer. When the container is removed (which happens during docker compose down and docker compose up), the writable layer is deleted and the data is lost.

This is by design. Containers are meant to be ephemeral: destroyed and recreated without consequence. The application code and dependencies are baked into the image at build time. Any data that needs to survive must be stored in a persistent volume.

Volumes: The Solution

Docker volumes are directories that exist outside the container's filesystem. When you mount a volume, the container reads and writes to the volume instead of its own filesystem. When the container is destroyed, the volume persists.

For a SQLite database, the volume needs to contain the .db file. The simplest configuration:

services:
  app:
    image: your-app
    volumes:
      - ./data:/app/data

This mounts the host directory ./data to /app/data inside the container. The SQLite file at /app/data/dev.db is written to the host's ./data directory. When the container is rebuilt, the file is still there.

The DATABASE_URL Path

Prisma's DATABASE_URL for SQLite uses the file: protocol:

DATABASE_URL=file:/app/data/dev.db

The path /app/data/dev.db is the path inside the container. This must match the mount point of the volume. If the volume is mounted at /app/data but the database URL points to /app/database/dev.db, they are different directories and the data will not persist.

A common mistake is using a relative path like file:./dev.db. This resolves relative to the working directory inside the container, which might not be where the volume is mounted. Always use absolute paths in DATABASE_URL.

File Permissions

SQLite creates the database file as the user running the process inside the container. If the container runs as root (the default), the file is owned by root. If the container runs as a non-root user (which is better for security), the file must be owned by that user.

My Docker entrypoint handles this:

if [ "$(id -u)" = '0' ]; then
  chown -R nextjs:nodejs /app/data
fi

This runs before the application starts, ensuring the data directory is writable by the nextjs user. Without this step, the application might fail to create or open the database file.

Migrations

SQLite does not have a built-in migration system. Prisma fills this role. The command prisma db push applies the current schema to the database, creating tables and columns as needed.

The order of operations in the Docker entrypoint matters:

  1. Fix permissions on the data directory
  2. Run prisma db push to apply schema changes
  3. Run the seed scripts
  4. Start the application

If you start the application before running migrations, the schema might be out of date and queries will fail. If you run seeds before migrations, the tables might not exist yet.

For production, prisma migrate deploy is the recommended command because it applies only pending migrations from the migration history. prisma db push is more aggressive: it applies the current schema state, which can include destructive changes if the schema has changed in incompatible ways. For a single-developer project, db push is fine. For a team, use migrate deploy.

The Seed Problem

A seed script that runs on every container restart must be idempotent. If it uses create without checking for existing records, you get duplicates after every restart.

The solution is upsert with the unique field as the identifier:

await prisma.project.upsert({
  where: { slug: project.slug },
  update: {},
  create: projectData,
});

With update: {}, existing records are found but not modified. Only missing records are created. Running this seed ten times produces the same result as running it once.

Multiple Instances

If you run multiple containers simultaneously (for example, during a rolling update), they might try to apply migrations or seed at the same time. SQLite handles concurrent writes with file-level locking, which means one process waits while the other completes.

Prisma's upsert handles the race condition: if two instances try to create the same record simultaneously, the unique constraint causes one to fail. The failing instance catches the error and continues.

For migrations, the situation is more delicate. Two instances running prisma db push simultaneously can cause conflicts. The safe approach is to run migrations from a single entrypoint (like the Docker entrypoint) and ensure only one container starts at a time, or at least that only one runs migrations.

Backups

Backing up a SQLite database is simpler than backing up a client-server database. The entire database is a single file (plus WAL and SHM files if WAL mode is active).

To create a consistent backup:

sqlite3 /app/data/dev.db ".backup /app/data/backup.db"

Or copy the file while the application is not writing (which is rare for a web application that handles requests):

cp /app/data/dev.db /backups/dev-$(date +%Y%m%d).db

If WAL mode is active, you also need to copy the -wal and -shm files, or run PRAGMA wal_checkpoint(TRUNCATE) before copying to ensure all data is in the main file.

WAL (Write-Ahead Logging) mode is the default for modern SQLite. It allows concurrent reads while a write is in progress, which improves performance for web applications. The tradeoff is that the database consists of multiple files instead of one.

When to Switch to PostgreSQL

SQLite is excellent for small to medium applications. The threshold for switching to PostgreSQL is not about data size (SQLite handles multi-gigabyte databases fine) but about concurrency and features.

Consider PostgreSQL when:

  • You need true concurrent writes from multiple application instances
  • You need advanced features like JSON queries, full-text search, or materialized views
  • You have a team of developers who need migration branching and merging
  • Your application serves hundreds of concurrent users with frequent writes

For a personal portfolio or a small internal tool, SQLite is more than adequate. The operational simplicity of a single file with no server process is a significant advantage when you are the person responsible for keeping it running.

Real-World Example

My portfolio uses this exact setup: Next.js with Prisma and SQLite in Docker. The database file lives in a mounted volume at /app/data/dev.db. The entrypoint runs migrations and seeds before starting the application. Backups are daily file copies.

The entire database is under 1MB. The application handles a handful of projects and blog posts. There is no reason to run a PostgreSQL server for this workload. SQLite does the job, and the simplicity means fewer things can break.

If the portfolio grows to the point where SQLite becomes a bottleneck, switching to PostgreSQL is a straightforward change: update the Prisma datasource provider, switch the DATABASE_URL to a connection string, and run migrations against the new database. Prisma abstracts most of the database-specific differences.