SQLite Databases
SQLite is a self-contained, file-based database engine. Unlike MySQL or PostgreSQL, SQLite doesn’t require a server - the entire database is stored in a single file on your Mac.
Quick Setup
Open Connection Form
Click New Connection from the Welcome screen or File > New Connection
Select SQLite
Choose SQLite from the database type selector
Select Database File
Click Browse and select your .sqlite, .db, or .sqlite3 file
Create Connection
Give your connection a name and click Create
Connection Settings
SQLite connections are simple since no server is involved:
| Field | Description |
|---|
| Name | A friendly name for this connection |
| File Path | Full path to the SQLite database file |
No username, password, or network settings are needed for SQLite databases.
Common SQLite Locations
Application Databases
Many macOS applications use SQLite:
| Application | Database Location |
|---|
| Safari | ~/Library/Safari/History.db |
| Photos | ~/Pictures/Photos Library.photoslibrary/database/Photos.sqlite |
| Messages | ~/Library/Messages/chat.db |
| Notes | ~/Library/Group Containers/group.com.apple.notes/NoteStore.sqlite |
System databases may be locked while their parent applications are running. Quit the application before opening its database.
Development Databases
Common development database locations:
# Ruby on Rails
./db/development.sqlite3
# Django
./db.sqlite3
# iOS Simulator
~/Library/Developer/CoreSimulator/Devices/*/data/Containers/Data/Application/*/Documents/*.sqlite
# Core Data
*.sqlite (in app's container)
Creating a New Database
To create a new SQLite database:
- Create a new connection
- Enter a path for a file that doesn’t exist yet
- TablePro will create the file when you connect
Or use the command line:
sqlite3 ~/Documents/newdatabase.sqlite "SELECT 1;"
Features
Database Browser
The sidebar shows all tables and views in your SQLite database:
For each table, TablePro provides:
- Structure: Column names, types, and constraints
- Indexes: All indexes including UNIQUE constraints
- DDL: The CREATE TABLE statement
SQLite System Tables
TablePro can display SQLite’s internal tables:
| Table | Purpose |
|---|
sqlite_master | Schema information |
sqlite_sequence | AUTOINCREMENT tracking |
Query Execution
Execute queries with 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;
SQLite Data Types
SQLite uses a dynamic type system with type affinity:
| Affinity | Description | Examples |
|---|
| TEXT | String data | VARCHAR, TEXT, CLOB |
| INTEGER | Signed integer | INT, INTEGER, BIGINT |
| REAL | Floating point | REAL, DOUBLE, FLOAT |
| NUMERIC | Numeric or text | DECIMAL, BOOLEAN, DATE |
| BLOB | Binary data | BLOB, raw bytes |
SQLite accepts any data type in any column (except INTEGER PRIMARY KEY). The type is a hint, not a constraint.
Working with Large Files
File Size
SQLite databases can grow quite large. TablePro handles:
- Databases up to several GB
- Tables with millions of rows
- BLOB data (images, files)
- Use pagination: Enable pagination in settings for large tables
- Add indexes: Create indexes for frequently queried columns
- Use LIMIT: Always limit result sets when exploring
- Vacuum periodically: Reclaim space with
VACUUM
-- Check database size
SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();
-- Analyze tables for query optimization
ANALYZE;
-- Reclaim unused space
VACUUM;
Troubleshooting
Database is Locked
Symptoms: “database is locked” or “database table is locked”
Causes:
- Another application has the database open
- A previous query is still running
- Write-ahead logging (WAL) mode conflicts
Solutions:
- Close other applications using the database
- Wait for running queries to complete
- Check for WAL files:
ls -la /path/to/database.sqlite*
# You might see: database.sqlite-wal, database.sqlite-shm
Cannot Open Database
Symptoms: “unable to open database file”
Causes and Solutions:
- File doesn’t exist: Verify the path is correct
- Permission denied: Check file permissions
ls -la /path/to/database.sqlite
chmod 644 /path/to/database.sqlite
- Path contains special characters: Use absolute paths without spaces
Corrupt Database
Symptoms: “database disk image is malformed”
Recovery options:
-
Check integrity:
-
Attempt recovery:
sqlite3 corrupt.sqlite ".recover" | sqlite3 recovered.sqlite
-
Restore from backup (if available)
Schema Changes Not Visible
Symptoms: Tables or columns you created aren’t showing
Solution: Refresh the connection:
- Right-click the connection
- Select Refresh
Or disconnect and reconnect.
SQLite Pragmas
TablePro supports SQLite PRAGMA commands for configuration:
-- Check SQLite version
SELECT sqlite_version();
-- Show table info
PRAGMA table_info(users);
-- Check foreign keys are enabled
PRAGMA foreign_keys;
-- Enable foreign key enforcement
PRAGMA foreign_keys = ON;
-- Check journal mode
PRAGMA journal_mode;
-- Use WAL mode for better concurrency
PRAGMA journal_mode = WAL;
Backup and Restore
Manual Backup
SQLite databases are just files. To backup:
# Simple copy (ensure database is not being written to)
cp database.sqlite database-backup.sqlite
# SQLite backup command (safer, handles locks)
sqlite3 database.sqlite ".backup backup.sqlite"
Export to SQL
Export your database as SQL statements:
sqlite3 database.sqlite .dump > backup.sql
Restore from SQL
sqlite3 newdatabase.sqlite < backup.sql
Differences from Server Databases
| Feature | SQLite | MySQL/PostgreSQL |
|---|
| Server required | No | Yes |
| Multiple connections | Limited | Unlimited |
| User authentication | No | Yes |
| Network access | File-based | TCP/IP |
| Concurrent writes | Limited | Full support |
| Stored procedures | No | Yes |
| Data types | Dynamic | Static |
Common Use Cases
SQLite is ideal for:
- Local development: Prototype applications quickly
- Embedded apps: Mobile apps, desktop apps
- Single-user apps: Personal databases
- Data analysis: Quick data exploration
- Testing: Unit tests with isolated databases
- Prototyping: Before migrating to a server database
Next Steps
