SQLite3 Command-Line Guide: Queries, Schema, and Administration

# Open (or create) a database file
sqlite3 mydb.db

# Open in-memory database (lost when you exit)
sqlite3 :memory:

# Open with a specific SQL command
sqlite3 mydb.db "SELECT * FROM users LIMIT 5;"

# Open and run a SQL file
sqlite3 mydb.db < schema.sql

# Help
sqlite> .help

# Exit
sqlite> .quit
sqlite> .exit

# Show all tables
sqlite> .tables

# Show tables matching a pattern
sqlite> .tables user%

# Show schema of all tables
sqlite> .schema

# Show schema of a specific table
sqlite> .schema users

# Show indexes
sqlite> .indexes
sqlite> .indexes users

# Show databases attached
sqlite> .databases

# Show current settings
sqlite> .show

sqlite> .tables
documents  settings   tokens

sqlite> .schema documents
CREATE TABLE documents (
  id      INTEGER PRIMARY KEY,
  title   TEXT NOT NULL,
  body    TEXT NOT NULL
);
CREATE UNIQUE INDEX title_index ON documents(title);

sqlite> .schema tokens
CREATE TABLE tokens (
  id         INTEGER PRIMARY KEY,
  token      TEXT NOT NULL,
  docs_count INT NOT NULL,
  postings   BLOB NOT NULL
);
CREATE UNIQUE INDEX token_index ON tokens(token);

# Describe a table (column info)
sqlite> PRAGMA table_info(users);

# Column mode with headers (most readable)
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT id, name, email FROM users LIMIT 5;

# Table mode (ASCII table)
sqlite> .mode table

# CSV output
sqlite> .mode csv
sqlite> .output users.csv
sqlite> SELECT * FROM users;
sqlite> .output stdout  # reset to screen

# JSON output (SQLite 3.33+)
sqlite> .mode json
sqlite> SELECT * FROM users LIMIT 3;

# Markdown table
sqlite> .mode markdown
sqlite> SELECT name, email FROM users LIMIT 5;

# Box drawing characters
sqlite> .mode box

-- Create a table
CREATE TABLE users (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    name     TEXT NOT NULL,
    email    TEXT UNIQUE NOT NULL,
    age      INTEGER,
    created  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (name, email, age) VALUES ('Alice', '[email protected]', 30);
INSERT INTO users (name, email, age) VALUES ('Bob', '[email protected]', 25);

-- Query
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 25;
SELECT * FROM users ORDER BY name ASC LIMIT 10;

-- Update
UPDATE users SET age = 31 WHERE name = 'Alice';

-- Delete
DELETE FROM users WHERE id = 2;

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE age > 25;

sqlite> .mode csv
sqlite> .import data.csv users

# With headers (skip first row)
sqlite> .import --skip 1 data.csv users

sqlite> .headers on
sqlite> .mode csv
sqlite> .output export.csv
sqlite> SELECT * FROM users;
sqlite> .output stdout

# Full database dump (SQL format)
sqlite> .dump

# Dump to file
sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

# Or from shell
sqlite3 mydb.db .dump > backup.sql

# Restore from dump
sqlite3 newdb.db < backup.sql

-- Create an index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age);

-- Composite index
CREATE INDEX idx_users_name_age ON users(name, age);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Drop index
DROP INDEX idx_users_age;

-- View query plan (EXPLAIN)
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';

-- Wrap multiple operations in a transaction
BEGIN TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (1, 99.99);
UPDATE users SET order_count = order_count + 1 WHERE id = 1;

COMMIT;

-- Rollback on error
BEGIN TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 49.99);
-- Something went wrong:
ROLLBACK;

-- Check database integrity
PRAGMA integrity_check;

-- Show foreign key enforcement status
PRAGMA foreign_keys;

-- Enable foreign keys (disabled by default!)
PRAGMA foreign_keys = ON;

-- Show page size
PRAGMA page_size;

-- Optimize database (reclaim space)
VACUUM;

-- Show table info
PRAGMA table_info(users);

-- Show index info
PRAGMA index_info(idx_users_email);

-- WAL mode (better concurrent reads)
PRAGMA journal_mode = WAL;

-- Store JSON
CREATE TABLE events (
    id   INTEGER PRIMARY KEY,
    data JSON
);

INSERT INTO events (data) VALUES ('{"type": "click", "user": 42, "page": "/home"}');

-- Query JSON fields
SELECT json_extract(data, '$.type') AS event_type,
       json_extract(data, '$.user') AS user_id
FROM events;

-- Filter by JSON field
SELECT * FROM events
WHERE json_extract(data, '$.type') = 'click';

#!/bin/bash
# Run SQL from a shell script

DB="myapp.db"

# Execute a query and capture output
count=$(sqlite3 "$DB" "SELECT COUNT(*) FROM users;")
echo "Total users: $count"

# Run multiple statements
sqlite3 "$DB" << 'EOF'
.headers on
.mode column
SELECT id, name, email FROM users ORDER BY name LIMIT 10;
EOF

# Check if a table exists
table_exists=$(sqlite3 "$DB" "SELECT name FROM sqlite_master WHERE type='table' AND name='users';")
if [ -z "$table_exists" ]; then
    echo "Table 'users' does not exist"
fi

import sqlite3

# Connect (creates file if not exists)
conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id    INTEGER PRIMARY KEY AUTOINCREMENT,
        name  TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
''')

# Insert with parameterized query (prevents SQL injection)
cursor.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Alice", "[email protected]")
)
conn.commit()

# Query
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
rows = cursor.fetchall()
for row in rows:
    print(row)

# Use as context manager
with sqlite3.connect('mydb.db') as conn:
    conn.row_factory = sqlite3.Row  # access columns by name
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    for row in cursor.fetchall():
        print(dict(row))

conn.close()