SQLite Troubleshooting

Caution

SQLite disables foreign keys by default. Add to fraiseql.toml to enable them automatically:

[database.sqlite]
foreign_keys = true

Or run PRAGMA foreign_keys = ON; on every connection. Verify with PRAGMA foreign_keys; — should return 1.

Solutions for SQLite-specific issues with FraiseQL.

Connection Issues

Database File Not Found

Problem: Error: cannot open database file

Cause: Database file path incorrect or doesn’t exist

Solutions:

# 1. Check file exists
ls -la /path/to/fraiseql.db

# 2. Verify DATABASE_URL format
echo $DATABASE_URL
# Should be: sqlite:///path/to/fraiseql.db (3 slashes)
# Or: sqlite:///:memory: (in-memory, for testing)

# 3. Create database if missing
touch /path/to/fraiseql.db

# 4. Check file permissions
chmod 666 /path/to/fraiseql.db

# 5. Check directory permissions
chmod 755 $(dirname /path/to/fraiseql.db)

Database is Locked

Problem: Error: database is locked

Cause: Concurrent access or lock timeout

Solutions:

# 1. Find what's locking database
lsof /path/to/fraiseql.db

# 2. Increase lock timeout
DATABASE_URL="sqlite:////path/to/fraiseql.db?timeout=20"

# 3. Restart application
docker-compose restart fraiseql

# 4. Check for corrupted lock file
ls -la /path/to/fraiseql.db*
# Delete .db-journal if exists (carefully)
rm -f /path/to/fraiseql.db-journal

# 5. Enable WAL mode for better concurrency
sqlite3 /path/to/fraiseql.db "PRAGMA journal_mode=WAL;"

Caution

SQLite allows only one concurrent writer. If you see locking errors frequently, consider migrating to PostgreSQL or MySQL for production.

Disk I/O Error

Problem: Error: disk I/O error

Cause: Disk problem, permission issue, or file system issue

Solutions:

# 1. Check disk space
df -h

# 2. Check file permissions
ls -la /path/to/fraiseql.db

# 3. Run disk check
sudo fsck /dev/sda1

# 4. Repair corrupted database
sqlite3 /path/to/fraiseql.db ".recover" | sqlite3 /tmp/recovered.db

# 5. Verify database integrity
sqlite3 /path/to/fraiseql.db "PRAGMA integrity_check;"

# 6. Check temp directory space
df -h /tmp

Out of Memory

Problem: Error: out of memory

Solutions:

# 1. Check available memory
free -h
docker stats fraiseql

# 2. Limit query results
-- Instead of: SELECT * FROM large_table
-- Use: SELECT * FROM large_table LIMIT 1000

# 3. Reduce page cache
sqlite3 /path/to/fraiseql.db "PRAGMA cache_size = 1000;"

---

Query Issues

Slow Queries

Problem: p95 latency > 500ms

Solutions:

# 1. Analyze query plan
sqlite3 /path/to/fraiseql.db "EXPLAIN QUERY PLAN SELECT * FROM posts WHERE user_id = 1;"

# 2. Create index if missing
sqlite3 /path/to/fraiseql.db "CREATE INDEX idx_posts_user_id ON posts(user_id);"

# 3. Update statistics
sqlite3 /path/to/fraiseql.db "ANALYZE;"

# 4. Reindex if corrupted
sqlite3 /path/to/fraiseql.db "REINDEX;"

Full Table Scan

Problem: “SCAN TABLE posts” in query plan

Solutions:

# 1. Add index on WHERE column
sqlite3 /path/to/fraiseql.db "CREATE INDEX idx_posts_user_id ON posts(user_id);"

# 2. Analyze table
sqlite3 /path/to/fraiseql.db "ANALYZE posts;"

# 3. Check query plan
sqlite3 /path/to/fraiseql.db "EXPLAIN QUERY PLAN SELECT * FROM posts WHERE user_id = 1;"

# 4. Force index use if needed
SELECT * FROM posts INDEXED BY idx_posts_user_id WHERE user_id = 1;

---

Data Issues

Foreign Key Constraint Violation

Problem: Error: FOREIGN KEY constraint failed

Note

Foreign keys are disabled by default in SQLite. You must run PRAGMA foreign_keys = ON; after every connection to enforce referential integrity.

Solutions:

# 1. Enable foreign keys (default is disabled!)
sqlite3 /path/to/fraiseql.db "PRAGMA foreign_keys = ON;"

# 2. Check if enabled
sqlite3 /path/to/fraiseql.db "PRAGMA foreign_keys;"

