# SQLite Driver

**Page:** foundation/storage/sqlite-drive

[Download Raw Markdown](./foundation/storage/sqlite-drive.md)

---

# SQLite Driver

**Store SQLite databases in `/hoody/databases/` and they automatically become concurrent-write-safe.** Multiple containers can write simultaneously without database corruption—zero code changes required.

**Critical for AI agents:** AI-generated code commonly causes SQLite corruption through race conditions. With `/hoody/databases/`, you can vibe-code without worrying that your databases will be corrupted—the FUSE mount prevents the most common AI coding mistakes automatically.

---

## API Endpoints Summary

**Database Access:**
- **[Hoody SQLite API](/api/sqlite/kv-store/)** - HTTP-based KV store access
- **[SQL Operations](/api/sqlite/sql-operations/)** - Execute SQL transactions via HTTP

**Direct Access:**
- **[Hoody Terminal](/kit/terminals/)** - Use standard `sqlite3` command
- **[SSH Access](/foundation/networking/ssh/)** - Access via secure shell

---

## The Problems It Solves

### Problem 1: Concurrent Write Corruption (Especially AI-Generated Code)

**AI agents frequently generate code that corrupts SQLite databases:**

```python
# AI-generated code (common pattern)
import sqlite3

# Multiple AI tasks running simultaneously
def process_task(task_id):
    conn = sqlite3.connect('/app/data.db')  # Same database
    cursor = conn.cursor()
    cursor.execute("INSERT INTO tasks VALUES (?, 'completed')", (task_id,))
    conn.commit()
    conn.close()

# Task 1, 2, 3... all running in parallel
# Result: ❌ "database is locked" or 💥 corruption
```

**With `/hoody/databases/`, this just works** - no race conditions, no corruption.

### Problem 2: Multi-Container Access

**Traditional SQLite issue:**

```bash
# Container A writes to database
sqlite3 /app/data.db "INSERT INTO users..."

# Container B writes simultaneously
sqlite3 /app/data.db "INSERT INTO posts..."

# Result: ❌ "database is locked" error
# or worse: 💥 database corruption
```

**SQLite uses file-level locking.** When multiple processes (or containers) try to write, one gets locked out or corruption occurs.

---

## The Hoody Solution

**Store database in `/hoody/databases/` instead:**

```bash
# Container A
sqlite3 /hoody/databases/shared.db "INSERT INTO users..."

# Container B (simultaneously)
sqlite3 /hoody/databases/shared.db "INSERT INTO posts..."

# Result: ✅ Both succeed
# No locking errors
# No corruption
# Zero code changes
```

**How it works:**

`/hoody/databases/` is a **special FUSE mount** on the **Host level** that implements proper concurrent write handling for SQLite databases.

---

## How It Works

### Automatic Availability

**Every container automatically has `/hoody/databases/` available:**

```bash
# No setup required - just use it
ls /hoody/databases/
# Directory exists and is ready to use
```

**This is automatic:**
- ✅ No configuration needed
- ✅ No mounting commands required
- ✅ Present in all containers
- ✅ Works immediately

### Host-Level FUSE Mount

```
┌─────────────────────────────────────────┐
│         Physical Host Server            │
│                                         │
│  ┌─────────────────────────────────┐   │
│  │  Special FUSE Driver Layer      │   │
│  │  (concurrent write coordination)│   │
│  └─────────────────────────────────┘   │
│             ↓                           │
│  ┌─────────────────────────────────┐   │
│  │  Actual Database Storage        │   │
│  └─────────────────────────────────┘   │
│                                         │
│  Container A    Container B    Container C
│       ↓              ↓              ↓
│  /hoody/databases/  /hoody/databases/  ...
└─────────────────────────────────────────┘
```

**The FUSE layer intercepts all writes** and coordinates them safely—preventing simultaneous write conflicts at the filesystem level.

---

## Usage Patterns

### Drop-In Replacement

