SQLite Cheat Sheet

Comprehensive reference for SQLite embedded database - covering embedded database operations, queries, CLI commands, and programming language integration for production applications.

Installation & Setup

Installation

# Ubuntu/Debian
sudo apt update && sudo apt install sqlite3

# CentOS/RHEL
sudo yum install sqlite

# macOS (Homebrew)
brew install sqlite

# Windows
# Download from https://sqlite.org/download.html

# Check version
sqlite3 --version

Creating and Opening Databases

# Create/open database file
sqlite3 mydatabase.db

# Open read-only
sqlite3 -readonly mydatabase.db

# Create in-memory database
sqlite3 :memory:

# Execute SQL from file
sqlite3 mydatabase.db < script.sql

# Execute single command
sqlite3 mydatabase.db "SELECT * FROM users;"

Command Line Interface

SQLite Shell Commands

-- Help
.help

-- Database info
.databases
.tables
.schema
.schema table_name

-- Output formatting
.mode csv         -- CSV format
.mode column      -- Column format
.mode html        -- HTML format
.mode json        -- JSON format
.mode list        -- List format
.mode tabs        -- Tab-separated

-- Headers
.headers on
.headers off

-- Import/Export
.output filename.csv
SELECT * FROM users;
.output stdout

.import filename.csv table_name

-- Backup and restore
.backup backup.db
.restore backup.db

-- Execute shell commands
.system ls -la

-- Exit
.quit
.exit

Useful CLI Options

# Execute command and exit
sqlite3 mydb.db "SELECT COUNT(*) FROM users;"

# Pipe commands
echo "SELECT * FROM users LIMIT 5;" | sqlite3 mydb.db

# Read SQL from file
sqlite3 mydb.db < queries.sql

# CSV output
sqlite3 -header -csv mydb.db "SELECT * FROM users;" > users.csv

# JSON output
sqlite3 -json mydb.db "SELECT * FROM users LIMIT 3;"

# Table format with headers
sqlite3 -column -header mydb.db "SELECT * FROM users LIMIT 5;"

Database Operations

Creating Database

-- Database is created automatically when first opened
-- or when first table is created

-- Check database list
.databases

-- Attach additional databases
ATTACH DATABASE 'other.db' AS other;
DETACH DATABASE other;

-- Database information
PRAGMA database_list;

Table Operations

Creating Tables

-- Basic table creation
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table with constraints
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product_name TEXT NOT NULL,
    quantity INTEGER DEFAULT 1,
    price REAL CHECK(price > 0),
    order_date DATE DEFAULT (DATE('now')),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Temporary table
CREATE TEMPORARY TABLE temp_data (
    id INTEGER,
    value TEXT
);

-- Table with composite primary key
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

Altering Tables

-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename table
ALTER TABLE users RENAME TO customers;

-- Rename column (SQLite 3.25.0+)
ALTER TABLE users RENAME COLUMN username TO user_name;

-- Drop column (SQLite 3.35.0+)
ALTER TABLE users DROP COLUMN phone;

-- For older versions, recreate table:
BEGIN TRANSACTION;
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);
INSERT INTO users_new SELECT id, username, email FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;

Table Information

-- Show table structure
.schema users
PRAGMA table_info(users);

-- Show all tables
.tables
SELECT name FROM sqlite_master WHERE type='table';

-- Show indexes for table
.indices users
PRAGMA index_list(users);

-- Show foreign keys
PRAGMA foreign_key_list(users);

Data Types

SQLite Data Types

Storage Class	Description	Examples
`NULL`	NULL value	NULL
`INTEGER`	Signed integer	1, -500, 2147483647
`REAL`	Floating point	1.0, 3.14159, -2.5e10
`TEXT`	Text string	'Hello', "World"
`BLOB`	Binary data	X'0123456789ABCDEF'

Type Affinity

-- Integer affinity
CREATE TABLE numbers (
    int_col INTEGER,
    num_col NUMERIC,
    dec_col DECIMAL(10,2)
);

-- Text affinity
CREATE TABLE strings (
    text_col TEXT,
    char_col CHARACTER(20),
    varchar_col VARCHAR(255),
    clob_col CLOB
);

-- Real affinity
CREATE TABLE floats (
    real_col REAL,
    double_col DOUBLE,
    float_col FLOAT
);

-- Blob affinity
CREATE TABLE binary_data (
    blob_col BLOB
);

