How to fix SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified in SQLite

# List database and WAL-related files
ls -la /path/to/database.db*

# Example output:
# -rw-r--r-- 1 user group 8192 Dec 22 database.db
# -rw-r--r-- 1 user group 4096 Dec 22 database.db-wal
# -rw-r--r-- 1 user group  512 Dec 22 database.db-shm

# Check containing directory permissions
ls -ld /path/to/

# Output should show write permissions: drwxr-xr-x or similar

# Grant read/write permissions to database files
chmod 664 /path/to/database.db
chmod 664 /path/to/database.db-wal
chmod 664 /path/to/database.db-shm

# Grant read/write/execute permissions on directory
chmod 775 /path/to/

# If needed, change ownership to the process user
sudo chown user:group /path/to/database.db*
sudo chown user:group /path/to/

# Example: For a web application running as www-data
sudo chown -R www-data:www-data /path/to/database/
sudo chmod -R 775 /path/to/database/

# Verify permissions
su - www-data -s /bin/bash -c "ls -la /path/to/database.db"

import os
import stat

db_path = '/path/to/database.db'

# Check if file is writable
if os.access(db_path, os.W_OK):
    print(f"{db_path} is writable")
else:
    print(f"{db_path} is NOT writable")

# Check directory permissions
db_dir = os.path.dirname(db_path)
if os.access(db_dir, os.W_OK | os.X_OK):
    print(f"Directory {db_dir} is writable and executable")
else:
    print(f"Directory {db_dir} lacks necessary permissions")

# Open database to trigger recovery
sqlite3 /path/to/database.db "PRAGMA integrity_check;"

# Check WAL mode status
sqlite3 /path/to/database.db "PRAGMA journal_mode;"

# Force WAL checkpoint to merge changes
sqlite3 /path/to/database.db "PRAGMA wal_checkpoint(FULL);"

import sqlite3

# Open database with write permissions to allow recovery
conn = sqlite3.connect('/path/to/database.db')
cursor = conn.cursor()

# Check database integrity
cursor.execute("PRAGMA integrity_check")
print("Integrity check:", cursor.fetchone()[0])

# Verify journal mode
cursor.execute("PRAGMA journal_mode")
print("Journal mode:", cursor.fetchone()[0])

# Checkpoint WAL file (merge into main database)
cursor.execute("PRAGMA wal_checkpoint(TRUNCATE)")
result = cursor.fetchone()
print(f"Checkpoint result: {result}")
# Returns (busy, log_size, checkpointed_frames)

conn.close()
print("Recovery complete")

const Database = require('better-sqlite3');

// Open database with write permissions
const db = new Database('/path/to/database.db');

// Check integrity
const integrity = db.pragma('integrity_check');
console.log('Integrity check:', integrity);

// Checkpoint WAL
const checkpoint = db.pragma('wal_checkpoint(TRUNCATE)');
console.log('Checkpoint result:', checkpoint);

db.close();
console.log('Recovery complete');

# Convert from WAL to DELETE mode
sqlite3 /path/to/database.db "PRAGMA journal_mode=DELETE;"

# Verify the change
sqlite3 /path/to/database.db "PRAGMA journal_mode;"
# Should output: delete

# Vacuum to clean up (optional but recommended)
sqlite3 /path/to/database.db "VACUUM;"

# Remove WAL and SHM files (they're no longer needed)
rm -f /path/to/database.db-wal
rm -f /path/to/database.db-shm

import sqlite3
import os

db_path = '/path/to/database.db'

# Connect to database
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# Convert to DELETE mode
cursor.execute("PRAGMA journal_mode=DELETE")
new_mode = cursor.fetchone()[0]
print(f"Journal mode changed to: {new_mode}")

# Checkpoint and cleanup
cursor.execute("PRAGMA wal_checkpoint(TRUNCATE)")
print("WAL checkpoint complete")

# Optional: vacuum to optimize
cursor.execute("VACUUM")
print("Database vacuumed")

conn.close()

# Remove WAL and SHM files
wal_file = db_path + '-wal'
shm_file = db_path + '-shm'

if os.path.exists(wal_file):
    os.remove(wal_file)
    print(f"Removed {wal_file}")

if os.path.exists(shm_file):
    os.remove(shm_file)
    print(f"Removed {shm_file}")

print("Database ready for read-only deployment")

const Database = require('better-sqlite3');
const fs = require('fs');
const path = require('path');

