How do I enable WAL mode and improve concurrency?

Enable WAL via PRAGMA journal_mode=WAL in the connection setup, as shown in get_connection, to allow concurrent reads and writes.

How are foreign keys enforced?

Run PRAGMA foreign_keys=ON to enforce FK constraints.

How do I safely commit or rollback multiple writes?

Wrap writes inside the db_transaction(context manager) so it commits on success and rolls back on error.

sqlite-ops

npx machina-cli add skill aiskillstore/marketplace/sqlite-ops --openclaw

Files (1)

SKILL.md

2.2 KB

SQLite Operations

Patterns for SQLite databases in Python projects.

Quick Connection

import sqlite3

def get_connection(db_path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, check_same_thread=False)
    conn.row_factory = sqlite3.Row  # Dict-like access
    conn.execute("PRAGMA journal_mode=WAL")  # Better concurrency
    conn.execute("PRAGMA foreign_keys=ON")
    return conn

Context Manager Pattern

from contextlib import contextmanager

@contextmanager
def db_transaction(conn: sqlite3.Connection):
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise

WAL Mode

Enable for concurrent read/write:

conn.execute("PRAGMA journal_mode=WAL")

Mode	Reads	Writes	Best For
DELETE (default)	Blocked during write	Single	Simple scripts
WAL	Concurrent	Single	Web apps, MCP servers

Common Gotchas

Issue	Solution
"database is locked"	Use WAL mode
Slow queries	Add indexes, check EXPLAIN QUERY PLAN
Thread safety	Use `check_same_thread=False`
FK not enforced	Run `PRAGMA foreign_keys=ON`

CLI Quick Reference

sqlite3 mydb.sqlite    # Open database
.tables                # Show tables
.schema items          # Show schema
.headers on && .mode csv && .output data.csv  # Export CSV
VACUUM;                # Reclaim space

When to Use

Local state/config storage
Caching layer
Event logging
MCP server persistence
Small to medium datasets

Additional Resources

For detailed patterns, load:

./references/schema-patterns.md - State, cache, event, queue table designs
./references/async-patterns.md - aiosqlite CRUD, batching, connection pools
./references/migration-patterns.md - Version migrations, JSON handling

Source

git clone https://github.com/aiskillstore/marketplace/blob/main/skills/0xdarkmatter/sqlite-ops/SKILL.mdView on GitHub

Overview

sqlite-ops provides patterns for managing SQLite databases in Python projects, focusing on state management, caching layers, and async operations with aiosqlite. It covers configuration for WAL mode, foreign keys, and practical gotchas to improve concurrency, reliability, and performance.

How This Skill Works

Key components include a get_connection function that opens the DB, enables WAL and foreign keys, and returns rows as dict-like objects. A context manager db_transaction ensures atomic commits or rollbacks around a block of work. For async workloads, the patterns outline using aiosqlite-based CRUD, batching, and connection pools.

When to Use It

Local state/config storage
Caching layer for fast lookups
Event logging with a small footprint
MCP server persistence
Small to medium datasets

Quick Start

Step 1: Open the database with get_connection('/path/to/db.sqlite')
Step 2: Wrap writes in a transaction using the db_transaction(conn) context manager
Step 3: For async workloads, follow the aiosqlite patterns (CRUD, batching, pools) as described in the references

Best Practices

Enable WAL and proper isolation for concurrency
Enable foreign keys with PRAGMA foreign_keys=ON
Return rows as dict-like objects using sqlite3.Row
Use a context manager to ensure atomic commits/rollbacks
Profile slow queries and add indexes; check EXPLAIN QUERY PLAN

Example Use Cases

Web app with concurrent reads/writes using WAL mode
Desktop app storing local config for fast lookups
Event logging table capturing user actions with batched writes
MCP server persistence layer ensuring durability across restarts
Analytics cache for small to medium datasets with simple migrations

Frequently Asked Questions

Add this skill to your agents