**No code changes required in your applications:**


  
    ```python
    # Risk of corruption with multiple containers
    import sqlite3
    conn = sqlite3.connect('/app/database.db')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users VALUES (?, ?)", (1, 'Alice'))
    conn.commit()
    ```
  
  
  
    ```python
    # Concurrent-write-safe - ONLY path changed
    import sqlite3
    conn = sqlite3.connect('/hoody/databases/database.db')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users VALUES (?, ?)", (1, 'Alice'))
    conn.commit()
    ```
    
    **Only the path changed.** Everything else identical.
  


**This works with ANY program using `sqlite3`:**
- Python (sqlite3 module)
- Node.js (better-sqlite3, sqlite3)
- Go (mattn/go-sqlite3)
- PHP (PDO SQLite)
- Any language with SQLite bindings

### Pair with hoody-sqlite HTTP API

**Access the same database via HTTP AND native sqlite3:**

```bash
# Container A: Native sqlite3 (fast, direct access)
sqlite3 /hoody/databases/app.db "SELECT * FROM users"

# Container B: hoody-sqlite HTTP API (remote access)
curl "https://...-sqlite-1.../api/v1/sqlite/db?db=/hoody/databases/app.db" \
  -d '{"transaction": [{"statement": "SELECT * FROM users"}]}'

# Same database, two access methods
```

**Why this is powerful:**
- **Native access** for performance-critical operations
- **HTTP access** for remote monitoring, web dashboards, API integrations
- Both methods work simultaneously without conflicts

See: [Hoody SQLite](/kit/sqlite/) for complete HTTP API documentation.

---

## What It Does (and Doesn't Do)

### ✅ What It Provides

**Concurrent Write Safety:**
- Multiple containers can write to the same database
- Multiple processes in ONE container can write
- No "database is locked" errors
- No corruption from simultaneous writes

**Zero Configuration:**
- Automatically available in all containers
- No special setup or mounting
- Works with standard sqlite3 library
- No code changes required (just path)

**Cross-Container Databases:**
- Share single database across many containers
- All containers see same data instantly
- Perfect for multi-service architectures
- Eliminates need for separate database server

### ❌ What It Doesn't Provide

**NOT a replication system:**
- ✅ Allows concurrent writes safely
- ❌ Does NOT replicate data to multiple hosts
- ❌ Does NOT provide automatic backups
- ❌ Does NOT provide failover

**Single host only:**
- Containers on SAME server can share databases
- Containers on DIFFERENT servers cannot (yet)
- Each server has its own `/hoody/databases/` space

**No automatic backups:**
- You must snapshot or backup databases yourself
- FUSE layer provides safety, not redundancy

---

## Common Use Cases

### Multi-Service Application

**Share application database across frontend, backend, and workers:**

```bash
# Container 1 (API Server)
# /hoody/databases/app.db
# Handles user registration, authentication

# Container 2 (Background Worker)
# /hoody/databases/app.db (same database)
# Processes jobs, updates status

# Container 3 (Reporting Dashboard)
# /hoody/databases/app.db (same database)
# Read-only queries for reporting

# All three write safely to the same database
```

**Traditional approach:** Run PostgreSQL/MySQL server, connect via network (complexity, overhead).

**Hoody approach:** Single SQLite file in `/hoody/databases/`, accessed by all containers (simple, fast).

### Development Database

**Developers share database during active development:**

```bash
# Developer A's container writes schema changes
sqlite3 /hoody/databases/dev.db < migrations/001.sql

# Developer B's container writes seed data (simultaneously)
sqlite3 /hoody/databases/dev.db < seeds/users.sql

# No conflicts - both succeed
```

### Analytics Pipeline

**Concurrent data ingestion + real-time queries:**

```bash
# Container A: Ingest metrics
while true; do
  sqlite3 /hoody/databases/metrics.db \
    "INSERT INTO events VALUES (datetime('now'), '$data')"
done

# Container B: Query metrics (simultaneously)
sqlite3 /hoody/databases/metrics.db \
  "SELECT COUNT(*) FROM events WHERE timestamp > datetime('now', '-1 hour')"

# No blocking - queries run while inserts happen
```

---

## Integration with hoody-sqlite

