Self-Hosted SQLite

sudo dnf update -y

# Install SQLite and development headers
sudo dnf install -y sqlite sqlite-devel sqlite-libs

# For legacy CentOS 7 systems using yum:
# sudo yum install -y sqlite sqlite-devel

sqlite3 --version

# Install SQLite documentation
sudo dnf install -y sqlite-doc

# Install EPEL for additional tools
sudo dnf install -y epel-release

# Install sqlitebrowser GUI (if desktop environment is available)
sudo dnf install -y sqlitebrowser

sudo apt update && sudo apt upgrade -y

# Install SQLite3 and development files
sudo apt install -y sqlite3 libsqlite3-dev

sqlite3 --version

# Install SQLite documentation
sudo apt install -y sqlite3-doc

# Install sqlitebrowser GUI (if desktop available)
sudo apt install -y sqlitebrowser

# Install litecli for enhanced CLI experience
sudo apt install -y python3-pip
pip3 install litecli

# Create a new database (creates file if it doesn't exist)
sqlite3 /var/lib/sqlite/myapp.db

# Or create an in-memory database for testing
sqlite3 :memory:

-- Create a sample table
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (username, email) VALUES ('admin', 'admin@example.com');
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');

-- Query data
SELECT * FROM users;

-- Enable Write-Ahead Logging (WAL) for better concurrency
PRAGMA journal_mode = WAL;

-- Set synchronous mode for performance/durability balance
PRAGMA synchronous = NORMAL;

-- Increase cache size (negative = KB, positive = pages)
PRAGMA cache_size = -64000;  -- 64MB cache

-- Enable memory-mapped I/O for large databases
PRAGMA mmap_size = 268435456;  -- 256MB

-- Enable foreign key enforcement
PRAGMA foreign_keys = ON;

-- Set busy timeout for concurrent access
PRAGMA busy_timeout = 5000;

# Create configuration script
cat > /usr/local/bin/sqlite-init.sql << 'EOF'
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA temp_store = MEMORY;
EOF

# Apply configuration when opening a database
sqlite3 myapp.db < /usr/local/bin/sqlite-init.sql

# Create dedicated directory for SQLite databases
sudo mkdir -p /var/lib/sqlite
sudo chmod 750 /var/lib/sqlite

# Set ownership for your application user
sudo chown www-data:www-data /var/lib/sqlite  # For web apps
# Or for custom application user:
# sudo chown appuser:appuser /var/lib/sqlite

# Set restrictive permissions on database files
chmod 640 /var/lib/sqlite/myapp.db
chmod 640 /var/lib/sqlite/myapp.db-wal
chmod 640 /var/lib/sqlite/myapp.db-shm

# Secure the database directory
chmod 750 /var/lib/sqlite

sudo apt install -y sqlcipher libsqlcipher-dev

sudo dnf install -y openssl-devel tcl
git clone https://github.com/sqlcipher/sqlcipher.git
cd sqlcipher
./configure --enable-tempstore=yes \
  CFLAGS="-DSQLITE_HAS_CODEC" \
  LDFLAGS="-lcrypto"
make && sudo make install

# Create encrypted database
sqlcipher encrypted.db
sqlite> PRAGMA key = 'your-secure-passphrase';
sqlite> CREATE TABLE secrets (id INTEGER PRIMARY KEY, data TEXT);
sqlite> .exit

# CORRECT (parameterized)
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

# WRONG (vulnerable to injection)
# cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

# Method 1: SQLite backup command (hot backup, safe)
sqlite3 myapp.db ".backup /backups/myapp-$(date +%Y%m%d).db"

# Method 2: Export to SQL dump
sqlite3 myapp.db .dump > /backups/myapp-$(date +%Y%m%d).sql

# Method 3: File copy (only when database is not in use)
cp myapp.db myapp.db-wal myapp.db-shm /backups/

# Method 4: Using VACUUM INTO (SQLite 3.27+)
sqlite3 myapp.db "VACUUM INTO '/backups/myapp-backup.db';"

#!/bin/bash
# /usr/local/bin/sqlite-backup.sh

DB_PATH="/var/lib/sqlite/myapp.db"
BACKUP_DIR="/backups/sqlite"
RETENTION_DAYS=30

# Create backup directory if needed
mkdir -p "$BACKUP_DIR"

# Create timestamped backup
BACKUP_FILE="$BACKUP_DIR/myapp-$(date +%Y%m%d-%H%M%S).db"
sqlite3 "$DB_PATH" ".backup '$BACKUP_FILE'"

# Compress backup
gzip "$BACKUP_FILE"

# Remove old backups
find "$BACKUP_DIR" -name "*.db.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: ${BACKUP_FILE}.gz"

# Make script executable
chmod +x /usr/local/bin/sqlite-backup.sh

# Add to crontab (daily at 2 AM)
echo "0 2 * * * /usr/local/bin/sqlite-backup.sh" | sudo crontab -

# From .db backup file
cp /backups/myapp-20240115.db /var/lib/sqlite/myapp.db

# From compressed backup
gunzip -c /backups/myapp-20240115.db.gz > /var/lib/sqlite/myapp.db

# From SQL dump
sqlite3 /var/lib/sqlite/myapp.db < /backups/myapp-20240115.sql

# Check database integrity
sqlite3 myapp.db "PRAGMA integrity_check;"

# Quick integrity check
sqlite3 myapp.db "PRAGMA quick_check;"

# Check for foreign key violations
sqlite3 myapp.db "PRAGMA foreign_key_check;"

# Rebuild database to reclaim space and defragment
sqlite3 myapp.db "VACUUM;"

# Update query planner statistics
sqlite3 myapp.db "ANALYZE;"

# Optimize all tables (SQLite 3.18+)
sqlite3 myapp.db "PRAGMA optimize;"

# Check file sizes
ls -lh /var/lib/sqlite/myapp.db*

# Get page statistics
sqlite3 myapp.db "
SELECT
  page_count * page_size as total_bytes,
  freelist_count * page_size as free_bytes
FROM pragma_page_count(), pragma_freelist_count(), pragma_page_size();"

# Table sizes
sqlite3 myapp.db "
SELECT name, SUM(pgsize) as size
FROM dbstat GROUP BY name ORDER BY size DESC;"

import sqlite3

# Connect with context manager
with sqlite3.connect('/var/lib/sqlite/myapp.db') as conn:
    conn.execute('PRAGMA journal_mode=WAL')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users WHERE id = ?', (1,))
    result = cursor.fetchone()

<?php
$db = new SQLite3('/var/lib/sqlite/myapp.db');
$db->exec('PRAGMA journal_mode=WAL');

$stmt = $db->prepare('SELECT * FROM users WHERE id = :id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);
$result = $stmt->execute();
?>

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

const db = sqlite3('/var/lib/sqlite/myapp.db');
db.pragma('journal_mode = WAL');

const user = db.prepare('SELECT * FROM users WHERE id = ?').get(1);