# 3. Check foreign key definitions
sqlite3 /path/to/fraiseql.db ".schema posts"

# 4. Verify referenced records exist
sqlite3 /path/to/fraiseql.db "SELECT * FROM posts WHERE id = 123;"

# 5. Find orphaned records
sqlite3 /path/to/fraiseql.db "
SELECT c.* FROM comments c
LEFT JOIN posts p ON c.post_id = p.id
WHERE p.id IS NULL;"

# 6. Delete orphaned records
sqlite3 /path/to/fraiseql.db "
DELETE FROM comments
WHERE post_id NOT IN (SELECT id FROM posts);"

# 7. For FraiseQL, enable in app initialization
conn.execute("PRAGMA foreign_keys = ON")

CASCADE Delete Not Working

Problem: Deleting parent doesn’t delete children

Solutions:

# 1. Check table definition
sqlite3 /path/to/fraiseql.db ".schema comments"
# Should show: REFERENCES posts(id) ON DELETE CASCADE

# 2. Enable foreign keys
sqlite3 /path/to/fraiseql.db "PRAGMA foreign_keys = ON;"

# 3. Recreate table with correct CASCADE
sqlite3 /path/to/fraiseql.db "
-- Backup data
CREATE TABLE comments_backup AS SELECT * FROM comments;

-- Drop old table
DROP TABLE comments;

-- Recreate with CASCADE
CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    text TEXT
);

-- Restore data
INSERT INTO comments SELECT * FROM comments_backup;

-- Cleanup
DROP TABLE comments_backup;"

# 4. Test CASCADE
sqlite3 /path/to/fraiseql.db "
BEGIN TRANSACTION;
DELETE FROM posts WHERE id = 1;
-- Check if comments deleted
SELECT COUNT(*) FROM comments WHERE post_id = 1;
ROLLBACK;"

Duplicate Key Error

Problem: Error: UNIQUE constraint failed

Solutions:

# 1. Check unique constraints
sqlite3 /path/to/fraiseql.db "PRAGMA index_list(users);"

# 2. Find duplicate values
sqlite3 /path/to/fraiseql.db "
SELECT email, COUNT(*) as cnt FROM users
GROUP BY email HAVING cnt > 1;"

# 3. Remove duplicates (keep one)
sqlite3 /path/to/fraiseql.db "
DELETE FROM users WHERE rowid NOT IN (
    SELECT MIN(rowid) FROM users GROUP BY email);"

# 4. Add unique constraint
sqlite3 /path/to/fraiseql.db "
CREATE UNIQUE INDEX idx_users_email ON users(email);"

NULL in NOT NULL Column

Problem: Error: NOT NULL constraint failed

Solutions:

# 1. Check column definition
sqlite3 /path/to/fraiseql.db ".schema posts"

# 2. Find NULL values
sqlite3 /path/to/fraiseql.db "SELECT * FROM posts WHERE title IS NULL;"

# 3. Fix NULL values
sqlite3 /path/to/fraiseql.db "UPDATE posts SET title = 'Untitled' WHERE title IS NULL;"

# Note: SQLite doesn't allow adding NOT NULL to an existing column that has NULLs
# Solution: Migrate the table carefully

---

Transaction Issues

Transaction Deadlock

Problem: Error: database is locked or timeouts

Cause: Concurrent writers (SQLite limitation)

Solutions:

# 1. Increase timeout
DATABASE_URL="sqlite:////path/to/fraiseql.db?timeout=30"

# 2. Enable WAL mode (better concurrency)
sqlite3 /path/to/fraiseql.db "PRAGMA journal_mode=WAL;"

# 3. Minimize transaction scope
-- BAD: Long transaction
BEGIN;
SELECT COUNT(*) FROM posts;
-- Long operation
UPDATE posts SET title = 'New';
COMMIT;

-- GOOD: Short transaction
BEGIN;
UPDATE posts SET title = 'New' WHERE id = 1;
COMMIT;
-- Long operation outside transaction

---

Index Issues

Unused Indexes

Problem: Taking space without improving performance

Solutions:

# 1. List all indexes
sqlite3 /path/to/fraiseql.db ".indices"

# 2. SQLite doesn't track index usage statistics
# Manually review and remove indexes that are not needed

# 3. Drop unused index
sqlite3 /path/to/fraiseql.db "DROP INDEX idx_old_index;"

# 4. Check database size
ls -lh /path/to/fraiseql.db

Index Corruption