-- No affinity (any type)
CREATE TABLE mixed (
    value_col  -- No type specified
);

Queries & Functions

Basic Queries

-- Select with conditions
SELECT * FROM users WHERE age >= 18;
SELECT username, email FROM users WHERE username LIKE 'john%';
SELECT * FROM orders WHERE price BETWEEN 10.0 AND 100.0;

-- Sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age ASC, username DESC;

-- Grouping and aggregation
SELECT age, COUNT(*) as count FROM users GROUP BY age;
SELECT AVG(price) as avg_price FROM orders;
SELECT user_id, SUM(price) as total_spent 
FROM orders 
GROUP BY user_id 
HAVING total_spent > 100;

-- Joins
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

String Functions

-- String manipulation
SELECT LENGTH('Hello World');                    -- 11
SELECT UPPER('hello');                          -- HELLO
SELECT LOWER('WORLD');                          -- world
SELECT SUBSTR('Hello World', 7, 5);            -- World
SELECT REPLACE('Hello World', 'World', 'SQLite'); -- Hello SQLite
SELECT TRIM('  Hello  ');                      -- Hello
SELECT LTRIM('  Hello');                       -- Hello (right spaces remain)
SELECT RTRIM('Hello  ');                       -- Hello (left spaces remain)

-- Pattern matching
SELECT * FROM users WHERE username GLOB 'john*';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username REGEXP '^[A-Za-z]+$';

-- String functions in queries
SELECT username, LENGTH(username) as name_length 
FROM users 
WHERE LENGTH(username) > 5;

Numeric Functions

-- Math functions
SELECT ABS(-15);                    -- 15
SELECT ROUND(3.14159, 2);           -- 3.14
SELECT RANDOM();                    -- Random integer
SELECT ABS(RANDOM() % 100);         -- Random 0-99

-- Aggregate functions
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT age) FROM users;
SELECT MIN(age), MAX(age), AVG(age) FROM users;
SELECT SUM(price) FROM orders;

-- Mathematical operations
SELECT price * quantity as total FROM order_items;
SELECT price * 1.08 as price_with_tax FROM products;

Date/Time Functions

-- Current date/time
SELECT DATE('now');                 -- Current date
SELECT TIME('now');                 -- Current time  
SELECT DATETIME('now');             -- Current datetime
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now'); -- Formatted datetime

-- Date arithmetic
SELECT DATE('now', '+1 day');       -- Tomorrow
SELECT DATE('now', '-7 days');      -- Week ago
SELECT DATE('now', '+1 month');     -- Next month
SELECT DATE('now', '+1 year');      -- Next year

-- Date formatting
SELECT STRFTIME('%Y', '2023-05-15');         -- 2023
SELECT STRFTIME('%m', '2023-05-15');         -- 05
SELECT STRFTIME('%d', '2023-05-15');         -- 15
SELECT STRFTIME('%w', '2023-05-15');         -- 1 (day of week, 0=Sunday)
SELECT STRFTIME('%j', '2023-05-15');         -- 135 (day of year)

-- Age calculation
SELECT username, 
       (JULIANDAY('now') - JULIANDAY(birth_date)) / 365.25 as age_years
FROM users;

Conditional Functions

-- CASE statements
SELECT username,
       CASE 
           WHEN age < 18 THEN 'Minor'
           WHEN age >= 18 AND age < 65 THEN 'Adult'
           ELSE 'Senior'
       END as age_group
FROM users;

-- IIF function (SQLite 3.32.0+)
SELECT username, IIF(age >= 18, 'Adult', 'Minor') as status FROM users;

-- NULLIF and COALESCE
SELECT COALESCE(phone, email, 'No contact') as contact FROM users;
SELECT NULLIF(division_result, 0) FROM calculations;

Indexes & Performance

Creating Indexes

-- Single column index
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Drop index
DROP INDEX idx_users_username;

Query Optimization

-- Analyze query performance
EXPLAIN QUERY PLAN SELECT * FROM users WHERE username = 'john';

-- Update statistics
ANALYZE;
ANALYZE users;

-- Check index usage
EXPLAIN QUERY PLAN 
SELECT u.username, COUNT(o.id) 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.username;

-- Optimization tips
-- 1. Create indexes on WHERE clause columns
-- 2. Create indexes on JOIN columns  
-- 3. Use LIMIT for large result sets
-- 4. Avoid SELECT * when possible
-- 5. Use appropriate data types

