Useful SQL and PRAGMA Commands (SQLCipher)

Keying and rekeying (advanced)

DB Browser provides UI actions for encryption and passphrase changes. For reference, advanced users may use PRAGMAs in compatible environments.

-- Open an encrypted database with a passphrase (when supported)
PRAGMA key = 'your passphrase here';

-- Change passphrase (rekey)
PRAGMA rekey = 'your new passphrase here';

Prefer the built‑in UI for routine operations. Always back up before rekeying.

Check cipher parameters (read‑only)

-- Cipher version (if exposed)
PRAGMA cipher_version;

-- Typical parameters to verify (availability may vary)
PRAGMA cipher_page_size;   -- e.g., 4096
PRAGMA kdf_iter;           -- e.g., iteration count

If these are unavailable in your build, check settings in the environment where the DB was created, or use project documentation.

Export a plaintext copy (handle with care)

To create a non‑encrypted copy for interoperability. Handle the plaintext output securely and remove when done.

-- Attach a new plaintext database and export
ATTACH DATABASE 'plain_copy.db' AS plaintext KEY '';
SELECT sqlcipher_export('plaintext');
DETACH DATABASE plaintext;

Verify the output by reopening the copy. Consider redacting sensitive fields before export.

Integrity checks

-- Quick check (general)
PRAGMA quick_check;

-- Full integrity check (general)
PRAGMA integrity_check;

-- Cipher integrity (if supported by your build)
PRAGMA cipher_integrity_check;

Run checks after unexpected shutdowns or when corruption is suspected. Always keep backups.

Performance PRAGMAs (tune and test)

-- WAL can improve read concurrency
PRAGMA journal_mode = WAL;

-- Balance durability and speed
PRAGMA synchronous = NORMAL;  -- test FULL vs NORMAL

-- Cache size (negative = KiB)
PRAGMA cache_size = -65536;   -- ~64 MiB cache (adjust)

-- Temp store in memory (if RAM allows)
PRAGMA temp_store = MEMORY;

Always test with realistic data and workloads. See Performance tips for context and caveats.

Analyze queries (indexes & plans)

-- See how a query will run
EXPLAIN QUERY PLAN
SELECT *
FROM your_table
WHERE columnA = ?
ORDER BY created_at DESC;

Create indexes that match WHERE/JOIN/ORDER BY usage. Avoid over‑indexing; it slows writes and uses disk.

Next steps