const dbPath = '/path/to/database.db';
const db = new Database(dbPath);

// Convert to DELETE mode
const result = db.pragma('journal_mode = DELETE');
console.log('Journal mode:', result);

// Checkpoint and vacuum
db.pragma('wal_checkpoint(TRUNCATE)');
db.pragma('vacuum');

db.close();

// Remove auxiliary files
const walFile = dbPath + '-wal';
const shmFile = dbPath + '-shm';

[walFile, shmFile].forEach(file => {
  if (fs.existsSync(file)) {
    fs.unlinkSync(file);
    console.log(`Removed ${path.basename(file)}`);
  }
});

console.log('Database converted to DELETE mode');

import sqlite3
import os

def open_database_readonly(db_path):
    """
    Open database in read-only mode with proper error handling.
    Returns connection if successful, None otherwise.
    """
    try:
        # First, try read-only mode
        conn = sqlite3.connect(f'file:{db_path}?mode=ro', uri=True)
        return conn
    except sqlite3.OperationalError as e:
        if 'SQLITE_READONLY_RECOVERY' in str(e):
            print(f"WAL recovery needed for {db_path}")

            # Check if we can perform recovery
            if os.access(db_path, os.W_OK):
                print("Attempting WAL recovery...")
                # Open with write permissions to complete recovery
                temp_conn = sqlite3.connect(db_path)
                temp_conn.execute("PRAGMA wal_checkpoint(FULL)")
                temp_conn.close()

                # Now try read-only mode again
                return sqlite3.connect(f'file:{db_path}?mode=ro', uri=True)
            else:
                print(f"Cannot recover: insufficient permissions on {db_path}")
                return None
        else:
            raise  # Re-raise other errors

# Usage
conn = open_database_readonly('/path/to/database.db')
if conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM table LIMIT 10")
    print(cursor.fetchall())
    conn.close()
else:
    print("Failed to open database")

const Database = require('better-sqlite3');
const fs = require('fs');

function openDatabaseReadonly(dbPath) {
  try {
    // Attempt read-only connection
    const db = new Database(dbPath, { readonly: true });
    return db;
  } catch (error) {
    if (error.message.includes('SQLITE_READONLY_RECOVERY')) {
      console.log(`WAL recovery needed for ${dbPath}`);

      // Check if file is writable
      try {
        fs.accessSync(dbPath, fs.constants.W_OK);

        // Perform recovery with write access
        console.log('Attempting WAL recovery...');
        const tempDb = new Database(dbPath);
        tempDb.pragma('wal_checkpoint(FULL)');
        tempDb.close();

        // Retry read-only connection
        return new Database(dbPath, { readonly: true });
      } catch (permError) {
        console.error('Cannot recover: insufficient permissions');
        return null;
      }
    } else {
      throw error;  // Re-throw other errors
    }
  }
}

// Usage
const db = openDatabaseReadonly('/path/to/database.db');
if (db) {
  const rows = db.prepare('SELECT * FROM table LIMIT 10').all();
  console.log(rows);
  db.close();
} else {
  console.log('Failed to open database');
}

// React Native example
import RNFS from 'react-native-fs';
import SQLite from 'react-native-sqlite-storage';

async function setupDatabase() {
  const bundleDbPath = RNFS.MainBundlePath + '/database.db';
  const writableDbPath = RNFS.DocumentDirectoryPath + '/database.db';

  // Check if database needs to be copied
  const exists = await RNFS.exists(writableDbPath);
  if (!exists) {
    console.log('Copying database to writable location...');
    await RNFS.copyFile(bundleDbPath, writableDbPath);
  }

  // Open from writable location
  const db = await SQLite.openDatabase({
    name: 'database.db',
    location: 'default'  // Uses DocumentDirectory
  });

  return db;
}

# Dockerfile
FROM node:18

WORKDIR /app
COPY package*.json ./
RUN npm install

# Copy application code
COPY . .

# Create writable database directory
RUN mkdir -p /app/data && chmod 777 /app/data

# If bundling a database, convert to DELETE mode first
# COPY database.db /app/data/
# RUN chmod 666 /app/data/database.db

USER node
CMD ["node", "server.js"]

version: '3.8'
services:
  app:
    build: .
    volumes:
      # Mount database directory as writable volume
      - ./database:/app/data
    environment:
      - DATABASE_PATH=/app/data/database.db
    user: "node"

