ATTACH DATABASE & PRAGMA

This lesson covers two more pieces of SQLite that don't fit neatly under "query language" but come up constantly in real use: ATTACH DATABASE, for working with more than one SQLite file in a single connection, and PRAGMA, the command SQLite uses both to configure the engine and to inspect its own metadata.

ATTACH DATABASE

ATTACH DATABASE opens a second SQLite file inside your current connection and gives it an alias, so you can query across both files as if they were one — useful for things like a separate archive file for old RecipeBox recipes.

-- Attach an archive database alongside the main recipebox.db connection
ATTACH DATABASE 'recipebox_archive.db' AS archive;

-- Query the attached database using alias.table
SELECT title FROM archive.recipes WHERE category = 'Dessert';

-- Or combine data from both files in one query
SELECT title FROM recipes
UNION ALL
SELECT title FROM archive.recipes;

When you're done with it, detach it to close the file and drop the alias:

DETACH DATABASE archive;

PRAGMA: Settings and Introspection

PRAGMA statements are SQLite's own mechanism for querying or setting engine configuration and metadata that isn't part of standard SQL. They fall into two broad uses: changing a setting, and inspecting the database's structure.

Setting configuration

-- Recap: enforce foreign key constraints for this connection
-- (off by default in SQLite for backward compatibility -- turn it on every connection)
PRAGMA foreign_keys = ON;

-- Recap from the previous lesson: switch to Write-Ahead Logging
PRAGMA journal_mode = WAL;

Inspecting the database

-- List every column in the recipes table: name, type, nullability, default, PK status
PRAGMA table_info(recipes);

-- Check the current journal mode without changing it
PRAGMA journal_mode;

PRAGMA table_info(recipes) returns one row per column with fields like cid, name, type, notnull, dflt_value, and pk — handy for inspecting a table's structure from code without hand-parsing the original CREATE TABLE statement.