# SQLite SQLite is a self-contained, serverless, zero-configuration SQL database engine. Unlike client-server databases ([[PostgreSQL]], Oracle Server, MariaDB, ...), SQLite runs *in-process* with the application and stores the entire database in a single file. Created by [[Richard Hipp|D. Richard Hipp]] in 2000, SQLite is the most widely deployed database engine in the world. It's embedded in every smartphone, most browsers, and countless applications. ## Key Characteristics - **Serverless**: No separate server process needed - **Zero-configuration**: No setup or administration required - **Self-contained**: Single library, minimal dependencies - **Single file**: Entire database in one cross-platform file - **ACID compliant**: Full transaction support - **Cross-platform**: Works on any OS - **Public domain**: No licensing restrictions ## When to Use SQLite **Good for:** - Mobile and desktop applications - Embedded systems and IoT devices - Local caching and temporary storage - Development and testing - Small to medium websites - Data analysis and exploration - Application file formats - Edge computing **Not ideal for:** - High-concurrency write-heavy workloads - Very large datasets (>1TB) - Client-server applications with many users - Distributed systems requiring replication ## Common Use Cases - **Mobile apps**: iOS and Android use SQLite extensively - **Browsers**: Chrome, Firefox, Safari store data in SQLite - **Desktop apps**: Local storage for Electron apps, etc. - **Caching**: Local cache for applications and CLI tools - **Configuration**: Application settings storage - **Analytics**: Quick data exploration with SQL ## Basic Usage ### Command Line ```bash # Open or create database sqlite3 mydb.db # Run SQL commands sqlite3 mydb.db "SELECT * FROM users;" # Import CSV sqlite3 mydb.db ".import data.csv tablename" # Export to CSV sqlite3 -header -csv mydb.db "SELECT * FROM users;" > users.csv ``` ### Common Commands (inside sqlite3) ```sql .tables -- List all tables .schema -- Show database schema .headers on -- Show column headers .mode column -- Columnar output .quit -- Exit ``` ### Python ```python import sqlite3 # Connect (creates file if doesn't exist) conn = sqlite3.connect('mydb.db') cursor = conn.cursor() # Execute queries cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)') cursor.execute('INSERT INTO users (name) VALUES (?)', ('Alice',)) # Fetch results cursor.execute('SELECT * FROM users') rows = cursor.fetchall() conn.commit() conn.close() ``` ### JavaScript (Node.js with better-sqlite3) ```javascript const Database = require('better-sqlite3'); const db = new Database('mydb.db'); // Synchronous API (faster than async for SQLite) const stmt = db.prepare('SELECT * FROM users WHERE id = ?'); const user = stmt.get(1); db.close(); ``` ## Data Types SQLite uses dynamic typing with five storage classes: - **NULL**: Null value - **INTEGER**: Signed integer (1-8 bytes) - **REAL**: Floating point (8 bytes) - **TEXT**: UTF-8 or UTF-16 string - **BLOB**: Binary data ## Performance Tips - Use transactions for bulk inserts - Create indexes for frequently queried columns - Use `PRAGMA journal_mode=WAL` for better concurrency - Use prepared statements to avoid SQL injection - Keep database file on fast storage (SSD) ## SQLite Extensions - **FTS5**: Full-text search - **R*Tree**: Spatial indexing - **JSON1**: JSON functions (built-in since 3.38) - **[[Litestream]]**: Streaming replication to S3 ## File Format - Extension: `.db`, `.sqlite`, `.sqlite3` - Maximum database size: 281 TB - Maximum row size: 1 GB - File is portable across platforms ## References - Official website: https://www.sqlite.org/ - Documentation: https://www.sqlite.org/docs.html - https://en.wikipedia.org/wiki/SQLite ## Related - [[Database]] - [[Database Management Systems (DBMS)]] - [[Relational Databases (RDBMS)]] - [[SQL]] - [[PostgreSQL]] - [[DuckDB]] - [[Dolt]] - [[Litestream]] - [[Write-Ahead Logging (WAL)]] - [[ACID]] - [[Richard Hipp]]