SQLite Essentials

calendarDec 12, 2024 · 3 min read · database-knowhow sqlite sql embedded  ·
Share on: linkedincopy

SQLite commands for embedded database applications.

Basic Commands

 1# Create/open database
 2sqlite3 mydb.db
 3
 4# Open in-memory database
 5sqlite3 :memory:
 6
 7# Execute SQL file
 8sqlite3 mydb.db < schema.sql
 9
10# Dump database
11sqlite3 mydb.db .dump > backup.sql
12
13# Export to CSV
14sqlite3 -header -csv mydb.db "SELECT * FROM users;" > users.csv

In SQLite Shell

 1-- Show tables
 2.tables
 3
 4-- Show schema
 5.schema
 6.schema users
 7
 8-- Show indexes
 9.indexes
10.indexes users
11
12-- Change output mode
13.mode column
14.mode csv
15.mode json
16.mode markdown
17
18-- Show headers
19.headers on
20
21-- Import CSV
22.mode csv
23.import users.csv users
24
25-- Export to CSV
26.headers on
27.mode csv
28.output users.csv
29SELECT * FROM users;
30.output stdout
31
32-- Timing
33.timer on
34
35-- Help
36.help
37
38-- Quit
39.quit

Docker Setup

 1version: '3.8'
 2
 3services:
 4  app:
 5    image: python:3.11-slim
 6    container_name: sqlite-app
 7    volumes:
 8      - ./app:/app
 9      - sqlite-data:/data
10    working_dir: /app
11    command: python app.py
12    environment:
13      DATABASE_PATH: /data/mydb.db
14
15volumes:
16  sqlite-data:

Python Integration

 1import sqlite3
 2from contextlib import contextmanager
 3
 4@contextmanager
 5def get_db_connection(db_path='mydb.db'):
 6    """Context manager for database connection"""
 7    conn = sqlite3.connect(db_path)
 8    conn.row_factory = sqlite3.Row  # Access columns by name
 9    try:
10        yield conn
11        conn.commit()
12    except Exception:
13        conn.rollback()
14        raise
15    finally:
16        conn.close()
17
18# Usage
19with get_db_connection() as conn:
20    cursor = conn.cursor()
21    cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
22    user = cursor.fetchone()
23    print(dict(user))
24
25# Create table
26with get_db_connection() as conn:
27    conn.execute("""
28        CREATE TABLE IF NOT EXISTS users (
29            id INTEGER PRIMARY KEY AUTOINCREMENT,
30            username TEXT UNIQUE NOT NULL,
31            email TEXT UNIQUE NOT NULL,
32            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
33        )
34    """)
35
36# Insert
37with get_db_connection() as conn:
38    conn.execute(
39        "INSERT INTO users (username, email) VALUES (?, ?)",
40        ('john', 'john@example.com')
41    )
42
43# Query
44with get_db_connection() as conn:
45    cursor = conn.execute("SELECT * FROM users")
46    for row in cursor:
47        print(dict(row))
48
49# Transaction
50with get_db_connection() as conn:
51    conn.execute("BEGIN")
52    try:
53        conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
54        conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
55        conn.execute("COMMIT")
56    except:
57        conn.execute("ROLLBACK")
58        raise

SQLite-Specific Features

AUTOINCREMENT

1CREATE TABLE users (
2    id INTEGER PRIMARY KEY AUTOINCREMENT,
3    username TEXT NOT NULL
4);

JSON Support (SQLite 3.38+)

 1-- Create table with JSON
 2CREATE TABLE products (
 3    id INTEGER PRIMARY KEY,
 4    name TEXT,
 5    attributes JSON
 6);
 7
 8-- Insert JSON
 9INSERT INTO products (name, attributes) VALUES
10    ('Laptop', '{"brand": "Dell", "ram": "16GB"}');
11
12-- Query JSON
13SELECT json_extract(attributes, '$.brand') FROM products;
14SELECT * FROM products WHERE json_extract(attributes, '$.ram') = '16GB';
15
16-- JSON functions
17SELECT json_type(attributes) FROM products;
18SELECT json_array_length(json_extract(attributes, '$.tags')) FROM products;

Full-Text Search (FTS5)

 1-- Create FTS table
 2CREATE VIRTUAL TABLE articles_fts USING fts5(title, content);
 3
 4-- Insert
 5INSERT INTO articles_fts (title, content) VALUES
 6    ('SQLite Tutorial', 'Learn SQLite database...');
 7
 8-- Search
 9SELECT * FROM articles_fts WHERE articles_fts MATCH 'sqlite database';
10
11-- Rank results
12SELECT *, rank FROM articles_fts 
13WHERE articles_fts MATCH 'sqlite'
14ORDER BY rank;

Performance

 1-- Analyze
 2ANALYZE;
 3
 4-- Vacuum (reclaim space)
 5VACUUM;
 6
 7-- Enable WAL mode (better concurrency)
 8PRAGMA journal_mode=WAL;
 9
10-- Increase cache size
11PRAGMA cache_size=10000;
12
13-- Synchronous mode
14PRAGMA synchronous=NORMAL;
15
16-- Show query plan
17EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';

Related Snippets