Problem: Query crashes or returns wrong results

Solutions:

# 1. Verify database integrity
sqlite3 /path/to/fraiseql.db "PRAGMA integrity_check;"

# 2. Reindex all
sqlite3 /path/to/fraiseql.db "REINDEX;"

# 3. Drop and recreate specific index
sqlite3 /path/to/fraiseql.db "
DROP INDEX idx_posts_user_id;
CREATE INDEX idx_posts_user_id ON posts(user_id);"

# 4. Vacuum to compact database
sqlite3 /path/to/fraiseql.db "VACUUM;"

---

Maintenance

Database File Growth

Problem: Database file keeps growing

Solutions:

# 1. Check current size
ls -lh /path/to/fraiseql.db

# 2. Vacuum to reclaim space
sqlite3 /path/to/fraiseql.db "VACUUM;"

# 3. Archive old data
sqlite3 /path/to/fraiseql.db "
DELETE FROM audit_logs WHERE created_at < datetime('now', '-1 year');"

# 4. Check free pages
sqlite3 /path/to/fraiseql.db "PRAGMA freelist_count;"

# 5. Enable incremental vacuum (WAL mode)
sqlite3 /path/to/fraiseql.db "PRAGMA journal_mode=WAL;"
sqlite3 /path/to/fraiseql.db "PRAGMA incremental_vacuum(1000);"

Backup Strategy

Solutions:

# 1. Simple file copy (if app not running)
cp /path/to/fraiseql.db /backups/fraiseql-$(date +%Y%m%d).db

# 2. SQL dump (works while running)
sqlite3 /path/to/fraiseql.db ".dump" > backup.sql

# 3. Backup with WAL files
cp /path/to/fraiseql.db* /backups/

# 4. Restore from SQL dump
sqlite3 /path/to/new.db < backup.sql

# 5. Restore from file copy
cp /backups/fraiseql-20240115.db /path/to/fraiseql.db

---

Performance Tuning

Journal Mode

Problem: Slow writes or frequent locking

Caution

WAL mode does not work on network filesystems (NFS). If deploying SQLite in Docker with a mounted volume, ensure the volume uses local storage, not NFS or similar network-attached storage. WAL mode corruption on NFS is a known SQLite limitation.

Solutions:

# 1. Check current journal mode
sqlite3 /path/to/fraiseql.db "PRAGMA journal_mode;"

# 2. Switch to WAL (Write-Ahead Logging)
sqlite3 /path/to/fraiseql.db "PRAGMA journal_mode=WAL;"
# Better for concurrent reads/writes

# 3. Synchronous mode
sqlite3 /path/to/fraiseql.db "PRAGMA synchronous=NORMAL;"
# Default: FULL (safest)
# NORMAL: Good balance
# OFF: Fastest but risky

Cache Size

Problem: High I/O or memory pressure

Solutions:

# 1. Check current cache size
sqlite3 /path/to/fraiseql.db "PRAGMA cache_size;"

# 2. Increase cache (negative value = MB)
sqlite3 /path/to/fraiseql.db "PRAGMA cache_size = -10000;"  # 10MB cache

# 3. For embedded use
sqlite3 /path/to/fraiseql.db "PRAGMA cache_size = 1000;"    # 1MB cache

---

When to Migrate from SQLite

SQLite is ideal for development but has production limitations:

Migrate to PostgreSQL or MySQL if:

• Multiple writers (SQLite allows one writer at a time)
• High concurrency (more than 100 concurrent users)
• Data size exceeds 10GB
• Replication or failover is required
• Production deployment is required

Keep SQLite if:

• Single application instance
• Low to moderate load
• Development or testing environment
• Embedded or mobile applications
• Data is less than 1GB

---

Diagnostic Queries

Run PRAGMA integrity_check; to verify database integrity:

sqlite> PRAGMA integrity_check;
ok

ok confirms the database has no corruption. Any other output indicates an issue.

-- Database integrity
PRAGMA integrity_check;

-- Foreign keys enabled?
PRAGMA foreign_keys;

-- Journal mode
PRAGMA journal_mode;

-- Cache info
PRAGMA cache_size;

-- Table info
PRAGMA table_info(posts);

-- Index info
PRAGMA index_info(idx_posts_user_id);

-- Database size
PRAGMA page_count;
PRAGMA page_size;

-- Free space
PRAGMA freelist_count;

Common Issues

General FraiseQL troubleshooting

SQLite Documentation

Official SQLite docs

PostgreSQL Guide

Migrate to PostgreSQL for production