apiVersion: v1
kind: Pod
metadata:
  name: app
spec:
  containers:
  - name: app
    image: myapp:latest
    volumeMounts:
    - name: database-storage
      mountPath: /app/data
    securityContext:
      runAsUser: 1000
      runAsGroup: 1000
      fsGroup: 1000  # Ensures volume files are writable
  volumes:
  - name: database-storage
    persistentVolumeClaim:
      claimName: database-pvc

#!/bin/bash
# prepare-database.sh

DB_FILE="database.db"

# Convert to DELETE mode before deployment
sqlite3 "$DB_FILE" "PRAGMA journal_mode=DELETE;"
sqlite3 "$DB_FILE" "PRAGMA wal_checkpoint(TRUNCATE);"
sqlite3 "$DB_FILE" "VACUUM;"

# Remove WAL files
rm -f "$DB_FILE-wal" "$DB_FILE-shm"

# Set appropriate permissions
chmod 644 "$DB_FILE"

echo "Database prepared for deployment"

// Swift example: Copy database from bundle to Documents
func setupDatabase() throws -> URL {
    let fileManager = FileManager.default
    let documentsURL = fileManager.urls(for: .documentDirectory, in: .userDomainMask)[0]
    let destURL = documentsURL.appendingPathComponent("database.db")

    // Only copy if not already in Documents
    if !fileManager.fileExists(atPath: destURL.path) {
        guard let bundleURL = Bundle.main.url(forResource: "database", withExtension: "db") else {
            throw DatabaseError.bundleDatabaseNotFound
        }

        try fileManager.copyItem(at: bundleURL, to: destURL)
        print("Database copied to: \(destURL.path)")
    }

    return destURL
}

// Open database from writable location
let dbURL = try setupDatabase()
let db = try Connection(dbURL.path)

# .env file
DATABASE_PATH=/var/lib/app/database.db
DATABASE_WRITABLE=true

# Startup script
#!/bin/bash
# Ensure database directory exists and is writable
mkdir -p "$(dirname "$DATABASE_PATH")"
chmod 755 "$(dirname "$DATABASE_PATH")"

# Start application
node server.js

# Check if SELinux is enabled
getenforce
# Output: Enforcing, Permissive, or Disabled

# Check SELinux context of database files
ls -Z /path/to/database.db*

# Example output showing wrong context:
# -rw-rw-r--. user group unconfined_u:object_r:user_home_t:s0 database.db

# Set correct context for web application
sudo chcon -t httpd_sys_rw_content_t /path/to/database.db*
sudo chcon -t httpd_sys_rw_content_t /path/to/database/

# Or use semanage for persistent rules
sudo semanage fcontext -a -t httpd_sys_rw_content_t "/path/to/database(/.*)?"
sudo restorecon -R /path/to/database/

# Verify new context
ls -Z /path/to/database.db

# Check audit logs for denials
sudo ausearch -m avc -ts recent | grep database

# If denials found, use audit2allow to create policy
sudo ausearch -m avc -ts recent | audit2allow -M mydatabase
sudo semodule -i mydatabase.pp

# Or temporarily set to permissive mode for testing (NOT for production)
sudo setenforce 0

# Check if AppArmor is active
sudo aa-status

# Check for your application profile
sudo aa-status | grep your-app

# Edit profile (example for Node.js app)
sudo nano /etc/apparmor.d/usr.bin.node

# Add database access rules:
# /path/to/database/** rw,
# /path/to/database/*.db-wal rw,
# /path/to/database/*.db-shm rw,

# Reload profile
sudo apparmor_parser -r /etc/apparmor.d/usr.bin.node

# Or temporarily disable for testing
sudo aa-complain /usr/bin/node

# /etc/systemd/system/myapp.service
[Unit]
Description=My Application
After=network.target

[Service]
Type=simple
User=myapp
Group=myapp
WorkingDirectory=/opt/myapp
Environment="DATABASE_PATH=/var/lib/myapp/database.db"

# Ensure writable directory access
ReadWritePaths=/var/lib/myapp
# Or use StateDirectory to auto-create with correct permissions
StateDirectory=myapp

ExecStart=/usr/bin/node server.js

[Install]
WantedBy=multi-user.target

# Run container with proper SELinux context
docker run -v /path/to/database:/data:z myapp

# The :z flag relabels volume content for container access
# Use :Z for exclusive access by one container