Transactions

Transaction Control

-- Basic transaction
BEGIN TRANSACTION;
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
INSERT INTO orders (user_id, product_name, price) VALUES (1, 'Laptop', 999.99);
COMMIT;

-- Rollback transaction
BEGIN TRANSACTION;
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- Something went wrong
ROLLBACK;

-- Savepoints
BEGIN TRANSACTION;
INSERT INTO users (username, email) VALUES ('jane', 'jane@example.com');
SAVEPOINT sp1;
UPDATE users SET email = 'jane.doe@example.com' WHERE username = 'jane';
ROLLBACK TO SAVEPOINT sp1;  -- Rollback to savepoint
COMMIT;

-- Transaction modes
BEGIN DEFERRED TRANSACTION;   -- Default, lock when first read/write
BEGIN IMMEDIATE TRANSACTION;  -- Reserved lock immediately
BEGIN EXCLUSIVE TRANSACTION;  -- Exclusive lock immediately

Locking and Concurrency

-- Check lock status
PRAGMA locking_mode;

-- Set WAL mode for better concurrency
PRAGMA journal_mode = WAL;

-- Set synchronous mode
PRAGMA synchronous = NORMAL;  -- FULL, NORMAL, OFF

-- Connection timeout for locks
PRAGMA busy_timeout = 30000;  -- 30 seconds

Pragma Statements

Database Configuration

-- Journal mode
PRAGMA journal_mode;
PRAGMA journal_mode = WAL;    -- Write-Ahead Logging
PRAGMA journal_mode = DELETE; -- Default rollback journal

-- Synchronous mode
PRAGMA synchronous;
PRAGMA synchronous = FULL;    -- Maximum safety
PRAGMA synchronous = NORMAL;  -- Good balance
PRAGMA synchronous = OFF;     -- Fastest, risk of corruption

-- Foreign keys
PRAGMA foreign_keys;
PRAGMA foreign_keys = ON;

-- Case sensitivity for LIKE
PRAGMA case_sensitive_like = ON;

-- Auto vacuum
PRAGMA auto_vacuum = FULL;    -- Automatic database shrinking
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA auto_vacuum = NONE;    -- Default

Database Information

-- Database info
PRAGMA database_list;
PRAGMA table_info(table_name);
PRAGMA index_list(table_name);
PRAGMA index_info(index_name);
PRAGMA foreign_key_list(table_name);

-- Statistics
PRAGMA page_count;
PRAGMA page_size;
PRAGMA freelist_count;
PRAGMA cache_size;

-- Integrity check
PRAGMA integrity_check;
PRAGMA quick_check;

-- Database size optimization
PRAGMA incremental_vacuum(100);  -- Free 100 pages
VACUUM;                          -- Full vacuum

Full-Text Search

FTS Virtual Tables

-- Create FTS table
CREATE VIRTUAL TABLE documents USING fts5(title, content);

-- Insert documents
INSERT INTO documents VALUES 
('SQLite Tutorial', 'Learn SQLite database basics'),
('Database Design', 'Principles of good database design'),
('SQL Queries', 'Advanced SQL query techniques');

-- Full-text search
SELECT * FROM documents WHERE documents MATCH 'sqlite';
SELECT * FROM documents WHERE documents MATCH 'database design';
SELECT * FROM documents WHERE documents MATCH 'title:sqlite';

-- Phrase search
SELECT * FROM documents WHERE documents MATCH '"database basics"';

-- Boolean search
SELECT * FROM documents WHERE documents MATCH 'sqlite AND database';
SELECT * FROM documents WHERE documents MATCH 'sqlite OR mysql';
SELECT * FROM documents WHERE documents MATCH 'database NOT design';

-- Ranking
SELECT *, rank FROM documents 
WHERE documents MATCH 'database' 
ORDER BY rank;

-- Highlighting
SELECT highlight(documents, 0, '<b>', '</b>') as highlighted_title,
       highlight(documents, 1, '<em>', '</em>') as highlighted_content
FROM documents 
WHERE documents MATCH 'database';

FTS Configuration

-- FTS table with options
CREATE VIRTUAL TABLE documents USING fts5(
    title, 
    content, 
    tokenize = 'porter ascii',  -- Porter stemming + ASCII folding
    content_rowid = id          -- Use external rowid
);