**Access via HTTP for maximum flexibility:**

> **`hoody` CLI prerequisite.** The CLI tabs below invoke `hoody db …`. If you haven't already, install the CLI (`npm i -g @hoody-ai/hoody-sdk` or `curl -fsSL https://install.hoody.com | bash`) and authenticate (`hoody auth login`) so the CLI picks up your Hoody token and default base URL.

### Execute SQL Transactions


  
    ```bash
    # Execute SQL transaction via CLI
    hoody db exec-transaction --db /hoody/databases/app.db \
      --transaction '[{"statement": "SELECT * FROM logs ORDER BY timestamp DESC LIMIT 100"}]'

    # Create a new database
    hoody db create --path /hoody/databases/analytics.db
    ```
  
  
    ```typescript
    const containerClient = await client.withContainer({
      id: CONTAINER_ID,
      project_id: PROJECT_ID,
      server: SERVER
    });

    // Execute SQL transaction. The transaction array is the request body;
    // `db` is a query param and goes in the 2nd options argument.
    // Use a "query" item for SELECTs (a "statement" item returns rowsUpdated only).
    const result = await containerClient.sqlite.database.executeTransaction(
      { transaction: [
        { query: 'SELECT * FROM logs ORDER BY timestamp DESC LIMIT 100' }
      ] },
      { db: '/hoody/databases/app.db' }
    );
    console.log(result.data); // Query results
    ```
  
  
    ```bash
    # Execute SQL transaction. $PROXY_TOKEN is the proxy-minted token for this
    # container-proxy path (the SDK/CLI set it automatically; raw curl must pass
    # it explicitly — Hoody Proxy owns Kit auth). See /foundation/proxy/permissions/.
    curl -X POST "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.icu/api/v1/sqlite/db?db=/hoody/databases/app.db" \
      -H "Authorization: Bearer $PROXY_TOKEN" \
      -H "Content-Type: application/json" \
      -d '{"transaction": [{"statement": "SELECT * FROM logs ORDER BY timestamp DESC LIMIT 100"}]}'
    ```
  


### KV Store Operations


  
    ```bash
    # Set a key-value pair
    hoody kv set user:1 --db /hoody/databases/app.db \
      --body '{"name": "Alice", "email": "alice@example.com"}'

    # Get a value by key
    hoody kv get user:1 --db /hoody/databases/app.db
    ```
  
  
    ```typescript
    // Set value. The value is a JSON-encoded STRING (encode objects yourself);
    // `db` is a query param and goes in the options argument.
    await containerClient.sqlite.kvStore.set(
      'user:1',
      JSON.stringify({ name: 'Alice', email: 'alice@example.com' }),
      { db: '/hoody/databases/app.db' }
    );

    // Get value
    const value = await containerClient.sqlite.kvStore.get('user:1', {
      db: '/hoody/databases/app.db'
    });
    console.log(value.data); // { name: 'Alice', email: 'alice@example.com' }
    ```
  
  
    ```bash
    # Set value
    curl -X PUT "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.icu/api/v1/sqlite/kv/user:1?db=/hoody/databases/app.db" \
      -H "Content-Type: application/json" \
      -d '{"name": "Alice", "email": "alice@example.com"}'

    # Get value
    curl "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.icu/api/v1/sqlite/kv/user:1?db=/hoody/databases/app.db"
    ```
  


### Native + HTTP Combined


  
    ```javascript
    // Backend container: Native sqlite3 for bulk inserts (faster)
    const db = require('better-sqlite3')('/hoody/databases/app.db');
    db.prepare('INSERT INTO logs VALUES (?, ?)').run(timestamp, message);

    // Frontend container: HTTP API for remote queries
    const response = await fetch(
      'https://...-sqlite-1.../api/v1/sqlite/db?db=/hoody/databases/app.db',
      {
        method: 'POST',
        body: JSON.stringify({
          transaction: [{
            statement: 'SELECT * FROM logs ORDER BY timestamp DESC LIMIT 100'
          }]
        })
      }
    );
    ```

    **Best of both:** Fast native writes, convenient HTTP reads.
  

  
    ```bash
    # Hoody SQLite KV Store API (even simpler)
    # Set value
    curl -X PUT "https://...-sqlite-1.../api/v1/sqlite/kv/user:1?db=/hoody/databases/app.db" \
      -d '{"name": "Alice", "email": "alice@example.com"}'

    # Get value
    curl "https://...-sqlite-1.../api/v1/sqlite/kv/user:1?db=/hoody/databases/app.db"

    # Concurrent writes automatically safe
    ```

    **Pure HTTP:** Never touch sqlite3 CLI—everything via API.
  


