Local Database (SQLite)

Manage local SQLite databases via the bundled localdb.py script. Databases are stored in ~/.openclaw/databases/.

When to use

✅ USE this skill when:

• User asks to "create a database", "store data", "create a table"
• User wants to query structured local data
• User needs relationships between data (foreign keys)
• User asks to track records, patients, inventory, contacts, etc.
• User wants a persistent local data store

When NOT to use

❌ DON'T use this skill when:

• User wants to search document content semantically → use rag-store
• User needs cloud/remote database access → use appropriate cloud tools
• User wants key-value or config storage → use files/JSON directly
• Data is unstructured (free text, PDFs) → use rag-store

Key safety rules

1. NEVER drop tables without explicit user confirmation
2. ALWAYS use migrations for schema changes (not raw ALTER/DROP)
3. ALWAYS backup before destructive operations
4. Foreign keys are enforced (PRAGMA foreign_keys = ON)
5. All schema changes are recorded in _migrations table
6. Destructive SQL (DROP, DELETE, TRUNCATE) requires --allow-destructive flag
7. ALWAYS ask the user for confirmation before using --allow-destructive

Commands

Create a database

python3 {baseDir}/scripts/localdb.py create mydata

List databases

python3 {baseDir}/scripts/localdb.py list

Apply a migration (safe schema change)

Always use migrations to create or alter tables:

# Create tables
python3 {baseDir}/scripts/localdb.py migrate mydata -d "Create patients table" -s "CREATE TABLE IF NOT EXISTS patients (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, phone TEXT, active INTEGER DEFAULT 1, created_at TEXT DEFAULT (datetime('now')))"

# Add a column (safe — IF NOT EXISTS not available for ALTER, so check first)
python3 {baseDir}/scripts/localdb.py migrate mydata -d "Add email to patients" -s "ALTER TABLE patients ADD COLUMN email TEXT"

# Create related table with foreign key
python3 {baseDir}/scripts/localdb.py migrate mydata -d "Create appointments table" -s "CREATE TABLE IF NOT EXISTS appointments (id INTEGER PRIMARY KEY AUTOINCREMENT, patient_id INTEGER NOT NULL REFERENCES patients(id), date TEXT NOT NULL, notes TEXT, created_at TEXT DEFAULT (datetime('now')))"

Execute queries

# Insert data
python3 {baseDir}/scripts/localdb.py exec mydata "INSERT INTO patients (name, phone) VALUES ('João Silva', '+351912345678')"

# Select data
python3 {baseDir}/scripts/localdb.py exec mydata "SELECT * FROM patients WHERE active = 1"

# JSON output (great for piping)
python3 {baseDir}/scripts/localdb.py exec mydata "SELECT * FROM patients" --json

# Join queries
python3 {baseDir}/scripts/localdb.py exec mydata "SELECT p.name, a.date, a.notes FROM patients p JOIN appointments a ON p.id = a.patient_id ORDER BY a.date DESC"

# Multi-statement (semicolon-separated)
python3 {baseDir}/scripts/localdb.py exec mydata "INSERT INTO patients (name) VALUES ('Alice'); INSERT INTO patients (name) VALUES ('Bob')"

# Destructive operations (requires explicit flag + user confirmation)
python3 {baseDir}/scripts/localdb.py exec mydata "DELETE FROM patients WHERE active = 0" --allow-destructive

View schema

# All tables
python3 {baseDir}/scripts/localdb.py schema mydata

# Specific table
python3 {baseDir}/scripts/localdb.py schema mydata -t patients

List tables

python3 {baseDir}/scripts/localdb.py tables mydata

View migration history

python3 {baseDir}/scripts/localdb.py migrations mydata

Backup a database

python3 {baseDir}/scripts/localdb.py backup mydata

Patterns

Safe column addition

Before adding a column, check if it exists:

python3 {baseDir}/scripts/localdb.py exec mydata "PRAGMA table_info(patients)" --json

Then migrate if the column doesn't exist.

Bulk inserts

Use multiple INSERT statements separated by semicolons in a single exec call:

python3 {baseDir}/scripts/localdb.py exec mydata "INSERT INTO patients (name) VALUES ('Alice'); INSERT INTO patients (name) VALUES ('Bob')"

Data export

python3 {baseDir}/scripts/localdb.py exec mydata "SELECT * FROM patients" --json > patients_export.json