DEV Community

Cover image for SQLite PRAGMA: The Underrated Lever That Controls Your DB
Athreya aka Maneshwar
Athreya aka Maneshwar

Posted on

SQLite PRAGMA: The Underrated Lever That Controls Your DB

#database #architecture

Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

Most developers treat SQLite like a simple file-based database—create tables, run queries, move on.

But if you stop there, you’re missing one of its most powerful features: PRAGMA statements.

PRAGMA is where SQLite stops being “just a database” and starts becoming something you can actually tune, inspect, and control.

This isn’t just syntax—it’s control over how your database behaves under the hood.

What PRAGMA Really Is

A PRAGMA is a special SQLite command that lets you:

  • Peek into internal database metadata (stuff not stored in tables)
  • Change how SQLite behaves internally

It looks like SQL, but it doesn’t behave like typical SQL.

Example:

PRAGMA synchronous = OFF;
Enter fullscreen mode Exit fullscreen mode

That single line changes how SQLite writes data to disk—trading safety for speed.

PRAGMA Doesn’t Always Run When You Think It Does

Unlike normal SQL, some PRAGMAs execute during the query preparation phase, not execution.

If you're using SQLite through APIs like:

  • sqlite3_prepare
  • sqlite3_step

Some PRAGMAs take effect before execution even begins.

So if something feels inconsistent, this might be why.

This Is SQLite-Only Territory

PRAGMAs are not portable.

If you:

  • Switch to PostgreSQL
  • Move to MySQL

Your PRAGMA-based logic? Gone.

So if portability matters, use them carefully and isolate them.

How PRAGMA Syntax Works

You’ve got two ways to use it:

PRAGMA name = value;
Enter fullscreen mode Exit fullscreen mode

or

PRAGMA name(value);
Enter fullscreen mode Exit fullscreen mode

To read a value:

PRAGMA name;
Enter fullscreen mode Exit fullscreen mode

Boolean shortcuts:

  • ON, TRUE, YES → 1
  • OFF, FALSE, NO → 0

Case doesn’t matter.

Even worse:

  • Invalid values are silently treated as 1

The 4 Types of PRAGMA You Should Actually Care About

Instead of memorizing everything, think in categories.

1. Schema Inspection (Understanding Your Database)

These help you explore structure without guessing.

PRAGMA table_info(users);
PRAGMA index_list(users);
PRAGMA foreign_key_list(users);
Enter fullscreen mode Exit fullscreen mode

Use this when:

  • Debugging relationships
  • Reverse engineering a database
  • Building migration tools

2. Behavior Control (Where the Real Power Is)

This is where performance and behavior tuning happens.

Auto Vacuum 

PRAGMA auto_vacuum = FULL;
Enter fullscreen mode Exit fullscreen mode

Controls how SQLite reclaims unused space.

Options:

  • NONE → default, no cleanup
  • FULL → automatic cleanup
  • INCREMENTAL → manual cleanup

⚠️ You must set this before creating tables.
After that, it won’t change and SQLite won’t complain.

Cache Size 

PRAGMA cache_size = 2000;
Enter fullscreen mode Exit fullscreen mode
  • Controls how much data SQLite keeps in memory
  • Faster reads if increased

But:

  • It resets when the connection closes

To make it stick:

PRAGMA default_cache_size = 2000;
Enter fullscreen mode Exit fullscreen mode

Synchronous Mode (Speed vs Safety) 

PRAGMA synchronous = OFF;
Enter fullscreen mode Exit fullscreen mode
  • FULL → safest, slowest
  • OFF → fastest, risky

Turning it off means:

  • Faster writes
  • Higher chance of corruption during crashes

This is a deliberate tradeoff, not a free optimization.

3. Versioning (For Migrations and Tracking)

User Version (Safe to Use) 

PRAGMA user_version = 3;
Enter fullscreen mode Exit fullscreen mode

This is for you, not SQLite.

Use it to:

  • Track schema versions
  • Manage migrations

4. Debugging and Integrity (Your Safety Net)

Integrity Check 

PRAGMA integrity_check;
Enter fullscreen mode Exit fullscreen mode

This scans the entire database and tells you if something is wrong.

Output:

  • "ok" → everything is fine
  • Error string → something is broken

Use this when:

  • You suspect corruption
  • After crashes
  • Before backups

Where People Usually Screw This Up

  • Misspelling PRAGMAs and assuming they worked
  • Using synchronous = OFF in production without understanding risk
  • Trying to change auto_vacuum after tables exist
  • Messing with schema_version
  • Relying on PRAGMAs in apps that need to support multiple databases

Why PRAGMA Actually Matters

If you ignore PRAGMA, you’re basically using SQLite in “default mode” forever.

That means:

  • Default performance
  • Default safety
  • Default behavior

But SQLite isn’t just a database—it’s configurable at a very low level.

PRAGMA is how you access that layer.

git-lrc
*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

GitHub logo HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit



AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

  • 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
  • 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
  • 🔁 Build a…
View on GitHub

Top comments (0)