Understanding SQLite PRAGMA (And How better-sqlite3 Makes It Nicer)

Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building **one place for all dev tools, cheat codes, and TLDRs* — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.*

If you’ve ever worked with SQLite long enough, you’ve probably bumped into these odd-looking statements:

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA table_info(users);

They look like SQL, but also… not exactly SQL.
They’re not part of your schema, not exactly configuration files, and not normal queries.

So what are PRAGMAs? Let’s unpack it in a clean, practical way.

What Exactly Is a PRAGMA in SQLite?

A PRAGMA is a special, SQLite-specific command used to:

• configure how SQLite behaves
• read internal metadata
• perform maintenance tasks
• change settings stored inside the database file
• tweak performance characteristics

Think of PRAGMA as:
“internal switches and inspection tools built into SQLite.”

They’re not standard SQL, and they don’t apply to tables or columns.
You never “set a PRAGMA on a column.”
They only act on the SQLite engine or the database file.

Two Types of PRAGMAs (Important Distinction)

SQLite’s PRAGMAs fall into two buckets:

1. Config PRAGMAs (they change behavior)

Examples:

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 32000;

These modify settings, often immediately, sometimes stored persistently.

2. Query/Info PRAGMAs (they return metadata)

Examples:

PRAGMA table_info('users');
PRAGMA index_list('orders');
PRAGMA page_size;

These return rows — essentially virtual tables — but they don’t change anything.

Here’s a simple diagram of how SQLite handles PRAGMA internally:

How SQLite Processes a PRAGMA (Under the Hood)

When you run something like:

PRAGMA foreign_keys = ON;

SQLite does this:

1. Recognizes “PRAGMA” → not standard SQL
2. Looks up the internal PRAGMA handler
3. Sets an internal flag (db->flags.foreign_keys)
4. Returns success

There is no schema change, no rewrite, no DDL.
It’s literally toggling switches inside SQLite’s internal C structs.

PRAGMAs That Persist vs. PRAGMAs That Don’t

Persist (stored in the .sqlite file)

(These are written to the database file header and will stay active the next time the DB is opened.)

• journal_mode (when set to a persistent mode such as WAL or DELETE; modes like MEMORY or OFF do not persist)
• auto_vacuum
• page_size (but only before the first table is created)

Do NOT persist (per-connection)

• foreign_keys
• cache_size (unless using negative value for KiB mode)
• temp_store

Very easy to get wrong.

Example:

PRAGMA foreign_keys = ON;

→ works only for your connection, not globally.

The New Feature: PRAGMA as Table-Valued Functions

SQLite added something interesting:
Every query PRAGMA can also be accessed as a table-valued function.

Classic PRAGMA:

PRAGMA index_info('idx_users_email');

Equivalent SELECT form:

SELECT * FROM pragma_index_info('idx_users_email');

Why is this cool?
Because you can join, filter, order, limit, etc.

Example: list all indexed columns in the whole DB:

SELECT DISTINCT m.name || '.' || ii.name AS indexed_column
FROM sqlite_schema AS m
JOIN pragma_index_list(m.name) AS il
JOIN pragma_index_info(il.name) AS ii
WHERE m.type = 'table'
ORDER BY 1;

You cannot do this kind of analysis with raw PRAGMA syntax.

Using PRAGMA in better-sqlite3

The PRAGMA functionality in SQLite works fine, but running it through SQL in Node can be messy.

Example:

db.prepare("PRAGMA cache_size").get(); 

might return weird shapes like:

{ cache_size: 2000 }

or sometimes:

{ 'cache_size(32000)': null }

So better-sqlite3 fixes this with a dedicated method:

db.pragma("cache_size = 32000");

And for reading:

db.pragma("cache_size", { simple: true });

Output:

32000

Much cleaner.

What better-sqlite3 .pragma() actually improves

1. Normalized return values

Consistent JS objects, unlike raw PRAGMA SQL.

2. Correct error handling

SQLite normally does not error on unknown PRAGMAs:

PRAGMA i_dont_exist;  -- silently ignored

better-sqlite3:

db.pragma("i_dont_exist"); // throws Error

Massive quality-of-life improvement.

3. Simple mode

Extracts first column of first row:

db.pragma("page_size", { simple: true }); // => 4096

4. Safe for PRAGMAs with odd output formats

Some PRAGMAs return multiple columns, some return one, some return rows, some return nothing — this API smooths over that.

Useful PRAGMAs You’ll Actually Use

Enable foreign keys

PRAGMA foreign_keys = ON;

Switch to WAL mode

PRAGMA journal_mode = WAL;

Check table columns

PRAGMA table_info('users');

Check indexes on a table

PRAGMA index_list('products');

Get index columns

PRAGMA index_info('idx_products_name');

Read DB page size

PRAGMA page_size;

Maintenance / Schema Commands (Often used alongside PRAGMAs)

(Note: VACUUM is a DDL command, not a PRAGMA.)

VACUUM;

Conclusion

PRAGMAs are one of the most powerful (and most misunderstood) parts of SQLite.
They’re not schema, not SQL extensions — they’re internal switches and metadata endpoints built inside SQLite itself.

And if you're using better-sqlite3, the .pragma() method gives you:

• consistent output
• better errors
• simple mode
• fewer head-scratching situations

Whether you’re tuning performance, introspecting schema, or enabling features like WAL mode, PRAGMAs are your main tool.

👉 Check out: FreeDevTools

Any feedback or contributors are welcome!

It’s online, open-source, and ready for anyone to use.

⭐ Star it on GitHub: freedevtools