-- External content FTS
CREATE VIRTUAL TABLE docs_fts USING fts5(
    title, content, 
    content='docs',           -- External table
    content_rowid='id'        -- Rowid column
);

JSON Support

JSON Functions (SQLite 3.45.0+)

-- Create table with JSON column
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    attributes JSON
);

-- Insert JSON data
INSERT INTO products VALUES 
(1, 'Laptop', '{"brand": "Dell", "ram": 16, "ssd": true, "ports": ["USB", "HDMI", "Ethernet"]}'),
(2, 'Phone', '{"brand": "Apple", "storage": 256, "color": "black", "features": {"camera": "12MP", "battery": "3000mAh"}}');

-- JSON extraction
SELECT name, JSON_EXTRACT(attributes, '$.brand') as brand FROM products;
SELECT name, attributes->>'$.brand' as brand FROM products;  -- Shorthand
SELECT name, attributes->'$.ports' as ports FROM products;

-- JSON path queries  
SELECT * FROM products WHERE JSON_EXTRACT(attributes, '$.ssd') = 1;
SELECT * FROM products WHERE attributes->>'$.brand' = 'Apple';

-- JSON array operations
SELECT name, JSON_ARRAY_LENGTH(attributes, '$.ports') as port_count FROM products;

-- JSON modification
UPDATE products 
SET attributes = JSON_SET(attributes, '$.warranty', '2 years') 
WHERE id = 1;

UPDATE products 
SET attributes = JSON_INSERT(attributes, '$.rating', 4.5) 
WHERE id = 2;

UPDATE products 
SET attributes = JSON_REMOVE(attributes, '$.color') 
WHERE id = 2;

Python Integration

Basic Usage

import sqlite3
from datetime import datetime

# Connect to database
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

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

# Insert data
cursor.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ('john_doe', 'john@example.com')
)

# Insert multiple records
users_data = [
    ('jane_doe', 'jane@example.com'),
    ('bob_smith', 'bob@example.com'),
    ('alice_jones', 'alice@example.com')
]
cursor.executemany(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    users_data
)

# Commit changes
conn.commit()

# Query data
cursor.execute("SELECT * FROM users WHERE username LIKE ?", ('john%',))
rows = cursor.fetchall()
for row in rows:
    print(row)

# Query with named parameters
cursor.execute(
    "SELECT * FROM users WHERE username = :username",
    {'username': 'john_doe'}
)
user = cursor.fetchone()
print(user)

# Close connection
cursor.close()
conn.close()

Advanced Python Integration

import sqlite3
from contextlib import contextmanager

# Connection manager
@contextmanager
def get_db_connection(db_path):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row  # Access columns by name
    try:
        yield conn
    finally:
        conn.close()

# User management class
class UserManager:
    def __init__(self, db_path):
        self.db_path = db_path
        self._init_db()
    
    def _init_db(self):
        with get_db_connection(self.db_path) as conn:
            conn.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT NOT NULL UNIQUE,
                    email TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            conn.commit()
    
    def create_user(self, username, email):
        with get_db_connection(self.db_path) as conn:
            try:
                cursor = conn.execute(
                    "INSERT INTO users (username, email) VALUES (?, ?)",
                    (username, email)
                )
                conn.commit()
                return cursor.lastrowid
            except sqlite3.IntegrityError:
                raise ValueError("Username already exists")
    
    def get_user(self, user_id):
        with get_db_connection(self.db_path) as conn:
            cursor = conn.execute(
                "SELECT * FROM users WHERE id = ?",
                (user_id,)
            )
            return cursor.fetchone()
    
    def get_all_users(self):
        with get_db_connection(self.db_path) as conn:
            cursor = conn.execute("SELECT * FROM users ORDER BY created_at")
            return cursor.fetchall()
    
    def update_user(self, user_id, username=None, email=None):
        fields = []
        values = []
        
        if username is not None:
            fields.append("username = ?")
            values.append(username)
        
        if email is not None:
            fields.append("email = ?")
            values.append(email)
        
        if not fields:
            return
        
        values.append(user_id)
        query = f"UPDATE users SET {', '.join(fields)} WHERE id = ?"
        
        with get_db_connection(self.db_path) as conn:
            conn.execute(query, values)
            conn.commit()
    
    def delete_user(self, user_id):
        with get_db_connection(self.db_path) as conn:
            conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
            conn.commit()

# Usage
user_mgr = UserManager('users.db')

