Useful queries for inspecting SQLite databases

List user tables and views

SELECT type, name, sql
FROM sqlite_master
WHERE type IN ('table', 'view')
  AND name NOT LIKE 'sqlite_%'
ORDER BY type, name;

Inspect a table’s columns

PRAGMA table_info('orders');

This returns the declared type, nullability, default value and primary-key position for each column.

Review foreign keys

PRAGMA foreign_key_list('orders');
PRAGMA foreign_key_check;

The first statement describes declared references for one table. The second reports rows that violate enabled foreign-key relationships.

Find duplicate values

SELECT email, COUNT(*) AS occurrences
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

Inspect indexes

PRAGMA index_list('orders');

After finding an index name, inspect its columns with PRAGMA index_info('index_name').

Check query behavior

EXPLAIN QUERY PLAN
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

The plan can reveal full table scans and whether SQLite can use a relevant index.

Page through large tables

SELECT *
FROM events
ORDER BY id
LIMIT 100 OFFSET 0;

For repeated navigation through very large tables, keyset pagination such as WHERE id > ? ORDER BY id LIMIT 100 is often more efficient than a very large offset.