samperalabs
• By Bernat Sampera • 2 min read

How to Manage SQLite Databases on a VPS ( with Coolify )

coolify docker

The Problem

Keeping a SQLite database consistent between a Docker‑based VPS and local development requires a shared persistent volume, correct path configuration in the app, and a reliable sync mechanism.

Services and Libraries

  • Coolify – manages persistent storage volumes for Docker services.

  • Docker – mounts the volume into the container at /app/data.

  • scp/ssh – transfers the .db file between host and local machine.

  • sqlite3 – lightweight embedded database engine.

  • import.meta.env – injects environment variables into a Node/Nixpacks build.

Implementation Details

  1. Add Persistent Volume

  2. Create samperalabvolume in Coolify’s Persistent Storage tab, mapping to /app/data.

  3. Point the App to the New Path

  4. In an NPM project:
    js const dbPath = import.meta.env.NODE_ENV === 'production' ? import.meta.env.DATABASE_PATH : join(projectRoot, 'db', 'content.db');

  5. In Docker:
    dockerfile RUN mkdir -p /app/data && chown -R app:app /app/data VOLUME ["/app/data"]

  6. Helper to open the DB:
    python def get_db_connection(): """Get a database connection""" return sqlite3.connect(DATABASE_PATH)

  7. Sync Database with a Python Script
    The script uses scp over SSH to pull or push the SQLite file.

import subprocess import sys import os

# Config SSH_USER = "default" SSH_HOST = "XXX.XXX.XXX.XXX" SSH_KEY = os.path.expanduser("~/.ssh/my_ssh_key") REMOTE_DB_PATH = "/var/lib/docker/volumes/my_volume/_data/my_db.db" current_dir = os.path.dirname(os.path.abspath(file)) LOCAL_DB_PATH = os.path.join(current_dir, "my_db.db")

def pull_db(): """Download the DB from the VPS to local machine.""" print(f"Downloading {REMOTE_DB_PATH} from {SSH_HOST} → {LOCAL_DB_PATH}") subprocess.run([ "scp", "-i", SSH_KEY, f"{SSH_USER}@{SSH_HOST}:{REMOTE_DB_PATH}", LOCAL_DB_PATH ], check=True) print("✅ Download complete.")

def push_db(): """Upload the local DB back to the VPS.""" if not os.path.exists(LOCAL_DB_PATH): print(f"❌ Local DB not found: {LOCAL_DB_PATH}") sys.exit(1) print(f"Uploading {LOCAL_DB_PATH} → {REMOTE_DB_PATH} on {SSH_HOST}") subprocess.run([ "scp", "-i", SSH_KEY, LOCAL_DB_PATH, f"{SSH_USER}@{SSH_HOST}:{REMOTE_DB_PATH}" ], check=True) print("✅ Upload complete.")

if name == "main": if len(sys.argv) != 2 or sys.argv[1] not in ["pull", "push"]: print("Usage: python index.py [pull|push]") sys.exit(1)