# Create users
user_id = user_mgr.create_user('john_doe', 'john@example.com')
print(f"Created user with ID: {user_id}")

# Get user
user = user_mgr.get_user(user_id)
print(f"User: {user['username']} - {user['email']}")

# Update user
user_mgr.update_user(user_id, email='john.doe@example.com')

# Get all users
users = user_mgr.get_all_users()
for user in users:
    print(f"{user['id']}: {user['username']} ({user['email']})")

Transaction Management

import sqlite3

def transfer_funds(db_path, from_account, to_account, amount):
    conn = sqlite3.connect(db_path)
    try:
        conn.execute("BEGIN TRANSACTION")
        
        # Check source account balance
        cursor = conn.execute(
            "SELECT balance FROM accounts WHERE id = ?",
            (from_account,)
        )
        balance = cursor.fetchone()[0]
        
        if balance < amount:
            raise ValueError("Insufficient funds")
        
        # Debit source account
        conn.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            (amount, from_account)
        )
        
        # Credit destination account
        conn.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            (amount, to_account)
        )
        
        # Log transaction
        conn.execute(
            "INSERT INTO transactions (from_account, to_account, amount, timestamp) VALUES (?, ?, ?, ?)",
            (from_account, to_account, amount, datetime.now())
        )
        
        conn.execute("COMMIT")
        print(f"Transfer completed: ${amount} from {from_account} to {to_account}")
        
    except Exception as e:
        conn.execute("ROLLBACK")
        print(f"Transfer failed: {e}")
        raise
    finally:
        conn.close()

Other Language Integration

Node.js Integration

const Database = require('better-sqlite3');

// Open database
const db = new Database('mydatabase.db');

