Skip to main content

​
SQLite Databases

SQLite is a self-contained, file-based database engine. No server required. The entire database lives in a single file on your Mac.

​
Quick Setup

1

Create Connection

Click New Connection, select SQLite, browse for your .sqlite/.db/.sqlite3 file, and click Create
File-based database, no server/auth required. Double-click .duckdb files in Finder to open directly.

​
Common Locations

macOS apps: Safari (~/Library/Safari/History.db), Photos (~/Pictures/Photos Library.photoslibrary/database/Photos.sqlite), Messages (~/Library/Messages/chat.db) Development: Rails (./db/development.sqlite3), Django (./db.sqlite3), iOS Simulator (~/Library/Developer/CoreSimulator/...), Core Data Create new: Point to a non-existent file path and TablePro creates it on connect. Or use sqlite3 ~/path/to/new.db "SELECT 1;"
System databases may be locked. Quit their parent app before opening.

​
Features

Sidebar shows tables, views, and system tables (sqlite_master, sqlite_sequence). Table info displays structure (columns, constraints), indexes, and DDL. Full SQLite syntax support: 
-- JSON functions (SQLite 3.38+)
SELECT json_extract(data, '$.name') as name
FROM users
WHERE json_extract(data, '$.active') = true;

-- Window functions
SELECT
    category,
    product,
    price,
    rank() OVER (PARTITION BY category ORDER BY price DESC) as rank
FROM products;

-- CTEs
WITH RECURSIVE
    cnt(x) AS (
        SELECT 1
        UNION ALL
        SELECT x+1 FROM cnt WHERE x < 10
    )
SELECT x FROM cnt;

-- Full-text search (if FTS enabled)
SELECT * FROM documents WHERE documents MATCH 'sqlite AND database';

-- UPSERT (INSERT OR REPLACE)
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;

​
Type System

SQLite uses dynamic typing with affinity: TEXT (strings), INTEGER (ints), REAL (floats), NUMERIC (flexible), BLOB (binary). Types are hints, not strict constraints.

​
Performance

Enable pagination for large tables, add indexes on frequently queried columns, use LIMIT in exploratory queries. Compact with VACUUM; and update stats with ANALYZE;.

​
Troubleshooting

Locked database: Close other apps using it, wait for queries to finish, or check for WAL files (database.sqlite-wal, database.sqlite-shm). Can’t open: Verify path exists, check permissions (chmod 644), avoid special characters in paths. Corrupt database: Run PRAGMA integrity_check; to check, try .recover to repair: sqlite3 corrupt.sqlite ".recover" | sqlite3 recovered.sqlite Changes not visible: Right-click connection and select Refresh, or disconnect/reconnect. PRAGMA commands: Check version (SELECT sqlite_version();), table info (PRAGMA table_info(users);), enable foreign keys (PRAGMA foreign_keys = ON;), switch to WAL mode (PRAGMA journal_mode = WAL;) Backup: Copy file directly, use sqlite3 db.sqlite ".backup backup.sqlite" to handle locks safely, or sqlite3 db.sqlite .dump > backup.sql for SQL export.