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:
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:
flowchart TD
A[SQL Parser] --> B{Is it a PRAGMA?}
B -->|Yes| C[Lookup PRAGMA Handler]
C --> D{Type?}
D -->|Config PRAGMA| E[Set internal flag or DB header field]
D -->|Query PRAGMA| F[Generate virtual rows
to return]
B -->|No| G[Process as normal SQL]
How SQLite Processes a PRAGMA (Under the Hood)
When you run something like:
PRAGMA foreign_keys = ON;
SQLite does this:
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.)
Do NOT persist (per-connection)
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:
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
More...
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:
flowchart TD
A[SQL Parser] --> B{Is it a PRAGMA?}
B -->|Yes| C[Lookup PRAGMA Handler]
C --> D{Type?}
D -->|Config PRAGMA| E[Set internal flag or DB header field]
D -->|Query PRAGMA| F[Generate virtual rows
to return]
B -->|No| G[Process as normal SQL]
How SQLite Processes a PRAGMA (Under the Hood)
When you run something like:
PRAGMA foreign_keys = ON;
SQLite does this:
- Recognizes “PRAGMA” → not standard SQL
- Looks up the internal PRAGMA handler
- Sets an internal flag (db->flags.foreign_keys)
- 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
More...