// Create table
db.exec(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        email TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
`);

// Prepared statements
const insertUser = db.prepare('INSERT INTO users (username, email) VALUES (?, ?)');
const selectUser = db.prepare('SELECT * FROM users WHERE id = ?');
const selectAllUsers = db.prepare('SELECT * FROM users ORDER BY created_at');

// Insert user
const result = insertUser.run('john_doe', 'john@example.com');
console.log(`User created with ID: ${result.lastInsertRowid}`);

// Get user
const user = selectUser.get(result.lastInsertRowid);
console.log(user);

// Get all users
const users = selectAllUsers.all();
console.log(users);

// Transaction
const transfer = db.transaction((fromId, toId, amount) => {
    const debit = db.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?');
    const credit = db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');
    
    debit.run(amount, fromId);
    credit.run(amount, toId);
});

// Close database
db.close();

Java Integration (JDBC)

import java.sql.*;

public class SQLiteExample {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:mydatabase.db";
        
        try (Connection conn = DriverManager.getConnection(url)) {
            // Create table
            String createTable = """
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT NOT NULL UNIQUE,
                    email TEXT NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            """;
            
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTable);
            }
            
            // Insert user
            String insertSQL = "INSERT INTO users (username, email) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "john_doe");
                pstmt.setString(2, "john@example.com");
                pstmt.executeUpdate();
            }
            
            // Query users
            String selectSQL = "SELECT * FROM users";
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(selectSQL)) {
                
                while (rs.next()) {
                    System.out.println(rs.getInt("id") + " - " + 
                                     rs.getString("username") + " - " + 
                                     rs.getString("email"));
                }
            }
            
        } catch (SQLException e) {
            System.out.println("SQLite error: " + e.getMessage());
        }
    }
}

Go Integration

package main

import (
    "database/sql"
    "fmt"
    "log"
    
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    // Open database
    db, err := sql.Open("sqlite3", "mydatabase.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // Create table
    createTable := `
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `
    
    _, err = db.Exec(createTable)
    if err != nil {
        log.Fatal(err)
    }
    
    // Insert user
    insertSQL := "INSERT INTO users (username, email) VALUES (?, ?)"
    result, err := db.Exec(insertSQL, "john_doe", "john@example.com")
    if err != nil {
        log.Fatal(err)
    }
    
    userID, _ := result.LastInsertId()
    fmt.Printf("User created with ID: %d\n", userID)
    
    // Query users
    rows, err := db.Query("SELECT id, username, email FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    for rows.Next() {
        var id int
        var username, email string
        err = rows.Scan(&id, &username, &email)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%d - %s - %s\n", id, username, email)
    }
}

Performance Optimization

General Optimization Tips

-- 1. Use indexes on frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(order_date);

-- 2. Use prepared statements to avoid SQL parsing overhead
-- (Done automatically in most programming language drivers)

-- 3. Use transactions for bulk operations
BEGIN TRANSACTION;
-- Multiple INSERT/UPDATE statements
COMMIT;

-- 4. Analyze query performance
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'john@example.com';

-- 5. Update statistics
ANALYZE;

-- 6. Use appropriate PRAGMA settings
PRAGMA journal_mode = WAL;      -- Better concurrency
PRAGMA synchronous = NORMAL;    -- Good balance of safety/speed
PRAGMA cache_size = 10000;      -- Increase cache size
PRAGMA temp_store = MEMORY;     -- Use memory for temp tables

Configuration Optimization

-- Memory settings
PRAGMA cache_size = -64000;     -- 64MB cache (negative = KB)
PRAGMA temp_store = MEMORY;     -- Temp tables in memory
PRAGMA mmap_size = 268435456;   -- Memory-mapped I/O (256MB)

-- I/O optimization
PRAGMA journal_mode = WAL;      -- Write-Ahead Logging
PRAGMA wal_autocheckpoint = 1000; -- Checkpoint every 1000 pages
PRAGMA synchronous = NORMAL;    -- Sync mode

-- Query optimization
PRAGMA optimize;                -- Run periodic optimization

Bulk Insert Optimization

import sqlite3

def bulk_insert_optimized(db_path, data):
    conn = sqlite3.connect(db_path)
    
    # Optimize for bulk operations
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")
    conn.execute("PRAGMA cache_size = 1000000")  # 1GB cache
    conn.execute("PRAGMA locking_mode = EXCLUSIVE")
    conn.execute("PRAGMA temp_store = MEMORY")
    
    try:
        conn.execute("BEGIN TRANSACTION")
        
        # Use executemany for bulk insert
        conn.executemany(
            "INSERT INTO users (username, email) VALUES (?, ?)",
            data
        )
        
        conn.execute("COMMIT")
        print(f"Inserted {len(data)} records")
        
    except Exception as e:
        conn.execute("ROLLBACK")
        print(f"Bulk insert failed: {e}")
        raise
    finally:
        conn.close()

# Usage
data = [('user1', 'user1@example.com'), ('user2', 'user2@example.com')]
bulk_insert_optimized('mydatabase.db', data)

Performance Tuning Tips

Use indexes: Create indexes on columns used in WHERE, ORDER BY, and JOIN clauses
Use transactions: Wrap multiple operations in transactions for better performance
Optimize PRAGMA settings: Use WAL mode, adjust cache size and synchronous mode
Use prepared statements: Avoid SQL parsing overhead
Analyze queries: Use EXPLAIN QUERY PLAN to understand query execution
Normalize appropriately: Balance between normalization and query performance
Use appropriate data types: Choose the smallest data type that fits your needs
Batch operations: Use bulk insert/update operations when possible
Regular maintenance: Run VACUUM and ANALYZE periodically
Consider memory databases: Use :memory: for temporary high-performance operations

Resources

Comprehensive SQLite reference for embedded database operations. Contributions welcome!

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
.gitattributes		.gitattributes
.gitignore		.gitignore
LICENSE.md		LICENSE.md
README.md		README.md

License

cheatsheetz/sqlite

Folders and files

Latest commit

History

Repository files navigation

SQLite Cheat Sheet

Table of Contents

Installation & Setup

Installation

Creating and Opening Databases

Command Line Interface

SQLite Shell Commands

Useful CLI Options

Database Operations

Creating Database

Table Operations

Creating Tables

Altering Tables

Table Information

Data Types

SQLite Data Types

Type Affinity

Queries & Functions

Basic Queries

String Functions

Numeric Functions

Date/Time Functions

Conditional Functions

Indexes & Performance

Creating Indexes

Query Optimization

Transactions

Transaction Control

Locking and Concurrency

Pragma Statements

Database Configuration

Database Information

Full-Text Search

FTS Virtual Tables

FTS Configuration

JSON Support

JSON Functions (SQLite 3.45.0+)

Python Integration

Basic Usage

Advanced Python Integration

Transaction Management

Other Language Integration

Node.js Integration

Java Integration (JDBC)

Go Integration

Performance Optimization

General Optimization Tips

Configuration Optimization

Bulk Insert Optimization

Performance Tuning Tips

Resources

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Packages