See: [Hoody SQLite KV Store](/api/sqlite/kv-store/) and [SQL Operations](/api/sqlite/sql-operations/)

---

## Best Practices

### 1. Always Use /hoody/databases/ for SQLite

**Never store SQLite databases outside this directory if multiple containers will access them:**

```bash
# ✅ Correct - concurrent write safe
/hoody/databases/production.db
/hoody/databases/cache.db
/hoody/databases/analytics.db

# ❌ Wrong - risk of corruption
/hoody/storage/production.db
/var/lib/myapp/data.db
/tmp/cache.db
```

### 2. Use Standard SQLite Libraries

**No special drivers needed:**

```python
# Works with standard library
import sqlite3
conn = sqlite3.connect('/hoody/databases/app.db')
```

The concurrent-write safety is handled by the FUSE mount—your code stays standard.

### 3. Backup Databases Regularly

**Concurrent-write safety ≠ automatic backups:**

```bash
# Via snapshot (captures entire container state)
POST /api/v1/containers/{id}/snapshots
{"alias": "before-migration"}

# Or copy database file
cp /hoody/databases/production.db /hoody/storage/backups/prod-2025-11-10.db

# Or use sqlite3 backup command
sqlite3 /hoody/databases/production.db ".backup /hoody/storage/backups/backup.db"
```

### 4. Monitor Database Size

```bash
# Check database sizes
du -sh /hoody/databases/*

# Vacuum to compact
sqlite3 /hoody/databases/app.db "VACUUM"

# Set up auto-vacuum
sqlite3 /hoody/databases/app.db "PRAGMA auto_vacuum = FULL"
```

### 5. Index for Performance

**Concurrent writes are safe, but indexing is still critical for performance:**

```sql
-- Create indexes for frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_author ON posts(author_id, created_at);

-- Analyze for query planner
ANALYZE;
```

---

## Useful Questions

### Do I need to do anything to enable /hoody/databases/?

No. It's **automatically available** in every container. Just start using it:

```bash
sqlite3 /hoody/databases/myapp.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"
```

### Can containers on different servers share databases?

Not yet. `/hoody/databases/` is **host-level**, meaning containers on the **same physical server** can share databases.

**Cross-server:** Use hoody-sqlite HTTP API to access databases remotely.

### Does this work with PostgreSQL or MySQL?

No. This is specifically for **SQLite databases** only. The FUSE mount handles SQLite's file-level locking protocol.

For PostgreSQL/MySQL, run them as services in containers and connect via network.

### What if my database gets really large (>10GB)?

The FUSE mount handles databases of any size. Performance remains good for databases up to hundreds of GBs.

**For very large datasets**, consider:
- Sharding across multiple databases
- Using indexes aggressively
- Running dedicated PostgreSQL container

### Can I use WAL mode with /hoody/databases/?

Yes! SQLite's Write-Ahead Logging (WAL) mode works perfectly with the concurrent-write FUSE mount:

```sql
PRAGMA journal_mode = WAL;
```

**Combined benefit:** WAL's concurrent read performance + FUSE mount's concurrent write safety.

### Does hoody-sqlite HTTP API require databases in /hoody/databases/?

No. hoody-sqlite can access databases **anywhere** in the container filesystem:

```bash
# Works - in /hoody/databases/
curl "...?db=/hoody/databases/app.db"

# Also works - anywhere else
curl "...?db=/home/user/data/test.db"
```

**But:** For concurrent write safety from multiple containers, the database **must** be in `/hoody/databases/`.

