SQLite

Use SQLite correctly with proper concurrency, pragmas, and type handling.

Join 0+ developers using this skill

skill

Development & Code Tools

intermediate

0 installs

View Source

Last updated: April 23, 2026

AI Skill Market Insights

Real data. Real impact.

Popularity

Rising

Emerging

Active Users

Developers

Quick Start

Manual Installation

No automatic installation available. Please visit the source repository for installation instructions.

View Installation Instructions

TEAR & SHARE

3-5hrs/WK

RISING

0.0K+ USING

Concurrency (Biggest Gotcha)

Only one writer at a time—concurrent writes queue or fail; not for high-write workloads
Enable WAL mode:
```
PRAGMA journal_mode=WAL
```
—allows reads during writes, huge improvement
Set busy timeout:
```
PRAGMA busy_timeout=5000
```
—waits 5s before SQLITE_BUSY instead of failing immediately
WAL needs
```
-wal
```
and
```
-shm
```
files—don't forget to copy them with main database
```
BEGIN IMMEDIATE
```
to grab write lock early—prevents deadlocks in read-then-write patterns

Foreign Keys (Off by Default!)

```
PRAGMA foreign_keys=ON
```
required per connection—not persisted in database
Without it, foreign key constraints silently ignored—data integrity broken
Check before relying:
```
PRAGMA foreign_keys
```
returns 0 or 1
ON DELETE CASCADE only works if foreign_keys is ON

Type System

Type affinity, not strict types—INTEGER column accepts "hello" without error
```
STRICT
```
tables enforce types—but only SQLite 3.37+ (2021)
No native DATE/TIME—use TEXT as ISO8601 or INTEGER as Unix timestamp
BOOLEAN doesn't exist—use INTEGER 0/1; TRUE/FALSE are just aliases
REAL is 8-byte float—same precision issues as any float

Schema Changes

```
ALTER TABLE
```
very limited—can add column, rename table/column; that's mostly it
Can't change column type, add constraints, or drop columns (until 3.35)
Workaround: create new table, copy data, drop old, rename—wrap in transaction
```
ALTER TABLE ADD COLUMN
```
can't have PRIMARY KEY, UNIQUE, or NOT NULL without default

Performance Pragmas

```
PRAGMA optimize
```
before closing long-running connections—updates query planner stats
```
PRAGMA cache_size=-64000
```
for 64MB cache—negative = KB; default very small
```
PRAGMA synchronous=NORMAL
```
with WAL—good balance of safety and speed
```
PRAGMA temp_store=MEMORY
```
for temp tables in RAM—faster sorts and temp results

Vacuum & Maintenance

Deleted data doesn't shrink file—
```
VACUUM
```
rewrites entire database, reclaims space
```
VACUUM
```
needs 2x disk space temporarily—ensure enough room

PRAGMA auto_vacuum=INCREMENTAL

with

PRAGMA incremental_vacuum

—partial reclaim without full rewrite

After bulk deletes, always vacuum or file stays bloated

Backup Safety

Never copy database file while open—corrupts if write in progress
Use
```
.backup
```
command in sqlite3—or
```
sqlite3_backup_*
```
API
WAL mode:
```
-wal
```
and
```
-shm
```
must be copied atomically with main file
```
VACUUM INTO 'backup.db'
```
creates standalone copy (3.27+)

Indexing

Covering indexes work—add extra columns to avoid table lookup
Partial indexes supported (3.8+):
```
CREATE INDEX ... WHERE condition
```
Expression indexes (3.9+):
```
CREATE INDEX ON t(lower(name))
```
```
EXPLAIN QUERY PLAN
```
shows index usage—simpler than PostgreSQL EXPLAIN

Transactions

Autocommit by default—each statement is own transaction; slow for bulk inserts
Batch inserts:
```
BEGIN; INSERT...; INSERT...; COMMIT
```
—10-100x faster
```
BEGIN EXCLUSIVE
```
for exclusive lock—blocks all other connections

Nested transactions via

SAVEPOINT name

RELEASE name

ROLLBACK TO name

Common Mistakes

Using SQLite for web app with concurrent users—one writer blocks all; use PostgreSQL
Assuming ROWID is stable—
```
VACUUM
```
can change ROWIDs; use explicit INTEGER PRIMARY KEY
Not setting busy_timeout—random SQLITE_BUSY errors under any concurrency
In-memory database
```
':memory:'
```
—each connection gets different database; use
```
file::memory:?cache=shared
```
for shared

SQLite

AI Skill Market Insights

Quick Start

Manual Installation

TEAR & SHARE

Tags

GitHub MCP Server

Be Part of the 0+ Developer Community

Concurrency (Biggest Gotcha)

Foreign Keys (Off by Default!)

Type System

Schema Changes

Performance Pragmas

Vacuum & Maintenance

Backup Safety

Indexing

Transactions

Common Mistakes

Pal MCP Server

using-git-worktrees

finishing-a-development-branch

test-driven-development

Channels

Learn

Compare

Company

Agents