skills.rest
viniciuscs84
viniciuscs84COMMUNITY
Explore all skills by this author →
Website𝕏@viniciuscs84GitHub

sqlite

Guides SQLite schema design, migrations, query tuning, and pragma configuration.

Updated May 22, 2026
Search Tags:#query optimization#indexing#database#migrations#sqlite#sql#wal
One-click install
npx skills add https://github.com/viniciuscs84/sdd-toolkit --skill sqlite-viniciuscs84
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sqlite
Source: https://github.com/viniciuscs84/sdd-toolkit/tree/main/skills/sqlite
Command: npx skills add https://github.com/viniciuscs84/sdd-toolkit --skill sqlite-viniciuscs84

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? SQLite behaves differently from client-server databases: foreign keys are off by default, ALTER COLUMN is unsupported, and write concurrency is limited to one writer. This Skill provides the correct patterns for schema design, safe migrations, query optimization, and connection configuration so you avoid common production failures. ## Core Features & Use Cases - Schema Design & Migrations: Enforce integrity with constraints, use INTEGER PRIMARY KEY correctly, and apply safe patterns for adding NOT NULL columns to populated tables. - Query Performance: Diagnose slow queries with EXPLAIN QUERY PLAN, build covering indexes, write sargable predicates, and replace slow OFFSET pagination with keyset pagination. - Configuration & Concurrency: Apply the right pragmas (WAL mode, busy_timeout, foreign_keys) per connection and handle SQLITE_BUSY contention. - Use Case: You are shipping an embedded database in a desktop or mobile app and need concurrent reads with safe writes, online backups via VACUUM INTO, and a migration path that does not lock the database. ## Quick Start Use the sqlite skill to review my schema and optimize this slow query on the orders table.

Frequently Asked Questions about sqlite

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I add a NOT NULL column to an existing SQLite table?▼

SQLite cannot alter columns directly, so add the column as nullable, backfill values with UPDATE, then enforce the constraint via a CHECK or a full table rebuild. The rebuild pattern is CREATE TABLE new, INSERT SELECT, DROP old, ALTER RENAME.

How do I speed up a slow SQLite query?▼

Run EXPLAIN QUERY PLAN on the query and look for SCAN instead of SEARCH USING INDEX. Add a covering index matching the WHERE and ORDER BY columns, and keep predicates sargable by avoiding functions on indexed columns.

Why are my SQLite foreign keys not being enforced?▼

SQLite disables foreign key enforcement by default on every connection. Set PRAGMA foreign_keys = ON in your connection initialization code, since the pragma is connection-scoped and must be reapplied each time.

Does SQLite support concurrent reads and writes?▼

Yes, with WAL mode enabled via PRAGMA journal_mode = WAL, multiple readers can run alongside one writer. SQLite still allows only a single writer at a time, so set busy_timeout and keep transactions short to avoid SQLITE_BUSY errors.

Why is OFFSET pagination slow in SQLite?▼

Large OFFSET values force SQLite to scan and discard rows from the beginning of the result set every time. Use keyset pagination instead: filter with WHERE id > :last_seen_id and ORDER BY id LIMIT n.

When should I use Postgres instead of SQLite?▼

Switch to Postgres when your workload has heavy concurrent writes, since SQLite permits only one writer at a time with a database-level write lock. SQLite fits embedded, file-based, or read-heavy single-writer deployments.

Related Skills

Explore More Software Engineering Skills→
clideyclidey
5.0k

schema-designer

Translate user requirements into structured database table designs with CREATE statements.

Official
Basic
interflownexinterflownex
2

cloud-sql-postgres-monitor

Analyze PostgreSQL performance on Google Cloud SQL using PromQL and pg_stat_activity.

Community
Advanced
AraszArasz
2

sqlite-bank-space-diagnosis

Diagnose SQLite database and WAL file bloat using read-only space analysis and checkpoint quantification.

Community
Advanced
harlanljonesharlanljones

SQLite

Guides correct SQLite usage covering concurrency, pragmas, type handling, and transactions.

Community
Intermediate