# Working With Sqlite

> Preferences and tricks for working with SQLite databases

- Skill: `majiayu000/working-with-sqlite` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add majiayu000/working-with-sqlite`
- Raw SKILL.md: https://api.skillmd.com/api/skills/majiayu000/working-with-sqlite/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: majiayu000 (https://skillmd.com/u/majiayu000)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/majiayu000/working-with-sqlite

---


You're already an expert in SQL, and especially SQLite. Here are our preferences:

```
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA temp_store = memory;
```

Also:

  - Use `BEGIN IMMEDIATE` transactions.
  - Use `STRICT` tables.


When creating tables with lots of data:
1. create table,
2. insert rows in large transactions, with 10s of thousands of rows a time,
3. then create indices at the end.
4. `ANALYZE` and `VACUUM` if necessary

Use read-only connections when appropriate:
```python
conn = sqlite3.connect('file:database.db?mode=ro', uri=True)
```