---

## Troubleshooting

### "Database is locked" Errors Still Occur

**Problem:** Getting lock errors even when using `/hoody/databases/`

**Possible causes:**

1. **Long-running transactions:**
   ```sql
   -- ❌ Holding write lock too long
   BEGIN EXCLUSIVE;
   -- Complex operations taking seconds
   COMMIT;
   
   -- ✅ Break into smaller transactions
   BEGIN; INSERT ...; COMMIT;
   BEGIN; INSERT ...; COMMIT;
   ```

2. **Busy timeout too low:**
   ```python
   # Increase timeout
   conn = sqlite3.connect('/hoody/databases/app.db')
   conn.execute('PRAGMA busy_timeout = 5000')  # 5 seconds
   ```

3. **Very high write concurrency:**
   - FUSE mount has limits
   - Consider connection pooling
   - Or use WAL mode

### Database File Not Found

**Problem:** `sqlite3: cannot open database`

**Check:**

```bash
# Verify directory exists
ls -la /hoody/databases/

# Create database if needed
sqlite3 /hoody/databases/newdb.db "CREATE TABLE test (id INTEGER)"

# Check permissions
ls -la /hoody/databases/newdb.db
# Should be: -rw-r--r-- root root
```

### Performance Slower Than Expected

**Problem:** Queries slower in `/hoody/databases/` vs regular filesystem

**Optimization:**

1. **Enable WAL mode:**
   ```sql
   PRAGMA journal_mode = WAL;
   PRAGMA synchronous = NORMAL;
   ```

2. **Use indexes:**
   ```sql
   CREATE INDEX idx_query ON table(column);
   ANALYZE;
   ```

3. **Increase cache size:**
   ```sql
   PRAGMA cache_size = -64000;  -- 64MB cache
   ```

4. **Batch operations:**
   ```sql
   BEGIN;
   -- Multiple INSERTs
   COMMIT;
   ```

---

## Concurrent Write Architecture

**How the FUSE mount coordinates writes:**

```
Time    Container A              FUSE Layer                Container B
─────────────────────────────────────────────────────────────────────
T1      BEGIN TRANSACTION        ← Request write lock      (waiting)
T2      INSERT INTO users        Lock granted to A         (waiting)
T3      INSERT INTO posts        Buffering A's writes      (waiting)
T4      COMMIT                   Flushing A's changes      (waiting)
T5      (done)                   Release lock              BEGIN TRANSACTION
T6      (ready for next)         Grant lock to B ←         INSERT INTO logs
T7                               Buffering B's writes      COMMIT
T8                               Release lock              (done)
```

**The FUSE layer serializes competing writes** while allowing concurrent reads.

---

## What's Next

**Storage ecosystem:**
- **[Container Storage →](./)** - Understanding `/hoody/storage` and container filesystem
- **[Mount Locally →](./mount-locally/)** - Access container files via SFTP/WebDAV
- **[Cloud Storage →](./cloud/)** - Connect 63 cloud providers
- **[Shared Storage →](./sharing-files/)** - Share directories between containers
- **[/ramdisk →](./ramdisk/)** - Ultra-fast RAM storage

**Database access:**
- **[Hoody SQLite KV Store →](/api/sqlite/kv-store/)** - HTTP-based key-value operations
- **[SQL Operations →](/api/sqlite/sql-operations/)** - Execute transactions via HTTP
- **[Hoody Terminal →](/kit/terminals/)** - Use sqlite3 command directly

**Understanding gained:**
- ✅ `/hoody/databases/` is automatic in all containers
- ✅ Host-level FUSE mount provides concurrent-write safety
- ✅ No code changes—just change database path
- ✅ Works with standard sqlite3 library in any language
- ✅ Pairs with hoody-sqlite for HTTP access
- ✅ Safety ≠ replication (you still need backups)

---

> **Concurrent writes without corruption.**  
> **Zero code changes. Just use /hoody/databases/.**

**Share SQLite databases safely across containers. HTTP access via hoody-sqlite. Simple, fast, reliable.**